betway必威官网欢迎您-最新官方网站

点击立可下载开户即可下载betway必威官网欢迎您提供的所有游戏,因为最新官方网站与欧洲的八大电视台都展开了合作,betway必威官网欢迎您为你选择最优质的娱乐,欢迎您来体验试玩!。

您的位置:betway必威官网欢迎您 > betway必威官网欢迎您 > 【betway必威官网欢迎您】设置TIMESTAMP和DATETIME的自

【betway必威官网欢迎您】设置TIMESTAMP和DATETIME的自

2019-09-17 01:03

最近有一个关于MySQL版本升级的事,涉及到一些关于时间类型的细节问题需要查明,因此到官网找到相关文章,翻出来比较方便自己理解,博客这里也贴一下。

MySQL中有关TIMESTAMP和DATETIME的总结

一、MySQL中如何表示当前时间?

 

其实,表达方式还是蛮多的,汇总如下:

 

CURRENT_TIMESTAMP

 

CURRENT_TIMESTAMP()

 

NOW()

 

LOCALTIME

 

LOCALTIME()

 

LOCALTIMESTAMP

 

LOCALTIMESTAMP()

 

二、关于TIMESTAMP和DATETIME的比较

 

一个完整的日期格式如下:YYYY-MM-DD HH:MM:SS[.fraction],它可分为两部分:date部分和time部分,其中,date部分对应格式中的“YYYY-MM-DD”,time部分对应格式中的“HH:MM:SS[.fraction]”。对于date字段来说,它只支持date部分,如果插入了time部分的内容,它会丢弃掉该部分的内容,并提示一个warning。

 

如下所示:

mysql> create table test(id int,hiredate date);
Query OK, 0 rows affected (0.01 sec)

mysql> insert into test values(1,'20151208000000');
Query OK, 1 row affected (0.00 sec)

mysql> insert into test values(1,'20151208104400');
Query OK, 1 row affected, 1 warning (0.01 sec)

mysql> show warning;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'warning' at line 1
mysql> select * from test;
+------+------------+
| id   | hiredate   |
+------+------------+
|    1 | 2015-12-08 |
|    1 | 2015-12-08 |
+------+------------+
2 rows in set (0.00 sec)

注:第一个没提示warning的原因在于它的time部分都是0

 

TIMESTAMP和DATETIME的相同点:

 

1> 两者都可用来表示YYYY-MM-DD HH:MM:SS[.fraction]类型的日期。

 

TIMESTAMP和DATETIME的不同点:

 

1> 两者的存储方式不一样

 

对于TIMESTAMP,它把客户端插入的时间从当前时区转化为UTC(世界标准时间)进行存储。查询时,将其又转化为客户端当前时区进行返回。

 

而对于DATETIME,不做任何改变,基本上是原样输入和输出。

 

下面,我们来验证一下

 

首先创建两种测试表,一个使用timestamp格式,一个使用datetime格式。

mysql> create table test(id int,hiredate timestamp);
Query OK, 0 rows affected (0.01 sec)

mysql> insert into test values(1,'20151208000000');
Query OK, 1 row affected (0.00 sec)

mysql> create table test1(id int,hiredate datetime);
Query OK, 0 rows affected (0.01 sec)

mysql> insert into test1 values(1,'20151208000000');
Query OK, 1 row affected (0.00 sec)

mysql> select * from test;
+------+---------------------+
| id   | hiredate            |
+------+---------------------+
|    1 | 2015-12-08 00:00:00 |
+------+---------------------+
1 row in set (0.01 sec)

mysql> select * from test1;
+------+---------------------+
| id   | hiredate            |
+------+---------------------+
|    1 | 2015-12-08 00:00:00 |
+------+---------------------+
1 row in set (0.00 sec)

两者输出是一样的。

 

其次修改当前会话的时区

mysql> show variables like '%time_zone%'; 
+------------------+--------+
| Variable_name    | Value  |
+------------------+--------+
| system_time_zone | CST    |
| time_zone        | SYSTEM |
+------------------+--------+
2 rows in set (0.00 sec)

mysql> set time_zone='+0:00';
Query OK, 0 rows affected (0.00 sec)

mysql> select * from test;
+------+---------------------+
| id   | hiredate            |
+------+---------------------+
|    1 | 2015-12-07 16:00:00 |
+------+---------------------+
1 row in set (0.00 sec)

mysql> select * from test1;
+------+---------------------+
| id   | hiredate            |
+------+---------------------+
|    1 | 2015-12-08 00:00:00 |
+------+---------------------+
1 row in set (0.01 sec)

上述“CST”指的是MySQL所在主机的系统时间,是中国标准时间的缩写,China Standard Time UT+8:00

 

通过结果可以看出,test中返回的时间提前了8个小时,而test1中时间则不变。这充分验证了两者的区别。

 

2> 两者所能存储的时间范围不一样

 

timestamp所能存储的时间范围为:'1970-01-01 00:00:01.000000' 到 '2038-01-19 03:14:07.999999'。

 

