explicit_defaults_for_timestamp

mysql explicit_defaults_for_timestamp 变量的作用
mysql 中有这样的一个默认行为,如果一行数据中某些列被更新了,如果这一行中有timestamp类型的列,那么么这个timestamp列的数据

也会被自动更新到 更新操作所发生的那个时间点;这个操作是由explicit_defaults_for_timestamp这个变更控制的

一、体验一下mysql的默认更新行为:

复制代码
mysql> create table t(x int ,y timestamp); – 创建一个带有timestamp列的表
Query OK, 0 rows affected (0.01 sec)

mysql> insert into t(x) values(1); – 只插x列
Query OK, 1 row affected (0.00 sec)

mysql> select * from t; – timestamp列会自动更新
+——+———————+
| x | y |
+——+———————+
| 1 | 2017-06-07 13:48:56 |
+——+———————+
1 row in set (0.00 sec)

mysql> update t set x=2 where x=1; – update 时timestamp列还是会自动更新
Query OK, 1 row affected (0.01 sec)
Rows matched: 1 Changed: 1 Warnings: 0

mysql> select * from t;
+——+———————+
| x | y |
+——+———————+
| 2 | 2017-06-07 13:49:21 |
+——+———————+
1 row in set (0.00 sec)
复制代码

二、如何关闭这一默认行为:

  聪明的你一想到了,只要把explicit_defaults_for_timestamp这个变更设置为on;对于timestamp列的值都要显示指定,那么这一默认行为就

  算是关闭了。来操作一把!

复制代码
mysql> set @@global.explicit_defaults_for_timestamp=on; – 把全局的设置为on 那么新的连接就会被设置成on
Query OK, 0 rows affected (0.00 sec)

mysql> set @@session.explicit_defaults_for_timestamp=on; – 把当前连接explicit_defaults_for_timestamp设置为on
Query OK, 0 rows affected (0.00 sec)

mysql> update t set x=3 ; – 第一次更新 结果是变了
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0

mysql> select * from t;
+——+———————+
| x | y |
+——+———————+
| 3 | 2017-06-07 13:57:21 |
+——+———————+
1 row in set (0.00 sec)

mysql> update t set x=4 ; – 第二次更新 结果还是变了
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0

mysql> select * from t;
+——+———————+
| x | y |
+——+———————+
| 4 | 2017-06-07 13:57:52 |
+——+———————+
1 row in set (0.00 sec)

mysql> show variables like ‘explicit_defaults_for_timestamp’; – 全局发动生效
+———————————+——-+
| Variable_name | Value |
+———————————+——-+
| explicit_defaults_for_timestamp | ON |
+———————————+——-+
1 row in set (0.00 sec)

mysql> show global variables like ‘explicit_defaults_for_timestamp’; – session 级别改动也升效了
+———————————+——-+
| Variable_name | Value |
+———————————+——-+
| explicit_defaults_for_timestamp | ON |
+———————————+——-+
1 row in set (0.00 sec)

– 发现了吧、就算把explicit_defaults_for_timestamp 设置成了on 也不会有效的
复制代码

三、找问题出在了哪里:

  1、explicit_defaults_for_timestamp=off 时表结构

复制代码
CREATE TABLE t (
x int(11) DEFAULT NULL,
y timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

y timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE
复制代码
  2、explicit_defaults_for_timestamp=on 时表结构

复制代码
CREATE TABLE t6 (
x int(11) DEFAULT NULL,
y timestamp NULL DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

y timestamp NULL DEFAULT NULL
复制代码

四、结论:

  explicit_defaults_for_timestamp 变量会直接影响表结构,也就是说explicit_defaults_for_timestamp的作用时间

  是在表定义的时候;你的update | insert 想通过它去改变行为已经太晚了!

五、如何解决这失控的场面:

  1、改表结构

复制代码
mysql> alter table t modify column y timestamp null default null; – 解决办法改表结构
Query OK, 0 rows affected (0.05 sec)
Records: 0 Duplicates: 0 Warnings: 0

mysql> update t set x=1;
Query OK, 1 row affected (0.01 sec)
Rows matched: 1 Changed: 1 Warnings: 0

mysql> select * from t;
+——+———————+
| x | y |
+——+———————+
| 1 | 2017-06-07 13:59:21 |
+——+———————+
1 row in set (0.00 sec)

mysql> update t set x=2; – 再次对比,发现时间没有变哦!
Query OK, 1 row affected (0.01 sec)
Rows matched: 1 Changed: 1 Warnings: 0

mysql> select * from t;
+——+———————+
| x | y |
+——+———————+
| 2 | 2017-06-07 13:59:21 |
+——+———————+
1 row in set (0.00 sec)
复制代码