1. 先搞明白 DATE、DATETIME、TIMESTAMP 到底差在哪做 MySQL 开发的这些年我有个很深的体会大部分人在字符和日期类型之间转换翻车不是因为 SQL 写错而是根本没搞清楚自己在跟谁打交道。你说要把字符串转成 TIMESTAMP结果字段建的是 DATETIME那转换逻辑从根上就是另一套。所以这一章先别急着写函数把三种类型掰开揉碎看清楚。DATE 类型只存日期没有时分秒范围是 1000-01-01 到 9999-12-31底层占 3 个字节。DATETIME 是在 DATE 基础上加了时间部分精确到秒如果你愿意可以扩展到微秒范围不变底层占 8 个字节。TIMESTAMP 表面上也存年月日时分秒但本质完全不同它底层只占 4 个字节存的是从 1970-01-01 00:00:00 UTC 到某个时刻的秒数。这意味着三件事第一TIMESTAMP 的范围被死死限制在 1970 到 2038 年之间2038 年问题就是这么来的。你拿字符转 TIMESTAMP 时如果目标是 2040 年直接报错或者变成 NULL。第二TIMESTAMP 存进去的是 UTC 秒数查出来的时候会自动换算成当前会话的时区时间。第三因为存储内容是秒数它在跨时区业务里有天然优势但如果不理解时区机制它就是最大的坑。特性DATEDATETIMETIMESTAMP存的内容仅日期日期时间UTC 秒数底层字节384范围1000-9999年1000-9999年1970-2038年时区敏感否否是自动初始化/更新不支持支持靠配置原生支持CURRENT_TIMESTAMP我见过不止一个项目把定单创建时间这个字段选成 TIMESTAMP理由是主流教程都这么写。结果业务做到第五年碰上 2038 年相关讨论才发现根源在于选型时没想清楚范围问题。反过来有些纯内部系统存的只是排期日期却用了 DATETIME 多占了 5 个字节倒也没什么错就是浪费。在动手转换之前你先问自己一个问题转换的目标类型到底是什么。很多时候我们写 STR_TO_DATE 只是为了把字符串变成能比较的日期那么用 DATE 还是 DATETIME 不关键只要两边对齐就行。但如果你要把转换结果写进 TIMESTAMP 字段就一定要额外考虑时区对存储值的影响——这一点我在第四章专门展开。2. 字符转 DATESTR_TO_DATE 和 CAST 的正确打开方式2.1 STR_TO_DATE 的格式掩码规则字符串转日期最稳的函数是 STR_TO_DATE(str, format)。它有两个参数第一个是待解析的字符串第二个是格式掩码。格式掩码定义了字符串里每一位代表什么含义从年份到秒一一对应。写掩码之前必须想清楚一件事格式掩码的结构必须和字符串结构完全一致否则结果可能就是 NULL。比如字符串是 2024-01-15那你格式掩码就得写成 %Y-%m-%d。这里的 %Y 代表四位年份%m 代表两位月份%d 代表两位日。如果你图省事写成 %Y/%m/%d解析直接失败。我列一份高频格式符对照表平时写转换基本就靠它格式符含义示例%Y四位年份2024%y两位年份24%m两位月份01%c月份可无前导零1%d两位日期15%e日期可无前导零15%H24小时制小时14%h / %I12小时制小时02%i分钟30%s / %S秒45%pAM/PMPM%f微秒6位123456举个完整例子。假设有一条日志里记录的原始时间是 2024-01-15 2:30 PM你想把它转成 DATE。这时候格式掩码必须照顾到 12 小时制SELECT STR_TO_DATE(2024-01-15 02:30 PM, %Y-%m-%d %h:%i %p);这条语句会返回 2024-01-15。因为 STR_TO_DATE 解析成功后会生成一个 DATETIME 值但当你把它作为 DATE 上下文使用时MySQL 会直接把时间部分截掉。你可以试试手动显式转换SELECT CAST(STR_TO_DATE(2024-01-15 02:30 PM, %Y-%m-%d %h:%i %p) AS DATE);结果同样是 2024-01-15。在这整个过程中格式掩码里任何一个字符对不上MySQL 都不会报错而是返回 NULL。这个静默失败的特性非常坑人——你以为是数据问题其实是掩码没配对。2.2 CAST 与隐式分配看似简单也有讲究除了 STR_TO_DATE很多人喜欢用 CAST(2024-01-15 AS DATE) 这种写法。对于标准格式的字符串它确实能直接用。MySQL 支持一些固定的格式才能被 CAST 识别比如 YYYY-MM-DD、YYYY-MM-DD HH:MM:SS还有带小数秒的变体。稍微偏离标准格式CAST 就无能为力了。除此之外还有一个非常容易忽略的点。当你把字符串列和 DATE 列做比较时MySQL 会尝试把两边转成同一种类型。注入数据的场景里最常见的写法是直接赋值INSERT INTO demo_table(create_date) VALUES (2024-01-15);如果 create_date 字段是 DATE 类型MySQL 会隐式地将字符串转换为日期然后存入。这套机制跟 CAST 走的是同一套解析规则只认标准格式。一旦你传入 2024/01/15 — 注意是斜杠——MySQL 也能识别因为它对日期的分隔符有一定的宽容度。但你传 15-Jan-2024 这种人类友好格式它就完全不认识了。我建议这类简单的转换统一用 STR_TO_DATE 配合掩码原因很实际格式写明白了可读性高别人接手时一眼就知道数据长什么样。CAST 虽然短但对格式的要求藏在隐含规则里排查问题要花更多时间。2.3 一个常见误区时间部分被静默丢弃再强调一次 DATE 类型的行为它根本不存时间所以任何时间部分都会被丢弃而且是悄悄丢弃没有任何警告。这在数据处理流水线里特别容易出幺蛾子。举个例子某个报表系统把上游推送的时间字符串 2024-01-15 14:30:00 转成 DATE 存入统计日期字段。当天的报表显然应该包含 14:30 这个时刻的数据但因为字段是 DATE时间信息全丢了。等你后面想统计每天各小时段的分布数据已经没法补救。解决办法有两个思路要么从一开始就把字段设计成 DATETIME只在展示层再截取日期部分要么你明确知道只需要日期那就主动用 DATE(字符串) 或 DATE_FORMAT 处理至少代码里写清楚时间不重要的意图。我个人更推荐第一种保留完整时间信息永远有回旋余地。3. DATE 转 TIMESTAMP这层窗户纸到底该往哪边捅3.1 为什么 DATE 转 TIMESTAMP 会问几点DATE 转 TIMESTAMP 和上面说的字符转 DATE 完全是两码事。DATE 没有时间部分但 TIMESTAMP 必须是年月日时分秒完整的时刻。转换过程中必须给时间部分填一个默认值。MySQL 选择的默认值是 00:00:00代表当天零点。比如你有一个 DATE 值 2024-01-15把它转到 TIMESTAMP结果就是 2024-01-15 00:00:00。这个逻辑合理但这里要提醒你一个容易犯的理解错误并不是所有写 DATE 的地方都只把时间归零。当你查询某个日期区间的数据时常见写法是WHERE create_time 2024-01-15这里 create_time 是 TIMESTAMP 或者 DATETIME右边传入的标准字符串会被隐式转成 2024-01-15 00:00:00取当天零点之后的数据逻辑没问题。可如果你写的是WHERE DATE(create_time) 2024-01-15那这就不叫转换了是把 create_time 整个变成 DATE 然后再比较。代价是无法走 create_time 索引。这个坑我在第六章还会细讲。3.2 CAST 直接转和拼接时间字符串两条路把 DATE 值转成 DATETIME 或 TIMESTAMP最直接的方式是SELECT CAST(2024-01-15 AS DATETIME); -- 结果2024-01-15 00:00:00 SELECT CAST(2024-01-15 AS TIMESTAMP); -- 结果2024-01-15 00:00:00注意这里把字符串传给 CASTMySQL 会先解析成日期再补默认时间。也可以用一个日期类型的字段直接参与转换。假设某表有一个 date_field 列类型是 DATESELECT CAST(date_field AS DATETIME) FROM demo_table;另一种思路是拼接字符串。因为你知道目标格式是 YYYY-MM-DD HH:MM:SS把 DATE 值格式化成 YYYY-MM-DD 字符串再拼上 00:00:00最后用 STR_TO_DATE 解析。这看起来绕但在某些场景下反而是更显式、更稳妥的做法尤其是你从外部系统拿到的是字符串想先验证格式再写库。SELECT STR_TO_DATE(CONCAT(2024-01-15, 00:00:00), %Y-%m-%d %H:%i:%s);就我个人的习惯纯内部逻辑用 CAST 就够了简洁但只要设计到外部接口、每次导入的数据格式可能变化时STR_TO_DATE 那套反而更可控。因为你可以把格式掩码做成配置外部格式一变更只改掩码就行。3.3 严格模式下的行为差异DATE 转 TIMESTAMP 还有一个你必须知道的分岔路口sql_mode 里的 strict 模式直接影响转换失败时的表现。在非严格模式下非法日期比如 2024-02-30 00:00:00 会被 MySQL 容忍为某种形式——通常是 NULL也可能被自动调整为 2024-03-01取决于版本和具体语境。在严格模式下同样的语句直接报错整条 SQL 事务回滚。这是个很容易被忽视的坑。生产库一般开着严格模式开发环境可能没有。结果同一套脚本在本地跑得好好的一提交到生产就报日期值非法错误。排查半天才想起来检查 sql_mode。SELECT SESSION.sql_mode;你可以在转换前加一道数据清洗层把明显不合法的字符串先过滤掉也可以统一用 STR_TO_DATE 加掩码解析因为掩码根本不认识 2 月 30 日它直接返回 NULL至少不会出现魔改后的脏数据。4. 字符转 TIMESTAMP真正的大坑藏在时区里4.1 TIMESTAMP 的存储真相UTC 与会话时区讲字符转 TIMESTAMP绕不开它的存储机制。TIMESTAMP 不是存一个漂亮字符串它存的是一个 UTC 秒数。写入时MySQL 会把当前会话时区下的时间换算成 UTC 秒数存进去读取时再按当前会话时区换算回来显示给你。用一个例子演示。假设你有一条语句SET time_zone 08:00; CREATE TABLE ts_demo(ts TIMESTAMP); INSERT INTO ts_demo(ts) VALUES (2024-01-15 12:00:00);此时 ts 内部的 UTC 值是 04:00:00 UTC。接下来换一个会话时区再查SET time_zone 00:00; SELECT ts FROM ts_demo;你看到的会是 2024-01-15 04:00:00。同一份数据时区一变显示就变了。并不是数据错了而是 TIMESTAMP 的语义就是这样——它表示的是某个绝对时刻。这跟 DATETIME 完全不同。DATETIME 是字面存储你写入 2024-01-15 12:00:00到时候查出来就是 2024-01-15 12:00:00不管会话时区怎么切换。所以跨时区系统我通常建议用 TIMESTAMP但如果你的业务严格以本地时间为准比如某机关单位只关心北京时间的上下班打卡DATETIME 反而省心。4.2 字符串里带着时区偏移怎么办接下来是字符转 TIMESTAMP 最头疼的情况外部系统传来的字符串自带时区偏移。比如某个海外服务商返回的支付完成时间是 2024-01-15 12:00:0008:00或者 2024-01-15T04:00:00Z。MySQL 的 STR_TO_DATE 并不直接支持时区偏移解析。你给它 %Y-%m-%dT%H:%i:%sZ 这种掩码它能勉强解析 Z 字面量但它不知道 Z 表示 UTC。你真正要做的是先把字符串拆出时间和偏移量再用 CONVERT_TZ 转换。举个例子假设字符串是 2024-01-15 12:00:0008:00。一个稳定的处理流程是先提取 2024-01-15 12:00:00转成 DATETIME再根据偏移 08:00 把它从 08:00 时区转换到目标时区。假如我要存的 TIMESTAMP 以北京时间08:00为显示基准但原始字符串是 UTCZ那么SET dt STR_TO_DATE(2024-01-15 04:00:00, %Y-%m-%d %H:%i:%s); SELECT CONVERT_TZ(dt, 00:00, 08:00); -- 结果2024-01-15 12:00:00CONVERT_TZ 这个函数你务必记牢它是处理跨时区字符串的钥匙。它的三个参数分别是待转换的 DATETIME 值、源时区、目标时区。注意它要求第一个参数是一个 DATETIME 值不识别 TIMESTAMP 内部秒数的概念所以应用场景更多集中在字符串/外部数据到标准时间的转换上。还有一个更省事的变种如果你拿到的就是 UNIX 秒数比如类似 1705305600 的数字直接用 FROM_UNIXTIME 秒数它会按当前会话时区给出对应的日期时间字符串然后你再用 CAST 或 STR_TO_DATE 落到 TIMESTAMP 字段。反过来要把 TIMESTAMP 变成秒数用 UNIX_TIMESTAMP(ts)。4.3 2038 年的坑给转换题加了一行注释前面说过 TIMESTAMP 只到 2038-01-19 03:14:07 UTC。2038 这个边界在字符转 TIMESTAMP 的时候会以一种特别恼人的方式出现如果你的字符串表示 2039-01-01用 STR_TO_DATE 转出来的 DATETIME/日期没有任何问题但你要把它存进 TIMESTAMP 字段MySQL 会抛出一个日期超出范围错误还可能因为在严格模式下导致整次写入失败。这个问题在数据库运维圈被讨论过很多轮但实际业务影响往往滞后。如果系统设计寿命超过 2038 年或者合同中明确了未来几十年的服务周期选 DATETIME 比硬撑 TIMESTAMP 要踏实得多。MySQL 官方对 TIMESTAMP 的支持文档也明确写了范围边界解决思路不外乎两种一是换 DATETIME二是把日期按 32 位以外的存储方式处理——但 DATETIME 通常才是那个最简单的答案。5. 日期时间回灌成字符串输出格式化的反向操作5.1 DATE_FORMAT 格式符一览与输出模板转换从来不是单向的。数据库里存的是 DATE 或 TIMESTAMP但到了接口返回、日志导出、报表文件这些环节最终都得变成字符串。日期转字符串第一主力函数是 DATE_FORMAT。DATE_FORMAT(dt, format) 用法很简单把日期时间值按掩码输出成字符串。一个典型场景是生成报表文件名里的日期标记SELECT DATE_FORMAT(NOW(), %Y%m%d_%H%i%s); -- 结果比如20240115_143000或者你要输出成 ISO 风格带毫秒的字符串SELECT DATE_FORMAT(2024-01-15 14:30:00.123456, %Y-%m-%dT%H:%i:%s.%f); -- 结果2024-01-15T14:30:00.123456DATE_FORMAT 能处理的格式符和 STR_TO_DATE 基本一一对应区别在于 STR_TO_DATE 是拿格式当模板去解析输入DATE_FORMAT 是拿格式当模板去渲染输出。这套格式符体系对你来说其实是同一套词汇表熟练了以后两个方向都能玩得转。我在实际项目里输出日期字符串时最常碰到的三种需求MySQL 客户端展示要人性化接口返回要给标准机器格式导出文件要符合业务模板。三种需求建议各写一套固定的格式模板别混用。比如接口统一 ISO8601 的 YYYY-MM-DDTHH:MM:SS 格式日志统一 YYYY-MM-DD HH:MM:SS文件名统一 YYYYMMDD这样下游消费方一目了然。5.2 面向程序的字符串输出秒级时间戳与 ISO 格式程序对接场景里还有一种字符串你经常要面对——纯数字的 UNIX 时间戳。很多人以为 UNIX_TIMESTAMP 和 FROM_UNIXTIME 只用于把日期时间转成秒数其实它们是双向转换里的另一套表达体系。一个典型的双向转换组合-- 日期时间 - 秒数 SELECT UNIX_TIMESTAMP(2024-01-15 14:30:00); -- 秒数 - 日期时间字符串 SELECT FROM_UNIXTIME(1705300200);注意 FROM_UNIXTIME 的结果是字符串它的内容依赖当前会话时区。如果你要的是一段固定格式的字符串可以在外层套一层 DATE_FORMAT。比如把秒数变成 YYYY-MM-DD HH:MM:SSSELECT DATE_FORMAT(FROM_UNIXTIME(1705300200), %Y-%m-%d %H:%i:%s);如果秒数带小数表示毫秒级别把它直接扔给 FROM_UNIXTIME 会返回带小数秒的字符串百分位以后的行为由 MySQL 版本决定。某些版本会把多余部分截断某些版本会四舍五入。写入前最好明确自己期望的精度避免下游解析时出现偏差。5.3 避免 LOCALE 差异导致的格式化意外MySQL 的 DATE_FORMAT 输出结果受会话级 locale 影响不大但有一个地方例外如果你的 SQL 用到了 %W星期的完整名称、%M月份的完整名称这些文字性输出它们会依据系统内置的语言包产生不同文字。这在高版本 MySQL 里很常见——服务器语言包是英文那你输出 %M 就是 January某些多语言发行版可能输出出中文。我记得有个项目要把日期展示成 Monday, January 15, 2024 这种人类可读格式测试环境输出正常部署到生产后变成 星期一, 一月 15, 2024。排查一圈发现是生产库的 language 配置不同。从那以后我给自己定了一条规矩输出给程序的字符串一律只用数字格式符文字信息在应用层处理数据库不去碰语言相关的格式化。另外一个容易被忽略的点是 sql_mode 中的 NO_ZERO_DATE 和 NO_ZERO_IN_DATE。如果你存的数据包含 0000-00-00 这种特殊日期老系统中很常见DATE_FORMAT 对它的行为在不同模式下不一致可能输出 0000-00-00也可能报错。如果确实有历史脏数据建议转换前统一用 CASE 判断处理掉。6. 实战视角从表设计到接口对接的转换策略6.1 表设计阶段日期字段到底选 DATETIME 还是 TIMESTAMP聊了这么多转换细节最终都要落到一张张表上。日期字段选型问题几乎每个 MySQL 项目都会遇到我直接给判断框架。第一如果系统跨时区、用户分布在不同地区、且你需要记录的是某个绝对时刻比如登录时间、下单时间选 TIMESTAMP。它天然解决全球同一时刻的换算问题只要客户端传来本地时间字符串服务器按当前时区换算成 UTC 存储各时区客户端读出来又按各自时区显示体验一致。第二如果业务只关心本地日历语义比如营业日报的日期、财务月结日期选 DATE 或 DATETIME。这类字段不关心 UTC 秒数更不该受会话时区影响。第三明确未来年限。2038 年不再是远得摸不着的事很多系统的设计周期就是 10 到 20 年。2035 年上线的系统再叠加 15 年合同2038 这个坎真的会踩到。TIMESTAMP 的死穴就在这里怎么绕都绕不开。下面是我常给团队的一张选型备忘场景推荐类型理由用户跨时区访问的订单时间TIMESTAMP存绝对时刻自动换算财报的统计基准日DATE与日历日对应无时间部分系统内部的创建时间单一时区DATETIME范围大不涉及时区需要时间戳自动更新TIMESTAMP原生支持 ON UPDATE6.2 接口对接的关键统一外部字符串格式接口对接时的日期转换问题多半不是 SQL 技巧问题而是格式约定问题。我见过最典型的反面教材外部系统给的是 15/01/2024内部系统写的是 2024-01-15两边都觉得自己没错结果对接表里堆满了 NULL。比较稳妥的做法是在系统边界统一做一道转换。外部进来的任意日期字符串先经过一个标准化的解析层解析成内部标准格式 YYYY-MM-DD HH:MM:SS再落库。解析层里你可以集中处理以下情况自动识别几种常见输入格式斜杠、连字符、中文年月日对非法日期进行标记或拒绝入库统一处理时区偏移所有外部字符串先转成标准时区对日期字段的分隔符做宽容处理这道边界处理的意义在于数据库内永远只有一种格式程序内永远只用同一套解析函数运维和排障时不会出现到底是谁的格式出了问题这种争论。6.3 一条常用综合 SQL 的逐步拆解最后放一条综合 SQL。它演示了从外部字符串提取、转换、格式化的完整闭环也是我在某个数据同步项目里实际打磨过的写法。假设有一张外部导入表 external_events里面有两列raw_time 存原始时间字符串含时区偏移e_date 是 DATE 类型的目标字段。目标是把 raw_time 转成目标库的 DATE同时排除非法数据。INSERT INTO target_table(e_date) SELECT DATE(CONVERT_TZ( STR_TO_DATE(SUBSTRING_INDEX(raw_time, , 1), %Y-%m-%d %H:%i:%s), 00:00, 08:00 )) FROM external_events WHERE raw_time IS NOT NULL AND STR_TO_DATE(SUBSTRING_INDEX(raw_time, , 1), %Y-%m-%d %H:%i:%s) IS NOT NULL;拆开看每个环节SUBSTRING_INDEX(raw_time, , 1) 把 2024-01-15 04:00:0000:00 截成 2024-01-15 04:00:00。STR_TO_DATE 把这个字符串按掩码解析成 DATETIME。CONVERT_TZ 把这个 DATETIME 从 00:00 时区转到 08:00 时区。DATE() 把你关心的日期部分抽取出来最后写入 DATE 字段。WHERE 条件里对原始字符串再做一次同样的解析并过滤 NULL防止非法字符串混进来。这样的一整条链路哪怕外部数据源加一个雷鸣你也能顺着每一步看到是哪一环节趴下了。字符串的截取、掩码对不上的解析、时区转换错位、日期被截断每个环节都有充分的排查空间。在实际处理中我发现很多人为了简洁跳过了中间步骤直接把 raw_time 扔给 CAST 或 STR_TO_DATE然后抱怨 MySQL 不按预期工作。其实大多数问题不是函数的问题而是你没给数据一个明确的来路和去路。转换工作从来都不是单行函数能解决的设计好数据流比背熟十个函数更重要。