23. Mysql中的排序规则

Mysql中的排序规则

1. 数据库默认的排序规则

mysql8的默认排序方式是 utf8mb4_0900_ai_ci
mysql5的默认排序规则是 utf8mb4_general_ci
参考文章https://www.cnblogs.com/seasonhu/p/14994857.html
但是排序规则不具备强制性，如数据库排序规则设置了utf8mb4_0900_ai_ci，表排序规则同样可以设置utf8mb4_general_ci，字段还可以设置utf8mb4_0900_ai_ci

2. 查看表的排序规则

2.1 查看表排序规则

SELECT TABLE_CATALOG, TABLE_SCHEMA, TABLE_NAME, TABLE_COLLATION
FROM INFORMATION_SCHEMA.TABLES
WHERETABLE_SCHEMA = 'product'

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-5Tu2okCQ-1690508562014)(https://note.youdao.com/yws/res/24484/WEBRESOURCEc5d195a64a5d70357b5bcd3d27c2c677)]

2.2 查看字段排序规则

SELECT TABLE_CATALOG, TABLE_SCHEMA, TABLE_NAME, COLUMN_NAME, COLUMN_TYPE, COLLATION_NAME
FROM INFORMATION_SCHEMA.COLUMNS
WHERETABLE_SCHEMA = 'product'

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-W30iCB6S-1690508562015)(https://note.youdao.com/yws/res/24482/WEBRESOURCEa1b76e38dd03d8478191a648f8b22814)]

3.修改排序规则

3.1 修改库

 ALTER DATABASE product_dscc CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-OAZtzMYl-1690508562015)(https://note.youdao.com/yws/res/24464/WEBRESOURCEef9b00d7d17f4ecbbfe0b9bf069c8217)]

3.2 修改表

单个表
ALTER TABLE exc_code_value
DEFAULT CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci;通过批量生成执行脚本方式来修改，如
SELECTCONCAT( 'ALTER TABLE ', TABLE_NAME, ' DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;' ) 
FROMinformation_schema.`TABLES` 
WHERETABLE_SCHEMA = 'xxx';

3.3 修改字段

单个字段
ALTER TABLE `exc_warn_notice` MODIFY `id` varchar(32) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL;通过批量生成执行脚本方式来修改，如
SELECT CONCAT('ALTER TABLE `', table_name, '` MODIFY `', column_name, '` ', DATA_TYPE, '(', CHARACTER_MAXIMUM_LENGTH, ') CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci', (CASE WHEN IS_NULLABLE = 'NO' THEN ' NOT NULL' ELSE '' END),(case when IFNULL(column_comment,'')='' then '' else concat(' COMMENT \'' , column_comment ,'\'') end),';') as `sql`
FROM information_schema.COLUMNS
WHERE 1=1and TABLE_SCHEMA = 'xxx' #要修改的数据库名称and DATA_TYPE = 'text'and COLLATION_NAME='utf8mb4_0900_ai_ci';