最近遇到幾個項目被MySQL的utf8編碼坑，想起之前編碼問題被坑的慘痛教訓，記錄一下，警示自己。
曾幾何時，每次建庫都選utf8，覺得自己比那些用亂七八糟編碼的人不知道酷到哪裡去了。直到好多年前的某次課程設計做項目的時候，愉快的建了個用戶表：
CREATE TABLE `test_user` (
`id` int(11) unsigned NOT NULL AUTO_INCREMENT,
`name` varchar(32) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
然後愉快的新增用戶：INSERT INTO test_user(name) VALUES("我是😁")，接著愉快的反思人生：
Incorrect string value: '\xF0\x9F\x98\x81' for column 'name' at row 1
我是誰？我來自哪裡？我在幹嘛？難道是我代碼裡面的字符集用錯了？不對啊我所有地方都用的utf8啊……
# MySQL的UTF8編碼是什麼？
首先來看官方文檔：
The character set named utf8 uses a maximum of three bytes per character and contains only BMP characters. The utf8mb4 character set uses a maximum of four bytes per character supports supplementary characters:
For a BMP character, utf8 and utf8mb4 have identical storage characteristics: same code values, same encoding, same length.
For a supplementary character, utf8 cannot store the character at all, whereas utf8mb4 requires four bytes to store it. Because utf8 cannot store the character at all, you have no supplementary characters in utf8 columns and need not worry about converting characters or losing data when upgrading utf8 data from older versions of MySQL.
我們再看看維基百科對UTF8編碼的解釋：
UTF-8 is a variable width character encoding capable of encoding all 1,112,064 valid code points in Unicode using one to four 8-bit bytes.
可以看出，MySQL中的utf8實質上不是標準的UTF8。MySQL中，utf8對每個字符最多使用三個字節來表示，所以一些emoji甚至是一些生僻漢字就存不下來了，比如「𡋾」。
MySQL一直不承認這是一個bug，他們在2010年發布了「utf8mb4」字符集來繞過這個問題，在MySQL中，utf8mb4才應該是標準的utf8編碼，並且官方很雞賊的偷偷在最新的文檔中加上了，算是認識到錯誤了吧：
utf8 is an alias for the utf8mb3 character set.
The utf8mb3 character set will be replaced by utf8mb4 in some future MySQL version. Although utf8 is currently an alias for utf8mb3, at that point utf8 will become a reference to utf8mb4. To avoid ambiguity about the meaning of utf8, consider specifying utf8mb4 explicitly for character set references instead of utf8.
# MySQL UTF8問題簡史
MySQL從4.1版本開始支持utf8，即2003年，但是現在的utf8標準（RFC 3629)是在其後發布的。MySQL在2002年3月28日的4.1預覽版中使用了舊版的utf8標準（RFC 2279)，該標準最多支持每個字符6個字節，同年9月MySQL調整其utf8字符集最多支持3位元組，而這個調整可能只是為了優化空間（05年前推薦使用CHAR類欄位，而一個utf8的CHAR將會佔用6位元組長度）和時間性能（05年前在MySQL中使用CHAR欄位會有更優的速度）。嗯可以在GitHub中看到大家對這個坑的吐槽：
但是這個字符編碼發布出來，就不能輕易的修改，因為如果已經有用戶開始使用了，就需要這些用戶重新構建其資料庫。
怎麼補救呢？在上面最新文檔中可以看出，他們將當前的utf8作為utf8mb3的別名，並且在將來的某一天會把utf8重新作為utf8mb4別名，這樣來解決這個多年的巨坑。
# 啥是UTF8
略
# utf8mb4_unicode_ci 和 utf8mb4_general_ci
字符除了存儲，還需要排序或者比較，這個操作與編碼字符集有關，稱為collation，與utf8mb4對應的是utf8mb4_unicode_ci 和 utf8mb4_general_ci這兩個collation。
準確性
utf8mb4_unicode_ci 是基於標準Unicode來進行排序比較的，能保持在各個語言之間的精確排序；
utf8mb4_general_ci 並不基於Unicode排序規則，因此在某些特殊語言或者字符上的排序結果可能不是所期望的。
性能
utf8mb4_general_ci 在比較和排序時更快，因為其實現了一些性能更好的操作，但是在現代伺服器上，這種性能提升幾乎可以忽略不計。
utf8mb4_unicode_ci 使用Unicode的規則進行排序和比較，其排序規則為了處理一些特殊字符，實現更加複雜。
現在基本沒有理由繼續使用utf8mb4_general_ci了，因為其帶來的性能差異很小，遠不如更好的數據設計，比如使用索引等等。
# MySQL用錯編碼怎麼救
備份，不然崩了就只有刪庫跑路了；
升級MySQL服務端到5.3.3及以上版本，以支持utf8mb4；
將資料庫、表、列的字符編碼、collation改為utf8mb4:
# For each database:
ALTER DATABASE database_name CHARACTER SET = utf8mb4 COLLATE = utf8mb4_unicode_ci;
# For each table:
ALTER TABLE table_name CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
# For each column:
ALTER TABLE table_name CHANGE column_name column_name VARCHAR(length) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
4.檢查列和索引鍵的最大長度；
5.修改連接、客戶端、服務端的字符集；
6.修復和優化所有的表，以免出現一些莫名其妙的錯誤，可以使用如下的方式：
# For each table
REPAIR TABLE table_name;
OPTIMIZE TABLE table_name;
或者是使用`mysqlcheck`工具：
$ mysqlcheck -u root -p --auto-repair --optimize --all-databases
# 其他坑
MySQL表欄位字符集不同導致的索引失效問題
# 參考
https://medium.com/@adamhooper/in-mysql-never-use-utf8-use-utf8mb4-11761243e434
https://dev.mysql.com/doc/refman/8.0/en/charset-unicode-utf8.html
https://www.joelonsoftware.com/2003/10/08/the-absolute-minimum-every-software-developer-absolutely-positively-must-know-about-unicode-and-character-sets-no-excuses/
https://stackoverflow.com/questions/766809/whats-the-difference-between-utf8-general-ci-and-utf8-unicode-ci
https://mathiasbynens.be/notes/mysql-utf8mb4#utf8-to-utf8mb4
作者：liexing
來源：https://blog.liexing.me/2018/07/27/mysql-utf8/