Set the default value of DateTime in Laravel 9, MySQL 8.0 to0000-00-00 00:00:00Time error: sqlstate[42000]: Syntax error or access Violation: 1067 Invalid default value forOPERATED_AT_GMT
1. Set the default value of DateTime in MySQL 8.0 to be0000-00-00 00:00:00Time error: sqlstate[42000]: Syntax error or access Violation: 1067 Invalid default value forOPERATED_AT_GMT. as shown in Figure 1
2. Reference:https://www.shuijingwanwq.com/2022/09/28/6993/In Navicat for MySQL, when the table is copied from one database to another, an error is reported:[ERR]1292 – Incorrect DateTime Value: ‘0000-00-00 00:00:00’ for column ‘created_at_gmt’ at row 1 (in MySQL 8.0).3. Now you need to set the SQL mode permanently. Reference:https://dev.mysql.com/doc/refman/8.0/en/sql-mode.html5.1.11 Server SQL Modes
4. Edit C:\ProgramData\MySQL\MySQL Server 8.0\My.ini , search: sql-mode, delete: no_zero_in_date, no_zero_date, keep other
# sql-mode="ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION"
sql-mode="ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION"
5. After restarting the computer, re-execute the corresponding SQL, and still report an error. However, the value of the datetime modified a record in the database is0000-00-00 00:00:00, can be modified successfully. as shown in Figure 2
6. Replicate the error-reporting SQL, and the manual execution is successful in the database. as shown in Figure 3
7. Reference:https://stackoverflow.com/questions/18067614/how-can-i-set-the-default-value-of-a-timestamp-column-to-the-current-timestamp-w. Adjust the migration file, abandon the setting default value:0000-00-00 00:00:00. as shown in Figure 4
// $table->DateTime(OPERATED_AT_GMT)->default(0000-00-00 00:00:00)->comment(operating time)->index();
$table->datetime(OPERATED_AT_GMT)->comment(operating time)->index();
![在 MySQL 8.0 中设置 datetime 的默认值为 '0000-00-00 00:00:00' 时报错:SQLSTATE[42000]: Syntax error or access violation: 1067 Invalid default value for 'operated_at_gmt'](https://www.shuijingwanwq.com/wp-content/uploads/2024/02/1-3.png)


