MySQL修改字符集步骤详解

在实际应用中,如果一开始没有正确的设置字符集,在运行一段时间以后,才发现当前字符集不能满足要求,需要进行调整,但又不想丢弃这段时间的数据,这个时候就需要修改字符集。

在《MySQL设置默认字符集和校对规则》一节我们讲到,ALTER DATABASE 或 ALTER TABLE 命令对已经存在的数据没有作用,只对新创建的表或记录生效。如果想修改已存在数据的字符集,需要先将数据导出,经过适当的调整后,再重新导入。

例 1

以下模拟的是将 gb2312 字符集的数据库修改成 gbk 字符集的数据库的过程。

1)创建 testset 数据库,设置其字符集为 gb2312,并添加数据。
mysql> CREATE TABLE test.testset(
    -> id INT(11) DEFAULT NULL,
    -> name VARCHAR(25) DEFAULT NULL
    -> )CHARSET=gb2312;
Query OK, 0 rows affected (0.10 sec)

mysql> INSERT INTO test.testset VALUES (1,'C语言');
Query OK, 1 row affected (0.01 sec)

mysql> INSERT INTO test.testset VALUES (2,'Java语言');
Query OK, 1 row affected (0.01 sec)

mysql> INSERT INTO test.testset VALUES (3,'Python语言');
Query OK, 1 row affected (0.01 sec)
2)导出 testset 表结构,命令如下:

mysqldump -uroot -p --default-character-set=gbk -d test testset> D:\testset.sql

其中,--default-character-set=gbk 表示以什么字符集连接;-d 表示只导出表结构,不导出数据。

3)打开 testset.sql 文件,修改表结构定义中的字符集为新的字符集,如下图所示。

修改表结构的字符集

4)确保表中的记录不再更新,导出所有记录。

mysqldump -uroot -p --quick --no-create-info --extended-insert --default-character-set=gb2312 test testset> D:\testdata.sql

  • --quick:该选项用于存储记录多的表。它强制 mysqldump 从服务器一次一行地查询表中的行,而不是查询所有行,并在输出前将它缓存到内存中。
  • --extended-insert:使用 INSERT 插入多行数据语法。可以使文件更小,导入文件时加速插入。
  • --no-create-info:不导出表的 CREATE TABLE 语句。
  • --default-character-set=gb2312:按照原有的字符集导出所有数据。这样导出的文件中,所有中文都是可见的,不会保存成乱码。

5)打开 testdata.sql,将 SET NAMES gb2312 修改成 SET NAMES gbk,如下图所示。


6)使用新的字符集创建新的数据库。

CREATE DATABASE test2 DEFAULT CHARSET gbk;


7)创建表,执行 testset.sql。

mysql -uroot -p test2 < D:\testset.sql


8)导入数据,执行 testdata.sql。

mysql -uroot -p test2 < D:\testdata.sql


9)查看 testset 表结构是否修改了字符集,以及表内数据是否丢失或乱码,SQL 语句和运行结果如下:
mysql> SHOW CREATE TABLE test2.testset \G
*************************** 1. row ***************************
       Table: testset
Create Table: CREATE TABLE `testset` (
  `id` int(11) DEFAULT NULL,
  `name` varchar(25) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=gbk
1 row in set (0.00 sec)

mysql> SELECT * FROM test2.testset;
+------+------------+
| id   | name       |
+------+------------+
|    1 | C语言      |
|    2 | Java语言   |
|    3 | Python语言 |
+------+------------+
3 rows in set (0.00 sec)
注意:选择目标字符集的时候,要注意最好的是原字符集的超集,或者确定比原字符集的字库更大,否则如果目标字符集的字库小于原字符集的字库,那么目标字符集中不支持的字符导入后会变成乱码,丢失一部分数据。

编程帮,一个分享编程知识的公众号。跟着站长一起学习,每天都有进步。

通俗易懂,深入浅出,一篇文章只讲一个知识点。

文章不深奥,不需要钻研,在公交、在地铁、在厕所都可以阅读,随时随地涨姿势。

文章不涉及代码,不烧脑细胞,人人都可以学习。

当你决定关注「编程帮」,你已然超越了90%的程序员!

编程帮二维码
微信扫描二维码关注