一、字符集和校对规则

在 MariaDB 中，字符集（Character Set）和校对规则（Collation）是确保数据正确存储和查询的关键配置，正确配置字符集可避免数据损坏和查询异常，避免乱码或者字符不兼容问题，是数据库设计的关键步骤。

1. 字符集（Character Set）
   定义数据库支持的字符集合（如 utf8mb4、latin1），决定可存储的字符范围（如多语言支持）。
2. 校对规则（Collation）
   定义字符的排序和比较规则（如是否区分大小写、重音敏感等）。每个字符集对应多个校对规则（如 utf8mb4_general_ci）。

二、查看字符集与校对规则

1. 查看支持的字符集

SHOW CHARACTER SET;

# 过滤结果
SHOW CHARACTER SET LIKE 'utf8%';

输出示例：

Charset    Description                 Default collation  
-----------------------------------------------------------
utf8mb4    UTF-8 Unicode               utf8mb4_general_ci  
latin1     cp1252 West European        latin1_swedish_ci

2. 查看支持的校对规则

SHOW COLLATION LIKE 'utf8mb4%';

输出示例：

Collation            Charset    Id  Default  Compiled  
-------------------------------------------------------
utf8mb4_general_ci   utf8mb4    45  Yes      Yes  
utf8mb4_bin          utf8mb4    46           Yes

3. 查看当前配置

# 查看服务器级默认字符集
SHOW VARIABLES LIKE 'character_set_server';

# 查看数据库级字符集
SHOW CREATE DATABASE mydb;

# 查看表级字符集
SHOW CREATE TABLE users;

# 查看连接字符集（客户端与服务器交互设置）
SHOW VARIABLES LIKE 'character_set%';
SHOW VARIABLES LIKE 'collation%';

三、设置字符集

1.服务器级默认配置

修改配置文件（/etc/my.cnf 或 /etc/my.cnf.d/server.cnf）：

[mysqld]
character-set-server = utf8mb4
collation-server = utf8mb4_unicode_ci

重启生效：

sudo systemctl restart mariadb

2.创建数据库时指定

CREATE DATABASE mydb
    CHARACTER SET utf8mb4
    COLLATE utf8mb4_unicode_ci;

3.创建表时指定

CREATE TABLE users (
    id INT PRIMARY KEY,
    name VARCHAR(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci
    ) DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

4.修改已有数据库/表

-- 修改数据库字符集
ALTER DATABASE mydb CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

-- 修改表字符集（仅新数据生效，已有数据需转换）
ALTER TABLE users CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

5.客户端连接配置

确保客户端连接时使用与服务器一致的字符集，可在配置文件中添加：

[client]
default-character-set = utf8mb4

或在连接时指定：

SET NAMES 'utf8mb4';

四、字符集推荐

1.优先选择utf8mb4

• 支持范围：完整的 Unicode 字符（包括表情符号），避免 utf8 的阉割版问题。
• 校对规则：utf8mb4_unicode_ci：更准确的 Unicode 排序（推荐）。utf8mb4_general_ci：更快但排序稍粗略。

2.示例场景

五、常见问题

1.乱码问题

• 原因：客户端、服务器、连接字符集不一致。
• 解决：确保配置文件中 [mysqld]、[client] 的字符集一致。连接后执行 SET NAMES 'utf8mb4';。

2.字符集转换失败

• 错误示例：

 1267 (HY000): Illegal mix of collations (latin1_swedish_ci,IMPLICIT) and (utf8mb4_unicode_ci,COERCIBLE)

• 解决：统一相关表/列的字符集和校对规则。

3.Emoji 存储失败

• 原因：使用 utf8 而非 utf8mb4。
• 解决：将字符集升级至 utf8mb4。

六、操作总结

最佳实践

• 统一字符集：从服务器到表、列，所有层级保持字符集一致。
• 提前规划：在创建数据库时明确指定字符集，避免后续转换。
• 测试验证：插入特殊字符测试存储和查询是否正常。