MySQL修改排序规则是否一定重建表

官方文档：

alter table:

https://dev.mysql.com/doc/refman/5.7/en/alter-table.html

 

online ddl:

https://dev.mysql.com/doc/refman/5.7/en/innodb-online-ddl-operations.html

 

其中都没有讲明修改整个表排序规则，或某个字段排序规则是否需要重建表。

根据 innodb 行存储格式判断，innodb 底层存储时并没有排序规则的概念，但是在创建索引时，确实会受到排序规则影响。

理论上来说，只要要修改排序规则的表里，不涉及到索引的排序规则，重新排列，即不用重建表。

 

测试如下：

生成批量测试数据，

create table t1(id int primary key auto_increment,name varchar(200));

mysql> insert into t1 select null,repeat('a',200);
Query OK, 1 row affected (0.01 sec)
Records: 1  Duplicates: 0  Warnings: 0
.....
填充大量数据
mysql> insert into t1 select null,repeat('a',200) from t1;
Query OK, 1048576 rows affected (6.80 sec)
Records: 1048576  Duplicates: 0  Warnings: 0

 

查看列排序规则

mysql> show full columns from t1;
+-------+--------------+-----------------+------+-----+---------+----------------+---------------------------------+---------+
| Field | Type         | Collation       | Null | Key | Default | Extra          | Privileges                      | Comment |
+-------+--------------+-----------------+------+-----+---------+----------------+---------------------------------+---------+
| id    | int          | NULL            | NO   | PRI | NULL    | auto_increment | select,insert,update,references |         |
| name  | varchar(200) | utf8_general_ci | YES  |     | NULL    |                | select,insert,update,references |         |
+-------+--------------+-----------------+------+-----+---------+----------------+---------------------------------+---------+
2 rows in set (0.00 sec)

 

修改表排序规则实验：

可以看到确实重建表了，我感觉不用重建表也是可以的，id-int 类型，并不受排序规则影响，name 列并没有索引，底层数据存储也不会受排序规则影响。

mysql> alter table t1 convert to character set  utf8mb4 collate utf8mb4_bin;
Query OK, 0 rows affected (3.61 sec)
Records: 0  Duplicates: 0  Warnings: 0


mysql> show processlist;
+-----+-----------------+-----------------+-------+---------+---------+------------------------+-----------------------------------------------------------------------------+
| Id  | User            | Host            | db    | Command | Time    | State                  | Info                                                                        |
+-----+-----------------+-----------------+-------+---------+---------+------------------------+-----------------------------------------------------------------------------+
|   5 | event_scheduler | localhost       | NULL  | Daemon  | 1012398 | Waiting on empty queue | NULL                                                                        |
|  18 | root            | 127.0.0.1:50427 | NULL  | Sleep   |     437 |                        | NULL                                                                        |
|  19 | root            | 127.0.0.1:50428 | NULL  | Sleep   |     437 |                        | NULL                                                                        |
| 119 | root            | 127.0.0.1:55559 | ceshi | Query   |       3 | altering table         | alter table t1 convert to character set  utf8mb4 collate utf8mb4_bin        |
| 121 | root            | 127.0.0.1:55821 | NULL  | Query   |       0 | init                   | show processlist                                                            |
+-----+-----------------+-----------------+-------+---------+---------+------------------------+-----------------------------------------------------------------------------+
5 rows in set (0.00 sec)

 

修改列排序规则实验：

现在 name 列是没有索引的，可以看到修改排序规则是马上修改，并没有重建表，应该是只修改数据字典。

mysql> alter table t1 modify name varchar(200) collate utf8mb4_bin;
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> alter table t1 modify name varchar(200) collate utf8mb4_general_ci;
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

 

为 name 列加上索引，再修改排序规则：

从结果看，应该是重建了表。

mysql> alter table t1 add index idx_name(name);
Query OK, 0 rows affected (27.90 sec)
Records: 0 Duplicates: 0 Warnings: 0

mysql> show full columns from t1;
+-------+--------------+--------------------+------+-----+---------+----------------+---------------------------------+---------+
| Field | Type         | Collation          | Null | Key | Default | Extra          | Privileges                      | Comment |
+-------+--------------+--------------------+------+-----+---------+----------------+---------------------------------+---------+
| id    | int          | NULL               | NO   | PRI | NULL    | auto_increment | select,insert,update,references |         |
| name  | varchar(200) | utf8mb4_general_ci | YES  | MUL | NULL    |                | select,insert,update,references |         |
+-------+--------------+--------------------+------+-----+---------+----------------+---------------------------------+---------+
2 rows in set (0.00 sec)

mysql> alter table t1 modify name varchar(200) collate utf8mb4_bin;
Query OK, 2097152 rows affected (12.57 sec)
Records: 2097152  Duplicates: 0  Warnings: 0