datetime所能存储的时间范围为:'1000-01-01 00:00:00.000000' 到 '9999-12-31 23:59:59.999999'。

 

总结:TIMESTAMP和DATETIME除了存储范围和存储方式不一样,没有太大区别。当然,对于跨时区的业务,TIMESTAMP更为合适。

 

三、关于TIMESTAMP和DATETIME的自动初始化和更新

 

首先,我们先看一下下面的操作

 

mysql> create table test(id int,hiredate timestamp);
Query OK, 0 rows affected (0.01 sec)

mysql> insert into test(id) values(1);
Query OK, 1 row affected (0.00 sec)

mysql> select * from test;
+------+---------------------+
| id   | hiredate            |
+------+---------------------+
|    1 | 2015-12-08 14:34:46 |
+------+---------------------+
1 row in set (0.00 sec)

mysql> show create table testG
*************************** 1. row ***************************
       Table: test
Create Table: CREATE TABLE `test` (
  `id` int(11) DEFAULT NULL,
  `hiredate` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=latin1
1 row in set (0.00 sec)

看起来是不是有点奇怪,我并没有对hiredate字段进行插入操作,它的值自动修改为当前值,而且在创建表的时候,我也并没有定义“show create table testG”结果中显示的“ DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP”。

 

其实,这个特性是自动初始化和自动更新(Automatic Initialization and Updating)。

 

自动初始化指的是如果对该字段(譬如上例中的hiredate字段)没有显性赋值,则自动设置为当前系统时间。

 

自动更新指的是如果修改了其它字段,则该字段的值将自动更新为当前系统时间。

 

它与“explicit_defaults_for_timestamp”参数有关。

 

默认情况下,该参数的值为OFF,如下所示:

mysql> show variables like '%explicit_defaults_for_timestamp%';
+---------------------------------+-------+
| Variable_name                   | Value |
+---------------------------------+-------+
| explicit_defaults_for_timestamp | OFF   |
+---------------------------------+-------+
1 row in set (0.00 sec)

下面我们看看官档的说明:

 

By default, the first TIMESTAMP column has both DEFAULT CURRENT_TIMESTAMP and ON UPDATE CURRENT_TIMESTAMP if neither is specified explicitly。

 

很多时候,这并不是我们想要的,如何禁用呢?

 

  1. 将“explicit_defaults_for_timestamp”的值设置为ON。

 

betway必威官网欢迎您,2. “explicit_defaults_for_timestamp”的值依旧是OFF,也有两种方法可以禁用

 

     1> 用DEFAULT子句该该列指定一个默认值

 

     2> 为该列指定NULL属性。

 

如下所示:

mysql> create table test1(id int,hiredate timestamp null);
Query OK, 0 rows affected (0.01 sec)

mysql> show create table test1G
*************************** 1. row ***************************
       Table: test1
Create Table: CREATE TABLE `test1` (
  `id` int(11) DEFAULT NULL,
  `hiredate` timestamp NULL DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1
1 row in set (0.00 sec)

mysql> create table test2(id int,hiredate timestamp default 0);
Query OK, 0 rows affected (0.01 sec)

mysql> show create table test2G
*************************** 1. row ***************************
       Table: test2
Create Table: CREATE TABLE `test2` (
  `id` int(11) DEFAULT NULL,
  `hiredate` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00'
) ENGINE=InnoDB DEFAULT CHARSET=latin1
1 row in set (0.00 sec)

 

 

在MySQL 5.6.5版本之前,Automatic Initialization and Updating只适用于TIMESTAMP,而且一张表中,最多允许一个TIMESTAMP字段采用该特性。从MySQL 5.6.5开始,Automatic Initialization and Updating同时适用于TIMESTAMP和DATETIME,且不限制数量。

一、MySQL中如何表示当前时间? 其实,表达方式还是蛮多的,汇总如下: CURRENT_TIMESTAMP CURRENT_TIMESTAMP() N...

参考官网网址:

一、简介

自MySQL 5.6.5开始TIMESTAMP和DATETIME类型可以实现自动初始化或更新为CURRENT_TIMESTAMP的功能,在5.6.5之前这个特性只有TIMESTAMP才能用。

鉴于现在时间类型基本都用DATETIME(因为TIMESTAMP的范围很小局限性也很大),因此5.6.5之后DATETIME也实现了这个功能。

这个功能详细描述下就是:

  • DATETIME也可以像TIMESTAMP一样将CURRENT_TIMESTAMP设为默认值
  • 如果你为此时间列设置了自动更新的属性,那么只要一条记录的其他任何列值发生改变,时间列都会自动更新为CURRENT_TIMESTAMP。

假如你不想让设置自动更新属性的时间列随其他列值的改变而改变,你可以为他显式的赋一个值。

TIMESTAMP类型:

  • 在8.0.2版本之前,如果你定义了这样一个列:ts timestamp [not null] default xxx,那么除非你显式的给此列赋一个非NULL的值,否则一律插入的当前时间戳,无论你的default值设置的是什么,你可以将其视为一个BUG。你可以通过将explicit_defaults_for_timestamp设置为ON来避免这个现象。
  • 在8.0.2版本之前,如果你定义了一个ts timestamp [not null]列,但没有设置默认值,那么实质上这个列是ts  timestamp not null default current_timestamp on update current_timestamp,同样的你可以通过设置explicit_defaults_for_timestamp为ON来解决此问题。
  • 当explicit_defaults_for_timestamp为OFF时,除非指定timestamp可为NULL,否则MySQL自动为timestamp设置not null属性。

在5.6.5之前,以上timestamp的表现可以算是他相比datetime的一个优势,也有人把它视为一个BUG认为他很麻烦,但无论如何在5.6.5之前如果你有将CURRENT_TIMESTAMP设为默认值的需求,那你只能选择timestamp类型,好在5.6.5之后datetime也支持了。

关于explicit_defaults_for_timestamp参数:

在MySQL5.6.6引入了explicit_defaults_for_timestamp参数来解决以上timestamp默认值的问题(此参数在8.0.1之前的版本默认是OFF,只读参数),而在MySQL8.0.2之后此参数默认值变为ON而且可以在线修改。将此参数设为ON后你为timestamp列设置的默认值就可以生效了。

从这里可以看出explicit_defaults_for_timestamp=on的作用就是,使得你自己为timestamp列设置的default值生效,当你未设置默认值和自更新时MySQL也不会自作主张的为not null时间列添加current_timestamp和on update current_timestamp的默认选项。

此外此参数在8.0.11中提示已经要逐步弃用了。

 

二、如何设置时间列(本文如无版本号提示,示例统一是MySQL5.6的环境)

如果你要设置一个自动初始化+自动更新为CURRENT_TIMESTAMP的时间列(TIMESTAMP or DATETIME),那么使用“DEFAULT CURRENT_TIMESTAMP”+“ON UPDATE CURRENT_TIMESTAMP”的组合,定义的顺序无所谓,可替代CURRENT_TIMESTAMP的关键字有:CURRENT_TIMESTAMP(), NOW(), LOCALTIME, LOCALTIME(), LOCALTIMESTAMP, 以及LOCALTIMESTAMP()。

官网详细的列出了可能出现的默认值+自动更新选项的组合:

1)

CREATE TABLE t1 (
ts TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
dt DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP);

2)

