1. 前言
近期看以下两篇博文,理清了MySQL中的时区设置,以及如何选择时间类型(timestamp或datetime)。
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
时区设置前:
时区设置后:
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作为时间类型。