MySQL 8.0 : Error when inserting date data

Question

我有一个制表符分隔的文件，如下所示:

当我尝试在 MySql 中导入如下文件时:

LOAD DATA INFILE 'C:/ProgramData/MySQL/MySQL Server 8.0/Uploads/customer.csv'
INTO TABLE customers
FIELDS TERMINATED BY '\t'
LINES TERMINATED BY '\n'
IGNORE 1 ROWS;

我收到以下错误:

Incorrect date value: '02/15/2008' for column 'BI_signup_date' at row 1

如何解决这个问题？

Answer 1

文件中的日期格式与 MySQL 期望的格式不匹配。该列中还有一些非日期值('not BI')。解决方案是预处理您的输入，将其转换为相关格式。

LOAD DATA INFILE 'C:/ProgramData/MySQL/MySQL Server 8.0/Uploads/customer.csv'
INTO TABLE customers
FIELDS TERMINATED BY '\t'
LINES TERMINATED BY '\n'
IGNORE 1 ROWS
(customer_id, @BI_signup_date, email_address)
SET BI_signup_date = CASE
    WHEN @BI_signup_date = 'not BI' THEN NULL
    ELSE STR_TO_DATE(@BI_signup_date, '%m/%d/%Y')
END;

这会将值'not BI'转换为NULL(因为显然这不能转换为日期)，并将其他值转换为MySQL日期格式。其他策略也是可能的，例如根据正则表达式检查值的格式。您可以根据需要修改逻辑。

引用:Input preprocessing in MySQL LOAD DATA syntax :

Each col_name_or_user_var value is either a column name or a user variable. With user variables, the SET clause enables you to perform preprocessing transformations on their values before assigning the result to columns.