CREATE TABLE t1 (
ts TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
dt DATETIME DEFAULT CURRENT_TIMESTAMP
);
CREATE TABLE t1 (
ts TIMESTAMP DEFAULT 0,
dt DATETIME DEFAULT 0
);

3)

CREATE TABLE t1 (
ts TIMESTAMP DEFAULT 0 ON UPDATE CURRENT_TIMESTAMP,
dt DATETIME DEFAULT 0 ON UPDATE CURRENT_TIMESTAMP
);

4)自更新的timestamp列如果未设置默认值且未明确指定可为NULL,则默认值为0,0代表0000-00-00 00:00:00。

CREATE TABLE t1 (
ts1 TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, -- default 0
ts2 TIMESTAMP NULL ON UPDATE CURRENT_TIMESTAMP -- default NULL
);
CREATE TABLE t1 (
dt1 DATETIME ON UPDATE CURRENT_TIMESTAMP, -- default NULL
dt2 DATETIME NOT NULL ON UPDATE CURRENT_TIMESTAMP -- default 0
);

    自更新的DATETIME列如果未设置默认值且定义了not null,那么默认值为0。

5)

CREATE TABLE t1 (
ts1 TIMESTAMP DEFAULT 0,
ts2 TIMESTAMP DEFAULT CURRENT_TIMESTAMP
ON UPDATE CURRENT_TIMESTAMP);
CREATE TABLE t2 (
ts1 TIMESTAMP NULL,
ts2 TIMESTAMP DEFAULT CURRENT_TIMESTAMP
ON UPDATE CURRENT_TIMESTAMP);
CREATE TABLE t3 (
ts1 TIMESTAMP NULL DEFAULT 0,
ts2 TIMESTAMP DEFAULT CURRENT_TIMESTAMP
ON UPDATE CURRENT_TIMESTAMP);

对于本次创建的3个表:

  • 3个表的第一个timestamp列都没有自动初始化和自动更新的属性。(自动初始化是指调用的系统时间函数)
  • 3个表的差别在于ts1列对NULL值的处理,t1.ts1非空,当你为他赋值为NULL时插入的其实是当前时间戳,t2.ts1,t3.ts1允许null值,因此当你插入NULL时最终被插入的就是NULL。
  • t2和t3对于ts1的默认值处理也不一样,t2.ts1允许null值且未定义默认值,因此默认值就是NULL,t3.ts1允许null值且定义了default 0,因此默认值就是0。

本文由betway必威官网欢迎您发布于betway必威官网欢迎您,转载请注明出处:【betway必威官网欢迎您】设置TIMESTAMP和DATETIME的自

关键词:

  • 上一篇:没有了
  • 下一篇:没有了