mysql导入的数据乱码_mysql导入数据后出现乱码的解决办法

问题：文件编码和数据库连接编码不一致，由于要添加初始化数据，批量执行，采用source命令，

复制代码 代码如下:

root@(none) 03:14:49>use test

Database changed

root@test 03:14:53>show create table rule\G;

*************************** 1. row ***************************

Table: rule

Create Table: CREATE TABLE `rule` (

`id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT '自增主键',

`name` varchar(64) NOT NULL COMMENT '规则名，如：分享',

`status` tinyint(4) NOT NULL COMMENT '状态',

PRIMARY KEY (`id`)

) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=gbk;

root@test 03:15:06>show variables like "%character%";

+--------------------------+----------------------------------+

| Variable_name            | Value                            |

+--------------------------+----------------------------------+

| character_set_client     | gbk                              |

| character_set_connection | gbk                              |

| character_set_database   | gbk                              |

| character_set_filesystem | binary                           |

| character_set_results    | gbk                              |

| character_set_server     | gbk                              |

| character_set_system     | utf8                             |

+--------------------------+----------------------------------+

7 rows in set (0.00 sec)

root@test 03:17:14>

可以看到客户端连接进来的编码方式是gbk,当我直接使用

复制代码 代码如下:

root@(none) 03:19:02>source a.sql;

root@test 03:20:45>select * from rule;

+----+--------------------+--------------------------------+

| id | name          | status |

+----+--------------------+--------------------------------+

|  1 | 娴囨按             |      0 |

|  2 | 鍏虫敞鎺屾煖鎺ㄦ帹 |        0 |

+----+--------------------+--------------------------------+

2 rows in set (0.00 sec)

root@test 03:20:46>

乱码，为什么？我客户端的连接，表的编码格式是一样的！

再看下a.sql的编码方式：

$file a.sql

a.sql: UTF-8 Unicode text, with CRLF line terminators

文件是utf8编码，再运行下看看：

复制代码 代码如下:

root@test 02:35:36>set names utf8;

Query OK, 0 rows affected (0.00 sec)

root@test 02:35:57>truncate table rule;

Query OK, 0 rows affected (0.00 sec)

root@test 02:36:04>source a.sql;

Query OK, 1 row affected (0.00 sec)

Query OK, 1 row affected (0.00 sec)

root@test 02:36:08>select * from rule;

+----+--------------------+--------------------------------+

| id |  name          | status |

+----+--------------------+--------------------------------+

|  1 | 娴囨按             |      0 |

|  2 | 鍏虫敞鎺屾煖鎺ㄦ帹 |      0 |

+----+--------------------+--------------------------------+

2 rows in set (0.00 sec)

root@test 02:36:10>set names gbk;

Query OK, 0 rows affected (0.00 sec)

root@test 02:36:23>select * from rule;

+----+--------------+--------------------------------+

| id | name    | status |

+----+--------------+--------------------------------+

|  1 | 灌水         |     0 |

|  2 | 打酱油       |     0 |

+----+--------------+--------------------------------+

2 rows in set (0.00 sec)

root@test 02:36:25>

您可能感兴趣的文章：

windows下Mysql中文乱码的解决方法

mysql中文乱码解决方法汇总

mysql导入导出数据时中文乱码的解决办法

mysql还原时乱码的解决方法

mysql备份还原后中文乱码的解决办法