MySQL DATE_FORMAT 內置字符集導致 Illegal mix of collations 錯誤分析與解決

對比 DATE_FORMAT() 與 TIMEDIFF() 時觸發 ERROR 1267: Illegal mix of collations (utf8_general_ci,COERCIBLE) and (latin1_swedish_ci,NUMERIC) for operation '>'。本文深度解析 DATE_FORMAT 返回值攜帶連接層字符集屬性、TIMEDIFF 返回二進制字符串的機制差異，給出 CONVERT 顯式轉換與會話級字符集統一兩種解決路徑。

現場還原

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

根因分析：字符集屬性不匹配

1. 官方文檔定性

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 字符集。

2. 實測驗證

SET NAMES utf8;
SELECT CHARSET(DATE_FORMAT('2013-11-19','%Y-%m-%d')), CHARSET(TIMEDIFF('2013-11-19', '2013-11-20'));
-- 結果：utf8 | binary

SET NAMES gb2312;
SELECT CHARSET(DATE_FORMAT('2013-11-19','%Y-%m-%d')), CHARSET(TIMEDIFF('2013-11-19', '2013-11-20'));
-- 結果：gb2312 | binary

結論：DATE_FORMAT 結果字符集隨 character_set_connection 變化；TIMEDIFF 固定 binary。比較操作時，MySQL 嘗試隱式轉換失敗，拋出 collation 混合錯誤。

解決方案

方案一：顯式轉換右側操作數 (推薦、精準)

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

也可用 CAST(... AS CHAR CHARACTER SET utf8)。

方案二：統一會話字符集 (全局、一次性)

SET NAMES utf8mb4 COLLATE utf8mb4_unicode_ci;
-- 或在 my.cnf [client]/[mysql] 設置 default-character-set
-- 則 DATE_FORMAT 返回 utf8mb4，TIMEDIFF 仍為 binary，但比較時隱式轉換規則不同，實測 8.0+ 可通過

⚠️ 5.7 及以下版本即使統一會話字符集，binary 與具體 collation 比較仍可能報錯，**方案一最通用穩健**。

DATE_FORMAT 格式化符號完整參考表

總結

DATE_FORMAT 返回值攜帶連接字符集屬性，TIMEDIFF 等時間函數返回 binary，混合比較觸發 collation 衝突。**顯式 CONVERT(... USING charset) 是最精準、版本通用的解法**。開發規範建議：SQL 中涉及字符串與時間/數值比較時，統一顯式 CAST/CONVERT，消除隱式轉換風險。