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)

更多相关文章

随机推荐