大家好,请问下MYSQL中使用LOAD功能的时候能不能跳过一些数据格式错误的行?
刚试了下,在使用MYSQL的LOAD语句时,哪怕数据文件中部分行的格式错误的话也会LOAD进去,只是报个WARNING而已。有没有什么参数指定如果数据行格式错误就跳过呢?
刚试了下,在使用MYSQL的LOAD语句时,哪怕数据文件中部分行的格式错误的话也会LOAD进去,只是报个WARNING而已。有没有什么参数指定如果数据行格式错误就跳过呢?
解决方案 »
- can't not update table 'szs_pick' in stored function/trigger because it is alrea
- 这个sql查询改怎么写
- 会什么才是数据库高手呀!!
- 请教一个mysql 查询后插入的insert into select 语句或者存储过程
- Mysql Update有办法返回影响的记录集吗?
- 我想请问用DECLARE EXIT HANDLER FOR SQLEXCEPTION时能不能得到发生错误的错误编码?
- 请教jsp连接mysql问题
- 在Win2003中启动MySQL遇到的问题
- mysql 左连接后 用mysql_fetch_assoc取键名的问题
- 关于join查询问题
- 大虾帮忙看看啊
- 【MYSQL约束项问题】
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> 谢谢回答,结果还是不行。