MySQL时间设置注意事项的深入总结
作者:Kevin崔 发布时间:2024-01-16 04:39:52
时间真的存在吗?有观点认为,时间只是人类构想出来的一种概念,是用来衡量事物变化的标准。对于数据库来说时间伴随着数据并进。进入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不要轻易改动。
来源:https://www.modb.pro/db/53474
猜你喜欢
- Git合并分支后,需要将子分支提交到git仓库,这个时候就需要单独提交子分支,其步骤如下:1.先创建子分支,并包含最新当前分支下的修改数据g
- 层次化索引是pandas的一项重要功能,它使你能在一个轴上拥有多个(两个以上)索引级别。创建一个Series,并用一个由列表或数组组成的列表
- 最近接触到Tencent的广告越来越多,户外广告牌和电视上都是,可以明显感觉到Tencent的扩张野心,以及对QQ品牌定位的重塑。摆脱低龄化
- 流式布局流式布局,也叫做瀑布流布局,是网页中经常使用的一种页面布局方式,它的原理就是将高度固定,然后图片的宽度自适应,这样加载出来的图片看起
- 题目:解法一:比较元素是否相等思路说明:这种应该是普通人最先想到的解法,先获取到数组之后进行有小到大排序,然后初始化一个min=0(代表新数
- 前言这几天看《流畅的python》这本书了,在一个示例中又看到了property作为装饰器在使用,因为很久没有用这个东西了,对它
- HTTP、HTTPS协议下session共享解决cookie失效 的办法:(也许不是最好的,但是实用)原理就是把session id设置到本
- 口罩佩戴检测一 题目背景1.1 实验介绍今年一场席卷全球的新型冠状病毒给人们带来了沉重的生命财产的损失。有效防御这种传染病毒的方法就是积极佩
- 本文实例主要实现给出任意字符串,获取字符串中某字符的位置以及出现的总次数。实现该功能代码的时候可以使用函数enumerate来将字符串分离成
- 一。首先,添加如下存储过程CREATE PROCEDURE dbo.ChangeObjectOwner @Ol
- 什么是 docopt?1、docopt 是一种 Python 编写的命令行执行脚本的交互语言。它是一种语言!它是一种语言!它是一种语言!2、
- 前言本文介绍的主要内容是 Redux-Toolkit 在 React + TypeScript 大型应用中的实践,主要解决的问题是使用 cr
- 今天来说一下,有些刚刚接触python的朋友,在使用pip install安装python 第三方库的过程中会出现网速很慢,或者是安装下载到
- eclipse 配置 python 默认头打开eclipse 点 窗口(Windows)->首选项(Preferences)Prefe
- 查看自己cuda版本,我的cuda是11版本了,所以可以安装11版本以下的任何版本。进入pytorch官网官网网址:https://pyto
- 在python中,一个文件(以“.py”为后缀名的文件)就叫做一个模块,每一个模块在python里都
- 获取Tensor的维数>>> import tensorflow as tf>>> tf.__versi
- 1.self只有在类的方法中才会有,其他函数或方法是不必带self的。2.在调用时不必传入相应的参数。3.在类的方法中(如__init__)
- Python是一门简单易学的编程语言。阅读好的Python程序感觉就像阅读英语,尽管是非常严格的英语。Python的这种伪代码特性是其最大强
- Python3中configparser模块简介configparser 是 Pyhton 标准库中用来解析配置文件的模块,并且内置方法和字