userdata.txt文件是一个12个字段,5万条数据的备份用户信息文件(从MSSQL导出)想要把它导入到MySql中使用。当配制如下时:
Server characterset: gb2312
Db characterset: gb2312
Client characterset: gb2312
Conn. characterset: gb2312
==================================
mysql> load data infile '\userdata.txt' into table userinfo
-> fields terminated by '\t';
ERROR 1366 (HY000): Incorrect string value: '\xAB\x91' for column 'name' at row 1142当更改配制如下时:
Server characterset: gb2312
Db characterset: gbk
Client characterset: gbk
Conn. characterset: gbk
=============================
mysql> load data infile '\userdata.txt' into table userinfo
-> fields terminated by '\t';
ERROR 1366 (HY000): Incorrect string value: '\xA1\xAA402' for column 'addr' at row 361请高手指点,谢谢!
Server characterset: gb2312
Db characterset: gb2312
Client characterset: gb2312
Conn. characterset: gb2312
==================================
mysql> load data infile '\userdata.txt' into table userinfo
-> fields terminated by '\t';
ERROR 1366 (HY000): Incorrect string value: '\xAB\x91' for column 'name' at row 1142当更改配制如下时:
Server characterset: gb2312
Db characterset: gbk
Client characterset: gbk
Conn. characterset: gbk
=============================
mysql> load data infile '\userdata.txt' into table userinfo
-> fields terminated by '\t';
ERROR 1366 (HY000): Incorrect string value: '\xA1\xAA402' for column 'addr' at row 361请高手指点,谢谢!
比如,xA1\xAA402這樣的數據\要轉義成\\
set names 'GBK';
set character set 'GBK';