mysql|character set和collate,utf8mb4_general_ci 和 utf8_general_ci 有什么区别

最近在线上遇到sql字符集无法转换的问题,查了下相关资料

Error 3988(HY000):Conversion from collation utf8mb4_0900_ai_ci into utf8_general_ci impossible for parameter

对比

utf8mb4_general_ci 和 utf8_general_ci 是 MySQL 中两种不同的字符集和校对规则组合，主要区别如下：

字符集范围

utf8_general_ci
基于 utf8 字符集，支持 3 字节 Unicode 字符，能覆盖大部分常用语言（如中文、日文、韩文等），但无法存储 4 字节字符（如表情符号、部分生僻字）。
utf8mb4_general_ci
基于 utf8mb4 字符集，支持 4 字节 Unicode 字符，包含所有 utf8 的字符，同时额外支持表情符号、部分东亚语言生僻字等。

校对规则
general_ci
两者的校对规则相同（general_ci），均表示不区分大小写（如 A 和 a 视为相同），且排序规则基于简单的字母顺序。
适用场景
utf8_general_ci
适用于仅需存储 3 字节字符的场景（如常规文字内容）。
utf8mb4_general_ci
必须用于需要存储 4 字节字符的场景（如表情符号、某些生僻字），否则可能导致存储错误或乱码。
注意事项
存储占用：utf8mb4 每个字符最多占用 4 字节，比 utf8 的 3 字节略多，但现代数据库中影响通常可忽略。
兼容性：若历史数据使用 utf8，需手动升级表和字段的字符集到 utf8mb4，避免数据丢失或错误。
总结
优先使用 utf8mb4_general_ci：除非明确不需要 4 字节字符，否则建议统一使用该组合，以确保兼容性和扩展性。

避免混用：确保数据库、表、字段的字符集和校对规则一致，避免因不一致导致的排序或存储问题。

character set和collate

character set

MySQL的字符集（Character Set）定义了字符的编码规则，确保数据存储、传输和处理的正确性。

常见字符集类型:

utf8, 作用：支持大部分Unicode字符（3字节编码），适用于多语言环境。限制：无法存储4字节的字符（如emoji表情）。
utf8mb4,作用：utf8的扩展，支持4字节Unicode字符（如emoji），是MySQL 5.5.3+的推荐字符集。
latin1, 作用：默认字符集，支持西欧语言（如英文、法文），单字节编码，存储效率高但不支持中文。
gbk,作用：支持简体中文，双字节编码，兼容gb2312

作用:

存储字符：将字符转换为二进制数据存储。
兼容多语言：支持不同语言的字符显示（如中文、日文、Emoji）。
避免乱码：通过统一编码规则，确保数据输入、输出的一致性。

collate

Specifies rules for sorting and comparing characters (e.g., case sensitivity, accent handling).
COLLATE（排序规则）是用于排序和比较字符的规则（例如，区分大小写、重音处理）,它与字符集（CHARACTER SET）紧密相关，共同确保数据在多语言环境下的正确性和一致性。

作用

定义字符比较规则

是否区分大小写：

_ci（Case Insensitive，如utf8mb4_general_ci）：不区分大小写，’A’和’a’视为相同。
_cs（Case Sensitive，如utf8mb4_general_cs）：区分大小写。

是否区分重音/变音符号：

某些规则（如utf8mb4_unicode_ci）会忽略重音，例如’café’和’cafe’可能被视为相同。

二进制比较：

_bin（如utf8mb4_bin）直接按字符的二进制编码比较，区分大小写和所有特殊字符。

控制排序顺序

影响ORDER BY的结果，例如：

-- 使用不区分大小写的排序规则
SELECT * FROM users ORDER BY name COLLATE utf8mb4_general_ci;
-- 结果可能将 'apple'、'Apple'、'APPLE' 视为相同并排序在一起

影响字符串匹配

决定WHERE条件、LIKE、DISTINCT、GROUP BY的行为。例如：

-- 使用区分大小写的规则
SELECT * FROM products WHERE name COLLATE utf8mb4_bin = 'iPhone';
-- 仅匹配精确的 'iPhone'，不会匹配 'iphone' 或 'IPHONE'

COLLATE的优先级

优先级顺序（从高到低）：

SQL语句级（直接在查询中指定） → 列级 → 表级 → 数据库级 → 服务器级

如:

CREATE TABLE users (
    id INT,
    username VARCHAR(50) COLLATE utf8mb4_bin  -- 列级设置（覆盖表级规则）
) DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;  -- 表级默认规则

最佳实践

默认推荐：

字符集：utf8mb4（支持4字节字符，如Emoji）。
排序规则：utf8mb4_unicode_ci（多语言场景）或utf8mb4_0900_ai_ci（MySQL 8.0+，更符合Unicode 9.0标准）。

一致性原则：

确保数据库、表、列的字符集和COLLATE一致，避免隐式转换带来的性能损耗。

谨慎选择_bin规则：

仅在明确需要区分大小写或二进制比较时使用，因其可能导致查询复杂度增加。