system_time_zone参数值由来

参考文档：

https://dev.mysql.com/doc/refman/5.7/en/server-system-variables.html#sysvar_system_time_zone

 

无意间注意到 system_time_zone 参数值经常是 UTC,CST,WIB 等.

查看帮助文档，这个参数取自操作系统时区，经过查看，取的是当前操作系统时区文件数据

[root@node1 ~]# cat /etc/localtime 
TZif'pZip ~h^JGg%_

CST-8

 

如果将时区文件替换为 UTC 时区，重启数据库后，则 system_time_zone 参数值是 UTC

[root@node1 ~]# cp /usr/share/zoneinfo/UTC /etc/localtime 
cp: overwrite ‘/etc/localtime’? yes

[root@node1 ~]# service mysqld restart
Redirecting to /bin/systemctl restart  mysqld.service

mysql> show global variables like '%time_zone%';
+------------------+--------+
| Variable_name    | Value  |
+------------------+--------+
| system_time_zone | UTC    |
| time_zone        | SYSTEM |
+------------------+--------+
2 rows in set (0.00 sec)

 

问题延伸：

tzselect 命令并不能更换操作系统时区，只告诉你选择的时区的写法。

参考：https://blog.csdn.net/csdnhnma/article/details/100044630

 

time_zone参数默认不能设置为 UTC 之类的关键字，需要导入时间数据。

mysql> set time_zone='UTC';
ERROR 1298 (HY000): Unknown or incorrect time zone: 'UTC'
mysql> exit

 

 

[root@node1 ~]# mysql_tzinfo_to_sql /usr/share/zoneinfo |mysql -uroot -p mysql
Enter password: 
Warning: Unable to load '/usr/share/zoneinfo/iso3166.tab' as time zone. Skipping it.
Warning: Unable to load '/usr/share/zoneinfo/zone.tab' as time zone. Skipping it.

 

mysql> set time_zone='UTC';
Query OK, 0 rows affected (0.00 sec)

mysql> 

 

mysql> set time_zone='Asia/Jakarta';    ---这个是印尼时区
Query OK, 0 rows affected (0.00 sec)

mysql> select now();                   ----now() 结果已经比北京时间早一小时
+---------------------+
| now()               |
+---------------------+
| 2021-09-08 15:06:11 |
+---------------------+
1 row in set (0.00 sec)

 

 

参考：https://dev.mysql.com/doc/refman/5.7/en/time-zone-support.html