用mysqldump导出数据库时,如果客户端字符集和数据库字符集不同,则导出的文件中中文可能为乱码。[root@localhost ~]# mysql
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 75
Server version: 5.0.77 Source distribution
Type 'help;' or '\h' for help. Type '\c' to clear the buffer.
mysql> use test;
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> show create table a;
+-------+---------------------------------------------------------------------------------------+
| Table | Create Table |
+-------+---------------------------------------------------------------------------------------+
| a | CREATE TABLE `a` (
`name` char(10) default NULL
) ENGINE=MyISAM DEFAULT CHARSET=gbk |
+-------+---------------------------------------------------------------------------------------+
1 row in set (0.00 sec) 可以看出,表a的字符集为gbk
[root@localhost ~]$ mysqldump -uroot --default-character-set=utf8 test a >a.txt
[root@localhost ~]$ more a.txt
-- MySQL dump 10.11
--
。。。。。。
DROP TABLE IF EXISTS `a`;
CREATE TABLE `a` (
`name` char(10) default NULL
) ENGINE=MyISAM DEFAULT CHARSET=gbk;
--
-- Dumping data for table `a`
--
LOCK TABLES `a` WRITE;
/*!40000 ALTER TABLE `a` DISABLE KEYS */;
INSERT INTO `a` VALUES ('涓浗'),('涓浗');
/*!40000 ALTER TABLE `a` ENABLE KEYS */;
UNLOCK TABLES;
/
。。。。。。
用file命令可以查看导出文件的字符集:
[root@localhost ~]$ file -i a.txt
a.txt: text/plain; charset=utf-8
可用iconv将此文件转换为其他字符集:
[root@localhost ~]$ iconv -f utf8 -t gbk a.txt >b.txt
[root@localhost ~]$ more b.txt
DROP TABLE IF EXISTS `a`;
CREATE TABLE `a` (
`name` char(10) default NULL
) ENGINE=MyISAM DEFAULT CHARSET=gbk;
--
-- Dumping data for table `a`
--
LOCK TABLES `a` WRITE;
/*!40000 ALTER TABLE `a` DISABLE KEYS */;
INSERT INTO `a` VALUES ('中国'),('中国');
/*!40000 ALTER TABLE `a` ENABLE KEYS */;
UNLOCK TABLES;
/*!40103 SET TIME_ZONE=@OLD_TIME_ZONE */;
。。。。。。
查看数据中的乱码已经变成了中文‘中国’
将utf8转换为Latin1的时候会报错:
[root@localhost ~]$ iconv -f utf8 -t latin1 a.txt >b.txt
iconv: illegal input sequence at position 1034
它怎么会无法转换为latin1呢?试了其他的字符集,也是同样问题,似乎只能在utf8和gbk或者gb2312之间转换
转载于:https://blog.51cto.com/xieyanhuang123/284986