總結：被MySQL UTF8編碼坑的慘痛教訓...

雲栖号資訊：【 點選檢視更多行業資訊

】

在這裡您可以找到不同行業的第一手的上雲資訊，還在等什麼，快來！

最近遇到幾個項目被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編碼，并且官方很雞賊的偷偷在最新的文檔中加上了，算是認識到錯誤了吧：更多的關于設計，原理知識點的問題，可以在網際網路架構師背景回複2T擷取。

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及以上版本，以支援utf8md4；
• 将資料庫、表、列的字元編碼、collation改為utf8md4:

# 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

【雲栖号線上課堂】每天都有産品技術專家分享！

課程位址：

https://yqh.aliyun.com/zhibo

立即加入社群，與專家面對面，及時了解課程最新動态！

【雲栖号線上課堂 社群】

https://c.tb.cn/F3.Z8gvnK

原文釋出時間：2020-05-11

本文作者：網際網路架構師

本文來自：“

網際網路架構師 微信公衆号

”，了解相關資訊可以關注“

網際網路架構師

”