mysql 迁移 乱码_Mysql迁移由于字符集导致乱码的数据

有时候会在不注意的情况下创建了字符集为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)