MySQL添加ps_product_shop表行时遇#1067错误求助

Hey there, let's tackle this #1067 - Invalid default value for 'available_date' error you're hitting when inserting into ps_product_shop. This issue almost always boils down to MySQL's strict SQL mode rejecting an invalid default value for the available_date field—here's how to fix it, with a few options depending on your needs:

1. Understand the Root Cause #

Chances are, your available_date column has a default value like '0000-00-00' (a "zero date"), and your MySQL server has the NO_ZERO_DATE or STRICT_TRANS_TABLES mode enabled. These modes prevent invalid date values from being used as defaults, which is why you're getting the error.

2. Quick Temporary Fix (Resets on MySQL Restart) #

If you just need to get the insert working right now, you can temporarily adjust the SQL mode:

• First, check your current SQL mode:

  SELECT @@sql_mode;
  

• If you see NO_ZERO_DATE or STRICT_TRANS_TABLES in the result, run this to disable the zero date restriction (adjust the mode list to match your current settings minus the problematic ones):

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

• Now try inserting your data again—it should work.

3. Permanent Fix (Modify MySQL Configuration) #

To make the change stick through MySQL restarts:

• Locate your MySQL configuration file: it's usually my.cnf (Linux) or my.ini (Windows).
• Open it and find the [mysqld] section. Add or update the sql_mode line to exclude NO_ZERO_DATE:

  [mysqld]
  sql_mode = "ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION"
  

• Save the file and restart your MySQL server. Verify the change with SELECT @@sql_mode;.

4. Best Practice Fix: Update the Column's Default Value (Recommended) #

Instead of disabling MySQL's strict checks, it's better to make the column's default value valid:

• If available_date can be empty, set its default to NULL:

  ALTER TABLE ps_product_shop MODIFY COLUMN available_date DATE NULL DEFAULT NULL;
  

• If you want the default to be the current date, use CURRENT_DATE:

  ALTER TABLE ps_product_shop MODIFY COLUMN available_date DATE DEFAULT CURRENT_DATE;
  

Note: Always back up your table before modifying its structure to avoid data loss!

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