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.sqlEnter password: *******
导出的文件,表的编码格式utf8,与之前导入文件的比较并看不出什么问题。
那么,就是之前导入文件的过程,创建数据库的过程除了问题,查看之前导入的mydb.sql 的mydb数据库结构,果然不对:
MysqL> use mydb;Database changedMysqL> 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
utf8mb4_unicode_ci 这个东西是怎么出来的...
然后改了编码,查询,还是不对,想起来之前创建数据库,可能是忘记设置编码,导入的东西本来就乱码了,也或者是my.ini配置不对,从头开始用正确流程试试吧。
先查看数据库的编码设置: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,140,141,112,147,125,110,131,132,133,136,137,145,146,148 | Goods-index,Goods-tianjia,category-index,Order-showList,Brand-index | 技术负责人 || 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,Order-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导入文件中文乱码怎么办全部内容,希望文章能够帮你解决mysql导入文件中文乱码怎么办所遇到的程序开发问题。
如果觉得内存溢出网站内容还不错,欢迎将内存溢出网站推荐给程序员好友。
欢迎分享,转载请注明来源:内存溢出
评论列表(0条)