mysql8 中文排序，中文拼音排序，批量修改排序规则

躁动的青年

已于 2023-11-08 17:50:41 修改

阅读量2.8k

点赞数

文章标签： mysql 批量修改排序规则中文排序中文拼音排序

于 2023-10-09 17:08:00 首次发布

本文链接：https://blog.csdn.net/haveqing/article/details/132411837

版权

一、中文排序规则，按中文拼音排序

CHARACTER_SET_NAME = 'utf8mb4'
COLLATION_NAME = 'utf8mb4_zh_0900_as_cs'

需要修改库的，表的，列的排序规则

二、修改效果

select * from test.test1 order by name;

修改之前中文排序乱序

修改之后中文按拼音排序

三、查看排序规则

查看排序规则命令（在查询窗口可执行）

mysql> show variables like 'collation%';
+----------------------+--------------------+
| Variable_name        | Value              |
+----------------------+--------------------+
| collation_connection | utf8mb4_0900_ai_ci |
| collation_database   | utf8mb4_0900_ai_ci |
| collation_server     | utf8mb4_0900_ai_ci |
+----------------------+--------------------+

查看my.cnf文件

[mysql]
default-character-set=utf8mb4
 
[mysqld]
# 服务器默认字符集。如果设置此变量，还应设置collation_server以指定字符集的排序规则。
character-set-server=utf8mb4
 
# 服务器的默认排序规则。
collation_server=utf8mb4_0900_ai_ci

四、修改数据库的排序规则

ALTER DATABASE test CHARACTER SET utf8mb4 COLLATE utf8mb4_zh_0900_as_cs;
show variables like 'collation%';

五、查看修改表的排序规则

查看表的排序规则

SELECT TABLE_CATALOG, TABLE_SCHEMA, TABLE_NAME, TABLE_COLLATION
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_SCHEMA = 'test' 
	and TABLE_TYPE='BASE TABLE'

修改表的默认排序规则

ALTER TABLE test.test1 DEFAULT CHARACTER SET = utf8mb4 COLLATE = utf8mb4_0900_ai_ci;

把表默认的字符集和所有字符列（CHAR,VARCHAR,TEXT）改为新的字符集：

ALTER TABLE tbl_name CONVERT TO CHARACTER SET character_name [COLLATE ...]
如：ALTER TABLE test1 CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci;

mysql修改表、字段、库的字符集

六、查看修改列的排序规则

查看列的排序规则

SELECT TABLE_CATALOG, TABLE_SCHEMA, TABLE_NAME, COLUMN_NAME, COLUMN_TYPE, COLLATION_NAME
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_SCHEMA = 'test'
    and TABLE_NAME='test1'

修改列的排序规则

包括是否可为NULL，默认值，列注释的处理

ALTER TABLE test.test1 MODIFY COLUMN name varchar(36) CHARACTER SET utf8mb4 COLLATE utf8mb4_zh_0900_as_cs NOT NULL comment '姓名';
ALTER TABLE test.test1 MODIFY COLUMN nick_name varchar(36) CHARACTER SET utf8mb4 COLLATE utf8mb4_zh_0900_as_cs default '小伙' comment '昵称';
ALTER TABLE test.test1 MODIFY COLUMN content text CHARACTER SET utf8mb4 COLLATE utf8mb4_zh_0900_as_cs comment '内容';

七、批量修改表的排序规则

SELECT TABLE_SCHEMA AS '数据库', TABLE_NAME AS '表', TABLE_COLLATION AS '原排序规则',
	CONCAT('ALTER TABLE ', TABLE_SCHEMA, '.', TABLE_NAME, ' DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_zh_0900_as_cs;') AS '修改排序的sql'
FROM information_schema.`TABLES`
WHERE TABLE_SCHEMA = 'test'
		and TABLE_TYPE='BASE TABLE'	-- 排除视图的
		AND TABLE_COLLATION ='utf8mb4_0900_ai_ci'; -- 查询需要修改的

八、批量修改列的排序规则

1、生成批量修改列的排序规则的sql

包括是否可为null，默认值，列注释的处理

SELECT TABLE_SCHEMA '库名', TABLE_NAME '表名', COLUMN_NAME '字段名', COLUMN_TYPE '字段类型', IS_NULLABLE '是否可为NULL', 
    COLUMN_KEY '主键,唯一索引,非唯一索引', COLUMN_COMMENT '字段注释', COLUMN_DEFAULT '字段默认值', CHARACTER_SET_NAME '原字符集', COLLATION_NAME '原排序规则', 
    CONCAT('ALTER TABLE ', TABLE_SCHEMA, '.', TABLE_NAME, ' MODIFY COLUMN `', COLUMN_NAME, '` ', COLUMN_TYPE,' CHARACTER SET utf8mb4 COLLATE utf8mb4_zh_0900_as_cs ',IF(IS_NULLABLE = 'NO', 'NOT NULL ', ''),IF(COLUMN_DEFAULT is null, '', CONCAT('DEFAULT \'', COLUMN_DEFAULT, '\' ')), 'COMMENT \'',COLUMN_COMMENT, '\';') '修改排序规则sql'
