mysql导入文件中文乱码怎么办

mysql导入文件中文乱码的解决办法：首先创建数据库并制定编码；然后在导入数据库文件之前，制定编码set names utf8即可。

推荐：《mysql视频教程》

继续昨天的问题，数据库配置好后，查询表发现中文乱码了，看了网上的几个方法也没解决。感觉是导入的sql文件问题，那么逆向的思考，用命令创建个数据库，添加些数据，然后导出来看看什么情况。向表内插入数据的时候发现报错：ERROR 1366 (HY000): Incorrect string value: '\xE6\xB5\x8B\xE8\xAF\x95' for column 'bookname' at row 1

这就奇怪了，查看表结构：show create table book;

看到了一个不想看到的编码格式latin1,果断改掉，通过命令：alter table book default character set utf8;改完看到一个神清气爽的结果，表的编码改过来了，但还存在一个“什么鬼？”，字段有个乱码latin1 ：

果断再改，通过命令：alter table book change bookname bookname varchar(32) character set utf8;改完后也不看了，直接插数据试试：

insert into book (id,bookname,size,price) values (1,"测试",2,3) ;

插入成功，查看数据是不是还中文乱码，发现并不乱码了：

那么开始进行下一步，导出sql文件，进入到mysql的bin目录下，开始通过命令导出，这个过程需要输入密码

E:\mysql-5.7.28-winx64\bin>mysqldump -u root -p test > test.sql Enter password: *******

mysql> use mydb; Database changed mysql> show variables like 'character_set_database'; +------------------------+--------+ | Variable_name          | Value  | +------------------------+--------+ | character_set_database | latin1 | +------------------------+--------+ 1 row in set, 1 warning (0.00 sec)

遂修改之：alert database mydb character set utf8;然后再查看表的编码，发现有鬼：

mysql> show create table sp_user_cart; +--------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | Table        | Create Table                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                    | +--------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | sp_user_cart | CREATE TABLE `sp_user_cart` (   `cart_id` int(11) unsigned NOT NULL AUTO_INCREMENT COMMENT '涓婚敭',   `user_id` int(11) unsigned NOT NULL COMMENT '瀛﹀憳id',   `cart_info` text CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci COMMENT '璐?墿杞﹁?鎯呬俊鎭?紝浜岀淮鏁扮粍搴忓垪鍖栦俊鎭',   `created_at` timestamp NULL DEFAULT NULL,   `updated_at` timestamp NULL DEFAULT NULL,   `delete_time` timestamp NULL DEFAULT NULL,   PRIMARY KEY (`cart_id`) ) ENGINE=InnoDB AUTO_INCREMENT=22 DEFAULT CHARSET=utf8

先查看数据库的编码设置:show variables like 'character%';发现很混乱：

mysql> show variables like 'character%'; +--------------------------+----------------------------------------+ | Variable_name            | Value                                  | +--------------------------+----------------------------------------+ | character_set_client     | gbk                                    | | character_set_connection | gbk                                    | | character_set_database   | latin1                                 | | character_set_filesystem | binary                                 | | character_set_results    | gbk                                    | | character_set_server     | latin1                                 | | character_set_system     | utf8                                   | | character_sets_dir       | D:\mysql-5.7.29-winx64\share\charsets\ | +--------------------------+----------------------------------------+

开始改，通过一系列的setset character_set_client = utf8;得到结果

+--------------------------+----------------------------------------+ | Variable_name            | Value                                  | +--------------------------+----------------------------------------+ | character_set_client     | utf8                                   | | character_set_connection | utf8                                   | | character_set_database   | utf8                                   | | character_set_filesystem | utf8                                   | | character_set_results    | utf8                                   | | character_set_server     | utf8                                   | | character_set_system     | utf8                                   | | character_sets_dir       | D:\mysql-5.7.29-winx64\share\charsets\ | +--------------------------+----------------------------------------+

创建数据库并制定编码：CREATE DATABASE test2 CHARACTER SET utf8 COLLATE utf8_general_ci;使用：use test2在导入数据库文件之前，制定编码set names utf8;导入：source F:xxxx\xxxx\mydb.sql;不算漫长的等待之后，查询，不乱码了

mysql> select * from sp_role; +---------+---------------+--------------------------------------------------------------------------------------------- ------------------------------------------------------+----------------------------------------------------------------- ------------------------+--------------------+ | role_id | role_name     | ps_ids                                                       | ps_ca                         | role_desc          | +---------+---------------+--------------------------------------------------------------------------------------------- ------------------------------------------------------+----------------------------------------------------------------- ------------------------+--------------------+ |      30 | 主管          | 101,0,104,116,115,142,143,144,121,122,123,149,102,107,109,103,111,129,130,134,135,138,139,14 0,141,112,147,125,110,131,132,133,136,137,145,146,148 | Goods-index,Goods-tianjia,Category-index,Order-showlist,Brand-in dex                     | 技术负责人         | |      31 | 测试角色      | 101,0,104,105,116,117,115,142,143,144,121,122,123,149,103,111,129,134,138,112,147                                                       | Goods-showlist,Goods-tianjia,Category-showlist,Order-showlist,Or der-dayin,Order-tianjia | 测试角色描述       | |      34 | 测试角色2     | 0,105,116,142,143,122                                                       | NULL                         | 测试描述12         | |      39 | 大发送到      | 101,0,104,105,116                                                       | NULL                         | 阿斯蒂芬           | |      40 | test          | 102,0,107,109,154,155,145,146,148                                                       | NULL

实战项目可以继续进行，遂生法喜。

以上就是mysql导入文件中文乱码怎么办的详细内容，更多内容请关注技术你好其它相关文章！