小攻城師的戰場筆記

筆記一下最近遇到的 MySQL 資料庫移轉至 Sql Server 問題。

我們的環境：

• PHP
• Linux (RedHat) + Windows

1. 加解密函式轉換

首先先用 SQL server 的 EncryptByPassPhrase() 替換掉 MySQL 的 AES_DECRYPT()。

（此二者安全性未必可完全替換，MSSQL 的 EncryptByPassPhrase() 是使用 Triple DES 加上 128 位元長度的金鑰，請先評估需要的加密強度）

不過，我們的線上環境是混合不同主機作為 load balance，我發現一樣的文字在 Linux 和 Windows 上，做了 EncryptByPassPhrase() 轉成 varbinary 後，再反解回來會不一樣，後來發現加密時：

• Ｗindows 的文字會自動轉 nvarchar，再進行加密
• Linux 的文字會被視為 varchar，所以特殊字元會遺失

（資料庫都是設定成 UTF-8 的前提下）

因為我們的 Linux 使用 freeTDS (dblib) 連接 SQL server、Windows 主機則是用微軟自己的 sqlsrv，在 "pdo dblib stored procedure insert wrong characters" 這篇有提到，只有微軟自己的 sqlsrv 才能支援 UTF-8, "Your driver (freeTDS) does not support UTF-8 for MSSQL. The only driver that natively supports UTF-8 for MSSQL is SQLDRV driver available only for windows platform."

2. 日期資料轉入

在 MySQL 匯出的資料，完整的日期資料 (datetime) 會帶到百萬分之一秒；在 MSSQL，預設的 datetime 只到千分之一秒，需要先捨去多餘位數（如果用不到的話），或改開 datetime2 格式。

3. 分頁的 SQL 寫法調整，limit → offset

要實作分頁功能時，MySQL 原本的寫法是「limit  [起始值], [筆數]」：

select * from test_table where a_col <> 'Y' order by b_col desc limit 0, 15

MSSQL 的寫法，變成「offset  [起始值] rows fetch next [筆數] rows only」：

select * from test_table where a_col <> 'Y' order by b_col desc offset 0 rows fetch next 15 rows only

不過，在撰寫參數化查詢 (prepared statement) 時，如果沒有指定參數型別，在取分頁結果時會出現錯誤訊息：

<?php
  $sth->execute(array(
    "CURPAGE" => $current_page,
   "PERPAGE" => $rows_per_page
));
?>

印出的 statement errorInfo 會出現以下資訊：

[0] => 42000
[1] => 1060
[2] => [Microsoft][ODBC Driver 17 for SQL Server][SQL Server]TOP 或 FETCH 子句的資料列數目必須為整數。
[3] => 42000
[4] => 8180
[5] => [Microsoft][ODBC Driver 17 for SQL Server][SQL Server]陳述式無法準備。

改成指定頁碼資訊傳入的型別為整數 (PDO::PARAM_INT)，這樣寫就好了：

<?php
    $sth->bindValue(':INX', (int) $current_page, PDO::PARAM_INT);
    $sth->bindValue(':PERPAGE', (int) $rows_per_page, PDO::PARAM_INT);
    $sth->execute();
?>

4. 函式改變：

