MySQL utf8 字符集问题
概要
MySQL 的 utf8 字符集确实是一个坑，之前以为自己懂了，直到最近遇到一个 4 字节的汉字。
关于编码
首先计算机并不能直接保存字符串(String)，它只能保存 0 和 1 ；为了把字符串保存下来，程序会把字符串先编码成01组成的串(字节串)，字节串是计算机可以保存的。而这个由字符到字节的函数y=f(x)就是我们所说的字符集。常见的字符集有 utf8,gbk,latin1 等等。
1、如何确认一个字符串通过特定编码后的字节串呢？
对于 python3 来说 str 对象的 encode 方法可以返回编码后的字节串
In [1]: s = "东临碣石，以观沧海。"
In [2]: s.encode('utf8')
Out[2]: b'\xe4\xb8\x9c\xe4\xb8\xb4\xe7\xa2\xa3\xe7\x9f\xb3\xef\xbc\x8c\xe4\xbb\xa5\xe8\xa7\x82\xe6\xb2\xa7\xe6\xb5\xb7\xe3\x80\x82'
2、如果是一条已经存在于数据库中的数据，我们可以用 hex 函数来检查它在数据库中的字节串。
mysql> select x,hex(x) from t limit 1;
+--------------------------------+--------------------------------------------------------------+
| x | hex(x) |
+--------------------------------+--------------------------------------------------------------+
| 东临碣石，以观沧海。 | E4B89CE4B8B4E7A2A3E79FB3EFBC8CE4BBA5E8A782E6B2A7E6B5B7E38082 |
+--------------------------------+--------------------------------------------------------------+
1 row in set (0.00 sec)
关于版本
以下内容摘自 8.0.24 版本的 release-note Bug fix 的第一条
Important Note: ... the character set as utf8 which is becoming a synonym for utf8mb4. Now in such cases, utf8mb3 is shown instead,
and CREATE TABLE raises the warning 'collation_name' is a collation of the deprecated character set UTF8MB3. Please consider using UTF8MB4 with an appropriate collation instead.
...
也就是说在 8.0.24 之前 utf8 事实上是 utf8mb3 这个就会导致一部分中文不能保存。比如 ? 字的字节串占 4 个字节，这个时候 MySQL 就会报错。
select @@version;
+-----------+
| @@version |
+-----------+
| 8.0.19 |
+-----------+
create table t(x varchar(16) charset utf8);
--
insert into t(x) values('?');
ERROR 1366 (HY000): Incorrect string value: '\xF0\xA3\x8C\x80' for column 'x' at row 1
8.0.24 版本之后在 MySQL 中使用 utf8 会被警告，提示再这几个版本 utf8 就是 utf8mb4 了，虽然它现在还是 utf8mb3 。
mysql> select @@version;
+-----------+
| @@version |
+-----------+
| 8.0.26 |
+-----------+
mysql> create table t( x varchar(16) charset utf8);
Query OK, 0 rows affected, 1 warning (0.03 sec)
mysql> show warnings;
+---------+------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Level | Code | Message |
+---------+------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Warning | 3719 | 'utf8' is currently an alias for the character set UTF8MB3, but will be an alias for UTF8MB4 in a future release. Please consider using UTF8MB4 in order to be unambiguous. |
+---------+------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
希望 utf8 快点转正！
怎么办
对于新系统我们的设计应该是面向未来的，所以我建议直接就写上 default charset utf8mb4 对于老的系统没有必要的我们可以先不动。
调整字符集要注意的事
1、调整字符集一定是针对列来的，而不是调整表的默认字符集。
mysql> create table t(x varchar(16)) default charset utf8;
mysql> insert into t(x) values("东临碣石，以观沧海。");
mysql> select x,hex(x) as hex_utf8 from t limit 1;
+--------------------------------+--------------------------------------------------------------+
| x | hex_utf8 |
+--------------------------------+--------------------------------------------------------------+
| 东临碣石，以观沧海。 | E4B89CE4B8B4E7A2A3E79FB3EFBC8CE4BBA5E8A782E6B2A7E6B5B7E38082 |
+--------------------------------+--------------------------------------------------------------+
调整表的默认字符集 MySQL 并不会对字体列进行转码。
-- 调整表的字符集
mysql> alter table t default charset gbk;
-- 可以看到列的二进制串并没有变
mysql> select x,hex(x) as hex_utf8 from t limit 1;
+--------------------------------+--------------------------------------------------------------+
| x | hex_utf8 |
+--------------------------------+--------------------------------------------------------------+
| 东临碣石，以观沧海。 | E4B89CE4B8B4E7A2A3E79FB3EFBC8CE4BBA5E8A782E6B2A7E6B5B7E38082 |
+--------------------------------+--------------------------------------------------------------+
-- 可以看到单单只是改了一下元数据
mysql> show create table t;
+-------+----------------------------------------------------------------------------------------------------------+
| Table | Create Table |
+-------+----------------------------------------------------------------------------------------------------------+
| t | CREATE TABLE `t` (
`x` varchar(16) CHARACTER SET utf8 DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=gbk |
+-------+----------------------------------------------------------------------------------------------------------+
2、正确的做法是对列进行调整，并且同时改变表的字符集(这样以后加列也方便了)。
-- 把列的字符集设置成 utf8
mysql> alter table t modify column x varchar(16) charset gbk , default charset gbk;
-- 可以看到整个二进制串变了
mysql> select x,hex(x) as hex_utf8 from t limit 1;
+--------------------------------+------------------------------------------+
| x | hex_utf8 |
+--------------------------------+------------------------------------------+
| 东临碣石，以观沧海。 | B6ABC1D9EDD9CAAFA3ACD2D4B9DBB2D7BAA3A1A3 |
+--------------------------------+------------------------------------------+