时间真的存在吗?有观点认为,时间只是人类构想出来的一种概念,是用来衡量事物变化的标准。对于数据库来说,时间伴随着数据并进。让我们进入MySQL时间漩涡中看一看。
1. 时间类型的字段
MySQL时间类型字段:
下面是容易忽略的内容:
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被禁用时:
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系统配置
系统相关事件参数包含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 |
+----------+
3)log_timestamps
备注: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、Redis、MongoDB数据库高可用设计和运维故障处理、备份恢复、升级迁移、性能优化。自学通过了MySQL OCP 5.6和MySQL OCP 5.7认证。2年多开发经验,10年数据库运维工作经验,其中专职做MySQL工作8年;曾经担任过项目经理、数据库经理、数据仓库架构师、MySQL技术专家、DBA等职务;涉及行业:金融(银行、理财)、物流、游戏、医疗、重工业等。
墨天轮原文链接:https://www.modb.pro/db/53474(复制到浏览器)
END