mysql 时间戳的用法
前言:
时间戳字段在MySQL中经常使用到,比如需要记录一行数据创建的时间或修改的时间时,我们通常会使用时间戳即timestamp字段。本篇文章主要介绍timestamp字段的使用方法及相关参数,希望大家读完能对timestamp有更深的认识。
1.TIMESTAMP字段类型简介
timestamp字段类型可存储时间类型数据,timestamp所能存储的时间范围为:'1970-01-0100:00:01.000000'到'2038-01-1903:14:07.999999',timestamp字段占用4个字节。在MySQL5.7版本,timestamp可以指定精度,即TIMESTAMP(fsp)中fsp可以指定一个介于0到6之间的可选值,以代表小数秒精度。值为0表示没有小数部分,如果省略,则默认精度为0。
下面我们来简单测试下:
#timestamp可以指定精度 mysql>CREATETABLE`stu_tb`( ->`id`int(11)NOTNULLAUTO_INCREMENTCOMMENT'自增主键', ->`stu_id`int(11)NOTNULLCOMMENT'学号', ->`stu_name`varchar(20)DEFAULTNULLCOMMENT'学生姓名', ->`create_time`timestampNOTNULLDEFAULTCURRENT_TIMESTAMPCOMMENT'创建时间', ->`update_time`timestamp(2)NOTNULLDEFAULTCURRENT_TIMESTAMP(2)ONUPDATECURRENT_TIMESTAMP(2)COMMENT'修改时间', ->PRIMARYKEY(`id`) ->)ENGINE=InnoDBDEFAULTCHARSET=utf8; QueryOK,0rowsaffected(0.04sec) mysql>showcreatetablestu_tb\G ***************************1.row*************************** Table:stu_tb CreateTable:CREATETABLE`stu_tb`( `id`int(11)NOTNULLAUTO_INCREMENTCOMMENT'自增主键', `stu_id`int(11)NOTNULLCOMMENT'学号', `stu_name`varchar(20)DEFAULTNULLCOMMENT'学生姓名', `create_time`timestampNOTNULLDEFAULTCURRENT_TIMESTAMPCOMMENT'创建时间', `update_time`timestamp(2)NOTNULLDEFAULTCURRENT_TIMESTAMP(2)ONUPDATECURRENT_TIMESTAMP(2)COMMENT'修改时间', PRIMARYKEY(`id`) )ENGINE=InnoDBDEFAULTCHARSET=utf8 1rowinset(0.00sec) mysql>insertintostu_tb(stu_id,stu_name)values(1001,'dfdfa'); QueryOK,1rowaffected(0.01sec) mysql>select*fromstu_tb; +----+--------+----------+---------------------+------------------------+ |id|stu_id|stu_name|create_time|update_time| +----+--------+----------+---------------------+------------------------+ |1|1001|dfdfa|2020-01-0717:33:51|2020-01-0717:33:51.63| +----+--------+----------+---------------------+------------------------+ 1rowinset(0.00sec)
2.影响时间戳的参数
影响时间戳显示的参数主要有两个,分别是explicit_defaults_for_timestamp,time_zone。下面简单介绍下这两个参数对时间戳的影响。
explicit_defaults_for_timestamp参数决定MySQL服务端对timestamp列中的默认值和NULL值的不同处理方法。此变量自MySQL5.6.6版本引入,分为全局级别和会话级别,可动态更新,默认值为OFF,关于该参数的更多内容可以查看这篇文章。
当explicit_defaults_for_timestamp参数默认设置为OFF时,其行为如下:
- 在默认情况下,如果timestamp列没有显式的指明null属性,那么该列会被自动加上notnull属性(而其他类型的列如果没有被显式的指定notnull,那么是允许null值的),如果往这个列中插入null值,会自动的设置该列的值为currenttimestamp值。
- 表中的第一个timestamp列,如果没有指定null属性或者没有指定默认值,也没有指定ONUPDATE语句。那么该列会自动被加上DEFAULTCURRENT_TIMESTAMP和ONUPDATECURRENT_TIMESTAMP属性。
- 对于其它TIMESTAMP列,如果没有显示指定NULL和DEFAULT属性的话,会自动设置为NOTNULLDEFAULT'0000-00-0000:00:00'。(当然,这个与SQL_MODE有关,如果SQL_MODE中包含'NO_ZERO_DATE',实际上是不允许将其默认值设置为'0000-00-0000:00:00'的。)
当explicit_defaults_for_timestamp参数设置为ON时,其行为如下:
- 如果timestamp列没有显式的指定notnull属性,那么默认的该列可以为null,此时向该列中插入null值时,会直接记录null,而不是currenttimestamp。
- 不会自动的为表中的第一个timestamp列加上DEFAULTCURRENT_TIMESTAMP和ONUPDATECURRENT_TIMESTAMP属性。
- 如果timestamp列被加上了notnull属性,并且没有指定默认值。这时如果向表中插入记录,但是没有给该TIMESTAMP列指定值的时候,如果strictsql_mode被指定了,那么会直接报错。如果strictsql_mode没有被指定,那么会向该列中插入'0000-00-0000:00:00'并且产生一个warning。
time_zone参数指定了数据库时区,time_zone分为全局级别和会话级别,可动态更新,默认为SYSTEM,表示数据库服务时区与系统时区相同。MySQL存储timestamp时间戳时,存入数据库的实际是UTC的时间,查询显示时会根据具体的时区来显示不同的时间。可以在配置文件中写入default_time_zone指定时区,比如指定中国时间东八区可以这样写:default_time_zone='+8:00'。下面简单演示下不同时区下时间的显示:
#初始默认值 mysql>showvariableslike'%time_zone%'; +------------------+--------+ |Variable_name|Value| +------------------+--------+ |system_time_zone|CST| |time_zone|SYSTEM| +------------------+--------+ 2rowsinset(0.00sec) mysql>select*fromtestdb.stu_tb; +----+--------+----------+---------------------+------------------------+ |id|stu_id|stu_name|create_time|update_time| +----+--------+----------+---------------------+------------------------+ |1|1001|dfdfa|2020-01-0717:33:51|2020-01-0717:33:51.63| +----+--------+----------+---------------------+------------------------+ 1rowinset(0.00sec) #改为UTC时区并重新连接发现timestamp存储的时间有变化 mysql>setglobaltime_zone='+0:00'; mysql>showvariableslike'%time_zone%'; +------------------+--------+ |Variable_name|Value| +------------------+--------+ |system_time_zone|CST| |time_zone|+00:00| +------------------+--------+ 2rowsinset(0.01sec) mysql>select*fromtestdb.stu_tb; +----+--------+----------+---------------------+------------------------+ |id|stu_id|stu_name|create_time|update_time| +----+--------+----------+---------------------+------------------------+ |1|1001|dfdfa|2020-01-0709:33:51|2020-01-0709:33:51.63| +----+--------+----------+---------------------+------------------------+ 1rowinset(0.00sec)
3.TIMESTAMP使用最佳实践
TIMESTAMP在使用是有很多变体,列举常用的几个如下:
- TIMESTAMPDEFAULTCURRENT_TIMESTAMPONUPDATECURRENT_TIMESTAMP
在创建新记录和修改现有记录的时候都对这个数据列刷新
- TIMESTAMPDEFAULTCURRENT_TIMESTAMP
在创建新记录的时候把这个字段设置为当前时间,但以后修改时,不再刷新它
- TIMESTAMPONUPDATECURRENT_TIMESTAMP
在创建新记录的时候把这个字段设置为0,以后修改时刷新它
- TIMESTAMPDEFAULT‘yyyy-mm-ddhh:mm:ss'ONUPDATECURRENT_TIMESTAMP
在创建新记录的时候把这个字段设置为给定值,以后修改时刷新它
实际情况下,这里建议这样创建表:
CREATETABLE`table_name`( `increment_id`INTUNSIGNEDNOTNULLauto_incrementCOMMENT'自增主键', ... `create_time`timestampNOTNULLDEFAULTCURRENT_TIMESTAMPCOMMENT'创建时间', `update_time`timestampNOTNULLDEFAULTCURRENT_TIMESTAMPONUPDATECURRENT_TIMESTAMPCOMMENT'修改时间', PRIMARYKEY(`increment_id`) )ENGINE=InnoDBDEFAULTCHARSET=utf8;
即每一行数据都包含创建时间和修改时间,程序写入数据并不需要显示指定创建时间和修改时间,由默认值自动填充。
关于时间戳,下面总结下几点经验建议,希望对你有所帮助:
数据表具有create_time、update_time时间戳字段,并设置好默认值。
对于时间戳字段,插入数据时建议不指定或插入now()函数。
explicit_defaults_for_timestamp参数建议默认为OFF。
若采用中国时间,建议time_zone设置为'+8:00'。
不同环境数据库时区建议保持一致,不要随意更改数据库时区。
不同实例间迁移数据要注意时区是否相同。
程序jdbc连接串建议指定时区并与数据库保持一致。
总结:
本篇文章详细介绍了timestamp的用法,对于影响时间戳的几个参数,我们也简单做了介绍。timestamp时间戳使用实践及相关建议希望对你有所帮助,其实这些内容在官方文档中都有记录,大家有事没事可以多翻阅官方文档哦。
PS:最近发了些广告恰饭文章,希望大家不要见怪,有收入我才有动力继续写下去。这个公众号以后还是会以原创技术文章为主,细心的读者会发现,几乎每周五都会推送技术文章,请大家放心,周五的文章绝对是干货,感谢大家的支持。
以上就是mysql时间戳的用法的详细内容,更多关于mysql时间戳的资料请关注毛票票其它相关文章!