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

 

上一篇:杂项专题-学习笔记:模拟退火


下一篇:C# winform 将秒转化成时分秒