如何设置MySQL的字符集编码？

夏芳君_4489

发布时间：2025-07-04 18:50:02

597人浏览过

来源于php中文网

原创

要解决mysql字符集乱码问题，需确保服务器、数据库、表和客户端连接的编码一致，并使用utf8mb4。1. 修改mysql配置文件my.cnf/my.ini，在[mysqld]段落添加character_set_server=utf8mb4和collation_server=utf8mb4_unicode_ci，并在[client]与[mysql]段落设置default-character-set=utf8mb4，保存后重启mysql服务；2. 创建或修改数据库时指定character set utf8mb4和collate utf8mb4_unicode_ci；3. 创建或修改表时同样指定utf8mb4字符集，对已有表可使用alter table convert to命令转换数据；4. 客户端连接后执行set names 'utf8mb4'，或在连接字符串中指定charset=utf8mb4。乱码通常源于配置未生效、旧数据未转换、客户端编码不匹配或误用utf8（仅支持3字节字符）。应优先使用utf8mb4以支持完整utf-8字符集，包括表情符号。批量修改现有数据库或表字符集时，可通过sql生成alter语句操作，但务必提前备份并测试，避免影响生产环境。

如何设置MySQL的字符集编码？

设置MySQL的字符集编码，核心在于确保服务器、数据库、表以及客户端连接的编码一致性，通常推荐使用utf8mb4来避免乱码问题，特别是对于需要支持表情符号等更广泛字符的场景。

解决方案

要正确设置MySQL的字符集编码，你需要从几个层面进行调整：

1. 服务器层面（全局配置）

这是最基础也最关键的一步。修改MySQL的配置文件my.cnf (Linux/macOS) 或 my.ini (Windows)。具体位置可能因安装方式而异，但通常在/etc/mysql/my.cnf、/etc/my.cnf或MySQL安装目录下的support-files或bin目录中。

找到或添加以下配置项，推荐在[mysqld]段落下：

[mysqld]
character_set_server=utf8mb4
collation_server=utf8mb4_unicode_ci
# 以下两项通常会继承server的设置，但明确指定更稳妥
character_set_database=utf8mb4
collation_database=utf8mb4_unicode_ci

[client]
default-character-set=utf8mb4

[mysql]
default-character-set=utf8mb4

保存文件后，务必重启MySQL服务才能使配置生效。例如在Linux上：sudo systemctl restart mysql 或 sudo service mysql restart。

2. 数据库层面

创建新数据库时指定字符集：

CREATE DATABASE your_database_name
CHARACTER SET utf8mb4
COLLATE utf8mb4_unicode_ci;

对于已存在的数据库，可以修改其字符集（注意，这不会改变已有表的字符集）：

ALTER DATABASE your_database_name
CHARACTER SET utf8mb4
COLLATE utf8mb4_unicode_ci;

3. 表层面

创建新表时指定字符集：

CREATE TABLE your_table_name (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(255)
) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

对于已存在的表，修改其字符集（这会转换表数据，需要时间且有风险，务必备份）：

ALTER TABLE your_table_name
CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

如果只修改表的默认字符集而不转换数据，可以使用：

ALTER TABLE your_table_name
DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

但通常我们希望数据也一并转换。

4. 连接层面（客户端）

即使服务器、数据库和表都设置正确，如果客户端连接时使用的字符集不匹配，依然可能出现乱码。

在应用程序连接MySQL后，立即执行以下SQL语句：

SET NAMES 'utf8mb4';

这会告诉MySQL客户端希望使用utf8mb4编码进行数据传输。大多数现代编程语言的MySQL驱动都支持在连接字符串中指定字符集，例如：

PHP (PDO): new PDO("mysql:host=localhost;dbname=testdb;charset=utf8mb4", $user, $pass);
Python (mysql-connector-python): cnx = mysql.connector.connect(user='root', database='test', charset='utf8mb4')
Java (JDBC): jdbc:mysql://localhost:3306/test?useUnicode=true&characterEncoding=utf8mb4

为什么我的MySQL字符集总是乱码？

说实话，乱码这事儿，十有八九是字符集设置不一致导致的“蝴蝶效应”。我见过太多开发者在这上面栽跟头，包括我自己。最常见的原因，我觉得，就是没有从头到尾把所有环节都考虑进去。

一个常见的误区是，很多人以为只改了my.cnf就万事大吉了，或者只在CREATE DATABASE的时候指定了，结果发现写入的数据还是乱码。这通常是因为：

Mysql Skill 1.0.0 (3)

MySQL 数据库管理技能，能够通过自然语言查询和管理数据库，支持 SELECT/INSERT/UPDATE/DELETE、表管理、备份恢复等操作。当用户提及 MySQL、数据库查询、建表或数据备份时使用此技能。

下载

