MySQL字符串日期转换全解析：STR_TO_DATE与DATE_FORMAT实战

1. 字符串日期转换的常见场景与核心思路

项目里凡是跟报表、统计、接口对接打过交道的人，基本都遇到过这么个情况：业务库里的日期字段好端端的是 DATE 或 DATETIME 类型，结果导出来的数据或者第三方传来的数据，日期全变成了字符串。有的长这样2023-01-15，有的长这样2023/01/15，还有带时间的20230115120000，更离谱的是存了一串纯时间戳1673767200。这时候你要是直接用字符串去比较、排序、聚合，轻则结果不对，重则直接给你报错，卡在那一大堆脏数据上半天动不了。

这篇博客就专门说清楚 MySQL 里字符串和日期类型互相转换这件事。先讲清楚什么时候必须转、什么时候其实不用转，再把 STR_TO_DATE、DATE_FORMAT、CAST/CONVERT 这几个核心函数的区别和用法一个个拆开，配合真实的业务场景给出可以直接抄的 SQL。最后是我踩过的一些坑——比如隐式类型转换导致索引失效、严格模式下非法日期直接报错、时间戳精度丢失这些问题，统统整理成速查表和排查思路。

适合谁看？刚接触 MySQL 的开发、做数据清洗和报表的同学，还有被日期格式折磨过好几次的老手，都能在里面找到用得上的东西。

多说一句，这类问题看起来就是几个函数的事，但实际生产环境里，格式千奇百怪，同样的写法在不同 MySQL 版本下表现可能还不一样，所以这篇文章不只是列函数，更多是给你一套判断和分析的思路。

1.1 为什么日期会变成字符串：从数据来源说起

先别急着学函数，弄清楚日期是怎么变成字符串的，后面很多“疑难杂症”就能自己推断出原因了。

常见来源有这么几类：

• 外部数据文件导入。Excel 导出的 CSV、同行发来的 TXT，日期列往往不是标准格式。比如中文习惯写成2023年1月15日，美国同事可能给你01/15/2023，这些进到 MySQL 里只能当字符串存。

• 接口对接。别的系统通过 HTTP 接口给你传数据，JSON 里就没有日期类型，只能传字符串。人家用什么格式取决于对方后端怎么序列化的，最常见的两种是yyyy-MM-dd HH:mm:ss和纯数字时间戳。

• 历史遗留数据。早期建表的人图省事，日期列直接用的 VARCHAR，存了几年的数据，格式五花八门，有的带时分秒，有的不带，有的中间用的斜杠分隔。

• 手工处理的中间表。有人用 Excel 处理完数据再导入，Excel 单元格格式一变，日期就变成了一串数字（这个在 Excel 里叫序列值，导出来就更乱了）。

说白了，问题不是“要不要转换”，而是“拿到什么格式都得能接得住”。所以核心思路其实只有两条：入库之前尽量把脏字符串统一转成 DATE/DATETIME 类型；万一源头动不了，那就靠 MySQL 的转换函数在查询层兜底。

这里想强调一个大家常有的误解：不是所有字符串日期都需要转成 DATE 类型才能用。如果只是按天分组统计，字符串2023-01-15其实可以直接 GROUP BY，因为这种格式字典序就是时间序。但如果要做日期加减、算两个日期间隔、按月份分组，字符串就不行了，必须先转成真正的日期类型。判断标准就一句话：你的下一步操作是不是需要日期“语义”。

1.2 核心转换函数对照：用哪个，看场景

MySQL 里做字符串和日期互转的函数不少，但真正核心的就那么几个。我用一张表说清楚每个函数干什么活、适合什么场景。

这些函数看着简单，但真正难的不是函数本身，而是“怎么判断手上的数据到底该用哪个”。我的习惯是：先看格式是否规整、是否唯一，再决定用转换还是截取。

比如收到的数据全是2023-01-15 10:30:00这种标准格式，那我直接CAST(col AS DATE)就行，甚至直接 DATE 函数截取日期部分都够用。但如果格式不统一，同一个字段里既有2023-01-15又有2023/01/15，那 CAST 肯定罢工，必须 STR_TO_DATE 配合多种格式去解析。

