您的位置:新葡亰496net > 网络数据库 > timestamp自动更新和初始化

timestamp自动更新和初始化

发布时间:2019-10-20 23:47编辑:网络数据库浏览(86)

    目前有贰个关于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,不做别的改动,基本上是面容输入和出口。

     

    上面,我们来证多美滋(Dumex)(Nutrilon)下

     

    第后生可畏创立两种测量检验表,一个用到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 斯坦dard Time UT 8:00

     

    通过结果能够见见,test中回到的时间提前了8个钟头,而test第11中学时间则不改变。那丰富证实了彼此的界别。

     

    2> 两个所能存款和储蓄的时光限制不平等

     

    timestamp所能存储的年月限制为:'一九六八-01-01 00:00:01.000000' 到 '2038-01-19 03:14:07.999999'。

     

    datetime所能存款和储蓄的时日范围为:'一千-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 CU君越RENT_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。

     

    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中如何表示方今光阴? 其实,表明情势照旧蛮多的,汇总如下: CU大切诺基RENT_TIMESTAMP CURRENT_TIMESTAMP() N...

    1.概述

    正文将因而实例比较MySQL中的datetime与timestamp,就算那三种方法其实表示同样种档次,但要么有一定的分别。

    参照官方网址网站:

    在我们设计表的时候,思索将行数据的始建时间和最终更新时间记录下来是很好的实行。非常是唯恐需求做多少同步照旧对数码新鲜度有须要的表。举些应用场景,更新距上次立异超过2小时的行数据,恐怕是将八个月前的订单数量归档等等。大家想把那么些的供给丢给数据库服务器管理,而不是在应用程序中对每一条语句设置创造时间和终极更新时间字段。在mysql中,那贯彻起来非常轻便。我们要求依靠于DEFAULT CURRENT_TIMESTAMP 和 ON UPDATE CURRENT_TIMESTAMP

      相同

    2.简短示例

      显示

    一、简介

     1 --创建测试表
     2 CREATE TABLE `timestampTest` (
     3   `id` int(11) NOT NULL AUTO_INCREMENT,
     4   `name` varchar(20) DEFAULT NULL,
     5   `create_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
     6   `last_modify_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
     7   PRIMARY KEY (`id`)
     8 ) ENGINE=InnoDB DEFAULT CHARSET=utf8
     9 
    10 --检测默认值,插入测试数据
    11 INSERT INTO timestampTest (name) VALUES ('aa'),('bb'),('cc');
    12 
    13 --检测自动更新,更新某条数据
    14 UPDATE timestampTest SET name = 'ab' WHERE id = 1;
    

      TIMESTAMP列的显得格式与DATETIME列一样。换句话说,显示上涨的幅度固定在19字符,并且格式为YYYY-MM-DD HH:MM:SS。

    新葡亰496net,自MySQL 5.6.5初步TIMESTAMP和DATETIME类型能够完结活动初步化或更新为CUKoleosRENT_TIMESTAMP的功效,在5.6.5事先这几个特点独有TIMESTAMP本事用。

    新葡亰496net 1

      不同

    鉴于未来日子项目基本都用DATETIME(因为TIMESTAMP的范围一点都不大局限性也异常的大),因而5.6.5自此DATETIME也达成了这几个职能。

    事例特别轻巧,结果也很醒目,我就不加赘述了。

      范围

    以此成效详细描述下就是:

    3.思考

      datetime 以YYYY-MM-DD HH:MM:SS格式检索和出示DATETIME值。协助的限量为一千-01-01 00:00:00到9999-12-31 23:59:59TIMESTAMP值无法早于一九六七或晚于2037

    • DATETIME也得以像TIMESTAMP同样将CURAV4RENT_TIMESTAMP设为暗许值
    • 若果您为此时间列设置了自动更新的脾气,那么风姿罗曼蒂克旦一条记下的别样任何列值发生转移,时间列都会自动更新为CU帕杰罗RENT_TIMESTAMP。
    • 进行update语句,并未有改观列值(也许说设置值为当下值),on update current_timestamp列是不是会更新?

      储存

    假若你不想让设置自动更新属性的光阴列随别的列值的更改而改变,你可感觉他显式的赋一个值。

        不会,大家能够看一下实践完update后出现的唤醒——Rows matched: 1 Changed: 0 Warnings: 0。官方文书档案的演说是

      TIMESTAMP

    TIMESTAMP类型:

    An auto-updated column remains unchanged if all other columns are set to their current values. To prevent an auto-updated column from updating when other columns change, explicitly set it to its current value. To update an auto-updated column even when other columns do not change, explicitly set it to the value it should have

      1.4个字节储存(Time stamp value is stored in 4 bytes)

    • 在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属性。

        新葡亰496net 2

      2.值以UTC格式保存( it stores the number of milliseconds)

    在5.6.5以前,以上timestamp的显现能够算是他对待datetime的三个优势,也是有人把它视为一个BUG认为他很辛劳,但无论如何在5.6.5以前即使你有将CU大切诺基RENT_TIMESTAMP设为暗中认可值的须要,那您不得不采取timestamp类型,幸好5.6.5随后datetime也扶助了。

    • CURRENT_TIMESTAMP,CURRENT_TIMESTAMP(),LOCALTIME,LOCALTIME(),LOCALTIMESTAMP,LOCALTIMESTAMP(),NOW()的关系?

      3.时区转变 ,存储时对日前的时区进行转移,检索时再转移回当前的时区。

    关于explicit_defaults_for_timestamp参数:

        那多少个是同义词关系

      datetime

    在MySQL5.6.6引入了explicit_defaults_for_timestamp参数来消除上述timestamp暗许值的标题(此参数在8.0.1在此之前的版本默许是OFF,只读参数),而在MySQL8.0.2自此此参数暗许值变为ON何况能够在线修改。将此参数设为ON后你为timestamp列设置的暗许值就足以生效了。

    • Timestamp类型的私下认可值

    timestamp自动更新和初始化。  1.8个字节积累(8 bytes storage)

    从这里能够看出explicit_defaults_for_timestamp=on的效应便是,使得你协和为timestamp列设置的default值生效,当您未设置私下认可值和自立异时MySQL也不会自作主张的为not null时间列增多current_timestamp和on update current_timestamp的私下认可选项。

        我们讨论暗许景况(严俊情势)下mysql对timestamp类型的拍卖:

      2.实在格式积存(Just stores what you have stored and retrieves the same thing which you have stored.)

    其他此参数在8.0.1第11中学晋升已经要逐级弃用了。

    mysql不会给timestamp设置暗中认可值,除非显式设置default约束照旧可空null。特例:mysql会给表第叁个timestamp类型的字段同期增多default current_timestamp和on update timestamp

      3.与时区毫不相关(It has nothing to deal with the TIMEZONE and Conversion.)

     

    明确命令禁绝mysql的特例管理有七个方法

      实例相比

    二、怎么着设置时间列(本文如无版本号提示,示例统蒸蒸日上是MySQL5.6的情况)

    1. 设置explicit_defaults_for_timestamp为enable
    2. 显式设置该字段default或然null

      今后本人来做个时区对她们的震慑。

    比如您要设置二个电动初始化 自动更新为CUXC90RENT_TIMESTAMP的时光列(TIMESTAMP or DATETIME),那么使用“DEFAULT CUXC90RENT_TIMESTAMP” “ON UPDATE CURRENT_TIMESTAMP”的构成,定义的依次不留意,可代表CUPAJERORENT_TIMESTAMP的重要字有:CUKoleosRENT_TIMESTAMP(), NOW(), LOCALTIME, LOCALTIME(), LOCALTIMESTAMP, 以及LOCALTIMESTAMP()。

    timestamp列私下认可not null。没有显式内定nullable,那么default null违规

      1.先插入多少个多少insert into `t8` values(now(), now());

    官方网站详细的列出了说不定出现的暗中认可值 自动更新选项的咬合:

    任何情形均会孳生非法报错

      2.转移顾客端时区(东9区,扶桑时区)。

    1)

    举一些事例,扶持精通

      3.重新彰显插入的数额,变化了,timestamp类型的数额 扩展了 1个小时

    CREATE TABLE t1 (
    ts TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    dt DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP);
    
     1 #语句不合法,出现了两个未显示设置default或null的timestamp
     2 CREATE TABLE `tt1` (
     3   `id` int(11) NOT NULL AUTO_INCREMENT,
     4   `name` varchar(20),
     5   `t1` timestamp ,
     6   `t2` timestamp ,
     7   PRIMARY KEY (`id`)
     8 ) ENGINE=InnoDB DEFAULT CHARSET=utf8
     9 
    10 #语句合法,t1字段 not null default current_timestamp on update current_timestamp,t2可空
    11 CREATE TABLE `tt2` (
    12   `id` int(11) NOT NULL AUTO_INCREMENT,
    13   `name` varchar(20),
    14   `t1` timestamp ,
    15   `t2` timestamp null,
    16   PRIMARY KEY (`id`)
    17 ) ENGINE=InnoDB DEFAULT CHARSET=utf8
    18 
    19 #语句不合法 t2字段没有设置default或null,也非表的第一个timestamp字段
    20 CREATE TABLE `tt3` (
    21   `id` int(11) NOT NULL AUTO_INCREMENT,
    22   `name` varchar(20),
    23   `t1` timestamp null,
    24   `t2` timestamp ,
    25   PRIMARY KEY (`id`)
    26 ) ENGINE=InnoDB DEFAULT CHARSET=utf8
    27 
    28 #语句不合法,这个看起来貌似合法,套用我们的规则,可以发现t2字段没有显示指定null/default,尽管指定了not null也不行
    29 CREATE TABLE `tt4` (
    30   `id` int(11) NOT NULL AUTO_INCREMENT,
    31   `name` varchar(20),
    32   `t1` timestamp null,
    33   `t2` timestamp not null,
    34   PRIMARY KEY (`id`)
    35 ) ENGINE=InnoDB DEFAULT CHARSET=utf8
    36 
    37 #语句不合法 t1,t2均合法,问题出在t3上,timestamp 默认not null,在没有显式指定null的时候,default null是不合法的
    38 CREATE TABLE `tt5` (
    39   `id` int(11) NOT NULL AUTO_INCREMENT,
    40   `name` varchar(20),
    41   `t1` timestamp not null,
    42   `t2` timestamp null,
    43   `t3` timestamp DEFAULT null,
    44   PRIMARY KEY (`id`)
    45 ) ENGINE=InnoDB DEFAULT CHARSET=utf8
    

    新葡亰496net 3

    2)

      小技术:能够利用show create table `tablename` 来查看mysql管理后的表定义,下边是tt2这张表的定义,验证了作者们的结论

      接下去 研商一些timestamp 的任何的质量

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

      新葡亰496net 4新葡亰496net 5

      1.null 是否为空

    3)

    • Timestamp和datetime的异同

      timestamp 暗中同意允许为 “非空”(not null by default), 假若你在概念“ts TIMESTAMP DEFAULT NULL” 是违规的。 可以钦命为空 null ,“ts TIMESTAMP NULL" ,那时能够在增加语句改动暗许值。

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

     

      ts2 TIMESTAMP NULL DEFAULT 0,ts3 TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP

    4)自立异的timestamp列若是未安装暗中认可值且未明显钦赐可为NULL,则私下认可值为0,0代表0000-00-00 00:00:00。

    timestamp

      default (贰个表中只可以有三个列选取上边在那之中方兴未艾种)

    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

      default CURRENT_TIMESTAMP default CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP

        自立异的DATETIME列如若未安装暗许值且定义了not null,那么私下认可值为0。

      ON UPDATE CURRENT_TIMESTAMP

    5)

    可自动更新和初阶化,私下认可展现格式一样YYYY-MM-dd HH:mm:ss

      ON UPDATE 见上2

    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);
    

    同样 显示 TIMESTAMP列的体现格式...

    对此此番创建的3个表:

    '1970-01-01 00:00:01' UTC to '2038-01-19 03:14:07' UTC

    • 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。

    机动时区转变,实际存款和储蓄微秒数,4字节囤积

    '1000-01-01 00:00:00' to '9999-12-31 23:59:59'

    不辅助时区,8字节积存

    4.参考

    1.Automatic Initialization and Updating for TIMESTAMP and DATETIME

    2.Date and Time Functions

    本文由新葡亰496net发布于网络数据库,转载请注明出处:timestamp自动更新和初始化

    关键词: