MySQL timestamp 的一个坑

最新推荐文章于 2025-06-26 15:31:54 发布
最新推荐文章于 2025-06-26 15:31:54 发布
阅读量1.6k 收藏
点赞数 2
本文解析了MySQL中TIMESTAMP字段自动更新的原因及其不同声明方式的效果，并提到了MySQL 5.6.6版本中参数explicit_defaults_for_timestamp的作用及推荐设置。

摘要生成于 C知道 ，由 DeepSeek-R1 满血版支持， 前往体验 >

前段时间用数据库，发现mysql里面的timestamp数据总是自动更新，但是自己设置的时候并没有设成 on update有啥操作。 
网上看到如下一段，谨记。

在CREATE TABLE语句中，第1个TIMESTAMP列可以用下面的任何一种方式声明： 
1： 如果定义时DEFAULT CURRENT_TIMESTAMP和ON UPDATE CURRENT_TIMESTAMP子句都有，列值为默认使用当前的时间戳，并且自动更新。 
2： 如果不使用DEFAULT或ON UPDATE子句，那么它等同于DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP。 
3： 如果只有DEFAULT CURRENT_TIMESTAMP子句，而没有ON UPDATE子句，列值默认为当前时间戳但不自动更新。 
4： 如果没用DEFAULT子句，但有ON UPDATE CURRENT_TIMESTAMP子句，列默认为0并自动更新。 
5： 如果有一个常量值DEFAULT，该列会有一个默认值，而且不会自动初始化为当前时间戳。如果该列还有一个ON UPDATE CURRENT_TIMESTAMP子句，这个时间戳会自动更新，否则该列有一个默认的常量但不会自动更新。

经他人提醒，Mysql5.6.6版本里面增加了一个参数 explicit_defaults_for_timestamp，其默认值为False，就是它搞得鬼。它会让表定义非所见即所得，存在极大隐患，所以，这个参数需要设置成为 True

