MySQL 與 MSSQL 函數對比學習筆記（六）—— 數學、字串、日期時間、加密與系統函數

MySQL 與 MSSQL 函數差異總覽

本文記錄從 MSSQL 轉向 MySQL 學習過程中，對兩者常用函數的對比心得，涵蓋數學、字串、日期時間、條件判斷、系統資訊、加密等類別，並附上實際測試範例。

一、數學函數

• MOD(X, Y)：求餘函數，支援小數精確運算。
  SELECT MOD(31, 8); -- 返回 7
• TRUNCATE(X, Y)：直接截斷至小數點後 Y 位，不四捨五入（MSSQL 無直接對應）。
  SELECT TRUNCATE(1.32, 1); -- 返回 1.3
• HEX() / UNHEX()：字串與十六進位互轉。
  SELECT HEX('test'); -- 74657374

二、字串函數

• CHAR_LENGTH(str)：字元數（多位元組字元算 1），MSSQL 用 LEN 或 DATALENGTH。
• CONCAT_WS(sep, s1, s2...)：帶分隔符連接，自動忽略 NULL 值，MSSQL 需用 CONCAT + ISNULL 組合。
• INSERT(s1, x, len, s2)：等同 MSSQL STUFF()。
• LPAD / RPAD：左右填充字串至指定長度，功能比 MSSQL REPLICATE 更強。
• TRIM([BOTH|LEADING|TRAILING] remstr FROM str)：刪除兩側指定字元，MSSQL 需 LTRIM(RTRIM())。
• LOCATE / POSITION / INSTR：查詢子串位置，對應 MSSQL CHARINDEX。
• ELT(N, ...)：返回第 N 個參數；FIELD(s, ...)：返回字串在列表中的位置。
• FIND_IN_SET(s1, s2)：針對逗號分隔字串列表查詢位置。
• MAKE_SET(x, s1, s2...)：依二進位位元組合字串。
• STRCMP(s1, s2)：字串比較，相等 0，小於 -1，大於 1。

三、日期時間函數

• MySQL 提供豐富的取值函數：NOW(), CURDATE(), UTC_DATE(), MONTHNAME(), QUARTER(), EXTRACT(type FROM date)（對應 MSSQL DATEPART）。
• 時間轉換：TIME_TO_SEC() / SEC_TO_TIME() 互為反函數。
• 日期運算：DATE_ADD(), DATE_SUB(), ADDDATE(), SUBDATE(), DATEDIFF()，語法比 MSSQL 單純的 DATEADD/DATEDIFF 更靈活。
• 格式化：DATE_FORMAT(), TIME_FORMAT(), GET_FORMAT()。

四、條件判斷函數

• IF(expr, v1, v2)：expr 為真回傳 v1，否則 v2。注意資料型別相容性。
• IFNULL(v1, v2)：v1 非 NULL 回傳 v1，否則 v2。不等同 MSSQL NULLIF（兩值相等回傳 NULL）。
• CASE：支援簡單與搜尋兩種語法，回傳型別取決於所有分支的相容型別，拼接動態 SQL 時需特別注意型別一致。

五、系統資訊函數

• VERSION(), CONNECTION_ID(), DATABASE()/SCHEMA(), USER()/CURRENT_USER() 等。
• SHOW PROCESSLIST / SHOW FULL PROCESSLIST：查看執行緒狀態，root 可見所有連線。
• CHARSET(str), COLLATION(str)：查看字元集與排序規則。
• LAST_INSERT_ID()：批次插入時只返回第一筆自增 ID，與 MSSQL SCOPE_IDENTITY()/IDENT_CURRENT() 行為不同，需特別注意。

六、加密函數

• PASSWORD()：MySQL 內部認證用，單向不可逆，不建議應用層使用。
• MD5()：32 位十六進位雜湊。
• ENCODE() / DECODE()：對稱加解密，互為反函數。

七、其他實用函數

• FORMAT(x, n)：千分位格式化並四捨五入。
• CONV(N, from, to)：進制轉換。
• INET_ATON() / INET_NTOA()：IP 與整數互轉。
• GET_LOCK() / RELEASE_LOCK()：應用層鎖機制。
• BENCHMARK(count, expr)：效能測試，重複執行表達式。
• CAST / CONVERT：型別轉換，支援 BINARY, CHAR, DATE, DATETIME, DECIMAL, SIGNED, UNSIGNED 等。

總結與提示

1. MySQL 日期時間以字串儲存，可直接用字串函數擷取部分。
2. 修改預設字元集：編輯 my.ini 中的 default-character-set 與 character-set-server，重啟服務生效。
3. 查看當前字元集：SHOW VARIABLES LIKE 'character_set_%'。