FROM information_schema.`COLUMNS`
WHERE TABLE_SCHEMA='test'  
-- and TABLE_NAME='test1'
	and CHARACTER_SET_NAME='utf8mb4'
    and COLLATION_NAME ='utf8mb4_0900_ai_ci'	-- 查询需要修改的
	-- and COLLATION_NAME ='utf8mb4_general_ci' 
ORDER BY TABLE_NAME, ORDINAL_POSITION;

其中：and CHARACTER_SET_NAME='utf8mb4' and COLLATION_NAME = 'utf8mb4_0900_ai_ci'，只处理有排序规则的，需要修改的。

2、生成批量修改列的排序规则的sql（表中文名，排除视图）

SELECT c.TABLE_SCHEMA '库名', t.TABLE_COMMENT AS '表中文名', c.TABLE_NAME AS '表英文名', COLUMN_NAME '字段名', COLUMN_TYPE '字段类型', IS_NULLABLE '是否可为NULL', 
    COLUMN_KEY '主键,唯一索引,非唯一索引', COLUMN_COMMENT '字段注释', COLUMN_DEFAULT '字段默认值', CHARACTER_SET_NAME '原字符集', COLLATION_NAME '原排序规则', 
    CONCAT('ALTER TABLE ', c.TABLE_SCHEMA, '.', c.TABLE_NAME, ' MODIFY COLUMN `', COLUMN_NAME, '` ', COLUMN_TYPE,' CHARACTER SET utf8mb4 COLLATE utf8mb4_zh_0900_as_cs ',IF(IS_NULLABLE = 'NO', 'NOT NULL ', ''),IF(COLUMN_DEFAULT is null, '', CONCAT('DEFAULT \'', COLUMN_DEFAULT, '\' ')), 'COMMENT \'',COLUMN_COMMENT, '\';') '修改排序规则sql'
FROM information_schema.`COLUMNS` c
INNER JOIN information_schema.`TABLES` t on c.TABLE_SCHEMA=t.TABLE_SCHEMA and c.TABLE_NAME=t.TABLE_NAME and t.TABLE_TYPE='BASE TABLE'
WHERE c.TABLE_SCHEMA='test'  
-- and c.TABLE_NAME='test1'
	and CHARACTER_SET_NAME='utf8mb4'
    and COLLATION_NAME ='utf8mb4_0900_ai_ci'	-- 查询需要修改的
	-- and COLLATION_NAME ='utf8mb4_general_ci' 
ORDER BY c.TABLE_NAME, ORDINAL_POSITION;

九、通过重建数据库修改排序规则

手动替换排序规则，然后再导入数据。

可以考虑在导出的表结构上手动替换排序规则。之后再导入数据。

1、导出数据库（导出表结构和数据）

mysqldump -h127.0.0.1 -uroot -ppassword --databases test --triggers --routines --events > /backup/mysql/test.sql

导出的文件查看是乱码，可以选择使用Notpad++查看，或者用编辑器文件--打开，选择符集打开。

2、导出数据库（只导出表结构）

-d, --no-data No row information.

mysqldump -h127.0.0.1 -uroot -ppassword --databases test --no-data --triggers --routines --events > /backup/mysql/test-create.sql

3、导出数据库（只导出数据）

-t, --no-create-info Don't write table creation info.

mysqldump -h127.0.0.1 -uroot -ppassword --databases test --no-create-info --triggers --routines --events > /backup/mysql/test-data.sql

4、用命令直接导入sql

mysql -h127.0.0.1 -P3306 -uroot -ppassword < /backup/mysql/test.sql
mysql -h127.0.0.1 -P3306 -uroot -ppassword -Dtest < /backup/mysql/test.sql

不要使用-Dmysql，如果备份的sql里没有建库语句，容易误把脚本导入到mysql库。

5、连接mysql客户端，用source命令导入sql

mysql -h127.0.0.1 -uroot -ppassword
mysql>use test
mysql>source /backup/mysql/test.sql

十、报错处理

ALTER TABLE escp.chailv_consumption_detail CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_zh_0900_as_cs
> 1118 - Row size too large. The maximum row size for the used table type, not counting BLOBs, is 65535. This includes storage overhead, check the manual. You have to change some columns to TEXT or BLOBs
> 时间: 0.056s

说明转换后，列太宽了。