在MySQL中实现Oracle的to_char与to_date自定义函数

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:SS，MySQL 里对应的是%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 的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_LANGUAGE=AMERICAN'),但为了保持轻量，我通常只实现两个参数，语言类参数直接忽略。下面给出一个能跑的基础版本:

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 常见问题速查表

我把实际运行中遇到的典型问题整理成了下面这个表，方便排错时快速定位。

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 迁移基本就稳了。这个经验我用了好几年，每次做数据库迁移都能省下不少排查时间。