提示：STR_TO_DATE 是按格式严格解析的，格式符不匹配就返回 NULL。有些人在网上看到 STR_TO_DATE 能自动识别格式，那是误传，它真没这么聪明。

2. 核心函数拆解：STR_TO_DATE 与 DATE_FORMAT 全解析

这两个函数是字符串和日期转换里出镜率最高的，一个负责进——字符串转日期，一个负责出——日期转字符串。把它们彻底搞明白，日常工作基本就够用了。

2.1 STR_TO_DATE 的格式符用法：一点点都不能差

STR_TO_DATE 的语法很简单：

STR_TO_DATE('2023-01-15 10:30:45', '%Y-%m-%d %H:%i:%s')

返回结果是一个 DATETIME 类型的值。但关键在于第二个参数——格式符，它决定了 MySQL 怎么把你给它的字符串拆开解析。

常用的格式符就这些，建议直接收藏：

举个几个实际例子：

-- 斜杠隔开的日期 SELECT STR_TO_DATE('2023/01/15', '%Y/%m/%d'); -- 结果：2023-01-15 -- 带时分秒 SELECT STR_TO_DATE('2023-01-15 10:30:45', '%Y-%m-%d %H:%i:%s'); -- 结果：2023-01-15 10:30:45 -- 中文格式 SELECT STR_TO_DATE('2023年01月15日', '%Y年%m月%d日'); -- 结果：2023-01-15 -- 带 AM/PM 的时间 SELECT STR_TO_DATE('2023-01-15 10:30:45 PM', '%Y-%m-%d %h:%i:%s %p'); -- 结果：2023-01-15 22:30:45

这里有个容易忽略的细节：字符串里的分隔符和格式符里的分隔符必须一致。你用%Y-%m-%d去解析2023/01/15，结果是 NULL。因为 MySQL 解析时是拿着格式串去“套”你的输入串，-对不上/，直接失败。

还有个比较坑的场景：有时候输入串里后面带了多余的空格或字符。比如2023-01-15 abc，你用'%Y-%m-%d'去解析，会返回 NULL 吗？实测告诉我不一定，MySQL 对尾部多余字符有容忍度，但结果不稳定，不同版本表现不一样。安全起见，先TRIM再转。

2.2 DATE_FORMAT 输出实战：从日期到任意展示格式

需要把日期输出成指定格式的字符串，用 DATE_FORMAT，这是 STR_TO_DATE 的逆操作。语法类似：

DATE_FORMAT(NOW(), '%Y年%m月%d日 %H:%i:%s') -- 结果：2023年01月15日 10:30:45

实际业务里最常见的需求有这么几个：

报表展示维度，要“2023年1月”这种格式：

SELECT DATE_FORMAT(order_date, '%Y年%m月') AS month_label, SUM(amount) FROM orders GROUP BY DATE_FORMAT(order_date, '%Y年%m月');

接口返回值，要求yyyy-MM-dd格式的字符串：

SELECT DATE_FORMAT(create_time, '%Y-%m-%d') AS create_date FROM users;

日期转成纯数字串，比如20230115103045：

SELECT DATE_FORMAT(NOW(), '%Y%m%d%H%i%s'); -- 结果：20230115103045

这个在生成流水号、文件命名的时候特别好用。

用 DATE_FORMAT 有个性能上的注意点：如果在 WHERE 条件里对日期字段用 DATE_FORMAT 再和字符串比较，索引会失效。比如：

-- 不推荐，索引失效 WHERE DATE_FORMAT(order_date, '%Y-%m-%d') = '2023-01-15' -- 推荐，走索引 WHERE order_date >= '2023-01-15 00:00:00' AND order_date < '2023-01-16 00:00:00'

这个问题后面会专门展开，这里先记个印象。

2.3 格式匹配背后的“为什么”：读懂解析规则

很多人对格式符记不住，其实只要理解一件事：MySQL 解析字符串日期时，跟你手写解析一样，是逐个字符匹配的。%Y会吃进去 4 个数字字符，%m吃 2 个，%d吃 2 个，剩下的分隔符必须原样匹配。

