官方文档：
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