Laravel 8迁移执行SQL报错但SQL客户端运行正常的原因及解决

一、报错原因 #

1. 数据库模式差异：SQL客户端（如SQLPlus）连接数据库时未开启严格模式，MySQL允许0000-00-00 00:00:00这类不符合SQL标准的零值时间存在；而Laravel 8默认在config/database.php中开启了MySQL的strict模式，该模式会严格校验datetime字段格式，零值时间会被判定为非法。
2. 外键触发全表校验：添加外键约束时，MySQL会扫描整张表的数据做一致性检查，这一过程会触发对现有数据所有字段的格式校验，而平时仅在插入/更新数据时校验，所以之前的零值时间问题未暴露，直到迁移加外键才触发报错。

二、部署避坑方法 #

• 提前清理脏数据：执行外键迁移前，批量修正created_at和updated_at的零值记录，替换为合法的默认时间（比如1970-01-01 00:00:01），执行SQL如下：

UPDATE `questions` 
SET `created_at` = '1970-01-01 00:00:01', `updated_at` = '1970-01-01 00:00:01' 
WHERE `created_at` = '0000-00-00 00:00:00' OR `updated_at` = '0000-00-00 00:00:00';

• 统一环境数据库配置：确保开发、测试、生产环境的MySQL严格模式一致，建议保持Laravel默认的strict开启状态，提前发现数据格式问题，避免脏数据积累。
• 迁移脚本前置数据修正：在Laravel迁移文件中，先添加数据修正逻辑，再执行外键创建操作，确保数据合法后再建约束，示例代码：

public function up()
{
    // 修正零值时间字段
    DB::table('questions')
        ->where(fn($query) => $query->where('created_at', '0000-00-00 00:00:00')
                                    ->orWhere('updated_at', '0000-00-00 00:00:00'))
        ->update([
            'created_at' => '1970-01-01 00:00:01',
            'updated_at' => '1970-01-01 00:00:01'
        ]);

    // 创建外键约束
    Schema::table('questions', function (Blueprint $table) {
        $table->foreign('question_display_format_id')
              ->references('id')
              ->on('question_display_formats');
    });
}

• 业务层避免零值时间：开发时依赖Laravel模型自动维护created_at和updated_at字段，不要手动设置为0000-00-00 00:00:00，确保所有新增/更新数据的时间字段合法。

内容的提问来源于stack exchange，提问作者Carmageddon