理解了这一点，下面这些现象你就不觉得奇怪了：

• %Y-%m-%d无法解析2023-1-15，因为%m期望两位数字，遇到1-15时只看到一位1，后面跟的是-，格式符里要求两位就卡住了。
• 那遇到2023-1-15怎么办？用%Y-%c-%e，这个格式支持不带前导零的月和日。
• 如果既不保证带不带前导零，最稳妥的办法是先数据清洗，把格式统一，而不是靠格式符去适配所有情况。

再有就是日期合理性校验。STR_TO_DATE 不只是把字符串拆开装进日期类型，它还会校验日期本身合不合理。比如：

SELECT STR_TO_DATE('2023-02-30', '%Y-%m-%d'); -- 结果：NULL（2月没有30号）

如果你希望宽松处理，允许这类“不存在的日期”入库，后面的第 4 节会讲到 MySQL 的 SQL 模式设置。这里先记住：STR_TO_DATE 自带校验逻辑，不是所有格式对了就能转成功。

3. 实操：典型业务场景下的转换示例

光讲语法没意思，直接上几个我实际做过的场景。每个场景我都写了完整的 SQL 和解释，按你的表结构调整字段名就能直接用。

3.1 场景一：外部文件导入时日期字段清洗

假设我们要导入一份 CSV，表结构大致这样：

CREATE TABLE user_order ( id INT PRIMARY KEY AUTO_INCREMENT, user_name VARCHAR(50), order_date DATETIME, amount DECIMAL(10,2) );

但 CSV 里的 order_date 字段格式不统一，有这几种：

• 2023/01/15
• 2023-01-15
• 2023年1月15日
• 2023-01-15 10:30:45

这个时候不能指望一行 SQL 搞定所有格式。我的做法是分两步。

第一步：导入到一个临时表，日期列先用 VARCHAR 存着。

CREATE TABLE temp_import ( user_name VARCHAR(50), order_date_str VARCHAR(30), amount DECIMAL(10,2) ); -- 使用 LOAD DATA 或手动 INSERT 导入原始数据

第二步：清洗转写到正式表。用 REPLACE 函数把“年月日”和“/”统一替换成-，然后用 STR_TO_DATE 解析。但这里有个隐藏问题：2023年1月15日直接替换“年”为“-”得到2023-1月15日，还是不合法。得把“月”“日”也处理掉，同时注意月和日不带前导零的问题。

我实际用的写法是这样：

INSERT INTO user_order (user_name, order_date, amount) SELECT user_name, STR_TO_DATE( REPLACE(REPLACE(REPLACE(order_date_str, '年', '-'), '月', '-'), '日', ''), -- 但这里格式要按实际值来 CASE WHEN order_date_str LIKE '%年%' THEN '%Y-%c-%e' WHEN order_date_str LIKE '%/%' THEN '%Y/%m/%d' ELSE '%Y-%m-%d %H:%i:%s' END ) AS order_date, amount FROM temp_import;

这里有个经验：宁可多写几个 CASE 分支，也不要试图用一个万能格式符去适配所有情况。因为 STR_TO_DATE 是严格匹配的，一旦某一行解析失败返回 NULL，整条数据就没了。在数据清洗环节，我的习惯是先跑一个诊断 SQL，看看各格式各占多少行：

SELECT CASE WHEN order_date_str LIKE '%年%' THEN 'chinese' WHEN order_date_str LIKE '%/%' THEN 'slash' WHEN order_date_str LIKE '%-%' THEN 'dash' ELSE 'other' END AS date_style, COUNT(*) AS cnt FROM temp_import GROUP BY date_style;

这个查询能让你心里有数，再做替换就有的放矢了。

注意：做数据清洗时，一定要先在临时表里验证结果，确认 NULL 比例可接受，再写正式表。我见过有人直接在正式表上跑 UPDATE，出问题才发现几万行数据被置空了。

3.2 场景二：字符串日期与时间戳互转

