MySQL timestamp 默认值(explicit_defaults_for_timestamp)

迁移数据库遇 timestamp 字段 set null。出现报错

解决方案

mysql> show variables like 'explicit_defaults_for_timestamp';
+---------------------------------+-------+
| Variable_name                   | Value |
+---------------------------------+-------+
| explicit_defaults_for_timestamp | ON   |
+---------------------------------+-------+

>mysql> set session explicit_defaults_for_timestamp=OFF;

set session 只针对当前会话有效。想要永久生效在数据库配置文件中添加 explicit_defaults_for_timestamp=OFF

官网说明：https://dev.mysql.com/doc/refman/8.0/en/server-system-variables.html#sysvar_explicit_defaults_for_timestamp

修改后实验结果

mysql> CREATE TABLE `test_timestamp` (
    ->   `id` int NOT NULL AUTO_INCREMENT,
    ->   `c1` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
    ->   `c2` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
    ->   `c3` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
    ->   `c4` timestamp NULL DEFAULT '0000-00-00 00:00:00',
    ->   `c5` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00' ON UPDATE current_timestamp(),
    ->   PRIMARY KEY (`id`)
    -> ) ENGINE=InnoDB AUTO_INCREMENT=8 DEFAULT CHARSET=utf8mb3;
Query OK, 0 rows affected, 1 warning (0.01 sec)

mysql> 
mysql> 
mysql> insert into test_timestamp (c1, c2, c3, c4, c5) values (null, null, null, null, null);
Query OK, 1 row affected (0.00 sec)

mysql> 
mysql> 
mysql> select * from test_timestamp;
+----+---------------------+------+---------------------+------+---------------------+
| id | c1                  | c2   | c3                  | c4   | c5                  |
+----+---------------------+------+---------------------+------+---------------------+
|  8 | 2024-11-21 17:52:36 | NULL | 2024-11-21 17:52:36 | NULL | 2024-11-21 17:52:36 |
+----+---------------------+------+---------------------+------+---------------------+
1 row in set (0.00 sec)