MySQL修改排序规则是否一定重建表
2022/1/8 19:06:19
本文主要是介绍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
这篇关于MySQL修改排序规则是否一定重建表的文章就介绍到这儿,希望我们推荐的文章对大家有所帮助,也希望大家多多支持为之网!
- 2024-05-29阿里 Canal 实时同步 MySQL 增量数据至 ClickHouse 数据库
- 2024-05-24在Linux下管理MySQL的大小写敏感性
- 2024-04-26MySQL查出时间比实际晚8小时的解决方案
- 2024-04-01JPA不识别MySQL的枚举类型
- 2024-03-30mysql数据库表卡死解决方法
- 2024-03-15MySQL多数据源笔记5-ShardingJDBC实战
- 2024-03-11natural join mysql
- 2024-03-11关于VS2017,VS2015 中利用 EF使用Mysql 不显示数据源问题解决方案
- 2024-02-26mysql 阿里云xb后缀备份文件恢复-icode9专业技术文章分享
- 2024-02-22docker mysql 5.7