這情況是一個我沒有特別發現，但是當出現錯誤時卻花了大把的時間 trace & debug 的事…
事由：
我有兩個表，就叫 a 跟 b 好了
a {
`id`: big integer, auto_increment, primary key,
`sn`: varchar(50), utf8mb4_bin, unique key
}
b {
`id`: big integer, auto_increment, primary key,
`a_id`: big integer, foreign key references `a`.`id`,
`sn`: varchar(50), utf8mb4_unicode_ci, unique key
}
我是後面接手維護的，我進行調整 a/b 表的 sn 的計算，利用 mysql trigger before insert 的時候去計算新的 sn，計算的操作是特別為了 a 跟 b 表各別寫一個 function 去做，就先叫 func_a 跟 func_b 好了
因為 b 跟 a 表的關係，所以 b.sn 的編碼格式中是有用到 a.sn 的，所以在 func_b 中，是要先以 NEW.`a_id` 到 a 表查 a.sn 後再回來用 a.sn 來串出 b.sn
在用 phpMyAdmin 進行設計的時候，過程跟結果都沒異常，新增資料時確實會以 func_a, func_b 計算出來的 sn 來寫入
但，異常是發生在，我把 db 匯出成 sql 後，再以這個 sql 匯入到另一個環境的 mysql 時，在 trigger b 表時就會提示有異常 `Illegal mix of collations (utf8mb4_unicode_ci,IMPLICIT) and (utf8mb4_bin,IMPLICIT) for operation '=' `
因為資料表不是我建的，甚至可以說我不是dba，但我在這個錯誤發生時有抓出是因為 func_b 所丟出的異常，但我當時找不出來是哪邊有狀況…而且，好玩的是，我用 phpMyAdmin 的後台去開啟 func_b 後，啥也不動就直接按下儲存，然後再測試時就發現已經正常了…奇也怪栽
後來比較有時間，就認真的 trace & debug 去，然後發現，我在用 b.a_id 進行查詢 a.a_id = b.b_id 要反回的 b.sn ，默認字元編碼是 utf8mb4_bin，然後依 a.sn 來組成新的編碼前綴跟接上流水號後就寫回 b.sn
結果出現剛上述 `Illegal mix of collations ..... `
中間我查過很多相關檢查方式跟資料，但都沒辦法解決這個問題
然後在 func_b 中進行斷點反查，結果發現是在進行 a.id = b.a_id 所取得的 a.sn 這欄位資料的 collation = utf8mb4，然後把 a.sn 再提供給我們的 func_b(b.a_id) 來進行統計計算
主因就是要反鐀的值的 collation 與 b.sn 的字碼級是不同的，但我本來以為都是 utf8mb4 是可以通用的，也沒想到如果由 a 表的 sn 是 utf8mb4_bin，而 sn 的 collation 就會是 utf8mb4_bin，然而 func_b 中要用這個 sn 來利用 CONCAT() 的方式來組合 b 表的 sn，但
b 表的 sn 是定義成 utf8mb4_unicode_c ，所以就出現上述的異常訊息
後來確定問題所在後，就事前用 COLLATION 轉碼成 utf8mb4_unicode_ci ，此問題就解決了
沒有留言:
張貼留言