mysql时间戳和datetime对比(MySQL时间设置注意事项的深入总结)
mysql时间戳和datetime对比
MySQL时间设置注意事项的深入总结时间真的存在吗?有观点认为,时间只是人类构想出来的一种概念,是用来衡量事物变化的标准。对于数据库来说时间伴随着数据并进。进入mysql时间漩涡中看看。
1.时间类型的字段
mysql时间类型字段:
下面的容易忽略的内容:
timestamp保存数据方式:
mysql将timestamp值从当前时区转换为utc进行存储,并从utc返回到当前时区进行检索。
(这不适用于其他类型,比如datetime。)默认情况下,每个连接的当前时区是服务器的时间。时区可以在每个连接的基础上设置。只要时区设置保持不变,就会返回所存储的相同值。如果存储一个时间戳值,然后更改时区并检索该值,则检索到的值与存储的值不同。出现这种情况是因为没有在两个方向上使用相同的时区进行转换。当前时区可以作为time_zone系统变量的值。
timestamp和sql_mode组合
sql_mode也会影响timestamp值:
|
mysql> create table ts ( id integer not null auto_increment primary key , col timestamp not null ) auto_increment = 1; mysql> show variables like '%sql_mode%' ; + ---------------+---------------------+ | variable_name | value | + ---------------+---------------------+ | sql_mode | strict_trans_tables | + ---------------+---------------------+ mysql> insert into ts (col) values ( '1969-01-01 01:01:10' ); error 1292 (22007): incorrect datetime value: '1969-01-01 01:01:10' for column 'col' at row 1 mysql> set sql_mode= "" ; query ok, 0 rows affected (0.00 sec) mysql> show variables like '%sql_mode%' ; + ---------------+-------+ | variable_name | value | + ---------------+-------+ | sql_mode | | + ---------------+-------+ mysql> insert into ts (col) values ( '1969-01-01 01:01:10' ),( '2999-01-01 01:01:10' ); query ok, 2 rows affected, 2 warnings (0.01 sec) records: 2 duplicates: 0 warnings: 2 mysql> show warnings; + ---------+------+----------------------------------------------+ | level | code | message | + ---------+------+----------------------------------------------+ | warning | 1264 | out of range value for column 'col' at row 1 | | warning | 1264 | out of range value for column 'col' at row 2 | + ---------+------+----------------------------------------------+ mysql> select * from ts; + ----+---------------------+ | id | col | + ----+---------------------+ | 1 | 0000-00-00 00:00:00 | | 2 | 0000-00-00 00:00:00 | + ----+---------------------+ 2 rows in set (0.00 sec) |
通过控制sql_mode,超出timestamp限制值还是插入进去了,但采用的是0填空方式。
对于strict_trans_tables, mysql将一个无效的值转换为最接近的有效值,然后插入调整后的值。如果缺少一个值,mysql将为列数据类型插入隐式的默认值。
2.explicit_defaults_for_timestamp时间处理机制
默认情况是启用。
在mysql 8.0.22中,如果试图在声明为timestamp not null的列中插入null,将会被拒绝,并产生错误。
1.explicit_defaults_for_timestamp被禁用时:
- 没有使用null属性显式声明的时间戳列将自动使用not null属性声明。允许为这样的列赋值为null,并将该列设置为当前时间戳。在mysql 8.0.22中,如果试图在声明为timestamp not null的列中插入null,将会被拒绝,并产生错误。
- 如果表中的第一列没有使用null属性或显式的default或on update属性进行声明,则会自动使用默认的current_timestamp属性和on update current_timestamp属性进行声明。
- timestamp 如果没有显式地使用null属性或显式默认属性声明,则自动声明为默认的'0000-00-00 00:00:00'(“零”时间戳)
- 根据启用的是strict sql模式还是no_zero_date sql模式,默认值“0000-00-00 00:00:00”可能无效。
2.explicit_defaults_for_timestamp被启用:
- 不可能为timestamp指定null值来将其设置为当前时间戳。要指定当前时间戳,设置为current_timestamp或一个同义词,比如now()。
- 没有使用not null属性显式声明的timestamp列将自动使用null属性声明并允许空值。
- 使用not null属性声明的时间戳列不允许空值。对于为这样的列指定null的插入,如果启用了strict sql模式,那么单行插入会出现错误,或者禁用了strict sql模式的多行插入会插入'0000-00-00 00:00:00'。在任何情况下,为列赋值为null都不会将其设置为当前时间戳。
- 使用not null属性显式声明且没有显式默认属性的时间戳列被视为没有默认值。对于未为此类列指定显式值的插入行,结果取决于sql模式。如果启用了严格sql模式,则会出现错误。如果没有启用严格的sql模式,则使用默认隐式值'0000-00-00 00:00:00'声明该列,并出现警告。
- timestamp类型字段 不会自动使用默认的current_timestamp属性或更新current_timestamp属性声明。这些属性必须显式指定。
测试:
|
create table `test1`( id bigint not null auto_increment comment '主键id' , name varchar (20) comment '主键id' , create_time timestamp not null default current_timestamp comment 'cr time' , primary key (id) )engine=innodb auto_increment=1 ; show variables like 'explicit_defaults_for_timestamp' ; set global explicit_defaults_for_timestamp= on ; set global explicit_defaults_for_timestamp= off ; insert into test1(id, name ,create_time) values (1, 'kit' , null ); |
3.mysql系统配置
系统相关事件参数包含3个:
|
mysql>show global variables where variable_name like '%time_zone%' or variable_name like 'log_timestamp%' ; + ------------------+--------+ | variable_name | value | + ------------------+--------+ | system_time_zone | cst | | time_zone | system | | log_timestamps | utc | + ------------------+--------+ 3 rows in set (0.00 sec) |
1.system time zone:当服务器启动时,它尝试自动确定主机的时区,并使用它来设置system_time_zone系统变量。此后该值不会改变。
2.time_zone:全time_zone表示服务器当前运行的时区。初始的time_zone值为“system”,表示服务器时区与系统时区一致。
- 如果设置为system, 如mysql函数调用都会调用一个系统库来确定当前的系统时区。这个调用可能被一个全局互斥锁保护,从而导致争用。cpu使用率高问题。
- 设置会话时区会影响时区敏感的时间值的显示和存储。这包括now()或curtime()等函数显示的值,以及存储在时间戳列中的值和从时间戳列检索到的值。时间戳列的值将从会话时区转换为utc用于存储,从utc转换为会话时区用于检索。
- 会话时区设置不影响utc_timestamp()等函数显示的值,也不影响date、time或datetime列中的值。这些数据类型的值也不存储在utc;时区仅在从时间戳值转换时适用它们。
备注:mysql还提供时区导入到mysql系统库的方法。通过mysql_tzinfo_to_sql程序加载/usr/share/zoneinfom下的时区信息。
|
mysql> select count (*) from mysql.time_zone_name; + ----------+ | count (*) | + ----------+ | 0 | + ----------+ |
##mysql_tzinfo_to_sql工具导入时区值
|
shell>mysql_tzinfo_to_sql /usr/share/zoneinfo | mysql -u root mysql |
|
mysql> select count (*) from mysql.time_zone_name; + ----------+ | count (*) | + ----------+ | 1780 | + ----------+ |
3.log_timestamps
这个变量控制写入错误日志的消息以及写入文件的一般查询日志和慢速查询日志消息中的时间戳的时区。
它不会影响一般查询日志的时区和慢速查询日志消息写入表(mysql。general_log mysql.slow_log)。
允许的log_timestamps值是utc(默认值)和system(本地系统时区)
备注:utc一般指协调世界时。协调世界时,又称世界统一时间、世界标准时间、国际协调时间,就是utc+8小时=中国时间
当然值 需要跟系统记录时间一致,才能更好的管理。
|
#设置时区,更改为东八区 set global time_zone = '+8:00' ; |
建议:
mysql配置文件my.cnf
|
[mysqld] log_timestamps=system default -time_zone = '+8:00' |
|
mysql>show global variables where variable_name like '%time_zone%' or variable_name like 'log_timestamp%' ; + ------------------+--------+ | variable_name | value | + ------------------+--------+ | log_timestamps | system | | system_time_zone | cst | | time_zone | +08:00 | + ------------------+--------+ |
总结
从时间类型,参数,系统时区了解到,mysql里时间应该怎样设置和使用。
特别是无特殊要求sql_mode不要轻易改动。
到此这篇关于mysql时间设置注意事项的文章就介绍到这了,更多相关mysql时间设置注意内容请搜索开心学习网以前的文章或继续浏览下面的相关文章希望大家以后多多支持开心学习网!
原文链接:https://www.modb.pro/db/53474
- mysqlbinlog怎么分析(MySQL中使用binlog时格式该如何选择)
- deepin数据库编程(deepin20.1系统安装MySQL8.0.23超详细的MySQL8安装教程)
- 在mysql语法中用来修改数据的命令(explain命令为什么可能会修改MySQL数据)
- mysql清空数据库所有表格(MySQL用truncate命令快速清空一个数据库中的所有表)
- 怎么看mysql有没有外键约束(MySQL外键约束的实例讲解)
- mysql存储过程limit参数(MySQL存储过程in、out和inout参数示例和总结)
- mysql 8.0.22 winx64安装配置图文教程(mysql 8.0.22 winx64安装配置图文教程)
- binlog怎么恢复mysql数据库(mysql5.7使用binlog 恢复数据的方法)
- mysql主机双向复制配置(浅析MySQL并行复制)
- python导出数据到mysql(python定时按日期备份MySQL数据并压缩)
- mysql锁类型大全(简单了解 MySQL 中相关的锁)
- mysql数据库如何删除重复记录(mysql数据库删除重复数据只保留一条方法实例)
- mysql常用存储方案及基本原理(详解分析MySQL8.0的内存消耗)
- mysqlpxc性能(MySQL之PXC集群搭建的方法步骤)
- mysql和explain哪个好(MySQL EXPLAIN输出列的详细解释)
- mysql 索引怎么实现(Mysql中索引和约束的示例语句)
- 哪里可以看熊猫(成都哪里可以看熊猫)
- oppo手机的三种录屏方法,你知道有哪些吗(oppo手机的三种录屏方法)
- 吉林神秘传染链跨省 传染源尚未找到,舒兰 封城(吉林神秘传染链跨省)
- 吉林舒兰 封城 聚集性疫情传播链已延至沈阳,有一个细节让人忧心(吉林舒兰封城)
- 1天密接者猛增77人,患者轨迹透露危险信号 吉林市全面封闭管理(1天密接者猛增77人)
- 吉林舒兰 封城 15人确诊 276人隔离,出现跨省传播(吉林舒兰封城)
热门推荐
- python利用空列表进行数字排序(python实现计数排序与桶排序实例代码)
- 云服务器实例一直在停止(云服务器重启原因有哪些?)
- mysql存储过程声明(MySQL存储过程的深入讲解in、out、inout)
- react定时任务(手把手带你用React撸一个日程组件)
- 怎样查看mysql的ddl窗口(详解MySQL8.0原子DDL语法)
- python中的reload(搞清楚 Python traceback的具体使用方法)
- python能把时间转化成毫秒级吗(python 获取毫秒数,计算调用时长的方法)
- 阿里云服务器ecs入口(阿里云ECS云服务器更换公网IP的方法)
- laravel 开发自定义组件(laravel框架模板之公共模板、继承、包含实现方法分析)
- 面试时如何回答为什么要离职的问题
排行榜
- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9