【转】迁移到 utf8mb4：需要考虑的事项

原文来自Percona Blog： ​​https://www.percona.com/blog/migrating-to-utf8mb4-things-to-consider/​​

utf8mb4字符集是 MySQL 8.0 的新默认字符集，此更改既不会影响现有数据，也不会强制进行任何升级。

迁移到utf8mb4有很多优点，包括：

• 它可以存储更多符号，包括表情符号
• 它有针对亚洲语言的新排序规则
• ​​它比它比utf8mb3快快​​​

不过，您可能想知道迁移如何影响您现有的数据。该博客涵盖了它的多个方面。

存储要求

顾名思义，使用字符集utf8mb4的一个字符可以占用的最大字节数是 4 个字节。这比utf8mb3的要求要大，后者需要三个字节和许多其他 MySQL 字符集。

幸运的是，utf8mb3是 utf8mb4 的子集，现有数据的迁移不会增加存储在磁盘上的数据大小：每个字符根据需要占用多少字节。例如，拉丁字母表中的任何数字或字母都需要一个字节。来自其他字母的字符最多可以占用四个字节。这可以通过一个简单的测试来验证。

mysql🐬> set names utf8mb4;
Query OK, 0 rows affected (0,00 sec)

mysql🐬> CREATE TABLE charset_len( name VARCHAR(255), val CHAR(1) ) CHARACTER SET=utf8mb4;
Query OK, 0 rows affected (0,03 sec)

mysql🐬> INSERT INTO charset_len VALUES('Latin A', 'A'),  ('Cyrillic А', 'А'), ('Korean ㉿', '㉿'), ('Dolphin 🐬', '🐬');
Query OK, 4 rows affected (0,02 sec)
Records: 4  Duplicates: 0  Warnings: 0

mysql🐬> SELECT name, val, HEX(val), BIT_LENGTH(val)/8 FROM charset_len;
+--------------+------+----------+-------------------+
| name         | val  | HEX(val) | BIT_LENGTH(val)/8 |
+--------------+------+----------+-------------------+
| Latin A      | A    | 41       |            1.0000 |
| Cyrillic А   | А    | D090     |            2.0000 |
| Korean ㉿    | ㉿    | E389BF   |            3.0000 |
| Dolphin 🐬   | 🐬    | F09F90AC |            4.0000 |
+--------------+------+----------+-------------------+
4 rows in set (0,00 sec)

SELECT * FROM charset_len LIMIT 10 ;

SELECT `name`, val, HEX(val), BIT_LENGTH(val)/8 FROM charset_len LIMIT 10 ;

因此，您所有最多使用三个字节的数据都不会改变，您将能够存储需要 4 字节编码的字符。

列的最大长度

虽然数据存储没有改变，但当 MySQL 计算列可以存储的最大数据量时，对于某些适用于utf8mb3的列大小定义，它可能会失败。例如，您可以有一个具有以下定义的表：

mysql🐬> CREATE TABLE len_test(
      -> foo VARCHAR(16384)
      -> ) ENGINE=InnoDB CHARACTER SET utf8mb3;
Query OK, 0 rows affected, 1 warning (0,06 sec)

如果您决定将此表转换为使用utf8mb4字符集，则操作将失败：

mysql🐬> ALTER TABLE len_test CONVERT TO CHARACTER SET utf8mb4;
ERROR 1074 (42000): Column length too big for column 'foo' (max = 16383); use BLOB or TEXT instead

原因是 MySQL 可以在VARCHAR列中存储的最大字节数为 65,535，即 utf8mb3 字符集为 21845 个字符，utf8mb4字符集为 16383个字符。

因此，如果在utf8mb4的场景下，您的列如果超过 16383 个字符，则需要将它们转换为TEXT或LONGTEXT数据类型。

如果您运行查询，您可以找到所有此类列：

-- 创建几个比较宽的varchar表
[test]> create table tb32344344(a int ,b varchar(16384) not null )  CHARACTER SET utf8mb3;;
Query OK, 0 rows affected, 1 warning (0.16 sec)
[test]> create table tb44445(a int ,b varchar(21000) not null )  CHARACTER SET utf8mb3;
Query OK, 0 rows affected, 1 warning (0.27 sec)


-- 查能从utf8升级到utf8mb4会出现问题的列：
SELECT TABLE_SCHEMA, TABLE_NAME, COLUMN_NAME,
       CHARACTER_MAXIMUM_LENGTH, DATA_TYPE
