MySQL STR_TO_DATE 函数在错误时返回 NULL。

Question

我现在正在将一个包含日期的表从使用VARCHAR列转移到新的使用DATE列的表中。我已经成功将旧表中的字符串值清理到"YYYY-MM-DD"格式，但是当我尝试插入时，由于一个日期字符串"2006-04-31"有误（实际4月只有30天），导致出现错误。

我的问题是：如何在日期无效时将列设置为NULL而不出错？我的SQL语句如下：

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

感谢

更新

我还尝试使用以下方法：

INSERT INTO newFancyTable (created_at)
SELECT CAST(created AS DATE) FROM oldCrappyTable;

如果出现相同的错误，并且尝试更新oldCrappyTable，将会返回相同的结果：

UPDATE oldCrappyTable SET created = CAST(created AS DATE);

两者都返回：

Error Code: 1292. Incorrect datetime value: '2006-04-31'

更新2

最终，我使用了多个CASE来隔离那些无效的日期，总共只有5个， 不过，这个问题可以通过以下方式再现：

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;

- Joaquín L. Robles

---

你为什么不写一个CASE或者过滤掉它呢？ - Kermit

那个表格有5000多行，我无法确定有多少个日期是无效的。我知道"2006-04-31"是第一个，但不知道还有多少其他的。 - Joaquín L. Robles

你的列被定义为非空吗？当传递给STR_TO_DATE()函数的字符串表示一个非法日期时，默认情况下会生成一个NULL值。 - Mike Brant

该列可以为空，实际上使用SELECT STR_TO_DATE("2006-04-31", '%Y-%m-%d');返回NULL，但在UPDATE、INSERT语句中使用它时会失败。 - Joaquín L. Robles

请查看我的答案这里。 - mixel

Answer 1

一个可能的解决方案是关闭严格模式，可以针对整个服务器、特定会话或仅针对几个语句。例如：

 set @old_sql_mode = @@sql_mode; 
 set sql_mode = ''; 
 -- Run some statements which may result in error
 set sql_mode = @old_sql_mode;

附加信息

MySQL文档

- Eduardo Dennis

---

这要看情况。当我需要这样做时，我只是试图将所有数据存入数据库中。之后我会对数据进行必要的清理。 - Eduardo Dennis

Answer 2

除了 @EduardoDennis 的回答之外，还可以使用 NULLIF 来过滤掉零日期：

INSERT INTO newFancyTable (created_at)
SELECT nullif(str_to_date(created, '%Y-%m-%d'), from_days(0)) FROM oldCrappyTable;

请点击这里查看我的完整回答。

- mixel