MySql--时区与地区设置

发表于 更新于 分类于 ， 置顶 精品
阅读次数： 本文字数： 544 阅读时长 ≈ 2 分钟

摘要

mysql时区设置

  • 查看当前mysql时区

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
# SYSTEM表示使用系统时区
SELECT @@global.time_zone, @@session.time_zone;
+--------------------+---------------------+
| @@global.time_zone | @@session.time_zone |
+--------------------+---------------------+
| SYSTEM             | SYSTEM              |
+--------------------+---------------------+

show variables like '%time_zone%';
+------------------+--------+
| Variable_name    | Value  |
+------------------+--------+
| system_time_zone | UTC    |
| time_zone        | SYSTEM |
+------------------+--------+

  • 查看系统时区

1
2
➜ date +"%Z %z"
UTC +0000

  • 设置时区

    • 临时设置
    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    15
    16
    17
    18
    19
    20
    21
    22
    23
    24
    25
    26
    27
    28
    29
    30
    select now()
    +---------------------+
    | now()               |
    +---------------------+
    | 2022-10-26 08:38:03 |
    +---------------------+

    set time_zone='+08:00'

    SELECT @@global.time_zone, @@session.time_zone;
    +--------------------+---------------------+
    | @@global.time_zone | @@session.time_zone |
    +--------------------+---------------------+
    | SYSTEM             | +08:00              |
    +--------------------+---------------------+

    show variables like '%time_zone%';
    +------------------+--------+
    | Variable_name    | Value  |
    +------------------+--------+
    | system_time_zone | UTC    |
    | time_zone        | +08:00 |
    +------------------+--------+

    select now()
    +---------------------+
    | now()               |
    +---------------------+
    | 2022-10-26 16:38:29 |
    +---------------------+
    • 永久设置
    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    15
    16
    17
    18
    19
    20
    21
    修改配置文件 /etc/my.cnf

    [mysqld]
    # 设置时区，默认系统时区
    default-time-zone='+08:00'


    SELECT @@global.time_zone, @@session.time_zone;
    +--------------------+---------------------+
    | @@global.time_zone | @@session.time_zone |
    +--------------------+---------------------+
    | +08:00             | +08:00              |
    +--------------------+---------------------+

    show variables like '%time_zone%';
    +------------------+--------+
    | Variable_name    | Value  |
    +------------------+--------+
    | system_time_zone | UTC    |
    | time_zone        | +08:00 |
    +------------------+--------+

  • 客户端连接时也要设置时区serverTimezone=Asia/Shanghai

mysql地区(区域)设置

  • lc_time_names系统变量指示的区域设置控制用于显示日期和月份名称和缩写的语言。此变量影响DATE_FORMAT()、DAYNAME()和MONTHNAME()函数的输出。

  • lc_time_names不影响STR_TO_DATE()或GET_FORMAT()函数。

  • lc_time_names值不影响FORMAT()的结果，默认值都是’en_US’，此函数采用可选的第三个参数设置区域，允许的区域设置值与lc_time_names系统变量的合法值相同

  • MySQL服务器区域设置支持请查看https://dev.mysql.com/doc/refman/8.0/en/locale-support.html

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
SELECT @@lc_time_names;
+-----------------+
| @@lc_time_names |
+-----------------+
| en_US           |
+-----------------+

SELECT DAYNAME('2020-01-01'), MONTHNAME('2020-01-01'),DATE_FORMAT('2020-01-01','%W %a %M %b')
+-----------------------+-------------------------+-----------------------------------------+
| DAYNAME('2020-01-01') | MONTHNAME('2020-01-01') | DATE_FORMAT('2020-01-01','%W %a %M %b') |
+-----------------------+-------------------------+-----------------------------------------+
| Wednesday             | January                 | Wednesday Wed January Jan               |
+-----------------------+-------------------------+-----------------------------------------+

SET lc_time_names = 'de_DE';

SELECT DAYNAME('2020-01-01'), MONTHNAME('2020-01-01'),DATE_FORMAT('2020-01-01','%W %a %M %b')
+-----------------------+-------------------------+-----------------------------------------+
| DAYNAME('2020-01-01') | MONTHNAME('2020-01-01') | DATE_FORMAT('2020-01-01','%W %a %M %b') |
+-----------------------+-------------------------+-----------------------------------------+
| Mittwoch              | Januar                  | Mittwoch Mi Januar Jan                  |
+-----------------------+-------------------------+-----------------------------------------+

# 可见lc_time_names无论设置为什么，format函数使用的local默认值都是en_US
select format(1450028.123,2),format(1450028.123,2,'en_US'),format(1450028.123,2,'de_DE');
+-----------------------+-------------------------------+-------------------------------+
| format(1450028.123,2) | format(1450028.123,2,'en_US') | format(1450028.123,2,'de_DE') |
+-----------------------+-------------------------------+-------------------------------+
| 1,450,028.12          | 1,450,028.12                  | 1.450.028,12                  |
+-----------------------+-------------------------------+-------------------------------+