MySQL - 字符集

MySQL - 字符集和排序规则

理解字符集和排序规则

这是在数据库中正确处理文本数据的基本概念。正确设置它至关重要，以避免数据损坏或意料之外的排序行为。

字符集： 一组符号及其编码。它定义了可以存储哪些字符（字母、数字、符号）。可以将其视为允许字符的字典。
排序规则： 一组用于在字符集中比较和排序字符的规则。它定义了比较是否区分大小写（_cs）、不区分大小写（_ci）或二进制（_bin），并处理特定语言的排序规则。

例如，utf8mb4 字符集可以存储大量的 Unicode 字符。utf8mb4_0900_ai_ci 排序规则告诉 MySQL 以一种不区分重音符号（ai）和不区分大小写（ci）的方式比较 utf8mb4 字符串。

`utf8mb4` 最佳实践

在现代 Web 开发中，标准且强烈推荐的字符集是 utf8mb4。

重要提示： 不要使用旧的 utf8 字符集。

MySQL 中的旧版 utf8 字符集（它是 utf8mb3 的别名）只支持 Unicode 字符的一个子集，并且每个字符最多使用三个字节。这意味着它无法存储许多表情符号（例如 👍）或某些亚洲语言字符等 4 字节字符。使用 utf8 可能会导致数据截断和错误。

utf8mb4 每个字符最多使用四个字节，并完全支持整个 Unicode 标准。新项目始终使用 utf8mb4。

显示可用字符集

您可以使用 SHOW CHARACTER SET 语句列出 MySQL 服务器支持的所有字符集。

SHOW CHARACTER SET;

要查找特定字符集的排序规则，您可以使用 LIKE 子句：

SHOW COLLATION LIKE 'utf8mb4%';

现代 MySQL (8.0+) 中 utf8mb4 的默认排序规则是 utf8mb4_0900_ai_ci，这对于大多数多语言应用程序来说非常出色。

设置字符集

SET NAMES 或 SET CHARACTER SET 语句用于配置当前客户端连接的字符集。这确保了从您的应用程序发送到 MySQL 的数据被正确解释。

语法

-- 这是设置连接字符集的推荐命令
SET NAMES 'utf8mb4' COLLATE 'utf8mb4_0900_ai_ci';

运行 SET NAMES 'utf8mb4' 是一个快捷方式，它设置了三个会话变量：character_set_client、character_set_connection 和 character_set_results。这对于您的应用程序数据库驱动程序在连接时执行此操作至关重要。

验证

连接后您可以验证会话变量：

SHOW VARIABLES LIKE 'character_set%';

您应该看到客户端、连接和结果变量都显示 utf8mb4。

配置最佳实践

为保持一致性，您应该在堆栈的每个级别配置 utf8mb4。

服务器级别： 在您的 MySQL 配置文件（my.cnf 或 my.ini）中，为所有新数据库设置默认值。 [mysqld] character-set-server=utf8mb4 collation-server=utf8mb4_0900_ai_ci
数据库级别： 创建数据库时，明确定义其字符集和排序规则。 CREATE DATABASE my_app_db CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci;
表级别： 表将从数据库继承，但您可以覆盖它。 CREATE TABLE my_table (...) DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci;
连接级别： 确保您的应用程序数据库连接器配置为使用 utf8mb4。大多数现代库默认或通过连接字符串中的简单 charset=utf8mb4 参数来实现这一点。

转换现有表

如果您需要将旧表（例如，从 latin1 或 utf8）转换为 utf8mb4，可以使用 ALTER TABLE 语句。在执行此操作之前，请务必备份您的数据。

示例：转换 `posts` 表

-- 步骤 1：转换表的默认字符集和排序规则
ALTER TABLE posts CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci;

-- 注意：`CONVERT TO` 子句也会将所有字符列（CHAR, VARCHAR, TEXT）
-- 转换为新的字符集。这是执行转换最安全的方法。

转换后，如果某些 VARCHAR 列是键的一部分，您可能需要调整它们的长度，因为 utf8mb4 字符可能占用更多空间，这可能会超出索引长度限制。