mysql load db2 gbk文件到utf8 遇到的部分问题及解决办法

mysql load db2 gbk文件到utf8 遇到的部分问题及解决办法

问题及解决办法

• 具体问题
• • value:'' for column
  • more data than there were input columns
  • Data too long
  • value:'18.36.30' for column
  • value:'1900-01-01-00.00.00.000000' for column
  • value:'xFEx9BxFE' for column

mysql 导入数据主要使用load命令
db2 文件 与 mysql文件 实际存在部分不同，导致导入数据时有错误发生。

具体问题

value:’’ for column

ERROR 1292 (22007) at line 1: Incorrect data value:’’ for column ‘TEST_DT’ at row 1
ERROR 1366 (HY000) at line 1: Incorrect decimal value:’’ for column ‘TEST_AMT’ at row 1
ERROR 1292 (22007) at line 1: Incorrect datetime value:’’ for column ‘TEST_DT’ at row 1
ERROR 1366 (HY000) at line 1: Incorrect integer value:’’ for column ‘TEST_AMT’ at row 1
原因：
此类问题根源在于不同数据库文件导出的文件分割符不同。通过查看文件，发现在db2中 上面以上几种类型数据为null时，文件均为空字符串，但是对于mysql，他的null为N。
解决办法：

1. 使用宽松模式ANSI,此模式可以解决绝大部分错误，简单粗暴。但是如果数据中间存在已上错误中的类型字段，可能导致数据后面信息无法导入。需要从根源上解决。即对文件空字符进行替换处理。

//将文件中所有分割符中间空字符串 替换成mysql识别的'N'
sed 's/x3x3/x3\Nx3/g;s/x3x3/x3\Nx3/g' beforedata.dat > afterdata.dat
//上面替换无法替换最后一列，以下为改进方式
sed 's/x3x3/x3\Nx3/g;s/x3x3/x3\Nx3/g;s/x3$/x3\N/g'  beforedata.dat > afterdata.dat

//使用宽松模式导入语句如下：
mysql -uroot -p'root'  -D testdb -e"set sql_mode='ANSI';load data local infile '/home/data/test1.dat' replace into table testTable character set gbk fields terminated by 0x03 lines terminated by 'n' "

2. 第二种解决办法是通过函数nullif，在导入过程中对‘’进行处理。缺点显而易见，如果表字段多了，列出所有字段并标记，是一项大工程。

//假如此表有三个字段，则涉及到要特殊处理字段，用@字段标识，具体如下
mysql -uroot -p'root'  -D testdb -e"load data local infile '/home/data/test1.dat' replace into table testTable character set gbk fields terminated by 0x03 lines terminated by 'n' （clo1,col2,@col3）set col3 = nullif(@col3,'') "

注意：在mysql中 timestamp 默认为插入记录时间记录。导入时，如果遇到此种字段请多加小心。
可能会导致原数据文件没有时间，但是入表后，却存在时间。

more data than there were input columns

ERROR 1262 (01000) at line1: Row 1 was truncated; it contained more data than there were input columns;

原因:
字面意思，第1行被截断；它包含的数据多于输入列;
解决办法:
查看文件字段个数是否与表字段匹配。

Data too long

ERROR 1406 (22001) at line1:Data too long for column ‘COL1’ at row 1
原因:
插入数据过长，此时需要注意后面一句at row 1 ，如果是在1行，则先排查是否存在字段与数据不匹配情况。如果在几百行后，那么要注意检查数据，是否存在非法内容，比如某个字段中有一个’’,这个在mysql是要进行转义的，所以肯定会出问题。
解决办法：
检查字段长度是否足够，确认字段是否匹配。
（以下为数据中有‘’ 解决办法）

1. 万能宽松模式，详见上面内容宽松模式使用。
2. 使用escaped by ‘’,看了好多博客，上面均为这么写，但是脚本中始终不认。最后改为escaped by ‘’ 发现也可成功导入‘’数据。

mysql -uroot -p'root'  -D testdb -e"load data local infile '/home/data/test1.dat' replace into table testTable character set gbk fields terminated by 0x03 escaped by '' lines terminated by 'n' 

value:‘18.36.30’ for column

ERROR 1292 (22007) at line 1: Incorrect time value:‘18.36.30’ for column ‘TEST_TM’ at row 1
问题原因：
mysql time类型只认xx:xx:xx 但是db2导出的数据形式如下
解决办法：
此问题不可使用宽松模式，导入数据会发现会变为00:00：xx。建议使用替换处理该问题。

mysql -uroot -p'root'  -D testdb -e"load data local infile '/home/data/test1.dat' replace into table testTable character set gbk fields terminated by 0x03 lines terminated by 'n' （clo1,@col2,col3）set col2 = replace(@col2,'.',':') "

value:‘1900-01-01-00.00.00.000000’ for column

ERROR 1292 (22007) at line 1: Incorrect datetime value:‘1900-01-01-00.00.00.000000’ for column ‘TEST_TMS’ at row 1
问题原因：
mysql timestamp范围为：1970-01-01 00:00:00 到 2037年;如果使用datatime不会有此问题。

解决办法：
如果数据库字段使用timestamp可能会遇到此问题，请改为datatime继续导入吧！

value:’xFEx9BxFE’ for column

ERROR 1366 (HY000) at line 1: Incorrect string value:’xFEx9BxFE’ for column ‘TEST_NM’ at row 1
问题原因：
遇上utf8没法识别的字符了，这种大部分解决问题时说需要配置字符集为utf8。
解决办法：其实只要是可以插入汉字，说明数据库方面已经配置好，这些字符一般比较生僻，在gbk中可能也是一些乱码，导入后测试结果，该是？？还是？？，所以可以通过宽松模式进行导入。详见上面宽松模式导入。