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

SET NAMES utf8mb4; 

CREATE TABLE charset_len(
  id INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY ,  
  `name` VARCHAR(255), val CHAR(1) 
) CHARACTER SET=utf8mb4;

INSERT INTO charset_len (`name`,val) 
VALUES('Latin A', 'A'),  
('Cyrillic А', 'А'), 
('Korean ㉿', '㉿'), 
('Dolphin 🐬', '🐬');

INSERT INTO charset_len (`name`,val) SELECT `name`,val FROM charset_len ;

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

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

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');


例如，在我的测试环境中，它返回：
*************************** 1. row ***************************
TABLE_SCHEMA: test
TABLE_NAME: setup
COLUMN_NAME: value
CHARACTER_MAXIMUM_LENGTH: 20000
DATA_TYPE: varchar
1 row in set (0,02 sec

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)

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^L
*************************** 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> 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