MySQL STR_TO_DATE错误为NULL [英] MySQL STR_TO_DATE NULL on error
问题描述
我目前正在将具有VARCHAR
列中的日期的表迁移到具有DATE
列的新表中.我设法将旧表中的字符串值清除为"YYYY-MM-DD" 格式,但是当我尝试执行插入操作时,日期为"2006-04的错误-31",因为那个四月只有30天(注册时是拼写错误),
I'm currently migrating a table with dates in VARCHAR
columns to a new table with DATE
columns. I managed to sanitize the string values in the old table to the format "YYYY-MM-DD" but when I try to perform the insert I got an error with the date "2006-04-31" because that April only had 30 days (was a typo when it was registered),
我的问题是:当日期无效时如何将列设置为NULL而不会出现错误?我的SQL如下:
My question is: how can I set to NULL the column when the date is invalid without getting an error? My SQL is the following:
INSERT INTO newFancyTable (created_at)
SELECT str_to_date(created, '%Y-%m-%d') FROM oldCrappyTable;
错误如下:
Error Code: 1292. Incorrect date value: '2006-04-31' for column 'created_at' at row 1
谢谢
更新
我也尝试使用以下方法:
I also tried using the following approach:
INSERT INTO newFancyTable (created_at)
SELECT CAST(created AS DATE) FROM oldCrappyTable;
出现相同的错误,并尝试更新oldCrappyTable
会返回相同的结果:
With the same error, and trying to update the oldCrappyTable
would return the same:
UPDATE oldCrappyTable SET created = CAST(created AS DATE);
两次返回:
Error Code: 1292. Incorrect datetime value: '2006-04-31'
更新2
最后,我使用了多个CASE
来隔离无效日期,总之它们只有5个,
但是,可以通过执行以下操作来重现该问题:
At last, I used multiple CASE
s to isolate that invalid dates, in sum they were only 5 of them,
Nevertheless, the issue can be reproduced by doing:
CREATE TABLE dates_temp (
test_date DATE DEFAULT NULL
) ENGINE=MEMORY;
INSERT INTO dates_temp
SELECT STR_TO_DATE("2006-04-31", '%Y-%m-%d');
DROP TABLE dates_temp;
推荐答案
一个可能的解决方法是关闭严格模式,无论是针对整个服务器,特定会话还是仅针对少数语句.例如:
A possible workaround is to turn off strict mode, either for the whole server, for a particular session, or for just a few statements. For example:
set @old_sql_mode = @@sql_mode;
set sql_mode = '';
-- Run some statements which may result in error
set sql_mode = @old_sql_mode;
其他信息
这篇关于MySQL STR_TO_DATE错误为NULL的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!