Mysql：时区问题

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>