有时候会在不注意的情况下创建了字符集为latin1的数据库,导致后续插入的中文显示乱码。这时有两种方法:1.修改数据库与数据表的字符集(只能向上调整,不能向下调整);2.数据迁移。但是两种方法都需要做好备份,谨慎操作。
创建测试环境:
[root@youxi1 ~]# vim user_tb.sql //创建一个sql脚本
drop database if exists test_mv;
create database test_mv character set latin1; //因为我默认的是UTF-8字符所以这里指定字符集
use test_mv;
create table user_tb(
id int,
name varchar(20)
)CHARSET=latin1; //指定字符集的原因和上面一样
/*!40101 SET character_set_client = latin1 */; //由于我是UTF-8所以需要,否则无法导入汉字。
lock tables user_tb write;
insert into user_tb values (1,'学生'),(2,'老师');
unlock tables;
[root@youxi1 ~]# mysql -u root -p123456 < user_tb.sql
mysql: [Warning] Using a password on the command line interface can be insecure.
[root@youxi1 ~]# mysql -u root -p123456
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 11
Server version: 5.7.16 MySQL Community Server (GPL)
Copyright (c) 2000, 2016, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> use test_mv;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed
mysql> select * from user_tb; //汉字显示乱码
+------+---------------+
| id | name |
+------+---------------+
| 1 | å¦ç”Ÿ |
| 2 | è€å¸ˆ |
+------+---------------+
2 rows in set (0.00 sec)
(2).修改字符
注意:选择目标字符集时,要注意最好大于等于原字符集(字库更大),否则可能会丢失不被支持的数据。
(3).数据迁移
1)导出test_mv数据库
[root@youxi1 ~]# mysqldump -uroot -p123456 --quick --extended-insert --default-character-set=latin1 -B test_mv > test_mv_latin1.sql
说明:--quick用于转储大的表,强制mysqldump从服务器一次一行的检索数据而不是检索所有行,并输出当前cache到内存中。
--extended-insert:使用包括几个values列表的多行insert语法,这样文件更小,IO也小,导入数据时会非常快。
--default-character-set=latin1:按照原有字符集导出数据,这样导出的文件中,所有中文都是可见的,不会保存成乱码。
注意:我这里使用了-B选项将整个数据库都导出了。如果使用的是-d选项表示导出表结构,而默认选项是导出整张表,默认选项和--no-create-info一起使用则表示只导出表数据。
(2).编辑test_mv_latin1.sql将latin1修改为utf8
首先做备份,做好备份后再进行如下操作:
[root@youxi1 ~]# vim test_mv_latin1.sql
:%s/latin1/utf8/g //替换文件中的所有latin1为utf8,之后保存退出
(3).删除原来的数据库,导入修改后的sql文件
[root@youxi1 ~]# mysql -u root -p123456
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 15
Server version: 5.7.16 MySQL Community Server (GPL)
Copyright (c) 2000, 2016, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> drop database test_mv;
Query OK, 1 row affected (0.02 sec)
mysql> source /root/test_mv_latin1.sql
(4).查看结果
mysql> use test_mv
Database changed
mysql> select * from user_tb;
+------+--------+
| id | name |
+------+--------+
| 1 | 学生 |
| 2 | 老师 |
+------+--------+
2 rows in set (0.00 sec)