FROM information_schema.columns
WHERE CHARACTER_MAXIMUM_LENGTH > 16383 AND
      DATA_TYPE NOT LIKE '%text%' AND 
      DATA_TYPE NOT LIKE '%blob%' AND
      TABLE_SCHEMA NOT IN ('mysql', 'information_schema', 'performance_schema');

在我的测试环境中，它返回：
+--------------+------------+-------------+--------------------------+-----------+
| TABLE_SCHEMA | TABLE_NAME | COLUMN_NAME | CHARACTER_MAXIMUM_LENGTH | DATA_TYPE |
+--------------+------------+-------------+--------------------------+-----------+
| test         | tb32344344 | b           |                    16384 | varchar   |
| test         | tb44445    | b           |                    21000 | varchar   |
+--------------+------------+-------------+--------------------------+-----------+
2 rows in set (0.01 sec)

索引存储要求

MySQL 事先并不知道在创建索引时将在列中存储哪些字符。因此，当它计算索引所需的存储空间时，它会取所选字符集的最大值。因此，当从另一个字符集转换为utf8mb4时，您可能会达到索引存储限制。对于 InnoDB，索引的最大大小对于REDUNDANT和COMPACT行格式为 767 字节，对于DYNAMIC和COMPRESSED行格式为 3072 字节(这里指的是字符长度，不区分语言。因此对于latin1 utf8 utf8mb4 实际上支持的最大索引列长度还是有区别的）。有关详细信息，请参阅​​用户参考手册​​。

这意味着您需要在执行更新之前检查是否有可能增长到超过这些值的索引。您可以使用以下查询执行此操作：

WITH indexes AS (
     WITH tables AS  (
          SELECT SUBSTRING_INDEX(t.NAME, '/', 1) AS `database`, SUBSTRING_INDEX(t.NAME, '/', -1) AS `table`, i.NAME AS `index`, ROW_FORMAT
          FROM information_schema.INNODB_INDEXES i JOIN information_schema.INNODB_TABLES t USING(TABLE_ID)
    )
    SELECT `database`, `table`, `index`, ROW_FORMAT, GROUP_CONCAT(kcu.COLUMN_NAME) AS columns,
           SUM(c.CHARACTER_MAXIMUM_LENGTH) * 4 AS index_len_bytes
    FROM tables JOIN information_schema.KEY_COLUMN_USAGE kcu
         ON (`database` = TABLE_SCHEMA AND `table` = kcu.TABLE_NAME AND `index` = kcu.CONSTRAINT_NAME)
         JOIN information_schema.COLUMNS c ON (kcu.COLUMN_NAME = c.COLUMN_NAME AND `database` = c.TABLE_SCHEMA AND `table` = c.TABLE_NAME)
    WHERE c.CHARACTER_MAXIMUM_LENGTH IS NOT NULL
    GROUP BY `database`, `table`, `index`, ROW_FORMAT ORDER BY index_len_bytes
) SELECT * FROM indexes WHERE index_len_bytes >= 768;


+----------+--------------+---------+------------+------------+-----------------+
| database | table        | index   | ROW_FORMAT | columns    | index_len_bytes |
+----------+--------------+---------+------------+------------+-----------------+
| cookbook | hitcount     | PRIMARY | Dynamic    | path       |            1020 |
| cookbook | phrase       | PRIMARY | Dynamic    | phrase_val |            1020 |
| cookbook | ruby_session | PRIMARY | Dynamic    | session_id |            1020 |
+----------+--------------+---------+------------+------------+-----------------+
3 rows in set (0,04 sec)

一旦您确定了此类索引，请检查列并相应地调整表定义。

注意：查询使用​​CTE​​，自 MySQL 8.0 起可用。如果您仍在使用 5.7 或更早版本，则需要重写查询。

补充，可以看这个例子：

mysql> show create table sb1 \G
*************************** 1. row ***************************
       Table: sb1
Create Table: CREATE TABLE `sb1` (
  `name` varchar(900) DEFAULT NULL,
  KEY `idx_name` (`name`(768))
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
1 row in set (0.00 sec)

-- 使用严格的sql_mode 看下效果
mysql> set sql_mode='STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION';
Query OK, 0 rows affected (0.00 sec)

mysql> show create table sb1 \G
*************************** 1. row ***************************
       Table: sb1
Create Table: CREATE TABLE `sb1` (
  `name` varchar(900) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
1 row in set (0.00 sec)

mysql> ALTER TABLE sb1 ADD INDEX idx_name(`name`);
ERROR 1071 (42000): Specified key was too long; max key length is 3072 bytes

mysql> select 3072/4;   -- utf8mb4每个会占4bytes，因此支持的最大index长度是768
+----------+
| 3072/4   |
+----------+
| 768.0000 |
+----------+
1 row in set (0.00 sec)

mysql> ALTER TABLE sb1 ADD INDEX idx_name(`name`(768));
Query OK, 0 rows affected (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> show create table sb1 \G
*************************** 1. row ***************************
       Table: sb1
Create Table: CREATE TABLE `sb1` (
  `name` varchar(900) DEFAULT NULL,
  KEY `idx_name` (`name`(768))
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
1 row in set (0.00 sec)


对于对个列的情况：
CREATE TABLE `sb2` (
  `name1` VARCHAR(300) DEFAULT NULL,
  `name2` VARCHAR(800) DEFAULT NULL,
  `name3` VARCHAR(768) DEFAULT NULL
  ) ENGINE=INNODB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

mysql> set sql_mode='NO_ENGINE_SUBSTITUTION';
Query OK, 0 rows affected (0.00 sec)

-- 在sql_mode严格情况下直接报错，不严格模式下，会根据允许的最大长度来创建（也就是name2(768）
ALTER TABLE sb2 ADD INDEX idx_name2(`name2`); 

-- 这个768长度满足最大限制，可以正常创建
ALTER TABLE sb2 ADD INDEX idx_name3(`name3`);

mysql> show create table sb2 \G
CREATE TABLE `sb2` (
  `name1` VARCHAR(300) DEFAULT NULL,
  `name2` VARCHAR(800) DEFAULT NULL,
  `name3` VARCHAR(768) DEFAULT NULL,
  KEY `idx_name2` (`name2`(768)),
  KEY `idx_name3` (`name3`)
) ENGINE=INNODB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci


TIPS： 对于utf8mb3的表，索引列最大支持到1024
CREATE TABLE `sb11` (
  `a` int NOT NULL,
  `foo` varchar(16384) NOT NULL,
  PRIMARY KEY (`a`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3

3072/3=1024

[test]> alter table sb11 add index idx_foo(foo(1025));
ERROR 1071 (42000): Specified key was too long; max key length is 3072 bytes

[test]> alter table sb11 add index idx_foo(foo(1024));
Query OK, 0 rows affected (0.00 sec)
Records: 0  Duplicates: 0  Warnings: 0

[test]> show create table sb11  \G
*************************** 1. row ***************************
       Table: sb11
Create Table: CREATE TABLE `sb11` (
  `a` int NOT NULL,
  `foo` varchar(16384) NOT NULL,
  PRIMARY KEY (`a`),
  KEY `idx_foo` (`foo`(1024))
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3
1 row in set (0.01 sec)

临时表

转换为utf8mb4字符集后您可能遇到的另一个问题是： MySQL 创建以解决查询的隐式临时表的大小增加。由于utf8mb4可能比其他字符集存储更多的数据，因此此类隐式表的列大小也会更大。要确定您是否受到此问题的影响，请查看全局状态变量Created_tmp_disk_tables。如果迁移后这开始显着增加，您可以考虑更新计算机上的 RAM 并增加临时表的最大大小。请注意，此问题可能是您的某些查询优化不佳的症状。

结论

转换为utf8mb4字符集可为您带来更好的性能、更大范围的字符可供您使用，包括表情符号和新的排序规则（排序规则）。这种转换几乎没有代价，而且可以顺利完成。

确保：

• 您将所有可以存储超过 16383 个字符的VARCHAR列转换为TEXT或LONGTEXT数据类型
• 您调整了索引定义，对于REDUNDANT和COMPACT行格式，迁移后可能需要超过 767 个字节，而对于DYNAMIC和COMPRESSED行格式，可能需要3072 个字节。
• 您优化了查询，使它们不应该开始使用基于内部磁盘的临时表

相关阅读：

​​https://dev.mysql.com/blog-archive/mysql-8-0-collations-migrating-from-older-collations/​​

​​https://dev.mysql.com/blog-archive/mysql-8-0-collations-migrating-from-older-collations-part-2/​​

​​https://dev.mysql.com/blog-archive/mysql-8-0-when-to-use-utf8mb3-over-utf8mb4/​​

​​https://dev.mysql.com/doc/refman/8.0/en/innodb-row-format.html​​