接口之间传日期，最常见的是传秒级时间戳。比如对方给你一个字符串1673767200，你要落库成 DATETIME。反过来，查询接口要给前端返回时间戳。这一来一回，用的就是 FROM_UNIXTIME 和 UNIX_TIMESTAMP。

-- 字符串时间戳转日期，注意先转数字再转 SELECT FROM_UNIXTIME(CAST('1673767200' AS UNSIGNED)); -- 结果：2023-01-15 10:30:00 -- 日期时间转时间戳 SELECT UNIX_TIMESTAMP('2023-01-15 10:30:00'); -- 结果：1673767200

第一个例子里为什么先CAST('1673767200' AS UNSIGNED)？因为 FROM_UNIXTIME 的入参是数字，直接传字符串其实也能隐式转，但写清楚类型转换能避免脏数据。比如字符串里有个空格、或者传成'1673767200XXX'，报错的时候不容易排查。

这里有个大坑：2038年问题。MySQL 里 TIMESTAMP 类型的范围到 2038-01-19 就到底了，因为底层是 32 位整数存秒数。如果你存储的时间戳超过这个范围，会直接报错。DATETIME 类型没这个问题，范围大得多。所以我的建议是：能选 DATETIME 就选 DATETIME，尽量别用 TIMESTAMP 存早晚会超过 2038 年的业务时间。

还有个毫秒精度的问题。前端传的时间戳经常是 13 位的毫秒时间戳，比如1673767200000。你要直接 FROM_UNIXTIME，它会当作 1673767200000 秒来算，结果直接超出范围报错。处理方式：

-- 毫秒时间戳先除以 1000 转成秒，再转日期 SELECT FROM_UNIXTIME(CAST('1673767200000' AS UNSIGNED) / 1000); -- 结果：2023-01-15 10:30:00 -- 如果希望保留毫秒 SELECT FROM_UNIXTIME(CAST('1673767200000' AS UNSIGNED) / 1000); SELECT CAST(FROM_UNIXTIME(CAST('1673767200000' AS UNSIGNED) / 1000) AS DATETIME(3));

MySQL 8.0 里 DATETIME(3) 支持存储毫秒，但 FROM_UNIXTIME 默认返回秒级。想要毫秒，可以这样：

SELECT DATE_FORMAT(FROM_UNIXTIME(1673767200.123), '%Y-%m-%d %H:%i:%s.%f');

这里传带小数的秒，%f会输出微秒部分。总之，时间戳互转要时刻关注“单位”，秒和毫秒差了整整一千倍，搞错了数据全乱。

3.3 场景三：按日期分组统计时的转换必要性

报表开发里最常干的事就是按月、按周、按天统计。如果日期列是 DATETIME 类型，用 DATE_FORMAT 格式化再做分组是最直接的：

-- 按月统计销售额 SELECT DATE_FORMAT(order_date, '%Y-%m') AS month, SUM(amount) AS total_amount FROM orders WHERE order_date >= '2023-01-01' AND order_date < '2024-01-01' GROUP BY DATE_FORMAT(order_date, '%Y-%m') ORDER BY month;

如果日期列本身是字符串，比如之前老系统遗留下来的 VARCHAR 字段，你就得先把字符串转成日期再格式化。比如字符串是2023/01/15这种：

SELECT DATE_FORMAT(STR_TO_DATE(order_date_str, '%Y/%m/%d'), '%Y-%m') AS month, SUM(amount) AS total_amount FROM orders_old GROUP BY DATE_FORMAT(STR_TO_DATE(order_date_str, '%Y/%m/%d'), '%Y-%m') ORDER BY month;

这里有个细节：GROUP BY 后面可以用字段别名month，MySQL 支持这个写法，比重复写一大串函数更清爽。但 ORDER BY 也可以用month，没问题。

还有一种需求：要按周统计。MySQL 的 YEARWEEK 函数帮忙：

SELECT YEARWEEK(order_date, 1) AS week_key, MIN(DATE(order_date)) AS week_start, SUM(amount) AS total_amount FROM orders GROUP BY week_key ORDER BY week_key;