xtqve
关注
Debezium同步之处理MySQL数据库的timestamp的坑
Auspicious航的博客
04-11 1166
1、修改mysql服务端的 my.ini 文件，加入时区设置 default-time_zone='+8:00' 即可，重启 MySQL 服务生效。使用 Debezium 连接 Windows操作系统的MySQL数据进行抽取数据 需要提前在MySQL数据库的配置文件中，加入时区设置。这就导致了，我的 timestamp 类型的 create_time 差了 8 个小时。但是实际上，配置了中国时区后，时间会变成格林威治时间，也就是英国 London 时间。官网上面说，需要设定时区，才能正确解析。
MySQL-connector升级后，应用本地缓存自动刷新遇到的一个Timestamp坑
架构突围
07-05 329
MySQL-connector升级后，应用本地缓存自动刷新遇到的一个Timestamp坑 阅读引导： 1、MySQL-connector升级后与MySQL进行协商获取的Timestamp的精度发生了变化 2、quartz框架定时任务刷新缓存，导致缓存永远无法刷新的问题。 最近组织内升级JDK版本，具体来说，就是JDK7升级到JDK8。 本来以为是很简单的事情，除非用到底层内容，否则正常情况下升级JDK不会有影响，而排查了一些所有系统以后，大部分都是调用的spring等开源框架的API，并没有涉及到JDK
【Mysql-时间戳】Mysql时间戳Timestamp的坑
Japathy
05-25 1866
建议在使用Mysql的时候，尽量不要使用Timestamp！ 因为TimeStamp类型的数据在Mysql中进行字符和timestamp转换没问题，但是一旦在Java代码中进行了转换，获取到的数据和原始数据竟然相差了好几个月，时间和日期都不对。 ...
MySQL 中 DATE、DATETIME 和 TIMESTAMP 的区别
最新发布
huangli的博客
06-26 863
MySQL中的DATE、DATETIME和TIMESTAMP是常用的时间数据类型。DATE仅存储日期（YYYY-MM-DD），范围1000-9999年，占3字节；DATETIME存储日期和时间（YYYY-MM-DD HH:MM:SS），范围1000-9999年，占5-8字节；TIMESTAMP也存储时间但会转换为UTC，范围1970-2038年，占4字节且支持自动更新。DATETIME和TIMESTAMP格式相同，但TIMESTAMP会进行时区转换。TIME存储时间（HH:MM:SS），YEAR存储年份。选
MySQL中字段类型为timestamp的小坑
weixin_30855099的博客
10-23 314
之前遇到过一个MySQL的字段为timestamp类型的小坑。 MySQL中一个字段存储时间类型数据的时候，该字段的类型如果为timestamp类型的话，最多只能存储到2038-01-19 11:14:07这个时间，大于这个时间段就会出现错误。 这是因为timestamp类型字段的长度为4个字节，最大值是231-1，是以从1970-01-01 00:00:00开始的计算的毫秒数。 所以...
【Mysql】探究Java Timestamp到 Mysql Timestamp发生的变化
12Dong的博客
11-07 1851
Timestamp 是一个没有时区观念的时间戳，任何一个时区拿到这个时间戳都会转变为所在时区的时间。探究探究Java Timestamp到 Mysql Timestamp发生的变化。 不附带任何东西的Timestamp 存储数据库中发生的事件。 ...
Kettle：Unable to get timestamp from resultset at index……
x²＋(y－√³x²)²＝1的博客
07-12 2693
问题：kettle在 ”表输入“ 中预览数据的时候，报如下错误 解决方式：在数据库配置的时候，设置该参数 zeroDateTimeBehavior=convertToNull
Mysql-时间的存储 datetime还是时间戳timestamp？
S_ZaiJiangHu的博客
07-20 5332
c、然后mysql数据库接收到这个时间字符串2020-02-23080000后，由于数据库时区配置是东9区，它会认为这个时间是东9区的，它会以东9区解析这个时间字符串，这时数据库保存的时间是东9区的2020-02-23080000，也就是东8区的2020-02-23070000，保存的时间就偏差了1个小时。从上表可以看到，DATETIME默认占用5个字节，而TIMESTAMP默认占用4个字节，如果需要更高精度的存储（秒后的小数点个数，比如毫秒）那么需要额外的存储空间。......
升级mysql-connector-java中timestamp的坑
变是永远不变的......
12-01 1975
现象：connector在5.1.23版本后，java.util.Date的数据中小数秒会被拼接到sql中，导致更新、查询中timestamp用在where条件找不到存在的数据。问题复盘：我们项目中之前用到的connector版本是5.1.21，需要升级到5.1.32。昨天升级后发布一台机器发现了报错。报错的方法是完成生成派单（F单insert），然后更新该派单的rootid（F单update）的功
MySQL驱动中关于时间的坑
JavaMonsterr的博客
07-27 816
最终返回了MySQL数据库中的默认时区，因为我们MySQL中默认时区是CST，所以这里也符合我们观察到的现象，最终保存到数据库的日期比实际我们当前的东8区慢14个小时，但是为什么使用mybatis的另外一个项目就没有问题呢，要知道这两个项目使用的测试数据库是同一个的，这就很奇怪，到这里，我决定再在另一个项目中也debug一下，看下问题到底出在了哪里；然后我瞬间明白了，这个问题与mybatis没有任何关系，是两个项目的MySQL驱动不一致导致的，使用mybatis的项目使用的MySQL驱动版本是。...
Kettle imestamp：无法从索引 22 处的结果集中获取时间戳
m0_46411851的博客
02-20 881
连接MySQL读取包含timestamp类型的表，经google，说是MySQL本身的问题。
kettle imestamp : Unable to get timestamp from resultset at index 22
weixin_34111790的博客
01-31 1486
在源数据库连接里增加参数 zeroDateTimeBehavior ：convertToNull 转载于:https://www.cnblogs.com/zhscn/p/8391200.html
kettle 转换字段遇到问题（couldn't get row from result set unable to get timestamp from resultset at index 9）
热门推荐
weixin_39855111的博客
05-18 2万+
kettle imestamp : Unable to get timestamp from resultset at index 22 在做ETL的时候，连接MySQL读取含有timestamp类型的表，出现如下错误： 经Google，据说是MySQL自身的问题。解决方法也很简单，在Spoon的数据库连接中，打开选项，加入一行命令参数： zeroDateTimeBehavior=co...
KETTLE - couldn't get row from result set unable to get timestamp from resultset at index 9
weixin_43125677的博客
12-06 7732
KETTLE - couldn’t get row from result set unable to get DATE from resultset at index 4 KETTLE输入MYSQL数据时 , 出现以下错误 MYSQL自身原因,解决如下 : zeroDateTimeBehavior=convertToNull ...
ETL工具--kettle篇（17.10.09更新）
weixin_34346099的博客
09-26 1273
　　ETL是EXTRACT（抽取）、TRANSFORM（转换）、LOAD（加载）的简称，实现数据从多个异构数据源加载到数据库或其他目标地址，是数据仓库建设和维护中的重要一环也是工作量较大的一块。当前知道的ETL工具有informatica, datastage,kettle,ETL Automation,sqoop,SSIS等等。这里我们聊聊kettle的学习吧（如果你有一定的kettle使用，推...
kettle 转换字段遇到问题（couldn't get row from result set）——摘
weixin_30634661的博客
09-17 3092
https://blog.youkuaiyun.com/weixin_39855111/article/details/80365160 摘自上述链接，感谢！ kettle 转换字段遇到问题（couldn't get row from result set unable to get timestamp from resultset at index 9） 2018年05月...
mysql+timestamp+坑_mysql TIMESTAMP 解决 方法
weixin_42492543的博客
02-27 799
[MYSQL]Cannot convert value '0000-00-00 00:00:00' from column 6 to TIMESTAMP / Value '[B@XXXXXXX' can not be represented as java.sql.Timestamp 解法方法Cannot convert value '0000-00-00 00:00:00' from colum...
深入探讨JDBC往MySQL中插入Timestamp类型字段报错问题
晨之风的专栏
08-10 3203
最近处于工作需要，用到了Apache的commons dbutils包来操作数据库。在保存数据到MySQL数据库时，报了一个空指针异常，作为开发多年的Java老鸟，最不怕的就是空指针异常，于是打上断点开始Debug，发现报错那行代码对象、传入方法中的参数竟然都不为空。。。
mysql datetime类型有什么坑
03-11
<think>嗯，我现在要了解一下MySQL中的datetime类型有什么坑。首先，我需要回忆一下datetime类型的基本概念。datetime在MySQL中是用来存储日期和时间的数据类型，格式通常是'YYYY-MM-DD HH:MM:SS'。那它和timestamp有什么区别呢？可能时区处理不同？timestamp会转换成UTC存储，而datetime不会？这可能在跨时区应用时出问题。 然后，存储范围的问题。datetime的范围是从'1000-01-01 00:00:00'到'9999-12-31 23:59:59'，而timestamp是从'1970-01-01 00:00:01' UTC到'2038-01-19 03:14:07' UTC。所以datetime的范围更大，但timestamp可能有2038年问题。不过这里的问题是datetime的坑，所以可能需要考虑存储范围是否足够，或者是否有人误用timestamp导致问题？ 接下来，时区的问题。datetime不存储时区信息，存入的是什么就是什么。如果应用有多个时区，处理起来可能需要转换，容易出错。比如，如果服务器和客户端处于不同时区，插入datetime值的时候可能没有正确转换，导致存储的时间不对。而timestamp则会自动转换为UTC存储，并在查询时转换回当前时区。 存储空间方面，datetime占8字节，timestamp占4字节。如果大量使用datetime，可能会占用更多存储空间，但这可能不算大坑，只是需要考虑优化。 默认值和自动更新。datetime的默认值可能需要显式设置，或者在某些版本中不支持函数作为默认值？例如，MySQL 5.6之前，datetime字段不能有默认值为CURRENT_TIMESTAMP，而timestamp可以。这可能是一个坑，如果用户想要自动记录创建时间，可能只能用timestamp或者需要更高版本的MySQL。 另一个问题可能是索引和查询效率。datetime作为索引字段是否有效？可能和timestamp类似，但如果有大量数据，类型的选择可能会影响性能？不过这可能更多取决于具体使用场景，而不是类型本身的坑。 还有，日期函数处理。处理datetime类型的字段时，函数如DATE_FORMAT、STR_TO_DATE等是否正确使用？比如，如果插入的字符串格式不正确，会导致错误或者截断。例如，无效的日期时间值会被处理成零值或者导致错误，这取决于SQL模式设置。 另外，微秒的支持。在MySQL 5.6.4之前，datetime不支持微秒，而之后支持。如果应用需要更高精度的时间，可能需要升级版本或者注意微秒的处理。 还有比较操作的问题。当比较datetime和timestamp时，由于时区不同，可能会出现预期外的结果。比如，一个带时区转换的timestamp和固定的datetime比较时，结果可能不一致。 还有，存储的时候如果插入的数据不带时区，应用层没有正确处理时区转换，导致时间错误。比如，应用服务器使用UTC时间，而数据库存储的datetime字段却用了本地时间，导致时间不一致。 总结可能的坑有： 1. 时区处理问题，不自动转换，导致跨时区应用出错。 2. 默认值和自动更新的限制，旧版本无法使用CURRENT_TIMESTAMP。 3. 无效日期处理，比如'0000-00-00 00:00:00'是否允许，取决于SQL模式。 4. 存储空间相对timestamp更大，但影响可能不大。 5. 微秒支持需要版本5.6.4以上。 6. 日期范围可能过大，需要确保应用逻辑不会超过。 7. 比较和计算时的时区差异，特别是与timestamp混用时。 8. 插入数据时的格式问题，如字符串格式不正确导致错误或截断。 需要验证这些点是否正确。比如，datetime在5.6之前是否不能设置默认值为CURRENT_TIMESTAMP？查阅资料发现，在MySQL 5.6.5之前，每个表只能有一个TIMESTAMP列有自动初始化和更新，而DATETIME则不能自动初始化。直到5.6.5之后，DATETIME才能设置默认值为CURRENT_TIMESTAMP，并且可以有多个这样的列。这可能是一个坑，因为旧版本不支持，用户可能不知道。 另外，关于无效日期，如果SQL模式设置为严格模式，插入无效日期会报错，而非严格模式可能存储'0000-00-00'导致数据问题。这也是需要注意的地方。 所以，总结下来，主要的坑包括时区处理、默认值限制、无效日期处理、版本差异导致的特性支持不同，以及存储和比较时的时区问题。</think>MySQL 的 `DATETIME` 类型在使用时需要注意以下潜在问题，逐步分析如下： --- ### 1. **时区处理不透明** - **问题**：`DATETIME` 不存储时区信息，存入和查询的值完全依赖应用层或会话时区设置。 - **示例**： - 若服务器时区为 UTC，插入 `2023-10-01 12:00:00`（客户端时区为 UTC+8），直接存储为字面值，可能导致时间逻辑错误。 - 与 `TIMESTAMP` 对比：`TIMESTAMP` 会按 UTC 存储并自动转换时区，而 `DATETIME` 不会。 - **解决方案**： - 统一时区配置（如全链路使用 UTC）。 - 显式转换时区：使用 `CONVERT_TZ()` 函数处理。 --- ### 2. **默认值与自动更新限制** - **问题**：在 **MySQL 5.6.5 之前**，`DATETIME` 字段无法设置默认值为 `CURRENT_TIMESTAMP`，且不支持自动更新。 - **示例**： ```sql CREATE TABLE t ( created_at DATETIME DEFAULT CURRENT_TIMESTAMP -- 5.6.5 之前会报错 ); ``` - **解决方案**： - 升级到 MySQL 5.6.5+。 - 旧版本中，改用 `TIMESTAMP` 或通过触发器实现自动填充。 --- ### 3. **无效日期处理** - **问题**：若插入非法日期（如 `2023-02-30`），行为取决于 SQL 模式： - **严格模式**（如 `STRICT_ALL_TABLES`）：直接报错。 - **非严格模式**：存储为 `0000-00-00 00:00:00`，可能引发数据逻辑错误。 - **解决方案**： - 启用严格模式：配置 `sql_mode` 包含 `STRICT_TRANS_TABLES`。 - 应用层校验日期合法性。 --- ### 4. **存储空间较大** - **问题**：`DATETIME` 占用 **8 字节**，而 `TIMESTAMP` 仅需 4 字节。对海量数据表可能影响存储和索引效率。 - **场景**：频繁读写的时间字段若无需大范围日期，可优先用 `TIMESTAMP`。 --- ### 5. **微秒精度支持** - **问题**：MySQL 5.6.4 之前 `DATETIME` 不支持微秒，之后可通过 `DATETIME(fsp)` 指定精度（如 `DATETIME(6)` 表示微秒级）。 - **风险**：低版本直接使用高精度会报错。 - **解决方案**：确认版本兼容性，必要时升级。 --- ### 6. **与 `TIMESTAMP` 的混淆** - **问题**：混合使用 `DATETIME` 和 `TIMESTAMP` 可能导致预期外结果，尤其在时区转换和比较时。 - **示例**： ```sql -- 假设会话时区为 UTC+8 SELECT * FROM table WHERE datetime_field = timestamp_field; -- 可能因时区差异不匹配 ``` - **解决方案**：统一字段类型，或显式转换时区后再比较。 --- ### 7. **范围过大导致逻辑漏洞** - **问题**：`DATETIME` 范围是 `1000-01-01 00:00:00` 到 `9999-12-31 23:59:59`，远超业务需求，可能存储无意义的极值。 - **示例**：未校验的输入可能插入 `9999-12-31`，影响查询和统计。 - **解决方案**：应用层增加业务逻辑校验。 --- ### 总结建议 - **使用时区明确的场景**：优先用 `TIMESTAMP`（需注意 2038 年问题）。 - **需要大范围或固定时间**：用 `DATETIME`，但需严格校验时区和合法性。 - **版本兼容性**：确认 MySQL 版本对默认值、微秒等特性的支持。 - **一致性**：避免混用 `DATETIME` 和 `TIMESTAMP`，统一时区配置。
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

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

抵扣说明：

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

余额充值