大家好,请问下MYSQL中使用LOAD功能的时候能不能跳过一些数据格式错误的行?
刚试了下,在使用MYSQL的LOAD语句时,哪怕数据文件中部分行的格式错误的话也会LOAD进去,只是报个WARNING而已。有没有什么参数指定如果数据行格式错误就跳过呢?
刚试了下,在使用MYSQL的LOAD语句时,哪怕数据文件中部分行的格式错误的话也会LOAD进去,只是报个WARNING而已。有没有什么参数指定如果数据行格式错误就跳过呢?
mysql> select @@session.sql_mode;
+----------------------------------------------------------------+
| @@session.sql_mode |
+----------------------------------------------------------------+
| STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION |
+----------------------------------------------------------------+
1 row in set (0.00 sec)mysql> load data local infile '/home/wanghai01/data/customer_20111022.dat' into table aa fields terminated by',' lines terminated by'\n';
Query OK, 3 rows affected, 2 warnings (0.01 sec)
Records: 3 Deleted: 0 Skipped: 0 Warnings: 1mysql>
mysql> select version();
+------------+
| version() |
+------------+
| 5.1.45-log |
+------------+
1 row in set (0.01 sec)
Query OK, 0 rows affected (0.00 sec)mysql> set global sql_mode=traditional;
Query OK, 0 rows affected (0.00 sec)mysql> delete from aa;
Query OK, 3 rows affected (0.00 sec)mysql> load data local infile '/home/wanghai01/data/customer_20111022.dat' into table aa fields terminated by',' lines terminated by'\n';
Query OK, 3 rows affected, 1 warning (0.00 sec)
Records: 3 Deleted: 0 Skipped: 0 Warnings: 1mysql> show warnings;
+---------+------+--------------------------------------------+
| Level | Code | Message |
+---------+------+--------------------------------------------+
| Warning | 1261 | Row 1 doesn't contain data for all columns |
+---------+------+--------------------------------------------+
1 row in set (0.00 sec)mysql> select * from aa;
+------+------------+---------------------+
| id | name | ins_date |
+------+------------+---------------------+
| 11 | 2011-01-01 | NULL |
| 33 | xx | 2011-01-01 00:00:00 |
| 22 | hehe | 2011-01-01 00:00:00 |
+------+------------+---------------------+
3 rows in set (0.00 sec)mysql> 谢谢回答,结果还是不行。