SQL報了一個不常見的錯誤，讓新來的實習生懵了

摘要：前些天一個很簡單的SQL報了一個不常見的錯誤。

本文分享自華為雲社群《記一次mysql關聯查詢格式沖突問題【五月04】》，作者： KevinQ 。

問題起源

作為CRUD程式員，最常用的功能當然是資料庫查詢了。

前些天一個很簡單的SQL報了一個不常見的錯誤：

Illegal mix of collations (utf8mb4_general_ci,IMPLICIT) and (utf8mb4_unicode_ci,IMPLICIT) for operation '='      

今天我們就來看看mysql的utf8mb4的兩種格式問題，以及如何解決這個問題？

mysql的utf8格式與排序規則

字元集uft8與uft8mb4

在mysql中建立表并添加字段的時候，想選utf8字元集時會有兩個選擇：

1. utf8
2. utf8mb4

這兩個有什麼差別呢？

MySQL是在5.5.3之後才有utf8mb4的字元集可選，mb4的意思是：Most Bytes 4，可以相容unicode。

而utf8最多支援3個位元組，比如3個位元組的uft8無法支援Emoji表情和不常用的漢字，以及任何新增的Unicode字元等，是以才引入了uft8mb4。

排序規則

我們這次的問題是排序規則沖突引起的：

查詢語句：

SELECT * FROM table1 t1 
left join table2 on t1.username = t2.username      

關聯字段的編碼字元集均為utf8mb4，但是t1.username的排序規則是utf8mb4_general_ci，而t2.username的排序規則是utf8mb4_unicode_ci。

兩種排序規則的差別與特點是什麼呢？

特點

utf8mb4_unicode_ci 是基于标準的 Unicode 來排序和比較，即能夠支援所有Unicode字元的精确排序；

而utf8mb4_general_ci沒有實作Unicode排序規則，在遇到特殊字元時，排序可能不同。

差別

也是以，uft8mb4_general_ci相比utf8mb4_unicode_ci，前者的準确性雖然打了折扣，但是比較和排序執行的速度更快，并且，通常遇到特殊字元的順序并不重要。

排序規則不相容的解決方案

在mysql中，排序規則不相容的解決方案有兩種：

1、第一種，修改表字段的格式一緻。

即修改表的排序規則統一為utf8mb4_unicode_ci，或者另一種。官方更推薦使用utf8mb4_unicode_ci的排序規則，借用StackOverflow上的一段話：

There is almost certainly no reason to use utf8mb4_general_ci anymore, as we have left behind the point where CPU speed is low enough that the performance difference would be important. Your database will almost certainly be limited by other bottlenecks than this.

大概意思是說，目前CPU的運作速度已經快到可以讓我們不再将此排序作為一個考慮參數，而更應該開了其他開銷。

2、另一種方式則是借助關鍵字，COLLATE

通過COLLATE屬性，可以指定列的排序和比較方式。

我們在使用時，将它放在關聯查詢需要修改排序規則的地方：

SELECT
    u.guid
FROM
    `test`  t
LEFT JOIN user u ON u.guid = t.guid COLLATE utf8mb4_unicode_ci
WHERE t.state = 1      

如此，使用COLLATE屬性，可以讓使用不同排序規則的字段進行關聯查詢。

但是，經過測試，這樣會減慢SQL查詢的速度。

具體采用哪種方式，需要細細考量。

點選關注，第一時間了解華為雲新鮮技術~