MySQL DATE_FORMAT 字符集陷阱：Illegal mix of collations 根因與修復

現場還原

mysql> SELECT DATE_FORMAT('2013-11-19','%Y-%m-%d') > TIMEDIFF('2013-11-19','2013-11-20');
ERROR 1267 (HY000): Illegal mix of collations (utf8_general_ci,COERCIBLE) and (latin1_swedish_ci,NUMERIC) for operation '>'

根因分析

查閱官方文檔發現關鍵段落：

DATE_FORMAT() returns a string with a character set and collation given by character_set_connection and collation_connection so that it can return month and weekday names containing non-ASCII characters.

而 TIMEDIFF() 返回 binary 字符集（無校驗規則）。當兩邊字符集/校驗規則不兼容且無法隱式轉換時，比較操作報錯。

驗證實驗

mysql> SET NAMES utf8;
mysql> SELECT CHARSET(DATE_FORMAT('2013-11-19','%Y-%m-%d')), CHARSET(TIMEDIFF('2013-11-19','2013-11-20'));
+--------------------------------------------------+--------------------------------------------------+
| CHARSET(DATE_FORMAT(...))                        | CHARSET(TIMEDIFF(...))                           |
+--------------------------------------------------+--------------------------------------------------+
| utf8                                             | binary                                           |
+--------------------------------------------------+--------------------------------------------------+

mysql> SET NAMES gb2312;
mysql> SELECT CHARSET(DATE_FORMAT('2013-11-19','%Y-%m-%d')), CHARSET(TIMEDIFF('2013-11-19','2013-11-20'));
+--------------------------------------------------+--------------------------------------------------+
| CHARSET(DATE_FORMAT(...))                        | CHARSET(TIMEDIFF(...))                           |
+--------------------------------------------------+--------------------------------------------------+
| gb2312                                           | binary                                           |
+--------------------------------------------------+--------------------------------------------------+

結論：DATE_FORMAT 結果字符集隨 SET NAMES（即 character_set_connection）變化；TIMEDIFF 永遠是 binary。

修復方案

方案 1：顯式轉換右側為同字符集（推薦）

SELECT DATE_FORMAT('2013-11-19','%Y-%m-%d') > CONVERT(TIMEDIFF('2013-11-19','2013-11-20') USING utf8);

返回 1，正常工作。

方案 2：統一會話字符集為 binary（若業務允許）

SET NAMES binary;
-- 之後 DATE_FORMAT 也返回 binary，不再衝突

方案 3：避免跨字符集比較，改用同類型函數

-- 將日期格式化為數字比較
SELECT CAST(DATE_FORMAT('2013-11-19','%Y%m%d') AS UNSIGNED) > 
       CAST(DATE_FORMAT('2013-11-20','%Y%m%d') AS UNSIGNED);

最佳實踐建議

1. 應用層統一設置連接字符集（SET NAMES utf8mb4），避免隱式轉換驚喜。
2. 涉及 DATE_FORMAT、CONCAT 等返回字符串的函數與 binary 類型（TIMEDIFF、SUBSTRING 等）混用比較時，顯式 CONVERT(... USING charset)。
3. Schema 設計階段統一字符集為 utf8mb4，表/列級別不再單獨指定，減少 COERCIBLE 推導複雜度。