MySQL explicit_defaults_for_timestamp

水深无声 2022-02-01 11:59 198阅读 0赞

默认情况下，timestamp类型字段所在数据行被更新时，该字段会自动更新为当前时间，而参数explicit\_defaults\_for\_timestamp控制这一种行为。  
explicit\_defaults\_for\_timestamp=off，数据行更新时，timestamp类型字段更新为当前时间；  
explicit\_defaults\_for\_timestamp=on，数据行更新时，timestamp类型字段不更新为当前时间。

例如：

mysql> set explicit_defaults_for_timestamp=off;
    Query OK, 0 rows affected (0.00 sec)
    
    mysql> create table sam (id int,col1 timestamp);
    Query OK, 0 rows affected (0.02 sec)
    
    mysql> insert into sam(id) values (1);
    Query OK, 1 row affected (0.01 sec)
    
    mysql> select * from sam;
    +------+---------------------+
    | id   | col1                |
    +------+---------------------+
    |    1 | 2019-05-14 14:22:23 |
    +------+---------------------+
    1 row in set (0.00 sec)

mysql> set explicit_defaults_for_timestamp=on;
    Query OK, 0 rows affected (0.00 sec)
    
    mysql> create table tom(id int,col1 timestamp);
    Query OK, 0 rows affected (0.01 sec)
    
    mysql> insert into tom(id) values (1);
    Query OK, 1 row affected (0.00 sec)
    
    mysql> select * from tom;
    +------+------+
    | id   | col1 |
    +------+------+
    |    1 | NULL |
    +------+------+
    1 row in set (0.00 sec)

两表的表结构差异：

mysql> show create table sam\G
    *************************** 1. row ***************************
           Table: sam
    Create Table: CREATE TABLE `sam` (
      `id` int(11) DEFAULT NULL,
      `col1` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4
    1 row in set (0.00 sec)
    
    mysql> 
    mysql> 
    mysql> show create table tom\G
    *************************** 1. row ***************************
           Table: tom
    Create Table: CREATE TABLE `tom` (
      `id` int(11) DEFAULT NULL,
      `col1` timestamp NULL DEFAULT NULL
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4
    1 row in set (0.00 sec)