1. to_date()为什么在MySQL里报错一个从Oracle带过来的惯性动作先讲一个我自己的事。前几年接手一个老系统改造把某数据库迁到MySQL。代码是从其他数据库方言直接抄过来的大量写法类似to_date(2024-03-15, yyyy-mm-dd)当时想都没想就放到MySQL里执行结果第一条就把我干懵了ERROR 1305 (42000): FUNCTION test_db.to_date does not exist看到这个报错的时候我才反应过来——MySQL压根就没有to_date()这个内置函数。那会儿我第一反应是是不是函数名拼错了还专门翻了一圈官方函数列表确认真没有。这个细节其实挺有代表性的因为很多从Oracle或者PostgreSQL转过来的人最初几天都会栽在函数名惯性上。就连我自己一开始也总觉得日期转换不就该有个to_date函数吗但MySQL的哲学不太一样它给了你一套自己的日期处理工具只是名字不叫这个。说个数据感受一下差距在Oracle里TO_DATE(2024-03-15, YYYY-MM-DD)是标准到不能再标准的写法几乎所有SQL语句涉及日期入参时都会用到它但MySQL这边靠的是STR_TO_DATE()、CAST()、DATE()这一组函数而且人家还特别傲娇——格式占位符体系跟Oracle完全不通用。你用yyyy-mm-dd作为格式串去调MySQL的STR_TO_DATE()八成得到NULL因为MySQL的格式符是%Y-%m-%d这种带百分号的风格。这个坑我后面具体展开先记住一个结论MySQL里没有to_date()它的核心替代品是STR_TO_DATE()偶尔用CAST()或DATE()就够了。不过你如果只是想把一个日期字符串转换成日期类型还有更偷懒的方式。MySQL对标准格式的日期字符串是自动识别的比如2024-03-15、20240315、2024/03/15这类写法直接CAST(2024-03-15 AS DATE)就行连格式串都不用写。所以对很多人来说从Oracle迁过来之后第一件要改的习惯就是别一上来就找to_date先想想这个字符串是不是标准格式如果是CAST就完了。这篇文章就说清楚这几件事MySQL里日期转换的完整函数清单是什么、怎么用、格式串那套%规则到底怎么回事、哪些场景最容易踩坑以及从Oracle迁移过来时怎么写等价SQL。全程用实际例子说话你对照着敲一遍基本就不会再在日期上翻车了。提示如果你是从MySQL往其他数据库迁比如迁到PostgreSQL那边也没有to_date()的同名函数但那边叫TO_DATE()这套先确认目标数据库函数清单的思路同样适用。2. 真正干活的日期函数STR_TO_DATE、CAST、DATE、DATE_FORMAT各有分工MySQL的日期处理函数确实多而且一眼看去容易混。我先给你一张表把这些函数的分工一次理清楚后面再逐个讲细节。函数作用典型写法返回类型STR_TO_DATE(str, format)按指定的格式串把字符串解析成日期STR_TO_DATE(20240315, %Y%m%d)DATE/DATETIMECAST(str AS DATE)将标准格式字符串强转为日期CAST(2024-03-15 AS DATE)DATEDATE(expr)从日期时间表达式里提取日期部分DATE(2024-03-15 10:30:00)DATEDATE_FORMAT(date, format)把日期格式化为指定格式的字符串DATE_FORMAT(NOW(), %Y-%m-%d)VARCHARFROM_UNIXTIME(ts)将Unix时间戳转为日期时间FROM_UNIXTIME(1710000000)DATETIMEUNIX_TIMESTAMP(date)把日期转为Unix时间戳UNIX_TIMESTAMP(2024-03-15)INTCONVERT(str, DATE)同CAST也属于标准转日期路径CONVERT(2024-03-15, DATE)DATE这表里最关键的其实是前三个。STR_TO_DATE是Oracleto_date()的正宗平替它把字符串和格式串两个参数一起接收解析完返回日期类型。CAST则适合偷懒只要字符串本身是MySQL认识的格式就没必要再写格式串。DATE()则是拿来从DATETIME里截取日期部分比如你有个created_at是2024-03-15 10:30:00想按天分组统计写DATE(created_at)就能干净地拿到2024-03-15。另一个容易忽视的点STR_TO_DATE()返回的到底是DATE还是DATETIME取决于你的格式串里有没有时间部分。比如STR_TO_DATE(2024-03-15, %Y-%m-%d)返回DATE但如果格式串里带着%H:%i:%s那返回的就是DATETIME。这个细节在后续比较和分组时会有影响最好心里有数。2.1 什么时候用STR_TO_DATE什么时候用CAST按我的经验判断规则很简单字符串是YYYY-MM-DD、YYYYMMDD、YYYY/MM/DD、YYYY-MM-DD HH:MM:SS这类MySQL原生能识别的格式直接用CAST或CONVERT一行搞定不用写格式串。字符串格式不标准比如15/Mar/2024、2024年3月15日、20240315 103020或者你从CSV、Excel导进来的各种奇葩格式必须用STR_TO_DATE自己指定格式串告诉MySQL怎么解析。举个实际例子。假设你有一张导入表日期列存的是03/15/2024这种美式格式直接CAST会返回NULL因为MySQL不认这种格式。但你写成SELECT STR_TO_DATE(03/15/2024, %m/%d/%Y);返回2024-03-15干净利落。再比如整个系统要从Oracle迁过来原代码里全是TO_DATE(20240315, YYYYMMDD)在MySQL里等价写法就是SELECT STR_TO_DATE(20240315, %Y%m%d);注意格式串从YYYYMMDD变成了%Y%m%d这就是两个数据库之间最经典的一处差异。很多人第一次写MySQL的时候照着Oracle的习惯把YYYYMMDD原封不动丢给STR_TO_DATE最后只会得到一个NULL然后开始怀疑人生。实际上MySQL的格式符体系跟Oracle完全不同下一节详细讲。2.2 标准字符串直接CAST就够了效率更高顺便说一个容易被忽略的点在某些版本和配置下STR_TO_DATE因为要做格式解析执行开销比CAST略高一点点。虽然单个查询感知不明显但如果你的查询在WHERE条件里对大表做日期过滤比如SELECT * FROM orders WHERE STR_TO_DATE(order_date_str, %Y-%m-%d) 2024-03-15;这会导致这列就算有索引也用不上因为你对列做了函数运算索引直接失效。但写成SELECT * FROM orders WHERE order_date_str DATE(2024-03-15);如果order_date_str本身是标准字符串且建立了索引就能命中索引。这类性能问题的经验是能用标准格式存就别在查询时再去做转换转换尽量放在写入阶段查询阶段直接比对原始列。你在设计表结构的时候如果知道这个字段将来要做范围查询或分组一定要存成DATE/DATETIME类型别存VARCHAR。很多老系统就是因为把日期存成字符串导致后面统计SQL又长又慢还得靠STR_TO_DATE在查询时硬转。这是我的第一条建议能用类型解决的问题不要用函数解决。3. 格式占位符才是最大的坑%Y和yyyy完全是两码事如果说to_date()不存在是MySQL给Oracle迁移者的下马威那格式符差异就是“持久战”里的地雷了。我见过太多人在STR_TO_DATE里写%Y-%m-%d以外的格式串然后翻车这里把MySQL格式符完整列一遍你直接收藏当字典查。格式符含义示例输出对应的Oracle习惯%Y四位数年份2024YYYY%y两位数年份24RR或YY%m两位数月份01-1203MM%c月份数字无前导零1-123MM有些用法%d两位数日期01-3115DD%e日期数字无前导零1-313DD有些用法%H24小时制小时00-2314HH24%h12小时制小时01-1202HH%i两位数分钟00-5908MI%s两位数秒00-5905SS%f微秒6位数123456FF%pAM或PMPMAM%W星期名Sunday-MondayFridayDAY%a缩写星期名FriDY%M月份名January-DecemberMarchMONTH%b缩写月份名MarMON%j一年中的第几天001-366075DDD%T时间24小时制HH:MM:SS14:08:05HH24:MI:SS%r时间12小时制HH:MM:SS AM/PM02:08:05 PMHH:MI:SS AM这张表你第一眼会觉得内容多但实际工作中真正高频的就这么几个%Y、%m、%d、%H、%i、%s。还有一个看似不起眼但很容易出错的%i是分钟不是%M也不是%m。Oracle里分钟用MI很多人照搬过来在MySQL里写成%Y-%m-%d %H:%MI:%SS结果解析出来的分钟永远是00或者直接NULL。因为%M在MySQL里是英文月份名March%m是数字月份五花八门的组合确实容易绕晕。3.1 字符串里的非格式字符必须和格式串一一对应这是一个特别容易踩但报错又很隐蔽的点。STR_TO_DATE(2024-03-15 14:08:05, %Y-%m-%d %H:%i:%s)能正常工作是因为格式串里的连字符-、空格、冒号都和字符串里的实际分隔符一一对应。如果你写的格式串跟字符串的结构对不上MySQL不会报错而是默默返回NULL。典型案例如下-- 这个是错的字符串里是/分隔格式串里却是- SELECT STR_TO_DATE(2024/03/15, %Y-%m-%d); -- 结果NULL -- 这个才对 SELECT STR_TO_DATE(2024/03/15, %Y/%m/%d); -- 结果2024-03-15更隐蔽的情况是字符串里用了T作为日期和时间的分隔符比如ISO格式2024-03-15T14:08:05。MySQL的STR_TO_DATE对ISO格式其实有自动识别能力直接CAST(2024-03-15T14:08:05 AS DATETIME)就能成功但如果你非要写格式串就得写成%Y-%m-%dT%H:%i:%s注意那个T要原样写进去。我见过有人在这里调了半天最后发现是格式串里漏了字母T这类非格式字符必须逐字对应的规则在做自定义日期格式时一定要记牢。3.2 12小时制的坑不写%p下午全变上午记得有一次做考勤统计需要解析一批打卡记录时间格式是2024-03-15 02:08:05 PM。我一开始写的格式串是%Y-%m-%d %h:%i:%s结果所有下午的记录全部解析成了凌晨2点。原因就是12小时制必须跟%p配合格式串得写成%Y-%m-%d %h:%i:%s %p才能正确识别PM后缀。-- 错误 SELECT STR_TO_DATE(2024-03-15 02:08:05 PM, %Y-%m-%d %h:%i:%s); -- 结果2024-03-15 02:08:05下午2点被当成凌晨2点 -- 正确 SELECT STR_TO_DATE(2024-03-15 02:08:05 PM, %Y-%m-%d %h:%i:%s %p); -- 结果2024-03-15 14:08:05所以我的建议是解析字符串时尽量用24小时制%H少用12小时制%h能少一堆麻烦。只有在数据源本身就带着AM/PM后缀且无法改变格式时才用%p去配合。4. 别让错误数据悄悄混进去解析宽容度与sql_mode防线日期转换还有一个很多人没意识到的角落MySQL解析日期的宽容度是可配置的默认情况下它可能帮你自动纠正错误也可能直接拒绝这取决于你的sql_mode设置。先看一段实际表现-- 正常情况下这个应该报错或者返回NULL SELECT STR_TO_DATE(2024-02-30, %Y-%m-%d);2月30日这个日期根本不存在。在默认的MySQL配置下不少版本会返回NULL但有些版本可能把它解析成2024-02-29或者干脆报错。这个行为差异容易让人困惑尤其是你把同样的SQL从开发环境搬到生产环境两个环境的sql_mode不一样表现就不一样。要把这个坑彻底堵住你要理解NO_ZERO_DATE、NO_ZERO_IN_DATE这两个sql_mode参数的含义。sql_mode值作用影响NO_ZERO_DATE不允许日期中的“零日期”0000-00-00会被拒绝或转为NULLNO_ZERO_IN_DATE不允许日期中有零部分2024-00-15、2024-03-00会被拒绝或转为NULLERROR_FOR_DIVISION_BY_ZERO除零时报错与日期无关但常一起出现STRICT_TRANS_TABLES严格模式数据非法时拒绝写入插入非法日期时会报错而不是警告ALLOW_INVALID_DATES允许存储无效日期与严格模式相矛盾会放宽日期的校验一个典型的场景你把一个CSV文件通过LOAD DATA导入里面有一行日期写的是2024-02-30。如果sql_mode不带NO_ZERO_IN_DATE和NO_ZERO_DATEMySQL可能会把这一行以警告级别接收存进去一个NULL或者一个被纠正过的日期。如果你没看警告这个脏数据就这么进了库。等你后面做统计就会发现某个月的订单数莫名其妙少了一条排查半天才能追到这个原始导入问题。我的建议是在日期敏感的项目里把sql_mode设置得严格一点SET GLOBAL sql_mode STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO;这样非法日期在写入阶段就直接报错你当场就能发现数据问题而不是让脏数据在库里潜伏几个月。4.1 转换失败的三个返回值NULL、0日期、报错用STR_TO_DATE解析失败时MySQL的返回结果有几种状态理解它们能帮你快速定位问题返回NULL最常见的失败状态原因通常是格式串不匹配、字符串里有非法字符、日期逻辑不存在如2月30日且严格模式下。返回0000-00-00在不带NO_ZERO_DATE的模式下解析一个空日期可能会返回这个值。直接报错在严格模式下非法日期会导致SQL执行失败错误信息通常是Incorrect date value或Truncated incorrect datetime value。所以我在写导入脚本时习惯加一层预检查逻辑用STR_TO_DATE去验证每一行数据如果结果是NULL就单独捞出来记日志-- 找出导入表里日期不合法的记录 SELECT row_id, raw_date_string FROM import_data WHERE STR_TO_DATE(raw_date_string, %Y-%m-%d) IS NULL;这个查询还有个额外好处当你的格式串写错了这个SQL会瞬间把大量行标成不合法你就能第一时间发现是格式串的问题还是数据本身的问题。这个方法我在处理多个导入项目时都用上了效率很高。注意STR_TO_DATE(, %Y-%m-%d)和STR_TO_DATE(NULL, %Y-%m-%d)都返回NULL但语义不同。导入数据时如果源字段是空字符串最好在SQL里先判断一下别把空值和格式错误混在一个排查逻辑里。5. 日期转换实战复盘从CSV导入到报表统计工具原理讲完必须拿实际场景来验证。这里从一个日常项目里抽三个片段完整走一遍日期转换的链路你对照着能直接抄。5.1 场景一CSV文件里8位数字串的批量导入很多业务系统导出的CSV日期列是20240315这种纯数字串。直接LOAD DATA导入后VARCHAR列里存的全是数字。后续要按日期分组统计就需要转换。通常我在导入阶段就会把它转成真正的DATE类型避免查询时再转。-- 建一张带DATE列的导入目标表 CREATE TABLE import_orders ( order_id INT PRIMARY KEY, order_date DATE, amount DECIMAL(10,2) ); -- 从CSV导入利用STR_TO_DATE在导入时转换 LOAD DATA LOCAL INFILE /data/orders.csv INTO TABLE import_orders FIELDS TERMINATED BY , (order_id, order_date_var, amount) SET order_date STR_TO_DATE(order_date_var, %Y%m%d);这个写法的关键在于order_date_var先把原始字符串读进用户变量然后通过SET子句里的STR_TO_DATE完成转换再落库。如果原始字符串里有脏数据LOAD DATA会警告你可以用SHOW WARNINGS;查看具体哪几行出了问题。注意由于这里用了DATE类型以后做任何日期比较、分组索引都能正常用上不用再套函数。5.2 场景二时间戳字段转成可读日期另一个常见需求是把Unix时间戳转成日期。比如某张日志表有个ts BIGINT字段存的是毫秒时间戳要统计每天的日志量-- 毫秒时间戳先除以1000再用FROM_UNIXTIME转换 SELECT DATE(FROM_UNIXTIME(ts / 1000)) AS log_day, COUNT(*) AS log_count FROM api_logs WHERE ts 1709308800000 GROUP BY DATE(FROM_UNIXTIME(ts / 1000)) ORDER BY log_day;这个查询的关键点在于FROM_UNIXTIME只能处理秒级时间戳毫秒必须先除1000。你如果直接传毫秒得到的日期会大得离谱1970年加几万天那种一眼就能看出不对劲。另外这里在GROUP BY里用了DATE()函数把DATETIME转成日期跟前面讲的一样如果数据量大且能接受一定的冗余建议在表中冗余一个日期字段避免每次都做函数运算。5.3 场景三统一各种口语化日期格式真实业务里同一张表里可能出现各种日期写法比如有的来源填2024-03-15有的填3/15/2024还有的填20240315。遇到这种多格式混杂的脏数据直接转换会大量失败。我当时的处理方法是写一个格式化归一的小SQL规则如下SELECT CASE WHEN raw_date REGEXP ^[0-9]{4}-[0-9]{2}-[0-9]{2}$ THEN STR_TO_DATE(raw_date, %Y-%m-%d) WHEN raw_date REGEXP ^[0-9]{1,2}/[0-9]{1,2}/[0-9]{4}$ THEN STR_TO_DATE(raw_date, %c/%e/%Y) WHEN raw_date REGEXP ^[0-9]{8}$ THEN STR_TO_DATE(raw_date, %Y%m%d) ELSE NULL END AS normalized_date, raw_date FROM messy_date_table;这种CASE REGEXP STR_TO_DATE的组合看起来长但实际运行很稳定。其中%c和%e是无前导零的月和日正好匹配3/15/2024这种写法。当你处理客户导入的Excel、第三方API的回传数据时这三条规则覆盖了绝大多数情况。剩下的乱格式要么在源头修要么就拎出来人工处理。我当时统计了一下这个SQL一次性把这批数据的可转换率从不到60%拉到了95%以上剩下5%是真的地址瞎填的只能单独处理。6. 从Oracle/PostgreSQL迁到MySQL日期转换的等价写法对照整个章节最实用的部分在这里。如果你的项目正在做数据库迁移或者你要同时维护两套数据库方言的代码下面这张等价写法对照表可以省你大量查文档的时间。Oracle/PostgreSQL写法MySQL等价写法说明TO_DATE(2024-03-15, YYYY-MM-DD)STR_TO_DATE(2024-03-15, %Y-%m-%d)基本等价注意格式符变化TO_DATE(20240315, YYYYMMDD)STR_TO_DATE(20240315, %Y%m%d)纯数字串最常用TO_CHAR(SYSDATE, YYYY-MM-DD)DATE_FORMAT(NOW(), %Y-%m-%d)日期格式化输出SYSDATENOW()或CURRENT_TIMESTAMP当前时间TO_CHAR(SYSDATE, YYYYMMDDHH24MISS)DATE_FORMAT(NOW(), %Y%m%d%H%i%s)时间戳字符串TO_DATE(2024-03-15 14:08:05, YYYY-MM-DD HH24:MI:SS)STR_TO_DATE(2024-03-15 14:08:05, %Y-%m-%d %H:%i:%s)带时间部分EXTRACT(YEAR FROM SYSDATE)YEAR(NOW())或EXTRACT(YEAR FROM NOW())提取年份TO_CHAR(SYSDATE, MON)DATE_FORMAT(NOW(), %b)缩写月份名写这段的时候我回忆起一个具体的坑Oracle的YYYY-MM-DD HH24:MI:SS写法在MySQL里直接魔改成%Y-%m-%d %H:%i:%s其中分钟从MI变成%i这个是最容易忽略的。有个同事当时从Oracle迁过来几乎所有格式串都改对了唯独分钟那里一直用%MI结果每次解析出来的分钟都是00。排查了一个下午最后发现是格式符写错。这种细节不踩一次真的记不住。另外还有一个很多迁移项目会忽略的差异Oracle的TO_DATE对字符串和格式串的匹配相对宽松某些情况下会自动做小范围纠正MySQLSTR_TO_DATE的严格程度明显更高格式不完全匹配就会返回NULL。所以迁移时原本在Oracle能正常解析的2024-3-5这种不补零的日期在MySQL的%Y-%m-%d格式下会失败得改用%Y-%c-%e才能解析。这算是一个隐性不兼容的典型案例-- Oracle里可能正常 TO_DATE(2024-3-5, YYYY-MM-DD); -- MySQL里必须写成 SELECT STR_TO_DATE(2024-3-5, %Y-%c-%e);这类细节在迁移测试用例设计时一定要覆盖到否则上线后某个边界数据一触发就是线上事故。6.1 别在Java/Python代码里转换日期尽量交给数据库最后说一个架构层面的建议。很多团队习惯在代码里先把日期字符串拼好再传给SQL比如Java里用SimpleDateFormat先在业务层把Date对象格式化成String再拼到SQL里传给MySQL。这种做法不是说不行但会带来两个麻烦代码时区、格式化风格和数据库不一致时会出现程序认为对数据库不认的尴尬。SQL里没法直接利用索引优化因为传入的是格式化后的字符串而不是数据库理解的日期类型。更好的做法是在代码层直接传Date对象或时间戳让数据库自己处理日期格式。比如MyBatis里用#{}绑定参数时传入一个java.util.DateMySQL驱动会自动转成数据库识别的日期类型既不丢精度也不容易出现格式歧义。日期格式化输出这类工作也统一放到SQL的DATE_FORMAT里做至少保证所有格式化逻辑集中在一处后续要改格式只改SQL不用翻代码。我在实际项目里还养成了一个习惯项目里所有跟日期转换相关的SQL统一写在一个专门的mapper XML或视图文件里方便团队review时专门盯。日期这种问题一旦出错影响面往往是统计少一天或时间差8小时这种特别隐蔽的线上故障比字段名拼写错误难查得多。集中管理、专项review能提前拦住大部分问题。7. 最后几个必须养成的日期处理习惯这篇聊了不少函数和参数最后按我个人经验分享几个值得保持的好习惯有兴趣可以慢慢培养第一个永远先确认输入字符串的实际格式再写格式串。不要想当然认为这列就是yyyy-mm-dd先看几行真实数据用REGEXP规则验证一下再动手。因为很多时候源系统改了导出格式但文档没更新你写的转换SQL就会突然开始静默地返回NULL。第二个排查转换问题时先单独执行一次SELECT STR_TO_DATE(具体值, 格式串)确认这个函数没问题再去查整表数据。这个单点验证习惯能帮你把问题快速定位到数据脏还是格式串错两个方向之一节省大量时间。-- 排查套路第一步拿一条真实数据试 SELECT STR_TO_DATE(2024-03-15 14:08:05, %Y-%m-%d %H:%i:%s);返回正常就说明格式串没错问题出在数据本身返回NULL就先检查格式串的字母大小写、分隔符是否和数据匹配。第三个导入脏数据前先跑一轮预检查查询把非法的日期值都捞出来再决定是清洗还是拒收。很多数据质量事故都是因为偷懒跳过这一步直接把数据LOAD进去等统计报表对不上账了才回头查。与其事后痛苦排查不如事前一分钟检查。第四个新表设计阶段所有日期字段一律用DATE/DATETIME/TIMESTAMP类型不要用VARCHAR。这看着像废话但很多系统的历史表里偏偏就有大量VARCHAR日期留着就是给后来人埋雷。如果你在做表结构评审看到日期字段是VARCHAR一定要问一句为什么大部分时候答案都是当初图省事——省事一时麻烦一世。我在一次项目交付中发现凡是日期字段设计得规范的表后端的BI统计、报表查询、定时任务写起来都特别顺手反之那些VARCHAR日期字段的表每个查询都绕不开一层STR_TO_DATE性能和可维护性都差了一个档次。日期转换本质上是一个少量函数一套格式规则的小问题但处理不好就是贯穿整个项目的数据质量隐患。把转换逻辑放在写入和清洗阶段把类型设计规范放在建表阶段比任何万能转换函数都管用。