环境

MySQL 8.0.40
使用 sysbench 已生成测试表 sbtest1 (250万数据)

问题描述

如何进行表字符集转换
如何进行列字符集转换
进行列/表字符集转换的时候需要注意什么，是否会锁表？

问题验证

1.表字符集转换

修改表字符集有2种方式，但两种修改方式会有很大的差异

方式1：

使用 ALTER TABLE xxx CHARACTER SET 方式修改

这个方法仅修改表的 元数据（默认字符集和排序规则），不会自动转换现有数据。
但是如果后续添加新列（未显式指定字符集），新列会继承表的默认字符集（utf8mb4）。

-- 很快，仅修改元数据
mysql> ALTER TABLE sbtest1 CHARACTER SET=utf8mb4 COLLATE=utf8mb4_general_ci, ALGORITHM=INPLACE, LOCK=NONE;
Query OK, 0 rows affected (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 0

-- 测试写入emoji
mysql> insert into sbtest1(c) values ('');
ERROR 1366 (HY000): Incorrect string value: '\xF0\x9F\x98\x84' for column 'c' at row 1

-- 添加新列
mysql> alter table sbtest1 add column d varchar(20);
Query OK, 0 rows affected (0.05 sec)
Records: 0  Duplicates: 0  Warnings: 0

-- 查看表结构
mysql> show create table sbtest1\G
*************************** 1. row ***************************
       Table: sbtest1
Create Table: CREATE TABLE `sbtest1` (
  `id` int NOT NULL AUTO_INCREMENT,
  `k` int NOT NULL DEFAULT '0',
  `c` char(120) CHARACTER SET utf8mb3 NOT NULL DEFAULT '',
  `pad` char(60) CHARACTER SET utf8mb3 NOT NULL DEFAULT '',
  `d` varchar(20) COLLATE utf8mb4_general_ci DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `k_1` (`k`)
) ENGINE=InnoDB AUTO_INCREMENT=2500001 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci
1 row in set (0.00 sec)

-- 往新加的列中写入emoji
mysql> insert into sbtest1(d) values ('');
Query OK, 1 row affected (0.02 sec)

方式2：

使用ALTER TABLE xxx CONVERT TO CHARACTER SET方式修改

使用这种方式有可能会锁表
utf8mb3 格式转为 utf8mb4 时，排序规则也可以改变，这种方式可以使用INPLACE方式，转换不锁表
通字符集下修改排序规则，转换不锁表
utf8mb4 格式转为 utf8mb3 时，由大转小，必然锁表
会重组数据，将表内数据字符都转换为当前设置的字符

-- 先还原下表
mysql> ALTER TABLE sbtest1 CHARACTER SET=utf8mb3 COLLATE=utf8mb3_general_ci, ALGORITHM=INPLACE, LOCK=NONE;
Query OK, 0 rows affected, 2 warnings (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 2
mysql> alter table sbtest1 drop column d;
Query OK, 0 rows affected (0.06 sec)
Records: 0  Duplicates: 0  Warnings: 0

-- 当前表结构为
mysql> show create table sbtest1\G
*************************** 1. row ***************************
       Table: sbtest1
Create Table: CREATE TABLE `sbtest1` (
  `id` int NOT NULL AUTO_INCREMENT,
  `k` int NOT NULL DEFAULT '0',
  `c` char(120) NOT NULL DEFAULT '',
  `pad` char(60) NOT NULL DEFAULT '',
  PRIMARY KEY (`id`),
  KEY `k_1` (`k`)
) ENGINE=InnoDB AUTO_INCREMENT=2500002 DEFAULT CHARSET=utf8mb3
1 row in set (0.00 sec)

-- 测试写入emoji
mysql> insert into sbtest1(c) values ('');
ERROR 1366 (HY000): Incorrect string value: '\xF0\x9F\x98\x84' for column 'c' at row 1

-- 执行数据转换
-- 从 utf8mb3 --> utf8mb4 , 同时修改了排序规则 COLLATE = utf8mb4_unicode_ci
mysql> ALTER TABLE sbtest1 CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci , ALGORITHM=INPLACE, LOCK=NONE;
Query OK, 0 rows affected (15.37 sec)
Records: 0  Duplicates: 0  Warnings: 0

-- 从 utf8mb4 --> utf8mb4 , 同时修改了排序规则 COLLATE = utf8mb4_general_ci
mysql> ALTER TABLE sbtest1 CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci , ALGORITHM=INPLACE, LOCK=NONE;
Query OK, 0 rows affected (17.06 sec)
Records: 0  Duplicates: 0  Warnings: 0

-- 验证是否可正常写入 emoji 了
mysql> insert into sbtest1(c) values ('');
Query OK, 1 row affected (0.01 sec)

-- 从 utf8mb4 --> utf8mb3， 锁表，不允许使用 INPLACE 方式
mysql> ALTER TABLE sbtest1 CONVERT TO CHARACTER SET utf8mb3 COLLATE utf8mb3_general_ci , ALGORITHM=INPLACE, LOCK=NONE;
ERROR 1846 (0A000): ALGORITHM=INPLACE is not supported. Reason: Cannot change column type INPLACE. Try ALGORITHM=COPY.

-- 先删除插入的 emoji 行数据，否则换位 utf8mb3 会报错
mysql> delete from sbtest1 where id > 2500000;
Query OK, 3 rows affected (0.01 sec)

-- 使用 copy 方式可以（整表锁表，无法变更操作，仅只读）
mysql> ALTER TABLE sbtest1 CONVERT TO CHARACTER SET utf8mb3 COLLATE utf8mb3_general_ci , ALGORITHM=COPY;
Query OK, 2500000 rows affected, 2 warnings (56.99 sec)
Records: 2500000  Duplicates: 0  Warnings: 2

mysql> insert into sbtest1(c) values ('');
ERROR 1366 (HY000): Incorrect string value: '\xF0\x9F\x98\x84' for column 'c' at row 1

2.表列转换

对某一列的字符集进行修改，表的字符集保持不动

需要重组数据，但不锁表

mysql> ALTER TABLE sbtest1 CHANGE c c char(120) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL ;
Query OK, 0 rows affected (15.90 sec)
Records: 0  Duplicates: 0  Warnings: 0
或
mysql> ALTER TABLE sbtest1 MODIFY COLUMN c char(120) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL;
Query OK, 0 rows affected (15.77 sec)
Records: 0  Duplicates: 0  Warnings: 0

-- 查看表结构
mysql> show create table sbtest1\G
*************************** 1. row ***************************
       Table: sbtest1
Create Table: CREATE TABLE `sbtest1` (
  `id` int NOT NULL AUTO_INCREMENT,
  `k` int NOT NULL DEFAULT '0',
  `c` char(120) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL,
  `pad` char(60) NOT NULL DEFAULT '',
  PRIMARY KEY (`id`),
  KEY `k_1` (`k`)
) ENGINE=InnoDB AUTO_INCREMENT=2500006 DEFAULT CHARSET=utf8mb3
1 row in set (0.00 sec)

-- 写入测试数据
mysql> insert into sbtest1(c) values ('');
Query OK, 1 row affected (0.01 sec)

3.表列字符集不一致，修改表字符集

表列字符集当前不一致，如某列为 utf8mb4 ，但表的字符集为 utf8mb3，这时候执行将表修改为 utf8mb3 的 CONVERT 转换会发生什么

mysql> ALTER TABLE sbtest1 CHANGE c c char(120) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL , ALGORITHM=INPLACE, LOCK=NONE;
Query OK, 0 rows affected (15.90 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> show create table sbtest1\G
*************************** 1. row ***************************
       Table: sbtest1
Create Table: CREATE TABLE `sbtest1` (
  `id` int NOT NULL AUTO_INCREMENT,
  `k` int NOT NULL DEFAULT '0',
  `c` char(120) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL,
  `pad` char(60) NOT NULL DEFAULT '',
  PRIMARY KEY (`id`),
  KEY `k_1` (`k`)
) ENGINE=InnoDB AUTO_INCREMENT=2500006 DEFAULT CHARSET=utf8mb3
1 row in set (0.00 sec)

mysql> ALTER TABLE sbtest1 CONVERT TO CHARACTER SET utf8mb3 COLLATE utf8mb3_general_ci , ALGORITHM=COPY;
Query OK, 2500000 rows affected, 2 warnings (59.24 sec)
Records: 2500000  Duplicates: 0  Warnings: 2

mysql> show create table sbtest1\G
*************************** 1. row ***************************
       Table: sbtest1
Create Table: CREATE TABLE `sbtest1` (
  `id` int NOT NULL AUTO_INCREMENT,
  `k` int NOT NULL DEFAULT '0',
  `c` char(120) CHARACTER SET utf8mb3 COLLATE utf8mb3_general_ci DEFAULT NULL,   -- 字符集被更改为与表设置的一致
  `pad` char(60) NOT NULL DEFAULT '',
  PRIMARY KEY (`id`),
  KEY `k_1` (`k`)
) ENGINE=InnoDB AUTO_INCREMENT=2500006 DEFAULT CHARSET=utf8mb3
1 row in set (0.00 sec)

问题结论

使用 ALTER TABLE xxx CONVERT TO CHARACTER SET 方式修改的进修改了元数据，不锁表，数据结构没有变化，但新增的列会根据新的字符集进行创建。

使用ALTER TABLE xxx CONVERT TO CHARACTER SET方式修改的，表进行了重组，列表字符集被刷新为同一的字符集，字符如果是从小扩大的话，不锁表，但从大缩小的话缩全表。避免缩全表的方式可以使用pt-osc或gh-osc方式进行处理。

表中有与表字符集不一致的列，若执行 CONVERT转换，则列字符集会被统一转换为设置的表字符集。

肥仔教程网

SEO 优化与 Web 开发技术学习分享平台

【藏经阁】表/列字符集转换（藏经阁经书）

环境