YEARWEEK的第二个参数是模式，1 表示周一作为一周开始。这个数字很关键，不同国家习惯不一样，做统计前先确认业务方的“周”怎么定义。我之前就遇到过，业务说“按周统计”，结果他们说的是自然周（周一到周日），而我用了默认的 YEARWEEK 参数，第一个星期老对不上。后来统一用模式 1（周一为一周起点）才解决。做这一类分组时，先花一分钟确认周定义，能省不少返工。

4. 常见陷阱与性能问题排查实录

字符串日期转换在语法上不难，难的是踩坑。我把这些年遇到的典型问题整理成一块，按“现象 → 原因 → 解法”的方式写清楚，可以直接当排查手册用。

4.1 隐式类型转换：索引失效的隐形杀手

先看一个场景。订单表比较庞大，order_date 是 DATETIME 类型，上面建了索引。你想查某一天的单量，写了这样的 SQL：

SELECT COUNT(*) FROM orders WHERE order_date = '2023-01-15';

这条 SQL 有没有走索引？答案是：取决于 MySQL 的版本和数据类型隐式转换规则。order_date是 DATETIME，和字符串'2023-01-15'比较时，MySQL 会把字符串隐式转成 DATETIME，如果版本支持，其实是可以走索引的。

但反过来就有问题了：

SELECT COUNT(*) FROM orders WHERE DATE(order_date) = '2023-01-15';

DATE(order_date)在每一行上先做函数计算，这一下就把索引搞失效了。数据量小的时候无所谓，到了百万级，差别是几十毫秒和几秒的差距。

还有个更隐蔽的：如果字段本身是 VARCHAR 存着日期，你拿它和日期字符串比较：

SELECT COUNT(*) FROM orders WHERE order_date_str = '2023-01-15';

这看起来没问题。但如果列是 VARCHAR，而另一端是 DATETIME 类型，那么 MySQL 会把列里的每个字符串转成 DATETIME 再比较，同样没法走索引。所以排查 SQL 慢的时候，先看 EXPLAIN，type 列如果出现 ALL，十有八九是隐式转换惹的祸。

EXPLAIN SELECT * FROM orders WHERE order_date_str = '2023-01-15';

我的建议很简单：字符串日期字段，要么直接落库成 DATE/DATETIME 类型，要么查询时让两边都保持字符串。别在查询条件里对索引列套函数，也别让两边类型不一致。

4.2 严格模式与非法日期：报错还是吃数据

MySQL 的 SQL 模式里头有个严格模式，5.7 之后默认开启。在严格模式下，插入非法日期时语句会直接报错。比如：

INSERT INTO orders (order_date) VALUES ('2023-02-30'); -- ERROR 1292 (22007): Incorrect date value: '2023-02-30' for column 'order_date'

原本在非严格模式下，这个日期会被置成0000-00-00，然后带病入库。开启严格模式后，MySQL 直接拒绝，这也是好事。但如果你在做数据导入，一行脏数据导致整个批次失败，处理起来就麻烦了。

排查时先看当前模式：

SELECT @@GLOBAL.sql_mode; SELECT @@SESSION.sql_mode;

如果是数据清洗场景，需要临时放宽限制该怎么办？有几个方向：

• 用ALLOW_INVALID_DATES模式，它允许2023-02-30这种“不存在但格式正确”的日期入库，但如果日期连格式都是错的（比如2023-13-45），还是会报错。

  注意：这个模式在 MySQL 8.0 里对 DATETIME 字段其实作用有限，而且放宽的代价是后续查询这些日期时可能出现莫名其妙的排序和比较结果，我的建议是只在临时表清洗时用，正式表别开这种口子。

• 用字符串中转，前面场景一的做法就属于这一类。先接收脏数据，清洗校验后再进入正式表。

• 清洗阶段用“先判断再转换”的方式，比如：

SELECT order_date_str, CASE WHEN order_date_str REGEXP '^[0-9]{4}-[0-9]{2}-[0-9]{2}' THEN STR_TO_DATE(order_date_str, '%Y-%m-%d') ELSE NULL END AS parsed_date FROM temp_import;

