MySql - 加载本地数据 Infile - 如何避免插入包含无效数据的行

Question

我正在使用“LOAD DATA LOCAL INFILE”查询将数据从 .csv 文件导入到 Mysql 数据库。.csv 包含以下内容:

ID | Name | Date | Price

 1. 01 | abc  | 13-02-2013 | 1500
 2. 02 | nbd  | blahblahbl | 1000
 3. 03 | kgj  | 11-02-2012 | jghj

我的 Mysql 表包含以下列:

Id INTEGER 
NAME Varchat(100)
InsertionTimeStamp Date
Price INTEGER 

将 .csv 数据加载到上表的 MySQL 查询:

LOAD DATA LOCAL INFILE 'UserDetails.csv' INTO TABLE userdetails
FIELDS TERMINATED BY ','
IGNORE 1 LINES
 (Id,Name,InsertionTimeStamp,Price)
 set InsertionTimeStamp = str_to_date(@d,'%d-%m-%Y');

当我执行查询时，3 条记录被插入到表中，但有 2 条警告

a) Incorrect datetime value : 'blahblahbl' for function str_to_date
b) Data truncate at row 3 (because of invalid INTEGER for Price column)

问题

1. Is there any way to avoid data being inserted in table which shows warnings/errors or the row which has invalid data 

I dont want Row 2 & Row 3 to be inserted as it contains invalid data

2. For WARNING of Incorrect datetime value above, can I get the row no also?
Basically I want to get exact warning/error with the row number.

Answer 1

我认为如果您使用其他语言(例如 php)来验证输入会容易得多。你只需要遍历 csv 的行并调用类似 this 的东西, this和 this !

如果您只需要摆弄 SQL，this可能有帮助!