★ FEATURED ARTICLE 年初协助某金融客户做系统数据库迁移原库是 Oracle目标库是 MySQL。计划排得很紧结果第一周就被一个看起来毫不起眼的问题绊住脚代码里到处是to_date(2025-01-15, yyyy-mm-dd)的写法Oracle 跑得风生水起一到 MySQL 就红字报错。排查了半天才确认MySQL 里压根没有这个函数。这篇就把 MySQL 日期转换这件事掰开揉碎讲清楚特别是给从 Oracle 阵营转过来的老伙计们省得再去翻文档。先给结论MySQL 里没有to_date()对应的替代函数是STR_TO_DATE()作用一致都是把字符串按指定格式转成日期。但这里头有不少细节差异比如格式符写法、日期类型精度、NULL 处理机制等等直接照着 Oracle 的习惯写必然踩坑。这篇文章我会把转换函数的用法、格式符规则、隐式转换的坑、以及性能和索引问题全部梳理一遍文末还有一份排查清单可以直接抄走。1. 先搞清楚为什么 MySQL 里没有 to_date()1.1 Oracle 老用户的第一反应在 Oracle 里TO_DATE几乎是所有日期处理的入口字符串转日期靠它默认日期格式也由它派生。很多从 Oracle 转战 MySQL 的开发者写 SQL 的第一反应仍然是-- Oracle 习惯写法在 MySQL 中会直接报错 SELECT TO_DATE(2025-01-15, yyyy-mm-dd);MySQL 的报错不是提示“函数不存在”那么简单它会把TO_DATE当作函数名解析失败直接抛语法错误。这个错误本身不可怕可怕的是团队里如果有一批人都习惯了 Oracle 风格排查起来就成体力活了。这里要说明一个核心差异Oracle 的DATE类型本身就含时分秒而 MySQL 的DATE类型只精确到“天”要带时分秒必须用DATETIME或TIMESTAMP。所以即使你把字符串转成了DATE时分秒也不会保留这是设计层面的区别不是函数能力不够。1.2 MySQL 的日期处理设计哲学MySQL 的日期转换体系是分布式的每个函数负责一段STR_TO_DATE(str, format)字符串转日期DATE_FORMAT(date, format)日期转字符串CAST(expr AS DATE)通用类型转换CONVERT(expr, DATE)通用类型转换的另一种形式UNIX_TIMESTAMP()/FROM_UNIXTIME()日期与时间戳互转这套体系的好处是职责单一你用STR_TO_DATE把字符串转成日期再用DATE_FORMAT把它输出成任意格式逻辑链条很清晰。缺点是刚上手时觉得函数多、记不住不像 Oracle 一个TO_DATE一把梭。对我个人来说MySQL 的这套设计在数据清洗场景下更好用因为字符串和日期之间经常需要来回折腾拆成两个独立函数反而灵活。但关键前提是——格式符得写对这一点后面细说。项目迁移提示如果代码里有大量 Oracle 的to_date调用不要试图在 MySQL 里建同名自定义函数去兼容后面维护和索引优化都会很痛苦。正确做法是统一替换为STR_TO_DATE一步到位。2. 核心操作STR_TO_DATE() 的完整用法与格式符详解2.1 语法拆解一个参数都不能错STR_TO_DATE()的完整语法是STR_TO_DATE(str, format)str待转换的字符串必须是可以解析的日期/时间文本format解析格式指明字符串里各部分的排列顺序第二个参数format是靠格式符驱动的它不只是一个装饰而是真正决定 MySQL 怎么解析字符串。比如-- 正确解析 SELECT STR_TO_DATE(2025-01-15 14:30:00, %Y-%m-%d %H:%i:%s); -- 结果2025-01-15 14:30:00 -- 格式符写错返回值就是 NULL SELECT STR_TO_DATE(2025-01-15 14:30:00, %Y-%m-%d); -- 结果NULL这是我实际调试里遇到最多的坑。很多人觉得“我只要转出来日期就行”随手写个%Y-%m-%d结果原字符串里还带着时间部分转换直接失败返回 NULL。format中的分隔符也要严格对齐。字符串里是-格式符里就不能写/必须一一对应-- 字符串和格式符分隔符不一致 SELECT STR_TO_DATE(2025/01/15, %Y-%m-%d); -- 结果NULL严格意义上说MySQL 对分隔符有一定容错但靠容错写代码就跟靠运气开发一样早晚出事。格式符和字符串保持完全一致才是正确姿势。2.2 必须掌握的格式符对照表这是我在项目里最常用的格式符集合整理成表方便查阅格式符含义示例%Y四位年份2025%y两位年份25%m两位月份01%c月份数字不带前导零1%d两位日期15%e日期数字不带前导零15%H24小时制14%h12小时制02%i分钟两位30%s秒两位00%pAM 或 PMPM%M完整月份名January%b缩写月份名Jan%W完整星期名Wednesday%a缩写星期名Wed%j一年中的第几天015%T时间部分hh:mm:ss14:30:00实际项目中%Y-%m-%d %H:%i:%s这个组合是出现频率最高的几乎覆盖了 80% 的常规日期时间转换。比如导入 Excel 导出的日志数据时原始字符串格式五花八门有些是2025/1/5 14:30有些是2025-01-05 02:30:00 PM这时候格式符的灵活组合价值就出来了-- 12小时制带 AM/PM 的转换 SELECT STR_TO_DATE(2025-01-15 02:30:00 PM, %Y-%m-%d %h:%i:%s %p); -- 结果2025-01-15 14:30:00注意末尾的%p和%h是配套的如果字符串里是 12 小时制带 AM/PM必须用%h而不是%H否则返回 NULL。2.3 时间部分缺失时的默认值行为另一个容易忽略的细节如果字符串只包含日期部分时间部分却省略了STR_TO_DATE会默认补上00:00:00SELECT STR_TO_DATE(2025-01-15, %Y-%m-%d); -- 结果2025-01-15 00:00:00这在返回类型是DATE时无感但如果字段类型是DATETIME你会看到一个不显眼的午夜时间。这个默认值本身不是逻辑错误但它会影响后续对时间段的判断。比如你要按“当天某个时间点之后”筛选数据默认的00:00:00可能会让边界条件变得需要额外处理。3. 反方向同样关键DATE_FORMAT() 的格式化输出3.1 从日期到字符串的完整映射转换从来不是单向的。实际业务里经常需要把日期时间按特定格式输出比如生成报表文件名、拼接日志前缀、给前端返回特定格式字符串。在 MySQL 里做这件事的函数是DATE_FORMAT()DATE_FORMAT(date, format)第一个参数是日期/时间类型的值第二个参数是输出格式。它和STR_TO_DATE的格式符体系完全一致可以说是同一套规则的正反两个方向。SELECT DATE_FORMAT(NOW(), %Y-%m-%d %H:%i:%s); -- 结果2025-01-15 14:30:00 SELECT DATE_FORMAT(NOW(), %Y年%m月%d日); -- 结果2025年01月15日让输出带上中文单位也是DATE_FORMAT的常见用法。有些报表系统要求日期显示成“2025年01月15日”这种形式直接在 SQL 里格式化前端就不用再做二次处理。3.2 分组统计里最常用的日期维度我在做运营报表时DATE_FORMAT基本是分组条件的常客。按天、按月、按周聚合全靠它把DATETIME字段降维-- 按月统计订单量 SELECT DATE_FORMAT(order_time, %Y-%m) AS month, COUNT(*) AS order_cnt FROM orders WHERE order_time 2025-01-01 GROUP BY DATE_FORMAT(order_time, %Y-%m) ORDER BY month;有人会担心在GROUP BY里用函数会不会很慢这取决于是否走了索引。具体问题我会在章节 5 里专门讲这里先说结论如果数据量不大百万级以下直接这么写没问题数据量大了之后更优方案是GROUP BY 表达式配合覆盖索引或者把时间字段拆成冗余的year_month字段。3.3 输出格式里容易翻车的小细节格式符大小写敏感这是新手最容易忽略的。比如%M输出完整月份名January%m输出两位数字月份01%b输出缩写月份Jan。大小写写反输出结果完全变味SELECT DATE_FORMAT(NOW(), %M); -- January SELECT DATE_FORMAT(NOW(), %m); -- 01还有%i是分钟%s是秒这两个符号在 MySQL 里是固定的不能拿%M或%S去对应英文习惯写法。从其他数据库转过来的人最容易在这里卡住。4. 其他转换途径CAST、CONVERT 与隐式转换的边界4.1 CAST 和 CONVERT 的适用场景除了STR_TO_DATEMySQL 还提供CAST和CONVERT做类型转换。这两个函数在标准 SQL 中也有写法存在差异但能力接近-- CAST 语法 SELECT CAST(2025-01-15 AS DATE); -- CONVERT 语法 SELECT CONVERT(2025-01-15, DATE);CAST和CONVERT的优势在于通用性强不仅支持日期还支持CHAR、SIGNED、DECIMAL等类型转换。而STR_TO_DATE只专精于字符串转日期时间。但它俩的缺陷也很明显格式化的能力弱。CAST只能识别少量默认格式遇到20250115这种紧凑格式直接废掉SELECT CAST(20250115 AS DATE); -- 结果NULL所以我的使用原则是明确知道格式的日期字符串转换一律用STR_TO_DATE或者DATE_FORMAT来配合CAST和CONVERT留给那些格式无需自定义的简单场景。4.2 日期与时间戳互转UNIX_TIMESTAMP 和 FROM_UNIXTIME在接口对接、缓存系统、前端小程序的场景里时间戳和日期字符串之间的转换非常高频。MySQL 提供了一对函数-- 日期时间转时间戳 SELECT UNIX_TIMESTAMP(2025-01-15 14:30:00); -- 结果1736922600假设东八区 -- 时间戳转日期时间 SELECT FROM_UNIXTIME(1736922600); -- 结果2025-01-15 14:30:00这里有一个差异需要注意UNIX_TIMESTAMP默认按当前时区解析而FROM_UNIXTIME转换出来的时间也受时区影响。如果服务器时区设置不对这两个函数互相倒腾一圈结果可能和预期差了好几个小时。排查这类问题第一步先检查time_zone参数SELECT global.time_zone, session.time_zone;实战中我建议在应用层统一使用 UTC 时间戳展示层再按用户时区格式化数据库端少做时区运算。这样既能避免服务器时区漂移引发的混乱又方便前后端分工。4.3 隐式转换方便是真的坑也是真的MySQL 在字符串和日期之间有一定的隐式转换能力。比如你直接往DATE类型的字段里插入一个看起来像日期的字符串它也能工作INSERT INTO demo_table (create_date) VALUES (2025-01-15);这在快速开发时很方便但隐式转换有两条风险线第一可读性变差。SQL 里看不出明确的转换意图接手的人容易误解字段类型。第二隐式转换可能导致索引失效。最典型的是在WHERE条件里对索引字段做函数操作比如SELECT * FROM orders WHERE DATE_FORMAT(order_time, %Y-%m-%d) 2025-01-15;这个写法把order_time字段包在函数里即便该列有索引也无法高效使用索引失效后全表扫描数据量大时性能会明显恶化。后面我会给替代方案。5. 高频报错与排查实录日期转换的五个经典坑5.1 返回 NULL格式不匹配的典型场景STR_TO_DATE转换失败时不会报错而是返回NULL。这个“静默失败”的设计让很多开发者在数据统计时才发现问题排查周期反而被拉长。我梳理过几个高频失败原因字符串和格式符的分隔符不一致-对/24 小时制和 12 小时制混用%H对%h字符串里有多余空格月份或日期写成了超出范围的值如2025-13-45格式符大小写错误排查技巧单独执行SELECT STR_TO_DATE(待测字符串, 待测格式);看返回值只要返回NULL就说明字符串和格式之间有一个点对不上。逐段比对分隔符和数字位数一般都能快速定位。5.2 时区与零日期两个容易被忽视的系统性问题时区问题在部署到云服务器或者外部容器时特别突出。很多时候开发环境是东八区测试环境的time_zone被设成了SYSTEM而SYSTEM跟着操作系统走一旦操作系统时区不同NOW()和UNIX_TIMESTAMP的结果就跟着变。项目里我习惯启动时就固定数据库时区SET time_zone 08:00;或者在 MySQL 配置中写入default-time-zone 08:00。这一步解决了时间戳互转的很多怪问题都会消失。另一个坑是零日期。MySQL 支持0000-00-00这种特殊值但STR_TO_DATE和CAST碰到它时表现不稳定严格模式下直接报错非严格模式下返回NULL。从外部导入数据时如果源文件里有空日期常会变成0000-00-00。我建议在导入前把空日期统一替换成明确的默认值比如1970-01-01或者业务约定的最小值。5.3 索引失效与性能优化转换函数与查询的博弈前面提到WHERE DATE_FORMAT(order_time, %Y-%m-%d) 2025-01-15这种写法会让索引失效。替代方案其实不复杂核心思路是“把函数操作挪到等号右侧”-- 推荐范围查询能走索引 SELECT * FROM orders WHERE order_time 2025-01-15 00:00:00 AND order_time 2025-01-16 00:00:00;这种写法等价于“查某一天的数据”但order_time不被函数包裹索引利用率大大提高。在千万级订单表上两个写法性能差距可能超过一个数量级。如果业务里“按天查询”特别频繁又确实绕不开格式化还可以考虑加冗余列。比如在表里单独存一列order_date DATE应用写入时同步填充查询直接走冗余列索引。这种空间换时间的方案在报表场景里是常规操作。5.4 常见问题速查表症状可能原因解决方案STR_TO_DATE返回 NULL分隔符或格式符不匹配逐段比对字符串与 format转换后时间部分丢失目标类型用了 DATE 而非 DATETIME将列类型改为 DATETIME时间戳换算相差几小时数据库时区设置不一致固定连接时区或全局时区to_date函数不存在库版本/方言差异改为STR_TO_DATE隐式类型转换导致慢查询函数包裹了索引列把函数移到等号右侧或加冗余列导入数据出现零日期源数据空值或异常值导入前清洗替换为默认日期6. 实战案例订单统计系统的日期清洗流程6.1 从日志表到报表一次完整的日期处理链路前阵子做了一个订单看板项目数据来自上游多个系统目标是把这些数据统一成可统计的报表。原始日志里有三种时间格式2025-01-15 14:30:002025/1/5 14:302025-01-15 02:30:00 PM这三类只要混在一起就无法直接用 SQL 做排序和聚合。我的处理链路如下第一步先加一列标准时间字段ALTER TABLE raw_orders ADD COLUMN order_datetime DATETIME NULL;第二步分批使用STR_TO_DATE配合CASE清洗UPDATE raw_orders SET order_datetime CASE WHEN raw_time LIKE %/% THEN STR_TO_DATE(raw_time, %Y/%c/%e %H:%i) WHEN raw_time LIKE %PM% OR raw_time LIKE %AM% THEN STR_TO_DATE(raw_time, %Y-%m-%d %h:%i:%s %p) ELSE STR_TO_DATE(raw_time, %Y-%m-%d %H:%i:%s) END;第三步对清洗后的列建立索引ALTER TABLE raw_orders ADD INDEX idx_datetime (order_datetime);跑完这三步后续的按天、按月统计就能直接用DATE_FORMAT(order_datetime, %Y-%m)做分组也方便走索引做范围筛选。6.2 清洗过程中的数据校验在清理数据前务必做一次“能不能转”的校验把无法转换的行单独捞出来否则UPDATE执行完才发现部分行变成了NULL再找回原始值就比较被动SELECT raw_time FROM raw_orders WHERE order_datetime IS NULL AND raw_time IS NOT NULL;一般到这里能看到脏数据长什么样比如2025-13-45、2025-01-15 25:99之类。处理策略有两种要么跳过这些行另做告警要么把它们归入一个“无法识别”类别。我倾向于前者因为如果把异常日期强行转换或替换成默认值统计报表里的错误会被悄悄放大。6.3 小程序接口里的时间格式化场景除了报表接口开发也经常涉及日期转换。有一次调一个小程序的签到接口前端传过来的是20250115这种紧凑字符串后端需要比较是否在正常的签到时间段内-- 前端紧凑字符串转日期 SELECT STR_TO_DATE(20250115, %Y%m%d); -- 结果2025-01-15 -- 日期转给定展示格式 SELECT DATE_FORMAT(2025-01-15, %m/%d/%Y); -- 结果01/15/2025紧凑格式在日志文件和接口参数里很常见%Y%m%d这种无分隔符写法是STR_TO_DATE的强项CAST反而做不到。这也支持了章节 4.1 里的结论格式自定义需求一眼就能识别直接放弃CAST用专用函数。写在最后MySQL 的日期转换体系用熟了以后其实比 Oracle 那一套更顺手。STR_TO_DATE和DATE_FORMAT一正一反格式符共用一套记一次就能用两边。我个人在实际工作中最受益的习惯是任何从外部系统进来的数据先统一做一遍日期清洗再把清洗后的值落到标准字段上后续查询绝不碰原始字段。这样做至少让报表系统的可维护性提升了一个档次。最后再分享一个小经验遇到日期相关的 Bug先看格式符再看时区最后猜索引。我排查过的日期问题里七成以上在格式符这一步就结束了剩下两成是时区导致索引问题反而是少数。把这几个顺序刻在脑子里你能省掉很多无头苍蝇式的排查时间。
阅读完成 · 觉得有帮助？
