执行MySQL ALTER TABLE语句遇#1067错误：order_date默认值无效

Hey there, let's break down what's going on here and fix that annoying error.

First off, it might feel confusing—you're trying to add the customer_ar column, but the error is yelling about the existing order_date column. Here's why: when you run an ALTER TABLE statement, MySQL checks your entire table structure against its current SQL mode rules. If any existing column doesn't comply (like having an invalid default value), it'll throw an error—even if you're only modifying a different part of the table.

The root issue here is almost certainly your MySQL server's SQL mode settings, specifically modes like NO_ZERO_DATE or STRICT_TRANS_TABLES (enabled by default in MySQL 5.7+). These modes block invalid date values like '0000-00-00' from being used as defaults, which is likely what your order_date column is set to.

Here are two solid solutions to fix this: #

Solution 1: Fix the order_date column's default value directly

Update the order_date column to use a valid default date first, then run your original ALTER TABLE command:

-- Adjust the date type and default value to match your actual column type
ALTER TABLE foodapp_order MODIFY COLUMN order_date DATE DEFAULT '1970-01-01';

-- Now run your original command
ALTER TABLE foodapp_order ADD COLUMN customer_ar VARCHAR(15) AFTER customer_name;

If order_date is a DATETIME type, use '1970-01-01 00:00:00' as the default instead.

Solution 2: Adjust your MySQL SQL mode temporarily (or permanently)

If you need to allow zero-date values for your use case, you can modify the SQL mode to remove the restrictive rules:

1. First, check your current SQL mode settings:

SELECT @@sql_mode;

Look for values like NO_ZERO_DATE, NO_ZERO_IN_DATE, or STRICT_TRANS_TABLES in the result.

2. Temporarily update the SQL mode (resets when MySQL restarts):

SET sql_mode = 'ONLY_FULL_GROUP_BY,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION';

This removes the modes that block zero-date defaults. Now run your original ALTER TABLE command—it should work smoothly.

3. For a permanent change (requires restarting MySQL):
   Edit your MySQL configuration file (called my.cnf on Linux, my.ini on Windows) and update the sql_mode line to exclude the restrictive modes. For example:

sql_mode = "ONLY_FULL_GROUP_BY,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION"

Save the file, restart MySQL, and your changes will stick around through server reboots.

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