很久很久以前,中国人就在mysql中存汉字了,尽管那时候mysql还不支持汉字的字符集。
汉字当作latin1一样处理,GB(GB2312、GBK、GB18030)汉字,由两个字节组成。latin字符,一个字符是一个字节。这样一个汉字被当作两个latin1字符来处理。只要客户端和数据库之间不做字符集转换,客户端就能正常操作汉字。
如今,mysql早已经支持汉字的字符集,包括gbk和utf8。
当我们升级低版本的数据库,并且想用GBK字符集的表来保存汉字时,麻烦来了。
导出一切正常,导入则报一些奇怪的错误。
比如:
mysql -h127.0.0.1 -P3333 --default-character-set=gbk test1 < bbs_article_0.data.sql
PAGER set to stdout
PAGER set to stdout
ERROR at line 24: Unknown command '\"'.
这里的sql文件只有insert语句,因为数据库中事先准备好了一个gbk的表。
mysql> show create table bbs_article_0\G
*************************** 1. row ***************************
Table: bbs_article_0
Create Table: CREATE TABLE `bbs_article_0` (
`guid` bigint(15) NOT NULL AUTO_INCREMENT,
`status` int(4) NOT NULL DEFAULT '0',
`hit_count` int(8) NOT NULL DEFAULT '0',
`reply_count` int(8) NOT NULL DEFAULT '0',
`board_id` bigint(15) NOT NULL DEFAULT '0',
`user_id` bigint(15) NOT NULL DEFAULT '0',
`icon` varchar(100) NOT NULL DEFAULT
, `user_nick` varchar(20) NOT NULL DEFAULT
, `vote_id` bigint(15) NOT NULL DEFAULT '0',
`last_time` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
`subject` varchar(100) NOT NULL DEFAULT
, `add_time` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
`ischeck` int(11) NOT NULL DEFAULT '0',
`ip` varchar(20) NOT NULL DEFAULT
, `deletestatus` int(11) NOT NULL DEFAULT '0',
`subClass` int(11) NOT NULL DEFAULT '0',
`appType` int(11) NOT NULL DEFAULT '0',
`appParameter` int(11) NOT NULL DEFAULT '0',
`marrowArticle` int(11) NOT NULL DEFAULT '0',
`topArticle` int(11) NOT NULL DEFAULT '0',
`redHeart` int(11) NOT NULL DEFAULT '0',
`replyNo` int(11) NOT NULL DEFAULT '0',
`send_money` int(11) NOT NULL DEFAULT '0',
`circle_allow` int(11) NOT NULL DEFAULT '0',
`friend_allow` int(11) NOT NULL DEFAULT '0',
`once_money` int(11) NOT NULL DEFAULT '0',
`totle_money` int(11) NOT NULL DEFAULT '0',
`weekRedHeart` int(11) NOT NULL DEFAULT '0',
PRIMARY KEY (`guid`),
KEY `ind_vote` (`vote_id`),
KEY `ind_board` (`board_id`),
KEY `ind_last_time` (`last_time`),
KEY `ischeck` (`ischeck`)
) ENGINE=MyISAM AUTO_INCREMENT=8155287 DEFAULT CHARSET=gbk
1 row in set (0.01 sec)
注意使用了DEFAULT CHARSET=gbk。如果导入一个latin1的表,即表定义DEFAULT CHARSET=latin1,并且导入时使用--default-character-set=latin1,就不报错,汉字能正确导入。
用逐行(禁止extended insert)导出的方法导出,再导入,确定了具体是哪一条数据报的错。
发现一个单引号被转义了。是繁体的祷字,即“禱”后面的单引号被加了一个\给转义了,这样一个insert语句就被解释错了,导致报错。
mysqldump导出文本时,遇到特殊字符需要转义,比如引号、反斜杠等,如果不转义的话,这些字符会导致文本文件会被客户端(比如mysql)错误解释。
字段的值有\时,需要再加一个\进行转义。比如
mysql> select * from ttt;
+---------+
| tco |
+---------+
| aaaaaa\ |
+---------+
1 row in set (0.00 sec)
mysqldump -h127.0.0.1 --no-create-info --no-set-names -P3333 test ttt
-- MySQL dump 10.13 Distrib 5.1.41, for unknown-linux-gnu (x86_64)
--
-- Host: 127.0.0.1 Database: test
-- ------------------------------------------------------
-- Server version 5.1.54-debug
/*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */;
/*!40103 SET TIME_ZONE='+00:00' */;
/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;
/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;
/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;
--
-- Dumping data for table `ttt`
--
LOCK TABLES `ttt` WRITE;
/*!40000 ALTER TABLE `ttt` DISABLE KEYS */;
INSERT INTO `ttt` VALUES ('aaaaaa\\');
/*!40000 ALTER TABLE `ttt` ENABLE KEYS */;
UNLOCK TABLES;
/*!40103 SET TIME_ZONE=@OLD_TIME_ZONE */;
/*!40101 SET SQL_MODE=@OLD_SQL_MODE */;
/*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */;
/*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */;
/*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */;
-- Dump completed on 2011-03-22 16:41:17
导出文件中多了一个\,否则原有的\会把后面的单引号转义,导入时就报错了。
现在去看造成问题的那个汉字:
mysqldump -h127.0.0.1 -P3333 --default-character-set=latin1 --no-set-names --no-create-info test bbs_article_0 --where="guid=8038589"
-- MySQL dump 10.13 Distrib 5.1.41, for unknown-linux-gnu (x86_64)
--
-- Host: 127.0.0.1 Database: test
-- ------------------------------------------------------
-- Server version 5.1.54-debug
/*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */;
/*!40103 SET TIME_ZONE='+00:00' */;
/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;
/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;
/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;
--
-- Dumping data for table `bbs_article_0`
--
-- WHERE: guid=8038589
LOCK TABLES `bbs_article_0` WRITE;
/*!40000 ALTER TABLE `bbs_article_0` DISABLE KEYS */;
INSERT INTO `bbs_article_0` VALUES (8038589,0,40,1,11170,4254498,'','¤OTIAN艺¤',0,'2007-09-24 22:01:00','希望><祈禱\','2007-09-24 20:58:00',1,'',0,0,0,0,0,0,0,2,0,0,0,0,0,0);
/*!40000 ALTER TABLE `bbs_article_0` ENABLE KEYS */;
UNLOCK TABLES;
/*!40103 SET TIME_ZONE=@OLD_TIME_ZONE */;
/*!40101 SET SQL_MODE=@OLD_SQL_MODE */;
/*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */;
/*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */;
/*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */;
-- Dump completed on 2011-03-22 15:51:03
注意INSERT语句,这里没有做转义吗?有一个\把后面的单引号转义了,导入自然要报错。
mysql> set names latin1;
Query OK, 0 rows affected (0.00 sec)
mysql> select subject from bbs_article_0 where guid=8038589;
+------------+
| subject |
+------------+
| 希望><祈禱 |
+------------+
1 row in set (0.00 sec)
实际数据中并没有\,导出数据中加了一个\。是谁错乱了?
用hexdump来看一下有什么问题。
more qidao
祈禱
[@clubstdby.no.sohu.com work]# hexdump -C qidao
00000000 c6 ed b6 5c 0a |...\.|
00000005
这个繁体字其中一个字节是5c,和\ 是一样的值。
这下原因就明显了,latin1导出的时候,把禱认成了两个字符,而其中一个是\。遇到\自然要转义了。而导出的文件以latin1导入也不会有问题,因为mysql看到了两个\\,而不是一个禱再加一个\。
以gbk导入就不行了,mysql看到一个禱再加一个\,就把后面的单引号转义了。
原因明白了,如何去解决呢,不让mysql去以latin1转义,而是以gbk转义,但又不能做latin1到gbk的字符集转换。
当时就想到了要修改mysqldump源码,但是一个错误的判断让我走了很久弯路。
通过tcpdump抓包,发现字符集的转换是在server端做的,即mysqld做的,mysqld传给客户端的字符集就已经是转换完的了。比如gbk与utf8之间的转换。
由此我推测字符转义也是在server端进行的。之后去看源码,还进行了一些跟踪,始终未发现转义的地方。虽然发现了进行转义的函数,但似乎server并没有调用它,而是客户端在调用它。
突然有一天,我想到可能就是客户端进行转义,server端只做字符集转换。
这次再用tcpdump抓了一下mysqldump的过程,并且其中故意加了需要转义的数据。
mysql> select * from ttt;
+-------------+
| tco |
+-------------+
| aaaaaaaaaa\ |
+-------------+
1 row in set (0.05 sec)
tcpdump -s 1500 -w tcp.3333.out port 3333
mysqldump -h10.11.86.17 -P3333 -utest -ptest test ttt
[@clubstdby.no.sohu.com work]# hexdump -C tcp.3333.out
....
00001170 00 00 02 00 0c 00 00 04 0b 61 61 61 61 61 61 61 |.........aaaaaaa|
00001180 61 61 61 5c 05 00 00 05 fe 00 00 02 00 38 11 88 |aaa\.........8..|
....
网络上传输的并未转义,只有一个\
而最终dump出的内容是转义了的,有两个\
-- Table structure for table `ttt`
--
DROP TABLE IF EXISTS `ttt`;
CREATE TABLE `ttt` (
`tco` varchar(100) DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1;
--
-- Dumping data for table `ttt`
--
LOCK TABLES `ttt` WRITE;
/*!40000 ALTER TABLE `ttt` DISABLE KEYS */;
INSERT INTO `ttt` VALUES ('aaaaaaaaaa\\');
/*!40000 ALTER TABLE `ttt` ENABLE KEYS */;
UNLOCK TABLES;
于是直奔早已看到的转义函数,在libmysql/charset.c
size_t escape_string_for_mysql(CHARSET_INFO *charset_info,
char *to, size_t to_length,
const char *from, size_t length)
{
const char *to_start= to;
const char *end, *to_end=to_start + (to_length ? to_length-1 : 2*length);
my_bool overflow= FALSE;
/* gulei modified for latin1 to gbk */
charset_info=&my_charset_gbk_bin;
上面是我加了一行,直接把传入的指针值,改为固定的gbk字符集。
编译、安装后再试。
/opt/work/mysql/bin/mysqldump -h127.0.0.1 -P3333 --default-character-set=latin1 --no-set-names --no-create-info test bbs_article_0 --where="guid=8038589"
-- MySQL dump 10.13 Distrib 5.1.54, for unknown-linux-gnu (x86_64)
--
-- Host: 127.0.0.1 Database: test
-- ------------------------------------------------------
-- Server version 5.1.54-debug
/*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */;
/*!40103 SET TIME_ZONE='+00:00' */;
/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;
/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;
/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;
--
-- Dumping data for table `bbs_article_0`
--
-- WHERE: guid=8038589
LOCK TABLES `bbs_article_0` WRITE;
/*!40000 ALTER TABLE `bbs_article_0` DISABLE KEYS */;
INSERT INTO `bbs_article_0` VALUES (8038589,0,40,1,11170,4254498,'','¤OTIAN艺¤',0,'2007-09-24 22:01:00','希望><祈禱','2007-09-24 20:58:00',1,'',0,0,0,0,0,0,0,2,0,0,0,0,0,0);
/*!40000 ALTER TABLE `bbs_article_0` ENABLE KEYS */;
UNLOCK TABLES;
/*!40103 SET TIME_ZONE=@OLD_TIME_ZONE */;
/*!40101 SET SQL_MODE=@OLD_SQL_MODE */;
/*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */;
/*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */;
/*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */;
-- Dump completed on 2011-03-22 15:53:03
成功了,这次没做错误的转义。
整个表的导入也成功了,而用没改过的mysqldump导出再导入仍然是报错。
终于(注意这里是set names gbk了)
mysql> set names gbk;
Query OK, 0 rows affected (0.00 sec)
mysql> select subject from bbs_article_0 where guid=8038589;
+------------+
| subject |
+------------+
| 希望><祈禱 |
+------------+
1 row in set (0.00 sec)
大功告成