警告!别再使用 TIMESTAMP 作为日期字段~

ON UPDATE CURRENT_TIMESTAMP请慎用 Python微信订餐小程序课程视频 https://edu.youkuaiyun.com/course/detail/36074 Python实战量化交易理财系统 https://edu.youkuaiyun.com/course/detail/35475 大家好,我是架构摆渡人。这是实践经验系列的第七篇文章,这个系列会给大家分享很多在实际工作中有用的经验,如果有收获,还请分享给更多的朋友。 今天给大家分享一个容易忽略的问题,正是因为容易忽略,所以才要重视。 我们的业务表中有两个字段是必不可少的,分别是创建时间和修改时间,这样就知 阅读详情

在日常数据库设计中,几乎每张业务表都带有一个日期列,用于记录每条记录产生和变更的时间。比如用户表会有一个日期列记录用户注册的时间、用户最后登录的时间。又比如,电商行业中的订单表(核心业务表)会有一个订单产生的时间列,当支付时间超过订单产生的时间,这个订单可能会被系统自动取消。

日期类型虽然常见,但在表结构设计中也容易犯错,比如很多开发同学都倾向使用整型存储日期类型,同时也会忽略不同日期类型对于性能可能存在的潜在影响。所以你有必要认真看看这篇文章,举一反三,在自己的业务中做好日期类型的设计。

日期类型

MySQL 数据库中常见的日期类型有 YEAR、DATE、TIME、DATETIME、TIMESTAMEP。因为业务绝大部分场景都需要将日期精确到秒,所以在表结构设计中,常见使用的日期类型为DATETIME 和 TIMESTAMP。接下来,我就带你深入了解这两种类型,以及它们在设计中的应用实战。

DATETIME

类型 DATETIME 最终展现的形式为:YYYY-MM-DD HH:MM:SS,固定占用 8 个字节。

从 MySQL 5.6 版本开始,DATETIME 类型支持毫秒,DATETIME(N) 中的 N 表示毫秒的精度。

例如,DATETIME(6) 表示可以存储 6 位的毫秒值。同时,一些日期函数也支持精确到毫秒,例如常见的函数 NOW、SYSDATE:

mysql> SELECT NOW(6);
+----------------------------+
| NOW(6)                     |
+----------------------------+
| 2020-09-14 17:50:28.707971 |
+----------------------------+
1 row in set (0.00 sec)
复制代码

用户可以将 DATETIME 初始化值设置为当前时间,并设置自动更新当前时间的属性。例如用户表 User有register_date、last_modify_date两个字段的定义:

CREATE TABLE User (
    id BIGINT NOT NULL AUTO_INCREMENT,
    name VARCHAR(255) NOT NULL,
    sex CHAR(1) NOT NULL,
    password VARCHAR(1024) NOT NULL,
    money INT NOT NULL DEFAULT 0,
    register_date DATETIME(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6),
    last_modify_date DATETIME(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6) ON UPDATE CURRENT_TIMESTAMP(6),
    CHECK (sex = 'M' OR sex = 'F'),
    PRIMARY KEY(id)
);
复制代码

在上面的表 User 中,列 register_date 表示注册时间,DEFAULT CURRENT_TIMESTAMP 表示记录插入时,若没有指定时间,默认就是当前时间。

列 last_modify_date 表示当前记录最后的修改时间,DEFAULT CURRENT_TIMESTAMP(6) ON UPDATE CURRENT_TIMESTAMP(6) 表示每次修改都会修改为当前时间。

这样的设计保证当用户的金钱(money 字段)发生了变更,则 last_modify_date 能记录最后一次用户金钱发生变更时的时间。来看下面的例子:

mysql> SELECT name,money,last_modify_date FROM User WHERE name = 'David';
+-------+-------+----------------------------+
| name  | money | last_modify_date           |
+-------+-------+----------------------------+
| David |   100 | 2020-09-13 08:08:33.898593 |
+-------+-------+----------------------------+
1 row in set (0.00 sec)

