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

Figure 1

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

Figure 2

6. Replicate the error-reporting SQL, and the manual execution is successful in the database. as shown in Figure 3

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

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();