[[396879]]
本文转载自微信公众号「数据和云」,间设作者数据和云。置的注意转载本文请联系数据和云公众号。事项
时间真的间设存在吗?有观点认为,时间只是置的注意人类构想出来的一种概念,是事项用来衡量事物变化的标准。对于数据库来说,间设时间伴随着数据并进。置的注意让我们进入MySQL时间漩涡中看一看。事项
MySQL时间类型字段:
下面是容易忽略的内容:
MySQL将TIMESTAMP值从当前时区转换为UTC进行存储,并从UTC返回到当前时区进行检索。(这不适用于其他类型,比如DATETIME。)默认情况下,每个连接的当前时区是服务器的时间。时区可以在每个连接的基础上设置。只要时区设置保持不变,就会返回所存储的相同值。如果存储一个时间戳值,然后更改时区并检索该值,则检索到的值与存储的值不同。出现这种情况是因为没有在两个方向上使用相同的时区进行转换。当前时区可以作为time_zone系统变量的值。
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将为列数据类型插入隐式的默认值。
默认情况是启用。
在MySQL 8.0.22中,如果试图在声明为TIMESTAMP NOT NULL的列中插入NULL,将会被拒绝,并产生错误。
1)explicit_defaults_for_timestamp被禁用时:
2)explicit_defaults_for_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>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”,表示服务器时区与系统时区一致。
备注: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 |
- +----------+
备注:UTC一般指协调世界时。协调世界时,又称世界统一时间、世界标准时间、国际协调时间,就是UTC+8小时=中国时间。当然值需要跟系统记录时间一致,才能更好地管理。
- #设置时区,更改为东八区
- SET GLOBAL time_zone = '+8:00';
建议:
- [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、Redis、MongoDB数据库高可用设计和运维故障处理、备份恢复、升级迁移、性能优化。自学通过了MySQL OCP 5.6和MySQL OCP 5.7认证。2年多开发经验,10年数据库运维工作经验,其中专职做MySQL工作8年;曾经担任过项目经理、数据库经理、数据仓库架构师、MySQL技术专家、DBA等职务;涉及行业:金融(银行、理财)、物流、游戏、医疗、重工业等。
墨天轮原文链接:https://www.modb.pro/db/53474
责任编辑:武晓燕 来源: 数据和云 MySQL时间设置
(责任编辑:知识)
京北品质商业新旗舰9月1日亮相!龙湖北京北苑天街构建都市理想生活
牌库构建肉鸽游戏《Tamarak Trail》Steam页面上线 年内发售