MySQL关于时间设置的注意事项

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时间处理机制

默认情况是启用。

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”，表示服务器时区与系统时区一致。

如果设置为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';

建议：

[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、***数据库高可用设计和运维故障处理、备份恢复、升级迁移、性能优化。自学通过了MySQL OCP 5.6和MySQL OCP 5.7认证。2年多开发经验，10年数据库运维工作经验，其中专职做MySQL工作8年;曾经担任过项目经理、数据库经理、数据仓库架构师、MySQL技术专家、DBA等职务;涉及行业：金融(银行、理财)、物流、游戏、医疗、重工业等。

版权声明：本文内容由网络用户投稿，版权归原作者所有，本站不拥有其著作权，亦不承担相应法律责任。如果您发现本站中有涉嫌抄袭或描述失实的内容，请联系我们jiasou666@gmail.com 处理，核实后本网站将在24小时内删除侵权内容。