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
WHERE
	TABLE_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
WHERE
	TABLE_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;

通过批量生成执行脚本方式来修改，如
SELECT
	CONCAT( 'ALTER TABLE ', TABLE_NAME, ' DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;' ) 
FROM
	information_schema.`TABLES` 
WHERE
	TABLE_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=1
	and TABLE_SCHEMA = 'xxx' #要修改的数据库名称
	and DATA_TYPE = 'text'
	and COLLATION_NAME='utf8mb4_0900_ai_ci';