2团
Published on 2024-09-10 / 23 Visits
0
0

MySQL中基于时区选择timestamp或datetime

1. 前言

近期看以下两篇博文,理清了MySQL中的时区设置,以及如何选择时间类型(timestamp或datetime)。

  1. 技术分享 | MySQL:一文弄懂时区&time_zone

  2. time_zone 是怎么打爆你的MySQL的

2. 总结

2.1 时区设置

  • 在MySQL启动时显式设置时区(default-time-zone

    • 避免MySQL使用错误的系统时区(若系统时区未正确设置就悲剧了);

    • 避免MySQL处理时间字段时(例如timestamp)查询系统时区信息(系统调用实现里加了锁),导致性能雪崩。

  • MySQL启动后设置时区:

    • 修改全局时区:set global time_zone='+00:00';

    • 修改会话时区:set session time_zone='+00:00';

  • JDBC URL中必须设置serverTimezone

    • 若JDBC URL和MySQL并未设置全局时区,易导致出现经典的应用读取时间与北京时间相差14小时的问题。

docker-compose.yml文件中设置时区,参照如下:

version: "3.9"

services:
  mysql:
    image: mysql:8.4.0
    restart: always
    volumes:
      - ./data:/var/lib/mysql
    environment:
      - MYSQL_ROOT_PASSWORD=test
      - MYSQL_DATABASE=test
      - MYSQL_USER=test
      - MYSQL_PASSWORD=test
      - caching_sha2_password_auto_generate_rsa_keys=ON
      - TZ=Asia/Shanghai
    command:
      - --general-log=ON
      - --slow-query-log=1
      - --character-set-server=utf8mb4
      - --require_secure_transport=ON
      - --bind-address=0.0.0.0
      - --default-time-zone=+08:00
    ports:
      - 3306:3306

时区设置前:

屏幕截图 2024-09-10 161731.png

时区设置后:

屏幕截图 2024-09-10 164049.png

2.2 时间字段选择

时区对MySQL的影响,主要如下:

  • NOW() 和 CURTIME() 系统函数的返回值受当前session的时区影响,包括insert .. values(now())、以及字段的 DEFAULT CURRENT_TIMESTAMP 属性也受此影响;

  • timestamp 数据类型会存储当时session的时区信息,读取时会根据当前session的时区进行转换;而datetime数据类型插入的是什么值,再读取就是什么值,不受时区影响。

  • 已经运行一段时间的业务,修改MySQL的时区仅会影响到timestamp数据类型的读取。

  • 迁移数据时,mysqldump默认使用UTC时区读取timestamp类型数据,导致时间不准确。

    • 可以使用--compact结合--skip-tz-utc避免此现象。

结合以上因素,项目中数据库设计优先选择datetime作为时间类型。


Comment