尧图网站开发 
1. 为什么要在 MySQL 里造这两个轮子1.1 这个坑是怎么来的如果你是从 Oracle 迁到 MySQL 的大概率遇到过这种场景:原库的 SQL 里写着to_char(create_time, YYYY-MM-DD)或者to_date(2024-01-01, YYYY-MM-DD),一拿到 MySQL 里执行就直接报错FUNCTION db.to_char does not exist。MySQL 原生的日期格式化函数是DATE_FORMAT(),字符串转日期是STR_TO_DATE(),压根没有 Oracle 那套 to_char/to_date 函数。这时候通常有两条路:一是把所有 SQL 改成 MySQL 原生写法改动量大但一劳永逸二是在 MySQL 里定义同名自定义函数保持 SQL 层零改动应用代码一行不用动。本文要讲的正是第二种方案核心思路是先做一个Oracle 格式符转 MySQL 格式符的映射器再基于它实现 to_char 和 to_date 这两个自定义函数。这个方案非常适合 Oracle 向 MySQL 迁移、异构数据同步、报表 SQL 改造等场景也是我给好几个项目做过的基础工具实践中验证下来非常稳。1.2 动手前先盘点:你到底需要哪些格式写函数之前我强烈建议先盘一下现有 SQL 里到底用了哪些日期格式组合不要一上来就照着 Oracle 文档把所有格式符号都实现个遍。那样函数会变得又重又难维护。以我遇到过的真实业务来说90% 的场景其实只集中在七八个格式上:YYYY-MM-DD、YYYY-MM-DD HH24:MI:SS、YYYY/MM/DD、MM-DD-YYYY、HH24:MI再复杂一点会用到MON、DY、AM这种英文表达。先把需求摸清照着清单做映射后面能少走很多弯路。Oracle 的日期格式和 MySQL 完全是两套符号体系。Oracle 里写YYYY-MM-DD HH24:MI:SSMySQL 里对应的是%Y-%m-%d %H:%i:%s。如果不做映射手工一个个替换特别容易漏而且有些字母看着一样、含义完全不同。比如MM在 Oracle 里是两位月份数字在 MySQL 格式串里%M是英文月名%m才是数字月份一个字符区别结果就差了十万八千里。所以整个方案的第一步就是先把格式映射做对、做全这个映射器是整个自定义函数的地基。2. 整体设计方案:先封装一个格式符映射器2.1 别一上来就写两个大函数很多人接到需求后第一反应是直接创建 to_char 和 to_date 两个函数把格式转换逻辑分别写在各自函数体里。这样做不是不行但维护起来很痛苦:两边都有格式映射逻辑改一个映射规则要动两处漏改一处就出现行为不一致。我的习惯是先写一个内部映射函数oracle_format_to_mysql(fmt),专门把 Oracle 格式模型转换成 MySQL 格式模型然后 to_char 和 to_date 都去调用它。这样设计有几个好处:一是映射规则只维护一份修 bug 只改一处二是后续要支持更多格式符号时不用动两个大函数只改映射器即可三是两个函数的逻辑都被简化成传格式串、拿结果的薄封装出问题好排查。这个思路其实跟写代码时提炼公共方法是一样的道理函数之间的复用关系清晰测试也方便。2.2 Oracle 和 MySQL 格式符对照表下面这张表是我整理出的常用格式符映射关系基本覆盖了业务 SQL 里绝大多数场景。我在映射器里就是按这张表实现的。含义Oracle 格式MySQL 格式备注四位年份YYYY%Y两位年份YY%y世纪边界处理逻辑与 Oracle 不完全一致月份数字MM%m月份英文缩写MON%b输出受系统语言影响月份英文全称MONTH%M输出受系统语言影响日期数字DD%d12 小时制HH12%h24 小时制HH24%H分钟MI%i秒SS%s上午/下午AM 或 PM%pOracle 里 AM 和 PM 可以互换星期英文缩写DY%a星期英文全称DAY%W年份的周WW%v边界定义有差异ISO 年份IYYY%x需要 MySQL 8.0ISO 周IW%V需要 MySQL 8.0文本引号 去掉引号、保留内容映射器处理时原样保留引号内文本表格里没列出来的字符比如 Oracle 的Q季度、RR两位年份换算世纪这类我的映射器默认按普通字符透传因为 MySQL 原生格式符里没有对应项透传出去会被DATE_FORMAT()当作普通文本输出结果不对但不会报错。对于这类需求我更建议在业务 SQL 层用CONCAT手动拼季度别硬塞给自定义函数。提示:MySQL 的%p输出的是英文 AM/PM 还是上午/下午取决于会话的lc_time_names变量值。如果没有特殊要求建议保持默认否则迁移前后的展示结果会不一样容易被业务方误判成 bug。2.3 映射器的实现思路与代码骨架映射器本质上是一个字符串逐字符扫描的函数。Oracle 的格式串里既有格式占位符也可能有双引号包裹的文本例如to_char(sysdate, Today is DD of MONTH)这种写法。所以映射器不能简单用REPLACE做批量替换而是要逐字符处理遇到双引号里的内容原样保留遇到格式占位符再映射成 MySQL 格式。用REPLACE最怕的是格式符之间互相嵌套比如HH24如果先把HH替换掉就变成%h24后面再想按HH24整体处理就全乱了。下面给出映射器的核心骨架代码用存储函数实现循环扫描:DELIMITER $$ CREATE FUNCTION oracle_format_to_mysql(fmt VARCHAR(255)) RETURNS VARCHAR(255) DETERMINISTIC NO SQL BEGIN DECLARE res VARCHAR(255) DEFAULT ; DECLARE i INT DEFAULT 1; DECLARE n INT DEFAULT CHAR_LENGTH(fmt); DECLARE ch VARCHAR(4); DECLARE in_quote BOOLEAN DEFAULT FALSE; WHILE i n DO SET ch SUBSTRING(fmt, i, 1); -- 双引号内的文本原样保留 IF ch THEN SET in_quote NOT in_quote; SET i i 1; ITERATE; END IF; IF in_quote THEN SET res CONCAT(res, ch); SET i i 1; ITERATE; END IF; -- 多字符格式符优先匹配HH24 要先于 HH 处理 IF i 2 n AND SUBSTRING(fmt, i, 3) HH24 THEN SET res CONCAT(res, %H); SET i i 3; ITERATE; END IF; IF i 1 n AND SUBSTRING(fmt, i, 2) HH THEN SET res CONCAT(res, %h); SET i i 2; ITERATE; END IF; -- 单字符格式符分支按 CASE 处理 YYYY/YY、MONTH/MON/MM、DD、MI、SS 等 CASE ch WHEN Y THEN IF i 3 n AND SUBSTRING(fmt, i, 4) YYYY THEN SET res CONCAT(res, %Y); SET i i 4; ELSEIF i 1 n AND SUBSTRING(fmt, i, 2) YY THEN SET res CONCAT(res, %y); SET i i 2; ELSE SET res CONCAT(res, ch); SET i i 1; END IF; WHEN M THEN IF i 4 n AND SUBSTRING(fmt, i, 5) MONTH THEN SET res CONCAT(res, %M); SET i i 5; ELSEIF i 2 n AND SUBSTRING(fmt, i, 3) MON THEN SET res CONCAT(res, %b); SET i i 3; ELSE SET res CONCAT(res, %m); SET i i 1; END IF; ELSE SET res CONCAT(res, ch); SET i i 1; END CASE; END WHILE; RETURN res; END$$ DELIMITER ;我上面只写了核心逻辑的骨架实际使用还需要补全AM、PM、MI、SS、DAY、DY等分支。这里提醒一句:WHEN M分支里MONTH和MON必须优先于MM判断因为MONTH的前两个字符是MO如果先匹配MM就会把MONTH错误切分。这个顺序问题是新手最容易踩的坑。3. to_char 函数实现:格式化日期输出3.1 完整代码与调用方式to_char 的本质就是把 Oracle 格式串转成 MySQL 格式串后交给DATE_FORMAT()。Oracle 里还有三参数版本比如to_char(sysdate, YYYY-MM-DD, NLS_DATE_LANGUAGEAMERICAN),但为了保持轻量我通常只实现两个参数语言类参数直接忽略。下面给出一个能跑的基础版本:DELIMITER $$ CREATE FUNCTION to_char(dt DATETIME, fmt VARCHAR(255)) RETURNS VARCHAR(255) DETERMINISTIC NO SQL BEGIN DECLARE mysql_fmt VARCHAR(255); DECLARE ret VARCHAR(255); IF fmt IS NULL OR fmt THEN SET mysql_fmt %Y-%m-%d %H:%i:%s; ELSE SET mysql_fmt oracle_format_to_mysql(fmt); END IF; SET ret DATE_FORMAT(dt, mysql_fmt); -- Oracle 的 FM 修饰符会去掉结果里的前导空格这里统一处理 IF LOCATE(FM, fmt) 0 THEN SET ret LTRIM(ret); END IF; RETURN ret; END$$ DELIMITER ;实测用法:SELECT to_char(NOW(), YYYY-MM-DD HH24:MI:SS); -- 输出:2025-01-16 14:33:21 SELECT to_char(NOW(), MONTH DD, YYYY); -- 输出:January 16, 2025 SELECT to_char(NOW(), HH24:MI AM); -- 输出:14:33这里有个容易忽略的点:DATE_FORMAT对格式串里非格式符的部分是原样透传的所以YYYY-MM-DD映射成%Y-%m-%d后中间的连字符会原样保留正好符合预期。但如果格式串里用了 Oracle 的RR,映射器透传后DATE_FORMAT会把它当普通字符输出结果长得像RR-01-16不会报错但数据是错的。这种问题只能靠测试用例来兜底所以后面专门有一节讲测试。3.2 带 FM 修饰符的情况怎么处理Oracle 里的FM修饰符用于去掉结果中的前导空格和补零比如to_char(sysdate, FMMONTH DD)会把JANUARY 16变成JANUARY 16。MySQL 的DATE_FORMAT没有完全对应的概念好消息是%m、%d这类格式符天生不补前导零比如2025-1-6这跟 Oracle 默认输出2025-01-06不一样。所以我在函数里做了折中:如果格式串里出现FM忽略这个修饰符并对其结果做一次LTRIM去掉 MySQL 格式符可能产生的首部空格。说实话FM场景在真实业务里并不多见报表场景大多希望固定宽度而不是去掉前导零。如果你的报表确实需要补零效果建议在函数外层用LPAD,或者干脆直接写DATE_FORMAT(date_col, %Y-%m-%d),它天然就是两位月、两位日。3.3 传入字符串类型时要注意什么一个常见的坑是:业务 SQL 里对字符串字段直接调 to_char比如to_char(birthday, YYYY-MM-DD)而birthday实际是VARCHAR类型。MySQL 里函数参数声明为DATETIME传字符串会发生隐式转换绝大多数情况下能转成功但遇到2024-13-45这种不合法日期MySQL 会返回 NULL 并产生一条 warning不会直接报错。这其实是好事至少不会让整条查询崩溃。如果你希望函数更严格可以在函数里判断返回值发现 NULL 就主动抛异常:IF ret IS NULL THEN SIGNAL SQLSTATE 45000 SET MESSAGE_TEXT Invalid date value in to_char; END IF;但我的建议是日常实现的 to_char 不要加这个判断。因为这会改变函数在查询中的行为万一某条数据本身就脏整批查询会全部挂掉到时候排查起来更麻烦。保持 MySQL 原生的宽容风格让结果返回 NULL、业务侧做数据清洗是更稳妥的做法。4. to_date 函数实现:字符串转日期4.1 核心代码与调用示例to_date 的方向正好反过来把字符串按 Oracle 格式解析成日期时间。MySQL 里对应的原生函数是STR_TO_DATE实现思路同样是先做格式映射再调用STR_TO_DATE:DELIMITER $$ CREATE FUNCTION to_date(str VARCHAR(255), fmt VARCHAR(255)) RETURNS DATETIME DETERMINISTIC NO SQL BEGIN DECLARE mysql_fmt VARCHAR(255); IF fmt IS NULL OR fmt THEN SET mysql_fmt %Y-%m-%d %H:%i:%s; ELSE SET mysql_fmt oracle_format_to_mysql(fmt); END IF; RETURN STR_TO_DATE(str, mysql_fmt); END$$ DELIMITER ;实测用法:SELECT to_date(2025-01-16 14:33:21, YYYY-MM-DD HH24:MI:SS); -- 输出:2025-01-16 14:33:21 SELECT to_date(16-Jan-2025, DD-MON-YYYY); -- 输出:2025-01-16 SELECT to_date(2025/01/16, YYYY/MM/DD); -- 输出:2025-01-16有个细节值得注意:STR_TO_DATE的返回值是DATETIME如果原 SQL 里期望的是日期类型比如只要2025-01-16下游是DATE字段时写库会自动截断时间部分没问题。但如果下游是字符串比较就可能会带出00:00:00这种尾巴。所以我在实际项目中保留了一个额外逻辑:如果格式串里只含日期格式符、不含时间格式符调用STR_TO_DATE后再用DATE()包一层让返回值类型更干净。你可以根据自己项目的下游消费方式决定是否保留这段逻辑。4.2 容错处理:数据很脏时怎么办to_date 最怕的是业务数据不干净。比如字符串是2025-1-6,格式指定成YYYY-MM-DD,Oracle 里解析可能直接报错但 MySQL 的STR_TO_DATE对%m、%d格式符允许一位数所以2025-1-6能正常解析成2025-01-06这算是迁移时一个意外之喜。反过来如果字符串是2025-13-45,STR_TO_DATE返回 NULL只给 warning 不给 error查询不会中断结果变成 NULL 留给业务侧去排查。还有一类情况是只有时间没有日期比如to_date(14:33:21, HH24:MI:SS)。STR_TO_DATE遇到格式里只有时间格式符时日期部分会得到0000-00-00在 MySQL 严格模式下可能直接报错。为了避免这种不确定性我通常在函数里先判断格式串是否包含年份、月份、日期格式符如果这三者都不含就用CURDATE()把当天日期补进去再返回结果。这个判断逻辑看起来多余但在真实数据导入场景里帮了大忙免得每次都在导出数据时被零日期卡住。4.3 和原生 STR_TO_DATE 的兼容关系有一点要强调:to_date 的第二个参数映射完之后本质上就是 MySQL 格式串所以它和直接写STR_TO_DATE在行为上完全一致。迁移过程中如果有些 SQL 是后来新写的你可以不用自定义函数直接用原生写法结果没有差异。但自定义函数的意义在于:老系统里几百个存储过程、几十个报表 SQL 全部不用改只要在库里创建同名函数迁移后的行为就和 Oracle 保持大体一致。这也是我推荐做这个工具的根本原因。唯一要提醒的是MySQL 的自定义函数是库级别的不同库要各自创建一遍如果建在公共 schema 里又可能有权限问题。我的经验是每个业务库都执行一遍创建脚本最省心。5. 测试用例与日常踩坑记录5.1 用一组 SQL 做冒烟测试函数写完别急着上线先跑一组冒烟测试把常见格式和边界场景都覆盖到。下面是我自己项目里的测试脚本可以直接复制去跑:-- 基础日期格式 SELECT to_char(NOW(), YYYY-MM-DD) AS c1; SELECT to_char(NOW(), YYYY/MM/DD) AS c2; SELECT to_char(NOW(), MM-DD-YYYY) AS c3; SELECT to_char(NOW(), HH24:MI:SS) AS c4; SELECT to_char(NOW(), HH12:MI AM) AS c5; -- 带英文月份/星期 SELECT to_char(NOW(), MONTH DD, YYYY) AS c6; SELECT to_char(NOW(), DY, MON DD) AS c7; -- 含引号文本 SELECT to_char(NOW(), Today is DD) AS c8; -- to_date 解析 SELECT to_date(2025-01-16, YYYY-MM-DD) AS d1; SELECT to_date(2025/01/16 14:33:21, YYYY/MM/DD HH24:MI:SS) AS d2; SELECT to_date(16-Jan-2025, DD-MON-YYYY) AS d3; -- 错误场景:期望返回 NULL不期望报错 SELECT to_date(2025-13-45, YYYY-MM-DD) AS e1; SELECT to_char(NULL, YYYY-MM-DD) AS e2;这些用例里值得注意的:e1如果返回 NULL说明容错逻辑正常e2也一样NULL 传入 to_char 应该返回 NULL而不是报错。如果你的函数在 NULL 输入时报错需要检查参数声明是否为DETERMINISTIC NO SQL并在函数入口加一段IF dt IS NULL THEN RETURN NULL; END IF;。5.2 格式映射的几个高发坑第一个坑是HH和HH24的判断顺序。映射器如果先匹配HH那么HH24会被解析成%h24最终输出像1424这种乱码。必须像前面代码骨架那样先判断三位HH24再判断两位HH。第二个坑是MONTH和MON的优先级因为MONTH以MON开头先匹配MON后残留TH两个字面字符输出像JanTH。解决办法同样是多字符优先匹配。第三个坑来自AM和PM的处理。Oracle 里格式串可以同时出现HH24和AM但逻辑上是矛盾的。MySQL 的DATE_FORMAT会同时生效结果出现23:00 PM这种不伦不类的值。这不是函数本身的问题而是格式串写得就不合理建议迁移时直接让业务方改成HH24:MI,不要带 AM。第四个坑是双引号文本。Oracle 的格式串用双引号包普通文本映射器里要正确识别引号并保留内容否则Today is 里的空格和大小写会丢。这个逻辑我在前面的骨架代码里已经用in_quote变量处理了但如果你是自己从零写很容易漏掉这个细节。5.3 常见问题速查表我把实际运行中遇到的典型问题整理成了下面这个表方便排错时快速定位。现象可能原因排查与修复FUNCTION does not exist函数没创建成功或建在别的库确认当前库重新执行创建脚本输出HH24原样文本映射器里HH先于HH24匹配调整判断逻辑多字符格式符优先输出JanTHMONTH被当成MON解析调整MONTH判断优先于MON日期返回 NULL 带 warning输入字符串非法或格式不匹配用SHOW WARNINGS查看细节检查数据质量to_char 结果末尾带空格%b、%M等英文月份受语言包影响设置lc_time_names或统一会话语言创建函数报权限错误当前用户没有CREATE ROUTINE权限授权CREATE ROUTINE, ALTER ROUTINE查询很慢、全表扫描WHERE 条件里对索引列调用 to_char改写 SQL保证索引列不被函数包裹6. 性能考量与更优的替代做法6.1 存储函数在查询中的真实开销自定义存储函数如果只用在 SELECT 输出列比如SELECT to_char(create_time, YYYY-MM-DD) FROM t,性能和原生DATE_FORMAT差距不大因为格式串短、执行次数有限。但一旦用在 WHERE 条件里比如WHERE to_char(create_time, YYYY-MM-DD) 2025-01-16问题就来了:MySQL 对索引列套了函数之后无法走索引范围扫描只能全表扫数据量一大基本就废。我之前在一张千万级订单表上做过对比用to_char(create_time, YYYY-MM-DD)过滤跑了 3 秒多改成create_time 2025-01-16 00:00:00 AND create_time 2025-01-17 00:00:00之后走了索引耗时降到几十毫秒。所以自定义函数可以做但别滥用尤其别把它当成一种可以随意套在索引列上的工具。所有从 Oracle 迁过来的 SQL只要涉及函数包裹索引列都要在迁移清单里重点审查。6.2 索引列上要过滤日期应该怎么写如果业务就是要按天过滤并且字段是datetime类型最佳写法是给日期时间加一个区间条件:WHERE create_time 2025-01-16 00:00:00 AND create_time 2025-01-17 00:00:00。这个写法可以让优化器直接对索引列做范围扫描。相比之下WHERE DATE(create_time) 2025-01-16在 MySQL 5.7 及以下版本是不走索引的8.0 对一部分内置函数有优化但对用户自定义函数没有同样的优化能力。如果实在没法改 SQL又希望查询快可以考虑加一个生成列把格式化后的字符串存下来并建索引。这个做法适合查询模式固定的报表场景代价是写入时多占一点存储空间换来查询走索引的效率提升。生成列对函数有要求函数声明必须带DETERMINISTIC这就引出了下面的问题。6.3 函数声明为什么要加 DETERMINISTIC创建存储函数时DETERMINISTIC声明的含义是:对同样的输入函数一定返回同样的结果。MySQL 拿到这个声明后知道可以放心地对函数做表达式优化甚至允许它在生成列等场景中使用。如果你不加MySQL 会默认函数有副作用在特定场景下直接限制使用。我见过不少人在创建函数时对声明随意处理结果后面想用函数定义生成列时才发现报错。此时要回头修改函数定义如果函数已被其他对象引用还会遇到权限和 metadata 锁的问题。所以创建时就规范起来to_char 和 to_date 都加上DETERMINISTIC和NO SQL一劳永逸。这个习惯在写任何自定义函数时都值得保持。6.4 有没有比自定义函数更彻底的办法如果你不是做 Oracle 迁移而是新系统从零开发我的建议是直接用 MySQL 原生函数不碰自定义函数。DATE_FORMAT和STR_TO_DATE已经覆盖了绝大多数场景原生函数执行效率更高还能和优化器配合做更多执行计划优化。此外很多 ORM 框架本身有日期格式化的方言层比如 MyBatis 可以在 SQL 中用框架的格式化标签Hibernate 有专门的 dialect 配置这些都比在数据库里补函数更优雅。自定义函数真正适用的场景是:老系统代码量巨大、短期内无法逐条改造 SQL、DBA 也希望尽量少动应用代码。这种时候造两个 to_char/to_date 轮子确实是性价比最高的方案。我在实际项目中就是把创建脚本放进统一的迁移工具目录里每次新库部署时批量执行一遍几十个库一跑应用的 SQL 就都能正常运行了。写到这儿核心内容基本讲完了。最后再分享一个小技巧:函数上线后我习惯在测试环境跑一遍全量回归 SQL 对比表把原 Oracle 库的输出和 MySQL 自定义函数的输出逐条比对重点看月份名、星期名和 AM/PM 的差异因为这三类最容易受系统语言影响。只要这三类对得上业务 SQL 迁移基本就稳了。这个经验我用了好几年每次做数据库迁移都能省下不少排查时间。 
1. 为什么要在 MySQL 里造这两个轮子1.1 这个坑是怎么来的如果你是从 Oracle 迁到 MySQL 的大概率遇到过这种场景:原库的 SQL 里写着to_char(create_time, YYYY-MM-DD)或者to_date(2024-01-01, YYYY-MM-DD),一拿到 MySQL 里执行就直接报错FUNCTION db.to_char does not exist。MySQL 原生的日期格式化函数是DATE_FORMAT(),字符串转日期是STR_TO_DATE(),压根没有 Oracle 那套 to_char/to_date 函数。这时候通常有两条路:一是把所有 SQL 改成 MySQL 原生写法改动量大但一劳永逸二是在 MySQL 里定义同名自定义函数保持 SQL 层零改动应用代码一行不用动。本文要讲的正是第二种方案核心思路是先做一个Oracle 格式符转 MySQL 格式符的映射器再基于它实现 to_char 和 to_date 这两个自定义函数。这个方案非常适合 Oracle 向 MySQL 迁移、异构数据同步、报表 SQL 改造等场景也是我给好几个项目做过的基础工具实践中验证下来非常稳。1.2 动手前先盘点:你到底需要哪些格式写函数之前我强烈建议先盘一下现有 SQL 里到底用了哪些日期格式组合不要一上来就照着 Oracle 文档把所有格式符号都实现个遍。那样函数会变得又重又难维护。以我遇到过的真实业务来说90% 的场景其实只集中在七八个格式上:YYYY-MM-DD、YYYY-MM-DD HH24:MI:SS、YYYY/MM/DD、MM-DD-YYYY、HH24:MI再复杂一点会用到MON、DY、AM这种英文表达。先把需求摸清照着清单做映射后面能少走很多弯路。Oracle 的日期格式和 MySQL 完全是两套符号体系。Oracle 里写YYYY-MM-DD HH24:MI:SSMySQL 里对应的是%Y-%m-%d %H:%i:%s。如果不做映射手工一个个替换特别容易漏而且有些字母看着一样、含义完全不同。比如MM在 Oracle 里是两位月份数字在 MySQL 格式串里%M是英文月名%m才是数字月份一个字符区别结果就差了十万八千里。所以整个方案的第一步就是先把格式映射做对、做全这个映射器是整个自定义函数的地基。2. 整体设计方案:先封装一个格式符映射器2.1 别一上来就写两个大函数很多人接到需求后第一反应是直接创建 to_char 和 to_date 两个函数把格式转换逻辑分别写在各自函数体里。这样做不是不行但维护起来很痛苦:两边都有格式映射逻辑改一个映射规则要动两处漏改一处就出现行为不一致。我的习惯是先写一个内部映射函数oracle_format_to_mysql(fmt),专门把 Oracle 格式模型转换成 MySQL 格式模型然后 to_char 和 to_date 都去调用它。这样设计有几个好处:一是映射规则只维护一份修 bug 只改一处二是后续要支持更多格式符号时不用动两个大函数只改映射器即可三是两个函数的逻辑都被简化成传格式串、拿结果的薄封装出问题好排查。这个思路其实跟写代码时提炼公共方法是一样的道理函数之间的复用关系清晰测试也方便。2.2 Oracle 和 MySQL 格式符对照表下面这张表是我整理出的常用格式符映射关系基本覆盖了业务 SQL 里绝大多数场景。我在映射器里就是按这张表实现的。含义Oracle 格式MySQL 格式备注四位年份YYYY%Y两位年份YY%y世纪边界处理逻辑与 Oracle 不完全一致月份数字MM%m月份英文缩写MON%b输出受系统语言影响月份英文全称MONTH%M输出受系统语言影响日期数字DD%d12 小时制HH12%h24 小时制HH24%H分钟MI%i秒SS%s上午/下午AM 或 PM%pOracle 里 AM 和 PM 可以互换星期英文缩写DY%a星期英文全称DAY%W年份的周WW%v边界定义有差异ISO 年份IYYY%x需要 MySQL 8.0ISO 周IW%V需要 MySQL 8.0文本引号 去掉引号、保留内容映射器处理时原样保留引号内文本表格里没列出来的字符比如 Oracle 的Q季度、RR两位年份换算世纪这类我的映射器默认按普通字符透传因为 MySQL 原生格式符里没有对应项透传出去会被DATE_FORMAT()当作普通文本输出结果不对但不会报错。对于这类需求我更建议在业务 SQL 层用CONCAT手动拼季度别硬塞给自定义函数。提示:MySQL 的%p输出的是英文 AM/PM 还是上午/下午取决于会话的lc_time_names变量值。如果没有特殊要求建议保持默认否则迁移前后的展示结果会不一样容易被业务方误判成 bug。2.3 映射器的实现思路与代码骨架映射器本质上是一个字符串逐字符扫描的函数。Oracle 的格式串里既有格式占位符也可能有双引号包裹的文本例如to_char(sysdate, Today is DD of MONTH)这种写法。所以映射器不能简单用REPLACE做批量替换而是要逐字符处理遇到双引号里的内容原样保留遇到格式占位符再映射成 MySQL 格式。用REPLACE最怕的是格式符之间互相嵌套比如HH24如果先把HH替换掉就变成%h24后面再想按HH24整体处理就全乱了。下面给出映射器的核心骨架代码用存储函数实现循环扫描:DELIMITER $$ CREATE FUNCTION oracle_format_to_mysql(fmt VARCHAR(255)) RETURNS VARCHAR(255) DETERMINISTIC NO SQL BEGIN DECLARE res VARCHAR(255) DEFAULT ; DECLARE i INT DEFAULT 1; DECLARE n INT DEFAULT CHAR_LENGTH(fmt); DECLARE ch VARCHAR(4); DECLARE in_quote BOOLEAN DEFAULT FALSE; WHILE i n DO SET ch SUBSTRING(fmt, i, 1); -- 双引号内的文本原样保留 IF ch THEN SET in_quote NOT in_quote; SET i i 1; ITERATE; END IF; IF in_quote THEN SET res CONCAT(res, ch); SET i i 1; ITERATE; END IF; -- 多字符格式符优先匹配HH24 要先于 HH 处理 IF i 2 n AND SUBSTRING(fmt, i, 3) HH24 THEN SET res CONCAT(res, %H); SET i i 3; ITERATE; END IF; IF i 1 n AND SUBSTRING(fmt, i, 2) HH THEN SET res CONCAT(res, %h); SET i i 2; ITERATE; END IF; -- 单字符格式符分支按 CASE 处理 YYYY/YY、MONTH/MON/MM、DD、MI、SS 等 CASE ch WHEN Y THEN IF i 3 n AND SUBSTRING(fmt, i, 4) YYYY THEN SET res CONCAT(res, %Y); SET i i 4; ELSEIF i 1 n AND SUBSTRING(fmt, i, 2) YY THEN SET res CONCAT(res, %y); SET i i 2; ELSE SET res CONCAT(res, ch); SET i i 1; END IF; WHEN M THEN IF i 4 n AND SUBSTRING(fmt, i, 5) MONTH THEN SET res CONCAT(res, %M); SET i i 5; ELSEIF i 2 n AND SUBSTRING(fmt, i, 3) MON THEN SET res CONCAT(res, %b); SET i i 3; ELSE SET res CONCAT(res, %m); SET i i 1; END IF; ELSE SET res CONCAT(res, ch); SET i i 1; END CASE; END WHILE; RETURN res; END$$ DELIMITER ;我上面只写了核心逻辑的骨架实际使用还需要补全AM、PM、MI、SS、DAY、DY等分支。这里提醒一句:WHEN M分支里MONTH和MON必须优先于MM判断因为MONTH的前两个字符是MO如果先匹配MM就会把MONTH错误切分。这个顺序问题是新手最容易踩的坑。3. to_char 函数实现:格式化日期输出3.1 完整代码与调用方式to_char 的本质就是把 Oracle 格式串转成 MySQL 格式串后交给DATE_FORMAT()。Oracle 里还有三参数版本比如to_char(sysdate, YYYY-MM-DD, NLS_DATE_LANGUAGEAMERICAN),但为了保持轻量我通常只实现两个参数语言类参数直接忽略。下面给出一个能跑的基础版本:DELIMITER $$ CREATE FUNCTION to_char(dt DATETIME, fmt VARCHAR(255)) RETURNS VARCHAR(255) DETERMINISTIC NO SQL BEGIN DECLARE mysql_fmt VARCHAR(255); DECLARE ret VARCHAR(255); IF fmt IS NULL OR fmt THEN SET mysql_fmt %Y-%m-%d %H:%i:%s; ELSE SET mysql_fmt oracle_format_to_mysql(fmt); END IF; SET ret DATE_FORMAT(dt, mysql_fmt); -- Oracle 的 FM 修饰符会去掉结果里的前导空格这里统一处理 IF LOCATE(FM, fmt) 0 THEN SET ret LTRIM(ret); END IF; RETURN ret; END$$ DELIMITER ;实测用法:SELECT to_char(NOW(), YYYY-MM-DD HH24:MI:SS); -- 输出:2025-01-16 14:33:21 SELECT to_char(NOW(), MONTH DD, YYYY); -- 输出:January 16, 2025 SELECT to_char(NOW(), HH24:MI AM); -- 输出:14:33这里有个容易忽略的点:DATE_FORMAT对格式串里非格式符的部分是原样透传的所以YYYY-MM-DD映射成%Y-%m-%d后中间的连字符会原样保留正好符合预期。但如果格式串里用了 Oracle 的RR,映射器透传后DATE_FORMAT会把它当普通字符输出结果长得像RR-01-16不会报错但数据是错的。这种问题只能靠测试用例来兜底所以后面专门有一节讲测试。3.2 带 FM 修饰符的情况怎么处理Oracle 里的FM修饰符用于去掉结果中的前导空格和补零比如to_char(sysdate, FMMONTH DD)会把JANUARY 16变成JANUARY 16。MySQL 的DATE_FORMAT没有完全对应的概念好消息是%m、%d这类格式符天生不补前导零比如2025-1-6这跟 Oracle 默认输出2025-01-06不一样。所以我在函数里做了折中:如果格式串里出现FM忽略这个修饰符并对其结果做一次LTRIM去掉 MySQL 格式符可能产生的首部空格。说实话FM场景在真实业务里并不多见报表场景大多希望固定宽度而不是去掉前导零。如果你的报表确实需要补零效果建议在函数外层用LPAD,或者干脆直接写DATE_FORMAT(date_col, %Y-%m-%d),它天然就是两位月、两位日。3.3 传入字符串类型时要注意什么一个常见的坑是:业务 SQL 里对字符串字段直接调 to_char比如to_char(birthday, YYYY-MM-DD)而birthday实际是VARCHAR类型。MySQL 里函数参数声明为DATETIME传字符串会发生隐式转换绝大多数情况下能转成功但遇到2024-13-45这种不合法日期MySQL 会返回 NULL 并产生一条 warning不会直接报错。这其实是好事至少不会让整条查询崩溃。如果你希望函数更严格可以在函数里判断返回值发现 NULL 就主动抛异常:IF ret IS NULL THEN SIGNAL SQLSTATE 45000 SET MESSAGE_TEXT Invalid date value in to_char; END IF;但我的建议是日常实现的 to_char 不要加这个判断。因为这会改变函数在查询中的行为万一某条数据本身就脏整批查询会全部挂掉到时候排查起来更麻烦。保持 MySQL 原生的宽容风格让结果返回 NULL、业务侧做数据清洗是更稳妥的做法。4. to_date 函数实现:字符串转日期4.1 核心代码与调用示例to_date 的方向正好反过来把字符串按 Oracle 格式解析成日期时间。MySQL 里对应的原生函数是STR_TO_DATE实现思路同样是先做格式映射再调用STR_TO_DATE:DELIMITER $$ CREATE FUNCTION to_date(str VARCHAR(255), fmt VARCHAR(255)) RETURNS DATETIME DETERMINISTIC NO SQL BEGIN DECLARE mysql_fmt VARCHAR(255); IF fmt IS NULL OR fmt THEN SET mysql_fmt %Y-%m-%d %H:%i:%s; ELSE SET mysql_fmt oracle_format_to_mysql(fmt); END IF; RETURN STR_TO_DATE(str, mysql_fmt); END$$ DELIMITER ;实测用法:SELECT to_date(2025-01-16 14:33:21, YYYY-MM-DD HH24:MI:SS); -- 输出:2025-01-16 14:33:21 SELECT to_date(16-Jan-2025, DD-MON-YYYY); -- 输出:2025-01-16 SELECT to_date(2025/01/16, YYYY/MM/DD); -- 输出:2025-01-16有个细节值得注意:STR_TO_DATE的返回值是DATETIME如果原 SQL 里期望的是日期类型比如只要2025-01-16下游是DATE字段时写库会自动截断时间部分没问题。但如果下游是字符串比较就可能会带出00:00:00这种尾巴。所以我在实际项目中保留了一个额外逻辑:如果格式串里只含日期格式符、不含时间格式符调用STR_TO_DATE后再用DATE()包一层让返回值类型更干净。你可以根据自己项目的下游消费方式决定是否保留这段逻辑。4.2 容错处理:数据很脏时怎么办to_date 最怕的是业务数据不干净。比如字符串是2025-1-6,格式指定成YYYY-MM-DD,Oracle 里解析可能直接报错但 MySQL 的STR_TO_DATE对%m、%d格式符允许一位数所以2025-1-6能正常解析成2025-01-06这算是迁移时一个意外之喜。反过来如果字符串是2025-13-45,STR_TO_DATE返回 NULL只给 warning 不给 error查询不会中断结果变成 NULL 留给业务侧去排查。还有一类情况是只有时间没有日期比如to_date(14:33:21, HH24:MI:SS)。STR_TO_DATE遇到格式里只有时间格式符时日期部分会得到0000-00-00在 MySQL 严格模式下可能直接报错。为了避免这种不确定性我通常在函数里先判断格式串是否包含年份、月份、日期格式符如果这三者都不含就用CURDATE()把当天日期补进去再返回结果。这个判断逻辑看起来多余但在真实数据导入场景里帮了大忙免得每次都在导出数据时被零日期卡住。4.3 和原生 STR_TO_DATE 的兼容关系有一点要强调:to_date 的第二个参数映射完之后本质上就是 MySQL 格式串所以它和直接写STR_TO_DATE在行为上完全一致。迁移过程中如果有些 SQL 是后来新写的你可以不用自定义函数直接用原生写法结果没有差异。但自定义函数的意义在于:老系统里几百个存储过程、几十个报表 SQL 全部不用改只要在库里创建同名函数迁移后的行为就和 Oracle 保持大体一致。这也是我推荐做这个工具的根本原因。唯一要提醒的是MySQL 的自定义函数是库级别的不同库要各自创建一遍如果建在公共 schema 里又可能有权限问题。我的经验是每个业务库都执行一遍创建脚本最省心。5. 测试用例与日常踩坑记录5.1 用一组 SQL 做冒烟测试函数写完别急着上线先跑一组冒烟测试把常见格式和边界场景都覆盖到。下面是我自己项目里的测试脚本可以直接复制去跑:-- 基础日期格式 SELECT to_char(NOW(), YYYY-MM-DD) AS c1; SELECT to_char(NOW(), YYYY/MM/DD) AS c2; SELECT to_char(NOW(), MM-DD-YYYY) AS c3; SELECT to_char(NOW(), HH24:MI:SS) AS c4; SELECT to_char(NOW(), HH12:MI AM) AS c5; -- 带英文月份/星期 SELECT to_char(NOW(), MONTH DD, YYYY) AS c6; SELECT to_char(NOW(), DY, MON DD) AS c7; -- 含引号文本 SELECT to_char(NOW(), Today is DD) AS c8; -- to_date 解析 SELECT to_date(2025-01-16, YYYY-MM-DD) AS d1; SELECT to_date(2025/01/16 14:33:21, YYYY/MM/DD HH24:MI:SS) AS d2; SELECT to_date(16-Jan-2025, DD-MON-YYYY) AS d3; -- 错误场景:期望返回 NULL不期望报错 SELECT to_date(2025-13-45, YYYY-MM-DD) AS e1; SELECT to_char(NULL, YYYY-MM-DD) AS e2;这些用例里值得注意的:e1如果返回 NULL说明容错逻辑正常e2也一样NULL 传入 to_char 应该返回 NULL而不是报错。如果你的函数在 NULL 输入时报错需要检查参数声明是否为DETERMINISTIC NO SQL并在函数入口加一段IF dt IS NULL THEN RETURN NULL; END IF;。5.2 格式映射的几个高发坑第一个坑是HH和HH24的判断顺序。映射器如果先匹配HH那么HH24会被解析成%h24最终输出像1424这种乱码。必须像前面代码骨架那样先判断三位HH24再判断两位HH。第二个坑是MONTH和MON的优先级因为MONTH以MON开头先匹配MON后残留TH两个字面字符输出像JanTH。解决办法同样是多字符优先匹配。第三个坑来自AM和PM的处理。Oracle 里格式串可以同时出现HH24和AM但逻辑上是矛盾的。MySQL 的DATE_FORMAT会同时生效结果出现23:00 PM这种不伦不类的值。这不是函数本身的问题而是格式串写得就不合理建议迁移时直接让业务方改成HH24:MI,不要带 AM。第四个坑是双引号文本。Oracle 的格式串用双引号包普通文本映射器里要正确识别引号并保留内容否则Today is 里的空格和大小写会丢。这个逻辑我在前面的骨架代码里已经用in_quote变量处理了但如果你是自己从零写很容易漏掉这个细节。5.3 常见问题速查表我把实际运行中遇到的典型问题整理成了下面这个表方便排错时快速定位。现象可能原因排查与修复FUNCTION does not exist函数没创建成功或建在别的库确认当前库重新执行创建脚本输出HH24原样文本映射器里HH先于HH24匹配调整判断逻辑多字符格式符优先输出JanTHMONTH被当成MON解析调整MONTH判断优先于MON日期返回 NULL 带 warning输入字符串非法或格式不匹配用SHOW WARNINGS查看细节检查数据质量to_char 结果末尾带空格%b、%M等英文月份受语言包影响设置lc_time_names或统一会话语言创建函数报权限错误当前用户没有CREATE ROUTINE权限授权CREATE ROUTINE, ALTER ROUTINE查询很慢、全表扫描WHERE 条件里对索引列调用 to_char改写 SQL保证索引列不被函数包裹6. 性能考量与更优的替代做法6.1 存储函数在查询中的真实开销自定义存储函数如果只用在 SELECT 输出列比如SELECT to_char(create_time, YYYY-MM-DD) FROM t,性能和原生DATE_FORMAT差距不大因为格式串短、执行次数有限。但一旦用在 WHERE 条件里比如WHERE to_char(create_time, YYYY-MM-DD) 2025-01-16问题就来了:MySQL 对索引列套了函数之后无法走索引范围扫描只能全表扫数据量一大基本就废。我之前在一张千万级订单表上做过对比用to_char(create_time, YYYY-MM-DD)过滤跑了 3 秒多改成create_time 2025-01-16 00:00:00 AND create_time 2025-01-17 00:00:00之后走了索引耗时降到几十毫秒。所以自定义函数可以做但别滥用尤其别把它当成一种可以随意套在索引列上的工具。所有从 Oracle 迁过来的 SQL只要涉及函数包裹索引列都要在迁移清单里重点审查。6.2 索引列上要过滤日期应该怎么写如果业务就是要按天过滤并且字段是datetime类型最佳写法是给日期时间加一个区间条件:WHERE create_time 2025-01-16 00:00:00 AND create_time 2025-01-17 00:00:00。这个写法可以让优化器直接对索引列做范围扫描。相比之下WHERE DATE(create_time) 2025-01-16在 MySQL 5.7 及以下版本是不走索引的8.0 对一部分内置函数有优化但对用户自定义函数没有同样的优化能力。如果实在没法改 SQL又希望查询快可以考虑加一个生成列把格式化后的字符串存下来并建索引。这个做法适合查询模式固定的报表场景代价是写入时多占一点存储空间换来查询走索引的效率提升。生成列对函数有要求函数声明必须带DETERMINISTIC这就引出了下面的问题。6.3 函数声明为什么要加 DETERMINISTIC创建存储函数时DETERMINISTIC声明的含义是:对同样的输入函数一定返回同样的结果。MySQL 拿到这个声明后知道可以放心地对函数做表达式优化甚至允许它在生成列等场景中使用。如果你不加MySQL 会默认函数有副作用在特定场景下直接限制使用。我见过不少人在创建函数时对声明随意处理结果后面想用函数定义生成列时才发现报错。此时要回头修改函数定义如果函数已被其他对象引用还会遇到权限和 metadata 锁的问题。所以创建时就规范起来to_char 和 to_date 都加上DETERMINISTIC和NO SQL一劳永逸。这个习惯在写任何自定义函数时都值得保持。6.4 有没有比自定义函数更彻底的办法如果你不是做 Oracle 迁移而是新系统从零开发我的建议是直接用 MySQL 原生函数不碰自定义函数。DATE_FORMAT和STR_TO_DATE已经覆盖了绝大多数场景原生函数执行效率更高还能和优化器配合做更多执行计划优化。此外很多 ORM 框架本身有日期格式化的方言层比如 MyBatis 可以在 SQL 中用框架的格式化标签Hibernate 有专门的 dialect 配置这些都比在数据库里补函数更优雅。自定义函数真正适用的场景是:老系统代码量巨大、短期内无法逐条改造 SQL、DBA 也希望尽量少动应用代码。这种时候造两个 to_char/to_date 轮子确实是性价比最高的方案。我在实际项目中就是把创建脚本放进统一的迁移工具目录里每次新库部署时批量执行一遍几十个库一跑应用的 SQL 就都能正常运行了。写到这儿核心内容基本讲完了。最后再分享一个小技巧:函数上线后我习惯在测试环境跑一遍全量回归 SQL 对比表把原 Oracle 库的输出和 MySQL 自定义函数的输出逐条比对重点看月份名、星期名和 AM/PM 的差异因为这三类最容易受系统语言影响。只要这三类对得上业务 SQL 迁移基本就稳了。这个经验我用了好几年每次做数据库迁移都能省下不少排查时间。