从MySQL日期字段阻塞“0000-00-00”

Question

我有一个数据库，旧代码喜欢在date和DateTime列中插入“0000-00-00”，而不是真正的日期。所以我有以下两个问题：

1. 我能在db级别上做些什么来阻止它吗？我知道可以将列设置为not-null，但这似乎没有阻止这些零值。
2. 检测日期字段中现有的零值的最佳方法是什么？我有大约100个表，每个表有2-3个日期列，我不想单独查询它们。

后续行动：

默认值已设置为空。很久以前，缺省值是“0000-00-00”。有些代码仍然显式地放置“0000-00-00”。我更愿意强制该代码抛出一个错误，这样我就可以隔离并删除它。

Answer 1

我能在db级别上做些什么来阻止它吗？

是的，启用NO_ZERO_DATE模式：

SET sql_mode = 'NO_ZERO_DATE';

这种行为已记录在案。。此外，您可能还需要设置模式以包括NO_ZERO_IN_DATE.

还要确保sql_mode包括STRICT_ALL_TABLES或STRICT_TRANS_TABLES；如果没有这些NO_ZERO_IN_DATE，则只提供警告，但insert仍然成功。

检测日期字段中现有的零值的最佳方法是什么？我有大约100个表，每个表有2-3个日期列，我不想单独查询它们。

单独的列意味着它们必须被单独检查--你对此无能为力。

Answer 2

假设您不能轻松地修复数据并“设置sql_mode =‘NO_零_DATE’”，那么您可以在表上创建一个视图.

CREATE VIEW filter AS
SELECT other_column, 
CASE 
  WHEN realtable.dodgy_date = 0 THEN NULL 
  ELSE realtable.dodgy_date
END AS dodgy_date
FROM realtable;

Answer 3

SET SQL_MODE='ALLOW_INVALID_DATES';
SET GLOBAL sql_mode=(SELECT REPLACE(@@sql_mode,'NO_ZERO_DATE',''));   
SET GLOBAL sql_mode=(SELECT REPLACE(@@sql_mode,'NO_ZERO_IN_DATE',''));

只需在数据库上运行以上查询即可。它将永久解决这个问题。