Mysql：時區問題

1、時區配置

• Mysql默認使用系統的時區

mysql> show global variables like '%time%zone%';
+------------------+--------+
| Variable_name    | Value  |
+------------------+--------+
| system_time_zone |        |
| time_zone        | SYSTEM |	# 表明使用系統時間
+------------------+--------+
2 rows in set, 1 warning (0.17 sec)mysql>

• 在Mysql配置文件中設置時區

修改完配置之后，需要重啟MySQL服務，否則不生效。

[mysqld]# 設置默認時區
# default-time-zone='+08:00'

修改之后在終端查看是否生效

mysql> show variables like '%time_zone%';
+------------------+--------+
| Variable_name    | Value  |
+------------------+--------+
| system_time_zone |        |
| time_zone        | +08:00 |
+------------------+--------+
2 rows in set, 1 warning (0.03 sec)mysql>
# 設置新的時區
mysql>  SET TIME_ZONE = '-6:00';
Query OK, 0 rows affected (0.00 sec)
# 查看設置的時區是否生效
mysql> show variables like '%time_zone%';
+------------------+--------+
| Variable_name    | Value  |
+------------------+--------+
| system_time_zone |        |
| time_zone        | -06:00 |
+------------------+--------+
2 rows in set, 1 warning (0.00 sec)mysql> 

2、時區影響了什么

2.1、now() curtime()系統函數的影響

mysql> select now(), curtime();
+---------------------+-----------+
| now()               | curtime() |
+---------------------+-----------+
| 2024-07-09 16:27:06 | 16:27:06  |
+---------------------+-----------+
1 row in set (0.02 sec)mysql>
mysql>
mysql>  show variables like '%time_zone%';
+------------------+--------+
| Variable_name    | Value  |
+------------------+--------+
| system_time_zone |        |
| time_zone        | SYSTEM |
+------------------+--------+
2 rows in set, 1 warning (0.00 sec)mysql>
mysql>
mysql> SET TIME_ZONE = '-6:00';
Query OK, 0 rows affected (0.00 sec)mysql>  show variables like '%time_zone%';
+------------------+--------+
| Variable_name    | Value  |
+------------------+--------+
| system_time_zone |        |
| time_zone        | -06:00 |
+------------------+--------+
2 rows in set, 1 warning (0.00 sec)# 修改時區之后，系統返回返回的值相應的也變化了
mysql> select now(), curtime();
+---------------------+-----------+
| now()               | curtime() |
+---------------------+-----------+
| 2024-07-09 02:28:03 | 02:28:03  |
+---------------------+-----------+
1 row in set (0.00 sec)mysql>

2.2、timestamp 數據類型字段存儲的數據受時區影響

• timestamp 數據類型會存儲當時session的時區信息，讀取時會根據當前 session 的時區進行轉換；

• datetime 數據類型插入的是什么值，再讀取就是什么值，不受時區影響。

  ?

  也可以理解為已經存儲的數據是不會變的，只是 timestamp 類型數據在讀取時會根據時區轉換。

mysql> show global variables like '%time%zone%';
+------------------+--------+
| Variable_name    | Value  |
+------------------+--------+
| system_time_zone |        |
| time_zone        | SYSTEM |
+------------------+--------+
2 rows in set, 1 warning (0.17 sec)mysql> create table datedemo-> (->  mydatetime datetime,->  mytimestamp timestamp-> );
Query OK, 0 rows affected (0.17 sec)mysql>  select * from datedemo;
+---------------------+---------------------+
| mydatetime          | mytimestamp         |
+---------------------+---------------------+
| 2024-07-09 16:06:32 | 2024-07-09 16:06:32 |
| 2024-07-09 16:07:09 | NULL                |
| NULL                | NULL                |
+---------------------+---------------------+
3 rows in set (0.01 sec)mysql>  SET TIME_ZONE = '-6:00';
Query OK, 0 rows affected (0.00 sec)mysql> show variables like '%time_zone%';
+------------------+--------+
| Variable_name    | Value  |
+------------------+--------+
| system_time_zone |        |
| time_zone        | -06:00 |
+------------------+--------+
2 rows in set, 1 warning (0.00 sec)mysql> select * from datedemo;
+---------------------+---------------------+
| mydatetime          | mytimestamp         |
+---------------------+---------------------+
| 2024-07-09 16:06:32 | 2024-07-09 02:06:32 |# mytimestamp字段顯示的值發生了轉換
| 2024-07-09 16:07:09 | NULL                |
| NULL                | NULL                |
+---------------------+---------------------+
3 rows in set (0.00 sec)mysql>