DATE_ADD(間隔單位, 間隔量, 時間)
DATEADD(month, -1, GETDATE()

5. 取得資料筆數

在 PHP 的 PDO 資料庫連線裡，以前在 MySQL 時的寫法，可以用 rowCount() 取得資料筆數：

<?php
$sql = "select * from test";
$sth = $dbh->prepare($sql);
$sth->execute();
$num_results = $sth->rowCount();
?>

但是在 MSSQL 用一樣的寫法，只能取到資料內容，無法拿到筆數。

後來看到 "PDO rowCount() works on MySQL but not in SQL Server 2008 R2" 提到，要在 PDO 執行時加上 array(PDO::ATTR_CURSOR => PDO::CURSOR_SCROLL)：

<?php
$sql = "select * from test";
$sth = $dbh->prepare($sql, array(PDO::ATTR_CURSOR => PDO::CURSOR_SCROLL));
$sth->execute();
$num_results = $sth->rowCount();
?>

這樣就可以透過 rowCount() 取回筆數了。

微軟官網的程式設計手冊也有相關說明："PDO_SQLSRV 驅動程式參考 > PDOStatement 類別 > PDOStatement::rowCount"。

不過，加上  array(PDO::ATTR_CURSOR => PDO::CURSOR_SCROLL)，如果資料表沒有建索引，fetch 時會變得奇慢無比，後來同事說要在綁 PDO 的 prepare 時增加參數。原本為了抓 RowCount() 的寫法是：

<?php $sth = $dbh->prepare($sql, array(
PDO::ATTR_CURSOR => PDO::CURSOR_SCROLL
)); ?>

再加一個參數 PDO::SQLSRV_ATTR_CURSOR_SCROLL_TYPE，改成：

<?php $sth = $dbh->prepare($sql, array(
PDO::ATTR_CURSOR => PDO::CURSOR_SCROLL,
PDO::SQLSRV_ATTR_CURSOR_SCROLL_TYPE => PDO::SQLSRV_CURSOR_BUFFERED
)); ?>

但加上 SQLSRV_CURSOR_BUFFERED 後會吃記憶體，連線數變多時，系統負載會增加。詳細可參考：《資料指標類型 (PDO_SQLSRV 驅動程式)》。

目前想到最好的方法是資料筆數另外取，不要用到 PDO::ATTR_CURSOR => PDO::CURSOR_SCROLL。

6. SQL server 型別嚴格；MySQL 有自動轉型行為

在 SQL Sever Management Studio 執行以下語法是可以查到東西的：

select * from test where test_no = '123456'

但是如果在 PHP 裡用 PDO::PARAM_STR 代入 where 條件的參數，始終會回覆 0 筆資料。

後來發現是因為在以前，test_no 的欄位型別開成 varbinary，在 MySQL 會自動轉型為一般的字串，但是在 SQL server 型別嚴格，如果想繼續維持欄位型別為 varbinary，就需要用 cast 轉型：

select * from test where cast(test_no as varchar) = ?

7. MySQL 的 date_format，SQL server 需要使用 convert 搭配參數轉換

完整的轉換表可以參考："CAST 和 CONVERT (Transact-SQL) - 日期和時間樣式"、"Date and Time Conversions Using SQL Server"。

8. MySQL 的欄位說明寫在 comment，SQL Server 需要另建資料庫描述物件

在 MySQL，設定欄位說明的描述可以直接用 comment 寫出來：

CREATE TABLE `test_table`
(
`this_is_col` int(11) NOT NULL AUTO_INCREMENT COMMENT '這裡寫欄位描述',
  PRIMARY KEY (`id`)
) ENGINE InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='資料表說明描述';

在 SQL server 要利用 sp_addextendedproperty（新增屬性）或 sp_updateextendedproperty（更新屬性）來設定：

CREATE TABLE test_table (
[this_is_col] bigint NOT NULL IDENTITY(1,1) PRIMARY KEY
)
GO

-- 資料表註解
EXEC sys.sp_addextendedproperty
    @name=N'註解', @value=N'這裡是資料表的描述' ,
    @level0type=N'SCHEMA', @level0name=N'dbo',
    @level1type=N'TABLE', @level1name=N'test_table'
GO

-- 資料欄位註解
EXEC sys.sp_addextendedproperty
    @name=N'MS_Description', @value=N'這裡是資料欄位的描述',
    @level0type=N'SCHEMA', @level0name=N'dbo',
    @level1type=N'TABLE', @level1name=N'test_table',
    @level2type=N'COLUMN',@level2name=N'this_is_col'
GO

可以注意到，如果只想寫資料表本身的註解資訊，只要撰寫到 level1 就可以了；如果要寫到欄位註解，就要寫到 level2 (level2type & level2name)。

@name 可以是各種值，也可以拿來區隔註記系統負責人或其他資訊。

9. 流水編號欄位，SQL Server 無法直接指定 INSERT 指定的編號

在 MySQL 可以對流水號欄位 (IDENTITY) 塞進指定編號的資料值，但在 SQL server 要做兩件事。

• 第一件事，是要有權限設定為 IDENTITY_INSERT ON，否則會出現錯誤訊息「出現當 IDENTITY_INSERT 設為 OFF 時，無法將外顯值插入資料表 'TableName' 的識別欄位中」：
  SET IDENTITY_INSERT TableName On;
   
• 第二件事，是在塞資料時，要指定欄位，不可以省略欄位名稱：
  insert into TableName values (1,2,3);
  
  若省略欄位名稱，會出現錯誤訊息「位於資料表 TableName 的識別欄位其外顯值只有當使用了資料行清單且 IDENTITY_INSERT 為 ON 時才能指定」。
  必須要指定欄位名稱：
  insert into TableName (col1, col2, col3) values (1,2,3);

10. 參數化查詢時，即使同樣的值，參數名稱仍不可重複

例如像這樣的語句是不行的：

• insert into TableName values (:test,:test,:test);

會出現錯誤訊息 "COUNT field incorrect or syntax error"，要將參數名稱錯開：

• insert into TableName values (:test1,:test2,:test3);

11. 同一句 update 裡，不能重複更新同一欄位

之前偷懶，在同一句裡面用變數串 SQL，同時更新另一個可能不被更新到的欄位：

• update test_table set ".$Column." = ?, col2= ? where col3 = 1

變數 $Column 可能是 col1、也可能是 col2，所以當變數是 col2 時，會串成這樣：

•  update test_table set col2 = ?, col2 = ?, where col3 = 1

就會得到以下錯誤：

PHP Fatal error:  Uncaught PDOException: SQLSTATE[42000]: [Microsoft][ODBC Driver 17 for SQL Server][SQL Server]資料行名稱 'col2' 在 INSERT 的 SET 子句或資料行清單中指定一次以上。在同一個子句中，不能將一個以上的值指定給資料行。請修改子句，確定資料行只會更新一次。如果此陳述式在檢視中更新或插入資料行，則資料行別名可在程式碼中隱藏重複項。

一時想不到好的寫法，目前先把這兩個 case 拆成不同語句處理。

12. 小數若整數部分為 0 時，預設僅顯示小數點與其後

有一份報表，本來接 MySQL 產出的報表裡，資料值是「0.12」，在移轉到 MSSQL 後，變成「.12」。

在論壇找到 "PHP SQL Server decimal/numeric number can't starting with 0"，有人建議：

• SQLSRV : "FormatDecimals"=>true
• PDO_SQLSRV : PDO::SQLSRV_ATTR_FORMAT_DECIMALS => true

我查了一下，在《將十進位字串及貨幣值格式化 (PDO_SQLSRV 驅動程式)》發現，原來可以在 PDO 物件上指定參數處理這個問題。

可以在 PDO 物件建立時指定：

$attrs = array(PDO::SQLSRV_ATTR_FORMAT_DECIMALS => true, PDO::SQLSRV_ATTR_DECIMAL_PLACES => 2);
$conn = new PDO("sqlsrv:Server = myServer; Database = myDB", $username, $password, $attrs);

像我們是公用函式去建 PDO 物件，那麼也可以在連線物件建立後，事後指定：

$conn = new PDO("sqlsrv:Server = myServer; Database = myDB", $username, $password);
$conn->setAttribute(PDO::SQLSRV_ATTR_FORMAT_DECIMALS, true);
$conn->setAttribute(PDO::SQLSRV_ATTR_DECIMAL_PLACES, 2);

不過 SQL SRV 要到 5.6.0 版本開始才支援 "SQLSRV_ATTR_FORMAT_DECIMALS" 這個參數，不支援時，會出現錯誤訊息：

Fatal error: Uncaught Error: Undefined class constant 'SQLSRV_ATTR_FORMAT_DECIMALS' in {所執行的PHP檔案}:{錯誤發生的行號} Stack trace: #0 {main} thrown in {所執行的PHP檔案} on line {錯誤發生的行號}

從《Microsoft Drivers for PHP for SQL Server 的系統需求》的版本對照表看來，要等到 PHP 7.1 開始的版本才支援，如果是用比較舊版的 PHP 就要另覓他路了。