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

时间真的存在吗？有观点认为，时间只是人类构想出来的一种概念，是用来衡量事物变化的标准。对于数据库来说，时间伴随着数据并进。让我们进入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,  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:   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

#设置时区，更改为东八区
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不要轻易改动。