修改MySQL的时区，涉及参数time_zone

最新推荐文章于 2024-08-30 12:32:30 发布

小屁孩大帅-杨一凡

最新推荐文章于 2024-08-30 12:32:30 发布

阅读量5.6k

点赞数

分类专栏：小功能

小功能专栏收录该内容

459 篇文章 5 订阅

订阅专栏

首先需要查看mysql的当前时区,用time_zone参数


 
 
   
   
    
    
   
   
   
   
    
    
     
     mysql> show variables like '%time_zone%';   
    
    
   
   

   
   
    
    
   
   
   
   
    
    
     
     +------------------+--------+
    
    
   
   

   
   
    
    
   
   
   
   
    
    
     
     | Variable_name    | Value  |
    
    
   
   

   
   
    
    
   
   
   
   
    
    
     
     +------------------+--------+
    
    
   
   

   
   
    
    
   
   
   
   
    
    
     
     | system_time_zone | CST    |
    
    
   
   

   
   
    
    
   
   
   
   
    
    
     
     | time_zone        | SYSTEM |
    
    
   
   

   
   
    
    
   
   
   
   
    
    
     
     +------------------+--------+
    
    
   
   

   
   
    
    
   
   
   
   
    
    
     
     2 rows in set (0.00 sec)

1 可以通过修改my.cnf
在 [mysqld] 之下加
default-time-zone=timezone
来修改时区。如：
default-time-zone = '+8:00'
修改完了记得记得重启msyql
注意一定要在 [mysqld] 之下加，否则会出现 unknown variable 'default-time-zone=+8:00'

2 另外也可以通过命令行在线修改


 
 
   
   
    
    
   
   
   
   
    
    
     
     set time_zone = timezone
    
    
   
   

   
   
    
    
   
   
   
   
    
    
     
     比如北京时间（GMT+0800）
    
    
   
   

   
   
    
    
   
   
   
   
    
    
     
     set time_zone = '+8:00'; 如下:
    
    
   
   

   
   
    
    
   
   
   
   
    
    
     
     mysql> set time_zone='+8:00';
    
    
   
   

   
   
    
    
   
   
   
   
    
    
     
     Query OK, 0 rows affected (0.00 sec)
    
    
   
   

   
   
    
    
   
   
   
   
    
     
    
    
   
   

   
   
    
    
   
   
   
   
    
    
     
     mysql> show variables like '%time_zone%';   
    
    
   
   

   
   
    
    
   
   
   
   
    
    
     
     +------------------+--------+
    
    
   
   

   
   
    
    
   
   
   
   
    
    
     
     | Variable_name    | Value  |
    
    
   
   

   
   
    
    
   
   
   
   
    
    
     
     +------------------+--------+
    
    
   
   

   
   
    
    
   
   
   
   
    
    
     
     | system_time_zone | CST    |
    
    
   
   

   
   
    
    
   
   
   
   
    
    
     
     | time_zone        | +08:00 |
    
    
   
   

   
   
    
    
   
   
   
   
    
    
     
     +------------------+--------+
    
    
   
   

   
   
    
    
   
   
   
   
    
    
     
     2 rows in set (0.00 sec)

3 再通过select now()来验证时区


 
 
   
   
    
    
   
   
   
   
    
    
     
     mysql> show variables like '%time_zone%';   
    
    
   
   

   
   
    
    
   
   
   
   
    
    
     
     +------------------+--------+
    
    
   
   

   
   
    
    
   
   
   
   
    
    
     
     | Variable_name    | Value  |
    
    
   
   

   
   
    
    
   
   
   
   
    
    
     
     +------------------+--------+
    
    
   
   

   
   
    
    
   
   
   
   
    
    
     
     | system_time_zone | CST    |
    
    
   
   

   
   
    
    
   
   
   
   
    
    
     
     | 
     
     time_zone        | +08:00 |
    
    
   
   

   
   
    
    
   
   
   
   
    
    
     
     +------------------+--------+
    
    
   
   

   
   
    
    
   
   
   
   
    
    
     
     2 rows in set (0.00 sec)
    
    
   
   

   
   
    
    
   
   
   
   
    
     
    
    
   
   

   
   
    
    
   
   
   
   
    
    
     
     mysql> select now();
    
    
   
   

   
   
    
    
   
   
   
   
    
    
     
     +---------------------+
    
    
   
   

   
   
    
    
   
   
   
   
    
    
     
     | now()               |
    
    
   
   

   
   
    
    
   
   
   
   
    
    
     
     +---------------------+
    
    
   
   

   
   
    
    
   
   
   
   
    
    
     
     | 
     
     2013-08-05 10:35:31 |
    
    
   
   

   
   
    
    
   
   
   
   
    
    
     
     +---------------------+
    
    
   
   

   
   
    
    
   
   
   
   
    
    
     
     1 row in set (0.00 sec)
    
    
   
   

   
   
    
    
   
   
   
   
    
     
    
    
   
   

   
   
    
    
   
   
   
   
    
    
     
     mysql>  
     
     set 
     
     time_zone='+0:00';
    
    
   
   

   
   
    
    
   
   
   
   
    
    
     
     Query OK, 0 rows affected (0.00 sec)


 
 
   
   
    
    
   
   
   
   
    
    
     
     mysql> show variables like '%time_zone%';
    
    
   
   

   
   
    
    
   
   
   
   
    
    
     
     +------------------+--------+
    
    
   
   

   
   
    
    
   
   
   
   
    
    
     
     | Variable_name    | Value  |
    
    
   
   

   
   
    
    
   
   
   
   
    
    
     
     +------------------+--------+
    
    
   
   

   
   
    
    
   
   
   
   
    
    
     
     | system_time_zone | CST    |
    
    
   
   

   
   
    
    
   
   
   
   
    
    
     
     |
     
      time_zone        | +00:00 |
    
    
   
   

   
   
    
    
   
   
   
   
    
    
     
     +------------------+--------+
    
    
   
   

   
   
    
    
   
   
   
   
    
    
     
     2 rows in set (0.00 sec)


 
 
   
   
    
    
   
   
   
   
    
    
     
     mysql> select now();
    
    
   
   

   
   
    
    
   
   
   
   
    
    
     
     +---------------------+
    
    
   
   

   
   
    
    
   
   
   
   
    
    
     
     | now()               |
    
    
   
   

   
   
    
    
   
   
   
   
    
    
     
     +---------------------+
    
    
   
   

   
   
    
    
   
   
   
   
    
    
     
     | 
     
     2013-08-05 02:35:43 |
    
    
   
   

   
   
    
    
   
   
   
   
    
    
     
     +---------------------+
    
    
   
   

   
   
    
    
   
   
   
   
    
    
     
     1 row in set (0.00 sec)