服务器配置未生效或被覆盖： 你可能改了my.cnf，但忘了重启MySQL服务，或者MySQL启动时加载了另一个配置文件。有时候，一些发行版的MySQL会有多个配置文件片段，容易混淆。
旧数据的问题： 数据库或表在创建时使用了错误的字符集，即使你后来改了服务器配置，已存在的数据并不会自动转换。新的数据可能没问题，但旧数据依然是“天书”。
客户端连接编码不匹配： 这是最隐蔽也最常见的陷阱之一。你的应用程序在连接MySQL时，没有明确告诉MySQL它将使用什么编码发送和接收数据。MySQL默认的编码可能不是你想要的，于是数据在传输过程中就被“误解”了。比如，你的应用是UTF-8，但连接是latin1，那数据一进一出就全乱了。
utf8与utf8mb4的混淆： MySQL的utf8实现并非完整的UTF-8，它只支持最多3字节的UTF-8字符。这意味着像表情符号（emoji）或一些不常用的汉字（扩展区）这些4字节的字符，用utf8存储就会出问题，要么变问号，要么直接报错。很多人不清楚这个历史遗留问题，依然在使用utf8。
数据导入导出问题： 如果你是从一个旧数据库导入数据，或者从一个文本文件导入，而这个文件本身的编码与目标数据库不一致，那么导入后数据就已经是错的了。

诊断乱码问题，我通常会先用SHOW VARIABLES LIKE 'character_set%';和SHOW VARIABLES LIKE 'collation%';来检查当前会话和全局的字符集设置。然后，我会检查SHOW CREATE DATABASE your_db_name;和SHOW CREATE TABLE your_table_name;来确认数据库和表的实际字符集。最后，我会看应用程序的代码，确保连接字符串或SET NAMES命令是正确的。这个排查过程，其实就是一层层剥洋葱，总能找到症结所在。

utf8和utf8mb4有什么区别，我该用哪个？

这个问题，是MySQL字符集里一个非常重要的“坑”，也是很多乱码问题的根源。简单来说，MySQL的utf8和标准的UTF-8并不是一回事。

区别：

MySQL的utf8： 这是MySQL历史版本遗留下来的一个实现，它只支持每个字符最多占用3个字节的UTF-8编码。这意味着，它无法存储那些需要4个字节来表示的Unicode字符，比如我们现在日常生活中大量使用的表情符号（emoji），或者一些不常见的汉字、特殊符号等。如果你尝试将这些4字节字符插入到utf8编码的字段中，MySQL会报错或者直接将其截断、替换为问号。
utf8mb4： 这个才是MySQL对完整UTF-8编码的支持。utf8mb4中的mb4代表“most bytes 4”，表示它能够存储每个字符最多占用4个字节的UTF-8编码。因此，utf8mb4能够完整地支持Unicode标准中的所有字符，包括表情符号。

我该用哪个？

毫无疑问，你应该始终使用utf8mb4。

在现在这个时代，表情符号、各种语言的字符混合使用已经是常态。如果你还在用utf8，迟早会遇到存储问题。对于任何新的项目，从一开始就将服务器、数据库、表和连接都设置为utf8mb4_unicode_ci（或utf8mb4_general_ci，但unicode_ci通常更推荐，因为它基于Unicode标准，排序规则更准确，尽管性能略有牺牲，但对大多数应用来说影响不大）是最佳实践。

对于旧项目，如果遇到表情符号存储问题，或者未来有这方面的需求，那么将数据库和表从utf8迁移到utf8mb4是必须的。这个过程虽然可能有些繁琐，涉及到ALTER TABLE操作，需要备份数据，但长远来看是值得的。

如何批量修改现有数据库或表的字符集？

批量修改现有数据库或表的字符集，听起来像是要写个脚本，但其实SQL本身就能做到，只是你需要理解其背后的原理和潜在的风险。我个人在做这种操作时，总是提心吊胆，生怕数据丢失或损坏，所以数据备份是第一位的，永远是！

1. 批量修改数据库的默认字符集：

如果你想修改某个数据库下所有新建表将默认使用的字符集，可以这样做：

ALTER DATABASE your_database_name CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

注意： 这条命令只会影响后续在该数据库中创建的表的默认字符集，并不会改变数据库中现有表的字符集。

2. 批量修改表的字符集（包括数据转换）：

这是最关键也最复杂的一步，因为它会涉及到表的重建和数据的转换。

针对单个表：
```
ALTER TABLE your_table_name CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
```
这条命令会读取表中的所有数据，将其按照新的字符集编码重新写入，然后重建表。对于大表来说，这是一个耗时且资源密集的操作，可能会导致长时间的表锁定。
针对所有表（通过脚本）：

如果你有大量的表需要修改，手动一条条执行是不现实的。这时就需要编写一个脚本来自动生成并执行ALTER TABLE语句。以下是一个简单的SQL查询，可以帮助你生成这些语句：
```
SELECT CONCAT('ALTER TABLE ', table_schema, '.', table_name, ' CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;') AS alter_sql
FROM information_schema.tables
WHERE table_schema = 'your_database_name'
  AND table_type = 'BASE TABLE'
  AND table_collation != 'utf8mb4_unicode_ci';
```
运行这条查询，你会得到一系列的ALTER TABLE语句。你可以将这些语句复制出来，然后在一个安全的维护窗口期执行。

重要提示：
- 备份！备份！备份！ 重要的事情说三遍。在执行任何ALTER TABLE操作之前，务必对数据库进行完整备份。
- 测试环境先行： 绝不要在生产环境直接操作，务必先在测试环境进行充分测试，确保数据转换正确，没有乱码或数据丢失。
- 考虑停机维护： ALTER TABLE操作会锁定表，导致业务中断。对于核心业务系统，需要安排停机维护窗口。
- 分批执行： 如果表很多，或者有特别大的表，可以考虑分批执行，或者使用pt-online-schema-change等工具进行在线修改，以减少对业务的影响。这些工具可以在不锁定表的情况下进行模式修改，但配置和使用会更复杂一些。