mysql> UPDATE User SET money = money - 1 WHERE name = 'David';
Query OK, 1 row affected (0.06 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> SELECT name,money,last_modify_date FROM User WHERE name = 'David';
+-------+-------+----------------------------+
| name  | money | last_modify_date           |
+-------+-------+----------------------------+
| David |    99 | 2020-09-14 18:29:17.056327 |
+-------+-------+----------------------------+
1 row in set (0.00 sec)
复制代码

可以看到,当用户金额发生修改时,所对应的字段 last_modify_date 也修改成发生变更的时间。

TIMESTAMP

除了 DATETIME,日期类型中还有一种 TIMESTAMP 的时间戳类型,其实际存储的内容为‘1970-01-01 00:00:00’到现在的毫秒数。在 MySQL 中,由于类型 TIMESTAMP 占用 4 个字节,因此其存储的时间上限只能到‘2038-01-19 03:14:07’。

同类型 DATETIME 一样,从 MySQL 5.6 版本开始,类型 TIMESTAMP 也能支持毫秒。与 DATETIME 不同的是,若带有毫秒时,类型 TIMESTAMP 占用 7 个字节,而 DATETIME 无论是否存储毫秒信息,都占用 8 个字节。

类型 TIMESTAMP 最大的优点是可以带有时区属性,因为它本质上是从毫秒转化而来。如果你的业务需要对应不同的国家时区,那么类型 TIMESTAMP 是一种不错的选择。比如新闻类的业务,通常用户想知道这篇新闻发布时对应的自己国家时间,那么 TIMESTAMP 是一种选择。

另外,有些国家会执行夏令时。根据不同的季节,人为地调快或调慢 1 个小时,带有时区属性的 TIMESTAMP 类型本身就能解决这个问题。

参数 time_zone 指定了当前使用的时区,默认为 SYSTEM 使用操作系统时区,用户可以通过该参数指定所需要的时区。

如果想使用 TIMESTAMP 的时区功能,你可以通过下面的语句将之前的用户表 User 的注册时间字段类型从 DATETIME(6) 修改为 TIMESTAMP(6):

ALTER TABLE User 
CHANGE register_date 
register_date TIMESTAMP(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6);
复制代码

这时通过设定不同的 time_zone,可以观察到不同时区下的注册时间:

mysql> SELECT name,regist er_date FROM User WHERE name = 'David';
+-------+----------------------------+
| name  | register_date              |
+-------+----------------------------+
| David | 2018-09-14 18:28:33.898593 |
+-------+----------------------------+
1 row in set (0.00 sec)

mysql> SET time_zone = '-08:00';
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT name,register_date FROM User WHERE name = 'David';
+-------+----------------------------+
| name  | register_date              |
+-------+----------------------------+
| David | 2018-09-14 02:28:33.898593 |
+-------+----------------------------+
1 row in set (0.00 sec)
复制代码

从上述例子中,你可以看到,中国的时区是 +08:00,美国的时区是 -08:00,因此改为美国时区后,可以看到用户注册时间比之前延迟了 16 个小时。当然了,直接加减时区并不直观,需要非常熟悉各国的时区表。

在 MySQL 中可以直接设置时区的名字,如:

mysql> SET time_zone = 'America/Los_Angeles';
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT NOW();
+---------------------+
| NOW()               |
+---------------------+
| 2020-09-14 20:12:49 |
+---------------------+
1 row in set (0.00 sec)

mysql> SET time_zone = 'Asia/Shanghai';
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT NOW();
+---------------------+
| NOW()               |
+---------------------+
| 2020-09-15 11:12:55 |
+---------------------+
1 row in set (0.00 sec)
复制代码

讲到这儿,想必你已经了解了时间字段类型,接下来我将分享在真实业务设计中如何使用好时间类型。

业务表结构设计实战

DATETIME vs TIMESTAMP vs INT,怎么选?

在做表结构设计时,对日期字段的存储,开发人员通常会有 3 种选择:DATETIME、TIMESTAMP、INT。

INT 类型就是直接存储 '1970-01-01 00:00:00' 到现在的毫秒数,本质和 TIMESTAMP 一样,因此用 INT 不如直接使用 TIMESTAMP。

当然,有些同学会认为 INT 比 TIMESTAMP 性能更好。但是,由于当前每个 CPU 每秒可执行上亿次的计算,所以无须为这种转换的性能担心。更重要的是,在后期运维和数据分析时,使用 INT 存储日期,是会让 DBA 和数据分析人员发疯的,INT的可运维性太差。

也有的同学会热衷用类型 TIMESTEMP 存储日期,因为类型 TIMESTAMP 占用 4 个字节,比 DATETIME 小一半的存储空间。

但若要将时间精确到毫秒,TIMESTAMP 要 7 个字节,和 DATETIME 8 字节差不太多。另一方面,现在距离 TIMESTAMP 的最大值‘2038-01-19 03:14:07’已经很近,这是需要开发同学好好思考的问题。

总的来说,我建议你使用类型 DATETIME。对于时区问题,可以由前端或者服务这里做一次转化,不一定非要在数据库中解决。

不要忽视 TIMESTAMP 的性能问题

前面已经提及,TIMESTAMP 的上限值 2038 年很快就会到来,那时业务又将面临一次类似千年虫的问题。另外,TIMESTAMP 还存在潜在的性能问题。

虽然从毫秒数转换到类型 TIMESTAMP 本身需要的 CPU 指令并不多,这并不会带来直接的性能问题。但是如果使用默认的操作系统时区,则每次通过时区计算时间时,要调用操作系统底层系统函数 __tz_convert(),而这个函数需要额外的加锁操作,以确保这时操作系统时区没有修改。所以,当大规模并发访问时,由于热点资源竞争,会产生两个问题。

  • 性能不如 DATETIME:DATETIME 不存在时区转化问题。
  • 性能抖动:海量并发时,存在性能抖动问题。

为了优化 TIMESTAMP 的使用,强烈建议你使用显式的时区,而不是操作系统时区。比如在配置文件中显示地设置时区,而不要使用系统时区:

[mysqld]
time_zone = "+08:00"
复制代码

最后,通过命令 mysqlslap 来测试 TIMESTAMP、DATETIME 的性能,命令如下:

# 比较time_zone为System和Asia/Shanghai的性能对比

mysqlslap -uroot --number-of-queries=1000000 --concurrency=100 --query='SELECT NOW()'
复制代码

最后的性能对比如下:

图片

从表中可以发现,显式指定时区的性能要远远好于直接使用操作系统时区。所以,日期字段推荐使用 DATETIME,没有时区转化。即便使用 TIMESTAMP,也需要在数据库中显式地配置时区,而不是用系统时区。

表结构设计规范:每条记录都要有一个时间字段

在做表结构设计规范时,强烈建议你每张业务核心表都增加一个 DATETIME 类型的 last_modify_date 字段,并设置修改自动更新机制, 即便标识每条记录最后修改的时间。

例如,在前面的表 User 中的字段 last_modify_date,就是用于表示最后一次的修改时间:

CREATE TABLE User (
    id BIGINT NOT NULL AUTO_INCREMENT,
    name VARCHAR(255) NOT NULL,
    sex CHAR(1) NOT NULL,
    password VARCHAR(1024) NOT NULL,
    money INT NOT NULL DEFAULT 0,
    register_date DATETIME(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6),
    last_modify_date DATETIME(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6) ON UPDATE CURRENT_TIMESTAMP(6),
    CHECK (sex = 'M' OR sex = 'F'),
    PRIMARY KEY(id)
);
复制代码

通过字段 last_modify_date 定义的 ON UPDATE CURRENT_TIMESTAMP(6),那么每次这条记录,则都会自动更新 last_modify_date 为当前时间。

这样设计的好处是:用户可以知道每个用户最近一次记录更新的时间,以便做后续的处理。比如在电商的订单表中,可以方便对支付超时的订单做处理;在金融业务中,可以根据用户资金最后的修改时间做相应的资金核对等。

在后面的内容中,我们也会谈到 MySQL 数据库的主从逻辑数据核对的设计实现,也会利用到last_modify_date 字段。

总结

日期类型通常就是使用 DATETIME 和 TIMESTAMP 两种类型,然而由于类型 TIMESTAMP 存在性能问题,建议你还是尽可能使用类型 DATETIME。我总结一下今天的重点内容:

  • MySQL 5.6 版本开始 DATETIME 和 TIMESTAMP 精度支持到毫秒;

  • DATETIME 占用 8 个字节,TIMESTAMP 占用 4 个字节,DATETIME(6) 依然占用 8 个字节,TIMESTAMP(6) 占用 7 个字节;

  • TIMESTAMP 日期存储的上限为 2038-01-19 03:14:07,业务用 TIMESTAMP 存在风险;

  • 使用 TIMESTAMP 必须显式地设置时区,不要使用默认系统时区,否则存在性能问题,推荐在配置文件中设置参数 time_zone = '+08:00';

  • 推荐日期类型使用 DATETIME,而不是 TIMESTAMP 和 INT 类型;

  • 表结构设计时,每个核心业务表,推荐设计一个 last_modify_date 的字段,用以记录每条记录的最后修改时间。

     转自 警告!别再使用 TIMESTAMP 作为日期字段~ - 脉脉

来源:https://juejin.cn/post/7140097425708056613
NGSIM数据集字段解析:从车辆轨迹到交通行为分析 本文深度解析NGSIM数据集的核心字段及其在交通行为分析中的应用。详细解读了车辆时空定位、状态属性及交互关系等关键字段,并通过高速路“幽灵堵车”和城市路口左转行为两个实战案例,展示了如何利用该数据集进行微观交通行为建模与驾驶特征提取,为交通流研究和自动驾驶算法测试提供数据基础。 阅读详情

相关推荐

Unity2D像素刀光实现:粒子方向控制与像素级渲染规范

像素风游戏中的刀光特效并非普通粒子动画,而是需严格遵循像素艺术原理的视觉暂留模拟。其核心在于将‘瞬时切割感’转化为程序可执行的渲染逻辑:通过禁用纹理滤波保障1:1像素锐度,绑定刀锋向量控制粒子运动方向,限定≤0.15秒生命周期匹配人眼暂留阈值,并采用Stretched Billboard实现动态拉伸拖影。该方案兼顾美术语义(如直劈、横扫、突刺)与工程约束(Sorting Layer分层、对象池复用、单粒子GPU优化),广泛适用于Unity2D平台的动作反馈系统,尤其适配《空洞骑士》《死亡细胞》类高节奏像素R

weixin_32390647的博客 289

Oracle中Date与TIMESTAMP在实际应用中的坑

最近提数据的时候遇到了一个问题,日志表中有一个响应时间,需求是提取2022年1-3月的相应日志,那么我就根据这个响应时间去提取,例如响应时间是apply_time字段,那么我使apply_time > to_char('2022','yyyy'),逻辑上这样是可以的,根据多次测试,to_cahr 是一个字符串类型,是无法比大小的,而apply_time > to_date('2022','yyyy') ,在实际提取中,只能够提取到三月份,目前时间为2022年3月,应该是在2022...

wo2han123456的博客 1218

从零构建AXI接口的DDR3控制器:MIG IP核实战指南

本文详细介绍了如何从零构建AXI接口的DDR3控制器,重点解析MIG IP核的配置流程和优化策略。通过实战案例和仿真验证,帮助开发者掌握FPGA中DDR3控制器的AXI接口设计,提升高速数据传输效率。适用于图像处理、网络加速等高性能应用场景。

weixin_28256233的博客 452

Oracle日期踩坑问题

工作日常踩BUG

weixin_50875251的博客 1325

【AI Agent】Hermes Agent解析

Hermes Agent:一个具备持久化学习能力的开源AI Agent框架 核心特点: 突破传统Agent的"无状态困境",通过记忆系统(SQLite+FTS5全文检索)实现跨会话经验积累 支持200+模型自由切换,11种模型家族和15+平台接入 五层架构设计(入口层、编排层、推理层、工具执行层、存储层)实现平台无关性 47+内置工具和动态技能沉淀机制,支持从任务执行中自动提炼可复用Skill 独特的四层记忆体系(会话/每日/长期/向量索引)和辩证式用户建模 与传统Agent对比: 具备自我进化能力,通过A

sysu_lluozh 983

linux安装配置Mysql详细步骤

下载Mysql rpm包 官网下载:https://dev.mysql.com/downloads/mysql/ 如果你到了这里,本地应该有了如下四个rpm包: mysql-community-client-5.7.26-1.el6.x86_64.rpm mysql-community-common-5.7.26-1.el6.x86_64.rpm mysql-community-libs-5...

🏆 前端领域优质创作者 7万+

使用mybatis,数据库中字段类型为timestamp,且该字段为空,转化为localdatetime时报空指针异常,如何解决...

MyBatis在处理数据库中的时间戳字段时,如果该字段为空,转换为LocalDateTime时可能会报空指针异常。为了解决这个问题,可以在映射文件中使用ResultMap进行特殊处理。 示例代码: <resultMap id="timestampMap" type="java.time.LocalDateTime"> <result property="value" colu...

weixin_42604188的博客 1144

基于STM32设计的老人摔倒检测报警系统(OneNet+MQTT)(235)

基于STM32设计的老人摔倒检测报警系统,不仅能够实时监测老人的位置信息,方便在紧急情况下快速定位,还能通过陀螺仪模块精确检测老人的身体姿态,一旦判断为摔倒状态,系统将立即触发蜂鸣器报警,并通过4G网络向指定联系人发送短信警示,确保老人能够迅速得到援助。此外,系统还支持老人主动按下设备上的按键发送求助短信,增强了系统的灵活性和实用性。

4695

警告!别再使用TIMESTAMP作为日期字段~

文章来源:【公众号:JAVA日知录】目录日期类型业务表结构设计实战总结在日常数据库设计中,几乎每张业务表都带有一个日期列,用于记录每条记录产生和变更的时间。比如用户表会有一个日期列记录用户注册的时间、用户最后登录的时间。又比如,电商行业中的订单表(核心业务表)会有一个订单产生的时间列,当支付时间超过订单产生的时间,这个订单可能会被系统自动取消。日期类型虽然常见,但在表结构...

石杉的架构笔记 1135

TC3XX - MCAL知识点(四十二):AURIX™ TC3xx实现多相电机需要多核配合使用的场景与实操分析

本文探讨了多相电机控制系统中的关键技术问题,重点分析了多核处理器在电机控制中的应用。针对十二相电机等复杂应用场景,提出了同步采样、多核协同等解决方案。文章详细阐述了相电流同步采样原理及EBTresos配置方法,分析了ADC硬件触发机制和多核配合策略。通过Core0和Core1的协同工作设计,解决了位置信息采集与FOC算法执行的时序问题,并提供了代码实现示例。实验验证了采用ATOM0_7同时触发两个核ADC中断的可行性,为多相电机控制系统的实现提供了技术参考。

梦想技术家 501

用 SNC 和 SAP Single Sign-On Secure Login 把 SAP 登录这件小事,变成一件安全又不用输密码的事

在传统的 SAP 系统里,登录这件事看起来很朴素:用户打开 SAP GUI,输入一个 SAP 用户名,再输入一个 SAP 密码,点回车,进系统,开始干活。表面上没毛病,但从安全角度看,这种老办法问题很大。默认情况下,这些用户名和密码在网络上传输时是明文,也就是不加密传输。攻击者如果在公司网络里做监听,理论上可以直接截取到口令信息,这种风险对今天任何一家合规要求高、审计严格的企业来说,基本是不可接受的。(SAP Help Portal)SAP 给出的答案叫做 ,全称是 。SNC 做的事情可以概括成两类:一类是

2007 年 ~ 2025 年,深耕 SAP 技术 18 年 381

警告!别再使用 TIMESTAMP 作为日期字段~

点击上方“Java精选”,选择“设为星标”别问别人为什么,多问自己凭什么!下方有惊喜留言必回,有问必答!每天08:15更新文章,每天进步一点点...在日常数据库设计中,几乎每张业务表都...

Java精选 334

Openclaw第4章:本地文件管理

OpenClaw本地文件管理功能摘要: OpenClaw提供强大的本地文件管理能力,主要功能包括: 智能文件搜索:支持内容搜索、自然语言描述、跨文件夹搜索和智能过滤排序,比传统文件名搜索效率提升10倍以上 批量文件处理:可自动整理发票、提取PDF内容、识别名片信息,批量处理数百文件仅需几分钟 文件自动整理:支持智能重命名、自动分类和文件夹优化,如将1000+照片按日期地点自动分类 典型应用场景: 30秒找到忘记名称的重要文件 5分钟完成原本需要1小时的发票整理工作 1句话指令完成数百文件的批量重命名 优势:

Maynor的博客 1730
上一篇: 2万字系统总结,带你实现Linux命令自由
下一篇: 开发必备的常用 Linux 命令整理
码趣资源网
博客等级 码龄4年 269粉丝 518原创
评论
成就一亿技术人!
拼手气红包6.0元
还能输入1000个字符
 
 条评论被折叠 查看
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付:10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值