如何解决Timestamp字段的Defalut值不生效的问题？

如下的表结构， INSERT INTO tenant_rd (register_time) VALUES (null) ，其中 register_time 是一个空对象，数据库表中会新增一条记录，为什么 register_time 插入的值为 current date 而不是 DEFAULT '2022-04-05 10:00:00'？

  
CREATE TABLE `tenant_rd` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT COMMENT '主键ID',
  `register_time` timestamp NOT NULL DEFAULT '2022-04-05 10:00:00' COMMENT '注册时间',
  PRIMARY KEY (`id`),
  KEY `idx_register_time` (`register_time`)
) ENGINE=InnoDB AUTO_INCREMENT=2478899769 DEFAULT CHARSET=utf8mb4 

实际测试复现问题

MySQL [dbtest]> INSERT INTO tenant_rd (`register_time`) VALUES (null);
Query OK, 1 row affected (0.002 sec)


MySQL [dbtest]> select * from tenant_rd;
+------------+---------------------+
| id         | register_time       |
+------------+---------------------+
| 2478899767 | 2022-04-05 14:46:40 |
+------------+---------------------+
1 row in set (0.001 sec)

1.查看当前当前参数配置

MySQL [dbtest]> show variables like 'explicit_defaults_for_timestamp';
+---------------------------------+-------+
| Variable_name                   | Value |
+---------------------------------+-------+
| explicit_defaults_for_timestamp | OFF   |
+---------------------------------+-------+
1 row in set (0.003 sec)

2.根据官方文档的说明[1]，系统会自动将 null VALUE转换，这是预期行为，这种情况下 Defaut 值不会生效。

In addition, if the explicit_defaults_for_timestamp system variable is disabled, you can initialize or update any TIMESTAMP (but not DATETIME) column to the current date and time by assigning it a NULL value, unless it has been defined with the NULL attribute to permit NULL values.

3.如果想让 Default 值生效，可以插入数据值时，不插入 timestamp 类型的列

MySQL [dbtest]> INSERT INTO tenant_rd (`id`) VALUES (5);
Query OK, 1 row affected (0.001 sec)

MySQL [dbtest]> select * from tenant_rd;
+------------+---------------------+
| id         | register_time       |
+------------+---------------------+
|          5 | 2022-04-05 10:00:00 |
| 2478899767 | 2022-04-05 14:46:40 |
+------------+---------------------+

[1] https://dev.mysql.com/doc/refman/5.7/en/timestamp-initialization.html

如果您有其他问题，欢迎您联系火山引擎技术支持服务