MySQL 5.7+严格模式下，date字段设为'0000-00-00'默认值报错

Question

在MySQL 5.7+严格模式（STRICT_TRANS_TABLES 或 STRICT_ALL_TABLES）下，为 DATE 类型字段显式指定 DEFAULT '0000-00-00' 将导致建表或修改表结构失败，报错：`Invalid default value for 'xxx'`。这是因为严格模式禁用了“零日期”（'0000-00-00'）这一非标准、语义模糊的值——它既不合法（违反ISO日历规则），也不被DATE类型真正支持（仅在SQL_MODE宽松时被隐式转为NULL或截断）。即使配合 `NO_ZERO_DATE`（默认启用）和 `NO_ZERO_IN_DATE`，该默认值仍被拒绝。常见于从旧版本（如5.6）迁移表结构、ORM框架自动生成DDL或历史遗留脚本中。解决方案包括：改用 NULL 并设 DEFAULT NULL；使用有效最小日期如 '1000-01-01'；或临时调整 SQL_MODE（不推荐生产环境）。根本原则是：严格模式下，DATE 默认值必须为合法、可解析的公历日期。

Answer 1

```html

一、现象层：错误复现与典型报错场景

在 MySQL 5.7+ 启用严格模式（STRICT_TRANS_TABLES 或 STRICT_ALL_TABLES）时，执行如下 DDL 将直接失败：

CREATE TABLE orders (
  id INT PRIMARY KEY,
  shipped_at DATE DEFAULT '0000-00-00'
);

报错信息为：Invalid default value for 'shipped_at'。该错误高频出现在 Laravel/Eloquent、Django ORM 自动生成迁移、MyBatis XML DDL 脚本、或从 MySQL 5.6 升级至 5.7+ 的存量表结构导入过程中。

二、机制层：MySQL 日期语义与 SQL_MODE 深度协同

MySQL 对 DATE 类型的合法性校验并非孤立行为，而是由三重机制联合约束：

• ISO 8601 公历校验：'0000-00-00' 不符合格里高利历规则（无公元 0 年，月份/日范围非法）
• SQL_MODE 组合效应：NO_ZERO_DATE（默认启用）禁止零日期作为显式值；NO_ZERO_IN_DATE 禁止年/月/日任一字段为 0；而 STRICT_* 模式强制将此类违规转为错误而非警告
• 存储引擎层拦截：InnoDB 在元数据解析阶段即拒绝非法默认值，不进入行格式校验流程

三、溯源层：历史兼容性断点与迁移陷阱

下表对比了不同版本与模式下的行为差异：

MySQL 版本	SQL_MODE	'0000-00-00' 作为 DEFAULT	实际写入行为
5.6.x	空或宽松	允许建表	隐式转为 NULL（若列允许 NULL）或截断为 '0000-00-00'（仅 MyISAM 支持）
5.7.5+	含 STRICT_* + NO_ZERO_DATE	建表失败	DDL 解析阶段终止，不生成 .frm/.ibd
8.0.16+	默认强制 STRICT_TRANS_TABLES	绝对禁止	即使禁用 NO_ZERO_DATE，STRICT 模式仍拒绝

四、解决方案层：生产环境安全路径选择

以下为按风险等级排序的可行方案（✅ 推荐 / ⚠️ 谨慎 / ❌ 禁止）：

1. ✅ DEFAULT NULL：语义最清晰，适配空值业务逻辑（如“未发货”），且兼容所有索引类型
2. ✅ DEFAULT '1000-01-01'：MySQL 支持的最小合法公历日期（ISO 标准起始年），适用于需非 NULL 占位的场景
3. ⚠️ ALTER TABLE ... MODIFY COLUMN ... DEFAULT '1970-01-01'：业务可读性强，但需确认是否与 UNIX epoch 语义冲突
4. ❌ SET SQL_MODE = ''：临时绕过校验——破坏 ACID 一致性保障，违反 OWASP 安全基线

五、架构层：面向未来的防御性设计实践

为规避此类问题，建议在团队工程规范中嵌入以下检查点：

graph LR
  A[新表 DDL 编写] --> B{是否含 '0000-00-00'?}
  B -->|是| C[触发 CI/CD 静态扫描告警]
  B -->|否| D[检查 DEFAULT 是否为 ISO 合法日期]
  D --> E[验证年份 ≥ 1000 且月日有效]
  E --> F[通过 Schema Linter]

六、生态层：ORM 框架适配指南

主流框架需针对性配置：

• Laravel 9+：在 config/database.php 中设置 'strict' => true 并禁用 mysql_mode 自定义
• Django 4.2+：确保 USE_TZ = True，避免 auto_now_add 与零日期耦合
• MyBatis Generator：自定义 columnOverride 规则，将 date 类型的 default 映射为 NULL

七、验证层：自动化回归测试用例

在数据库 CI 流程中加入如下验证脚本：

-- 验证所有 DATE 字段默认值合法性
SELECT table_name, column_name, column_default 
FROM information_schema.columns 
WHERE table_schema = DATABASE() 
  AND data_type = 'date' 
  AND column_default IS NOT NULL 
  AND column_default NOT IN ('NULL', 'CURRENT_DATE', 'CURRENT_TIMESTAMP')
  AND column_default NOT REGEXP '^''[1-9][0-9]{3}-[0-1][0-9]-[0-3][0-9]''$';

该查询可捕获所有潜在非法默认值，集成至 GitLab CI 的 before_script 阶段。

```