如果你的项目里需要把Oracle迁移到MySQL那你对to_char和to_date这两个名字一定不陌生。Oracle里写惯了TO_CHAR(SYSDATE, YYYY-MM-DD HH24:MI:SS)、TO_DATE(20230101, YYYYMMDD)到MySQL这边直接就是FUNCTION mydb.TO_CHAR does not exist全线飘红。这篇文章要解决的问题很明确在MySQL 8.0里自己写一套兼容Oracle语法的to_char、to_date自定义函数把迁移SQL的改动量压到最低同时让新项目里处理日期格式化时也不用再记MySQL那套以%开头的格式符号。适合正在做数据迁移的开发者也适合被MySQL日期函数绕晕的新手。我先把话说在前头MySQL原生并不是没有替代品DATE_FORMAT和STR_TO_DATE都能干类似的活但Oracle格式串和MySQL格式串的符号体系完全不一样几百条SQL一个个改过去改到后面眼睛都是花的。封装成同名函数之后原来Oracle的SQL基本不需要动这才是这个方案最大的价值。1. 迁移项目里日期函数到底卡在哪1.1 两种数据库的日期格式符号体系差太多Oracle和MySQL在日期处理上都有“格式化”和“解析”两类操作但语法层面的差异非常直接。Oracle写法SELECT TO_CHAR(SYSDATE, YYYY-MM-DD HH24:MI:SS) FROM DUAL; SELECT TO_DATE(2023-01-15 10:30:00, YYYY-MM-DD HH24:MI:SS) FROM DUAL;MySQL写法SELECT DATE_FORMAT(NOW(), %Y-%m-%d %H:%i:%s); SELECT STR_TO_DATE(2023-01-15 10:30:00, %Y-%m-%d %H:%i:%s);YYYY要改成%YDD要改成%dHH24要改成%HMI要改成%iSS要改成%s。表面上看着只有几个符号的替换实际在业务SQL里往往还混着各种字符串拼接、子查询和条件判断一改就是一大片。1.2 全量改SQL和封装函数我为什么选后者遇到这种兼容性问题通常有两条路全量改写SQL把所有TO_CHAR、TO_DATE手动替换成DATE_FORMAT、STR_TO_DATE再把格式串逐项翻译。优点是零额外部署成本缺点是工作量大、容易漏、上线后出问题不好排查。封装自定义函数在MySQL里创建同名函数格式串仍然用Oracle的YYYY-MM-DD HH24:MI:SS写法底层自动翻译成MySQL格式再调用原生函数。优点是SQL改动极小团队心智负担低新写的代码还能保持Oracle风格方便两边维护。我当时毫不犹豫选了第二条。原因很简单迁移不是一次性把SQL改完就结束了后续还有新需求要开发如果新代码都要按两套格式标准来写早晚还要踩坑。用函数把差异封装住所有人只认一套语法这才是长远省事的做法。维度全量改SQL自定义函数方案改造工作量每条SQL都要动建几个函数SQL基本不动团队学习成本需要同时掌握两套格式符只写Oracle风格即可排查难度改动点多容易改错问题集中在函数内部性能开销无额外开销函数调用有轻微开销但可控长期维护两套标准混用统一一套标准1.3 这个方案能覆盖到什么程度先说清楚边界免得你看完期待值过高。这里实现的自定义函数主要覆盖Oracle日常SQL里最常见的格式元素四位/两位年份、两位月份、月份缩写、月份全称、两位日、24小时制、12小时制、分钟、秒、星期全称、星期缩写。以及FM修饰符的兼容处理、双引号文本原样输出的处理。MySQ L 8.0原生确实没有内置to_char和to_date所以函数名可以直接用不需要担心和内置函数冲突。但稳妥考虑生产环境我建议加个前缀比如ora_to_char、ora_to_date长期演进更安全后面我会专门说。2. to_char自定义函数核心实现拆解2.1 设计思路把Oracle格式串翻译成MySQL格式串to_char的核心逻辑不复杂输入一个日期和时间输入一个Oracle格式串先把格式串里的Oracle符号逐个翻译成MySQL的DATE_FORMAT能认识的符号然后调用DATE_FORMAT完成格式化。但翻译这件事有个顺序坑。比如YYYY和YY如果先把YY替换成%y那YYYY里开头两个字符就成了%yYY后面再想替换YYYY已经匹配不上了。同样的道理也适用于MONTH和MON、HH24和HH、DDD和DD。所以替换顺序必须严格遵循“长token优先”这是整个函数最容易出错的地方没有之一。2.2 可直接落地的to_char函数代码下面这段代码我在MySQL 8.0.36上实测过直接复制就能用。为了方便阅读函数名直接用了to_char你如果担心和未来版本冲突整体替换成ora_to_char即可。DROP FUNCTION IF EXISTS to_char; DELIMITER $$ CREATE FUNCTION to_char(dt DATETIME, fmt VARCHAR(255)) RETURNS VARCHAR(255) CHARSET utf8mb4 DETERMINISTIC NO SQL BEGIN DECLARE f VARCHAR(255) DEFAULT fmt; IF dt IS NULL OR fmt IS NULL THEN RETURN NULL; END IF; -- 长token优先替换避免短token把长token截胡 SET f REPLACE(f, MONTH, %M); SET f REPLACE(f, MON, %b); SET f REPLACE(f, DDD, %j); SET f REPLACE(f, YYYY, %Y); SET f REPLACE(f, HH24, %H); SET f REPLACE(f, HH12, %h); SET f REPLACE(f, DAY, %W); SET f REPLACE(f, DY, %a); SET f REPLACE(f, MI, %i); SET f REPLACE(f, SS, %s); -- 短token放在后面 SET f REPLACE(f, YY, %y); SET f REPLACE(f, MM, %m); SET f REPLACE(f, DD, %d); SET f REPLACE(f, HH, %h); -- FM修饰符直接删除效果接近 SET f REPLACE(f, FM, ); RETURN DATE_FORMAT(dt, f); END$$ DELIMITER ;代码里的字符集声明CHARSET utf8mb4是我后来补的。刚开始没加结果在部分库字符集为latin1的实例上格式化出英文月份全称January没问题一旦涉及中文配置就会出现乱码后来统一加上就再没出过事。2.3 双引号文本与FM修饰符如何做到更接近OracleOracle 的TO_CHAR支持在格式串里用双引号输出原样文本比如SELECT TO_CHAR(SYSDATE, YYYY年MM月DD日) FROM DUAL;注意双引号里的“年”“月”“日”如果直接交给上面的函数会被REPLACE链原样保留MySQL 的DATE_FORMAT看到普通字符也会原样输出看起来好像没问题。但碰到双引号里含字母的情况就糟了比如YYYYMM双引号里的MM会被误替换成数字月份这就和Oracle行为不一致了。解决办法是在做任何格式替换之前先把双引号里的内容“保护”起来。MySQL存储函数不方便用数组我的做法是提取前两组引号内容替换成唯一的占位符等所有格式翻译完成后再回填。DECLARE p1 VARCHAR(64) DEFAULT ; DECLARE p2 VARCHAR(64) DEFAULT ; SET p1 SUBSTRING_INDEX(SUBSTRING_INDEX(fmt, , 2), , -1); SET f REPLACE(fmt, CONCAT(, p1, ), 1); IF LOCATE(, f) 0 THEN SET p2 SUBSTRING_INDEX(SUBSTRING_INDEX(f, , 2), , -1); SET f REPLACE(f, CONCAT(, p2, ), 2); END IF; -- 所有格式替换做完之后 SET f REPLACE(f, 1, p1); SET f REPLACE(f, 2, p2);这个技巧我用了很久它解决的不是高频需求但一旦遇到就是救命级的。至于FM修饰符Oracle里FMMonth DD会去掉前导空格和零输出January 1。MySQL没有一一对应的符号我的处理是直接删除FM输出会变成January 01绝大多数业务都能接受。如果你确实要去掉前导零可以在翻译后的格式串里把月份的%m改成不带零的%c、日的%d改成%e但要注意这不是全局替换得针对FMMM、FMDD这种组合单独处理。2.4 边界行为NULL、非法日期、时间部分怎么处理Oracle的TO_CHAR(NULL, YYYY)返回NULL我们的函数同样返回NULL这个行为在迁移时必须保持一致。函数入参的dt类型是DATETIME如果你传入的是一个DATE类型MySQL会隐式转成DATETIME时间部分是00:00:00格式化出来也没问题。如果传入的字符串日期比如2023-01-01MySQL在DATETIME参数位置会尝试隐式转换能转换成功就用转不成功函数会直接报错。所以实际使用中我建议调用方先确保传入的是DATE或DATETIME类型字符串格式的日期交给后面的to_date函数处理。3. to_date自定义函数字符串解析的关键3.1 翻译方向相反坑却更隐蔽to_date做的事情和to_char相反输入一个日期字符串和一个格式串解析成日期时间类型。翻译格式串的思路一样把Oracle符号映射成MySQLSTR_TO_DATE能识别的符号然后交给STR_TO_DATE。但这里有个隐藏很深的坑STR_TO_DATE对格式和字符串的匹配要求极严格。STR_TO_DATE(2023-01-01, %Y-%m-%d %H:%i:%s)会返回NULL因为你的字符串里根本没有时间部分格式串却要求有时间。反过来字符串带了时间却用纯日期格式解析结果会直接丢掉时间。3.2 to_date基础版代码DROP FUNCTION IF EXISTS to_date; DELIMITER $$ CREATE FUNCTION to_date(str VARCHAR(255), fmt VARCHAR(128)) RETURNS DATETIME DETERMINISTIC NO SQL BEGIN DECLARE f VARCHAR(128) DEFAULT fmt; DECLARE ret DATETIME; IF str IS NULL OR fmt IS NULL THEN RETURN NULL; END IF; -- 同样先长后短 SET f REPLACE(f, MONTH, %M); SET f REPLACE(f, MON, %b); SET f REPLACE(f, DDD, %j); SET f REPLACE(f, YYYY, %Y); SET f REPLACE(f, HH24, %H); SET f REPLACE(f, HH12, %h); SET f REPLACE(f, DAY, %W); SET f REPLACE(f, DY, %a); SET f REPLACE(f, MI, %i); SET f REPLACE(f, SS, %s); SET f REPLACE(f, YY, %y); SET f REPLACE(f, MM, %m); SET f REPLACE(f, DD, %d); SET f REPLACE(f, HH, %h); SET ret STR_TO_DATE(str, f); IF ret IS NULL THEN SIGNAL SQLSTATE 45000 SET MESSAGE_TEXT to_date: 无法按指定格式解析日期; END IF; RETURN ret; END$$ DELIMITER ;加了SIGNAL报错之后非法日期不会再静默返回NULL而是像Oracle一样直接抛异常。这个细节在迁移时特别重要因为很多老系统是依赖报错来拦脏数据的改成NULL返回会让数据质量问题一路躺到月底对账才发现。3.3 不带格式参数的自动识别版本迁移时还会遇到一类SQLOracle里写的是TO_DATE(2023-01-15)根本没传格式串。MySQL的STR_TO_DATE没参数根本没法解析纯靠CAST也只能兜住少数固定格式。所以我额外写了一个自动识别常见格式的版本DROP FUNCTION IF EXISTS to_date_auto; DELIMITER $$ CREATE FUNCTION to_date_auto(str VARCHAR(255)) RETURNS DATETIME DETERMINISTIC NO SQL BEGIN IF str IS NULL THEN RETURN NULL; END IF; -- 2023-01-15 10:30:00 IF str REGEXP ^[0-9]{4}-[0-9]{1,2}-[0-9]{1,2} [0-9]{1,2}:[0-9]{1,2}:[0-9]{1,2}$ THEN RETURN STR_TO_DATE(str, %Y-%m-%d %H:%i:%s); END IF; -- 2023-01-15 IF str REGEXP ^[0-9]{4}-[0-9]{1,2}-[0-9]{1,2}$ THEN RETURN STR_TO_DATE(str, %Y-%m-%d); END IF; -- 20230115 IF str REGEXP ^[0-9]{8}$ THEN RETURN STR_TO_DATE(str, %Y%m%d); END IF; -- 20230115103000 IF str REGEXP ^[0-9]{14}$ THEN RETURN STR_TO_DATE(str, %Y%m%d%H%i%s); END IF; -- 2023/01/15 10:30:00 IF str REGEXP ^[0-9]{4}/[0-9]{1,2}/[0-9]{1,2} [0-9]{1,2}:[0-9]{1,2}:[0-9]{1,2}$ THEN RETURN STR_TO_DATE(str, %Y/%m/%d %H:%i:%s); END IF; -- 2023/01/15 IF str REGEXP ^[0-9]{4}/[0-9]{1,2}/[0-9]{1,2}$ THEN RETURN STR_TO_DATE(str, %Y/%m/%d); END IF; SIGNAL SQLSTATE 45000 SET MESSAGE_TEXT to_date_auto: 未知日期格式; END$$ DELIMITER ;正则判断的顺序也有讲究先判断带时间的格式再判断纯日期格式避免2023-01-15被2023-01-15 10:30:00的正则误伤两者的$锚点其实已经能区分但先长后短的习惯在这里依然成立。3.4 两位年份的世纪推断问题必须单独说Oracle的TO_DATE(23-01-15, YY-MM-DD)解析出来是2023年而RR格式会有另一套世纪推断规则。MySQL的%y解析两位年份时默认返回2000年到2069年这个段超过69就会判成1970到1999。这套规则和Oracle的RR并不完全一致。我在迁移中踩过这个坑表里有一批手工录入的生日数据两位年份69-01-01Oracle按1969年处理MySQL按2069年处理一迁移全变“未来人”。遇到这种情况最稳妥的做法是不要依赖数据库的世纪推断在SQL里显式把两位年份补成四位再做解析。这个逻辑不适合塞进通用的to_date函数里它属于业务数据清洗的范畴。4. 部署、权限与性能避坑4.1 创建函数时最容易碰到的ERROR 1418第一次在MySQL 8.0实例上创建函数很容易撞上这个错误ERROR 1418 (HY000): This function has none of DETERMINISTIC, NO SQL, or READS SQL DATA in its declaration and binary logging is enabled...原因是实例开启了binlog主从同步时MySQL无法判断这个函数是不是确定性的怕主库和从库执行结果不一致。解决办法有两个创建函数时声明DETERMINISTIC NO SQL我们上面的代码已经带了这两个声明可以绕开如果手上有一堆老函数没有声明也可以SET GLOBAL log_bin_trust_function_creators 1但这等于让MySQL信任所有函数创建者生产环境不建议。另外提醒一句创建函数本身需要CREATE ROUTINE权限迁移账号如果只给了DML权限是建不成的得找DBA开权限或者把函数建在专用的工具库。4.2 函数建在哪个库决定了你SQL怎么写MySQL的存储函数归属于当前数据库调用时如果当前库不对直接裸写to_char(...)会报FUNCTION db.to_char does not exist因为MySQL会去当前库找。我通常的做法是建一个独立的common工具库把所有兼容函数都扔进去然后调用方统一写成common.to_char(...)。这样SQL的可读性和可维护性都更好也方便多个业务库共享同一套函数。如果迁移的SQL实在太多不想每个函数名前都带库名那就确保所有会话执行前都USE对了库。4.3 性能上最大的坑在WHERE条件里对字段套函数自定义函数解决了语法兼容但它不是银弹最典型的误用是在过滤条件里对表字段套函数-- 这种写法会导致索引失效 SELECT * FROM orders WHERE to_char(create_time, YYYYMMDD) 20230115;create_time上有索引也没用因为to_char把字段的值全部处理了一遍MySQL无法走索引范围扫描。正确写法应该是把函数作用在常量一侧SELECT * FROM orders WHERE create_time to_date_auto(20230115) AND create_time to_date_auto(20230116);道理和Oracle里不能对索引列加函数一样但迁移时大家往往顾着改语法忽略了这个细节。我强烈建议在代码评审里专门加一条规则to_char只允许出现在SELECT列表to_date只允许作用在字符串常量上。4.4 与ORM框架和迁移工具的配合建议用MyBatis之类的框架如果你的Mapper里大量使用了${}字符串拼接日期条件换成自定义函数后基本无感因为最终拼出来的SQL本来就是这个样子。但有一点要注意MyBatis的二级缓存和函数结果没关系不用管。如果是用DataX、Kettle这类工具做数据迁移原库读出来再写入MySQL一般不涉及这两个函数因为数据不需要格式化直接DATETIME类型搬过去就行。真正需要这套函数的是应用层SQL迁移这个环节尤其是动态报表、老管理后台这类SQL散落在代码里的系统。5. 调用实测与常见问题排查实录5.1 高频报错速查表现象原因解决办法ERROR 1418binlog开启函数未声明确定性创建时加DETERMINISTIC NO SQLERROR 1305 FUNCTION xxx does not exist函数不在当前库或未带库名写common.to_char(...)或USE正确库STR_TO_DATE返回NULL字符串与格式串不完全匹配检查分隔符、时间部分是否存在调用to_date总报“无法解析”有空格、全角字符等不可见字符先TRIM再确认格式串与字符串一致格式化结果比Oracle多前导零FM修饰符被直接删除按需把%m改为%c、%d改为%e中文月份/星期乱码库字符集不是utf8mb4函数返回类型显式声明CHARSET utf8mb45.2 三个真实项目里遇到的问题复盘第一个问题是格式串里混了中文冒号。Oracle的SQL写的是TO_CHAR(SYSDATE, YYYY-MM-DD HH24:MI:SS)但有些历史SQL被程序替换过冒号变成了全角Oracle能忍MySQL的STR_TO_DATE直接不认。排查了半小时才发现是肉眼几乎分辨不出的字符差异。建议在函数入口对str和fmt都做一次REPLACE把全角冒号、全角横线统一替换成半角能省下大量这种“不可见”的排错时间。第二个问题是时间部分为空的字符串。业务系统传过来的是2023-01-15 00:00:00但有些历史数据变成了2023-01-15导致原先的固定格式解析失败。后来我把to_date做了一层兜底如果按传入格式解析失败再尝试不带时间部分的格式重新解析一次。也就是把%Y-%m-%d %H:%i:%s自动降级为%Y-%m-%d。加了这个兼容逻辑后脏数据引发的线上报错少了一大半。第三个问题是函数里的NO SQL声明当时漏了导致部分开启binlog的实例创建失败。这个我前面已经详细说了这里再提醒一次不是每个开发环境的MySQL都开着binlog所以本地测试一切正常一上生产就报1418先查环境差异。5.3 个人建议把兼容函数当独立小工具维护这套函数一开始我只是临时给迁移项目救火用的后来发现团队写新SQL时也在用干脆把它从业务库里抽出来放进专门的common工具库用版本管理工具单独维护还写了几条简单的单元测试SQLSELECT to_char(2023-01-15 10:30:00, YYYY-MM-DD HH24:MI:SS); -- 期望输出2023-01-15 10:30:00 SELECT to_char(2023-01-15 10:30:00, YYYY年MM月DD日); -- 期望输出2023年01月15日 SELECT to_date_auto(20230115); -- 期望输出2023-01-15 00:00:00每次改完函数跑一遍这些固定用例心里踏实很多。说到底这个方案的价值在于把数据库差异挡在了一个极小的封装层后面让业务SQL的写法保持一致。往后不管是迁到MySQL还是其他兼容MySQL协议的数据库只要把这一层函数重新实现一遍业务代码基本可以原封不动地跑起来这也算是在迁移这类项目里最省心的一种姿势了。