用了 REGEXP 先做格式校验，能解析的才转，解析不了的留 NULL 再人工看。这个手法在数据量大的时候特别实用，能把“异常数据单独揪出来处理”，而不是整个导入流程卡死。

提示：STR_TO_DATE 解析失败时返回 NULL，不是报错。所以你没做校验就转，结果就是一堆 NULL 悄悄入库。NULL 可比报错难发现多了，报错至少有提示，NULL 直接让你后面 GROUP BY、SUM 全出问题。

4.3 时区、精度和其他稀奇古怪的问题

时区问题。如果 MySQL 连接串里没设置时区，而服务器默认时区和你业务时区不一致，查询结果显示的时间可能差 8 个小时。比如：

SELECT NOW(); -- 服务器是 UTC 时区和中国时差 8 小时，结果比北京时间少 8 小时

排查时先看：

SELECT @@global.time_zone, @@session.time_zone;

不过时区问题跟字符串转换关系不大，更多发生在“时间戳转日期”的场景里。FROM_UNIXTIME 返回的时间是按当前时区换算的，如果你的应用以为存的是北京时间，而 MySQL 时区在 UTC，结果就是所有时间整体偏移。处理方式：在连接参数里指定connectionTimeZone=LOCAL（不同驱动写法不一样），或者干脆业务上统一约定用 UTC 存储、展示时再换算。我个人更推荐后者——存储层统一时区，展示层转换，这样可以避免不同客户端连接时区不一致导致的混乱。

格式符大小写问题。%Y和%y是年份位数差异，这个比较容易记混。但%i表示分钟，不是%M（月份全名），这种细节真有人踩过。我见过最离谱的是有人把分钟写成%m，结果月份当分钟用了，统计数据直接没法看。所以写格式串的时候，不要想当然，不确定就翻文档。

字符串日期里的空格和不可见字符。这是最烦的。肉眼看着一样，查询就是匹配不上。处理方法：

-- 去除首尾空格 SELECT TRIM(order_date_str) FROM temp_import; -- 查找包含不可见字符的数据 SELECT * FROM temp_import WHERE HEX(order_date_str) LIKE '%00%';

HEX 出来看到类似A0（不换行空格）这种你就明白了。这个问题没法靠函数一次解决，最好在数据源头就避免，实在不行就在清洗时把 TRIM 写成固定流程。

我把上面的坑整理成一个速查表：

4.4 从实践经验里总结出的几个操作习惯

最后分享一个我平时处理字符串日期转换的固定流程，不一定适合所有人，但对于“来路不明”的数据，这套流程能帮你少踩不少坑。

第一步，先在临时表里“看数据”。不看数据就写转换函数，那基本靠猜。我会先跑一条 SELECT，把要处理的字段全部 DISTINCT 出来，看看格式到底有多少种。比如字符串日期字段，直接看 DISTINCT LEFT(col, 7) 能看出月份格式是否统一。

第二步，“小样本验证”。拿三五条代表性的数据，用 STR_TO_DATE 试转一下，确认 NULL 比例。这一步不要在生产表上直接做，甚至不要在临时表全量上做，先用 LIMIT 100 跑一下。

第三步，正式清洗时“分批处理”。不要 UPDATE 全表一把梭，按主键范围或者日期范围分批，每批跑完看影响行数。

第四步，“校验结果”。清洗完之后拿计数校验：原始条数，转换成功条数，NULL 条数，三者对不上就说明有数据丢了。这一步说得容易，做起来也简单，就是几条 COUNT 语句拼一起的事，但真能发现大问题。

说回开头那个观点：字符串日期转换这个事，本质上不是“函数不会用”，而是“数据太乱”。所以我现在写这类转换逻辑，第一反应从来不是去搜哪个函数，而是先把数据结构看明白，再落地到具体的解析方案。

我自己踩了几次坑之后的体会是：格式越乱，越要分步清洗，不要指望一个 SQL 解决所有问题；同一个逻辑里 CASE 分支多写几个没坏处，怕的是解析失败返回 NULL 却没人发现。如果你也正在被一堆字符串日期折腾，建议先按这个思路把数据摸一遍底，很多问题其实没那么复杂。