问题及解决办法
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。
解决办法:
- 使用宽松模式ANSI,此模式可以解决绝大部分错误,简单粗暴。但是如果数据中间存在已上错误中的类型字段,可能导致数据后面信息无法导入。需要从根源上解决。即对文件空字符进行替换处理。
//将文件中所有分割符中间空字符串 替换成mysql识别的'\N'
sed 's/\x3\x3/\x3\\N\x3/g;s/\x3\x3/\x3\\Nx3/g' beforedata.dat > afterdata.dat
//上面替换无法替换最后一列,以下为改进方式
sed 's/\x3\x3/\x3\\N\x3/g;s/\x3\x3/\x3\\N\x3/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' "
- 第二种解决办法是通过函数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是要进行转义的,所以肯定会出问题。
解决办法:
检查字段长度是否足够,确认字段是否匹配。
(以下为数据中有‘\’ 解决办法)
- 万能宽松模式,详见上面内容宽松模式使用。
- 使用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导出的数据形式如下xx.xx.xx
解决办法:
此问题不可使用宽松模式,导入数据会发现会变为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:’\xFE\x9B\xFE’ for column
ERROR 1366 (HY000) at line 1: Incorrect string value:’\xFE\x9B\xFE’ for column ‘TEST_NM’ at row 1
问题原因:
遇上utf8没法识别的字符了,这种大部分解决问题时说需要配置字符集为utf8。
解决办法:其实只要是可以插入汉字,说明数据库方面已经配置好,这些字符一般比较生僻,在gbk中可能也是一些乱码,导入后测试结果,该是??还是??,所以可以通过宽松模式进行导入。详见上面宽松模式导入。