mysqlexplicit_defaults_for_timestamp参数
在mysql中：- timestamp列如果没有显式定义为null，默认会被设置为not null属性。(其它的数据类型如果没有显式定义为not null，默认是可以为null的)。设置timestamp的列值为null，会⾃动存储为当前timestamp- 表中的第⼀个timestamp列，如果没有定义为null、定义default值或者on update，会⾃动分配default current_timestamp和on update current_timestamp属性- 表中第⼀个timestamp列之后的所有timestamp 列，如果没有被定义为null、定义default值，会⾃动被指定默认值'0000-00-00 00:00:00'。在插⼊时，如果没有指定这些列的值，会⾃动指定为'0000-00-00 00:00:00'，且不会产⽣警告
1 mysql>create table timestamp_eg(
2-> id int not null auto_increment,
3-> time1 timestamp,
4-> time2 timestamp,
5-> time3 timestamp NOT NULL DEFAULT'2010-01-01 00:00:00',
6-> time4 timestamp,
7->primary key(id));
8 Query OK, 0 rows affected (0.01 sec)
9
10 mysql>insert into timestamp_eg(id) values(1);
11 Query OK, 1 row affected (0.00 sec)
12
13 mysql>select*from timestamp_eg;
14+----+---------------------+---------------------+---------------------+---------------------+
15| id | time1 | time2 | time3 | time4 |
16+----+---------------------+---------------------+---------------------+---------------------+
17|1|2015-12-1609:23:33|0000-00-0000:00:00|2010-01-0100:00:00|0000-00-0000:00:00|
18+----+---------------------+---------------------+---------------------+---------------------+
191 row in set (0.01 sec)
20
21 mysql>update timestamp_eg set id=2where id=1;
22 Query OK, 1 row affected (0.02 sec)
23 Rows matched: 1 Changed: 1 Warnings: 0
24
25 mysql>select*from timestamp_eg;
26+----+---------------------+---------------------+---------------------+---------------------+
27| id | time1 | time2 | time3 | time4 |
28+----+---------------------+---------------------+---------------------+---------------------+
29|2|2015-12-1609:25:01|0000-00-0000:00:00|2010-01-0100:00:00|0000-00-0000:00:00|
30+----+---------------------+---------------------+---------------------+---------------------+
311 row in set (0.00 sec)
32
33 mysql>insert into timestamp_eg(id,time4) values(3,'2011-01-01 00:00:00');
34 Query OK, 1 row affected (0.00 sec)
35
36 mysql>select*from timestamp_eg;
37+----+---------------------+---------------------+---------------------+---------------------+
38| id | time1 | time2 | time3 | time4 |
39+----+---------------------+---------------------+---------------------+---------------------+
40|2|2015-12-1609:25:01|0000-00-0000:00:00|2010-01-0100:00:00|0000-00-0000:00:00|
41|3|2015-12-1609:28:04|0000-00-0000:00:00|2010-01-0100:00:00|2011-01-0100:00:00|
42+----+---------------------+---------------------+---------------------+---------------------+
432 rows in set (0.00 sec)
44
45 mysql>update timestamp_eg set id=4where id=3;
46 Query OK, 1 row affected (0.00 sec)
47 Rows matched: 1 Changed: 1 Warnings: 0
48
49 mysql>select*from timestamp_eg;
50+----+---------------------+---------------------+---------------------+---------------------+
51| id | time1 | time2 | time3 | time4 |
52+----+---------------------+---------------------+---------------------+---------------------+
53|2|2015-12-1609:25:01|0000-00-0000:00:00|2010-01-0100:00:00|0000-00-0000:00:00|
54|4|2015-12-1609:28:24|0000-00-0000:00:00|2010-01-0100:00:00|2011-01-0100:00:00|
55+----+---------------------+---------------------+---------------------+---------------------+
562 rows in set (0.00 sec)
57
58 mysql>
从MySQL5.6.6这种默认设置的⽅法被废弃了。在MySQL启动时会出现以下警告：
1[Warning]TIMESTAMP with implicit DEFAULT value is deprecated.
2 Please use--explicit_defaults_for_timestamp server option (see
3 documentation for more details).
要想取消该警告，在启动mysql时，myf中加⼊
1[mysqld]
2 explicit_defaults_for_timestamp=true
修改该参数后，timestamp类型的列的处理⽅式也会发⽣变化：
- timestamp列如果没有显式定义为not null，则⽀持null属性。设置timestamp的列值为null，就不会被设置为current timestamp
- 不会⾃动分配default current_timestamp和on update current_timestamp属性，这些属性必须显式指定
- 声明为not null且没有显式指定默认值是没有默认值的。表中插⼊列，⼜没有给timestamp列赋值时，如果是严格sql模式，会抛出⼀个错误；如果严格sql模式没有启⽤，该列会赋值为’0000-00-00 00:00:00′，同时出现⼀个警告。（这和mysql处理其它时间类型数据⼀样，如datetime）
1 mysql>create table timestamp_02(
2-> id int not null auto_increment,mysql需要安装documentation
3-> time1 timestamp not null,
4->primary key(id)
5-> );
6 Query OK, 0 rows affected (0.03 sec)
7
8 mysql>insert into timestamp_02(id) values(1);
9 ERROR 1364 (HY000): Field 'time1' doesn't have a default value
10mysql>
版权声明：本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198，我们将在24小时内删除。
发表评论