做后端开发的人多多少少都在日期转换上交过学费。尤其是MySQL字符串和日期之间看似只有一层窗户纸但捅破它的方式错了轻则查出错误数据重则直接把线上接口拖垮。这篇文章我从MySQL字符串日期转换这个老话题出发整理了我在日常开发、数仓清洗、老系统迁移中实际用到的12个转换场景把常用函数、格式符、边界情况和防坑手段一次说清楚。不管你是刚入行的开发还是要接手一堆脏数据的分析师照着抄作业基本都能跑通。1. 字符串日期为何成不了“日期”类型边界与隐式转换的真相1.1 MySQL日期类型与VARCHAR的根本区别先说一个最基本的点MySQL里DATE、DATETIME、TIMESTAMP不是“长得像YYYY-MM-DD的字符串”而是一种有内部编码的二进制类型。你往表里插入一个日期字符串时MySQL是在做类型转换不是简单地存字符串。CREATE TABLE temp_orders ( id INT PRIMARY KEY, order_time DATETIME, remark VARCHAR(100) ); INSERT INTO temp_orders (id, order_time, remark) VALUES (1, 2024-05-15 18:30:45, 正常插入), (2, 2024/05/15 18:30:45, 斜杠也能插入);第二条记录能插入成功是因为MySQL的DATETIME类型在非严格模式下会自动解析常见的日期分隔符。但这不代表你可以依赖这种松散行为。我在实际项目中见过不少表order_time存的是VARCHAR里面的数据一会儿是2024-05-15一会儿是2024/5/15还有个别记录是20240515。这种表一旦要做范围查询或按月份分组问题就全部暴露出来了。1.2 隐式转换带来的隐蔽后果隐式转换最坑的地方在于它不出错但结果不对。比如你在WHERE里直接拿VARCHAR日期和字符串常量比较SELECT * FROM temp_orders WHERE order_date_str 2024-05-01;如果order_date_str是2024/05/15这种格式字符串比较的规则是按字符逐个大小比较2024/05/15和2024-05-01在前8个字符2024/05/和2024-05-上就分出了高低斜杠的ASCII码是47短横线是45所以2024/05/15 2024-05-01永远为真就算它实际日期是2024年1月1日也被算进去。这种错法极其隐蔽不加日志很难发现。所以处理日期字符串的第一步不是急着转换而是先认定一个事实存进各种格式字符串的表必须显式转换后再当日期用永远不要赌隐式转换会做正确的事。2. STR_TO_DATE核心转换函数说明书与12个实战场景2.1 格式说明符速查表STR_TO_DATE是MySQL专门用来把字符串按指定格式解析成日期的函数。它的语法是STR_TO_DATE(str, format)关键是format参数。这里我把常用格式符整理成一张表用的时候直接对照格式符含义示例输入解析结果%Y四位数年份20242024%y两位数年份242024%m两位数月份055%c一位或两位数月份55%d两位数日1515%e一位或两位数日55%H24小时制小时1818%h12小时制小时066%i分钟3030%s秒4545%f微秒123456123456%pAM或PMPMPM%T等同于%H:%i:%s18:30:4518:30:45%W星期几全名Wednesday—%M月份全名May5%x作为周的年份20242024%v周数2020记住一个原则STR_TO_DATE的格式符要和字符串本身完全对应。多写一个空格、少写一个前导零结果都可能变成NULL。2.2 场景1到场景6常见格式的标准解法下面这12个场景前6个处理日常最常见的格式。场景1标准日期2024-05-15SELECT STR_TO_DATE(2024-05-15, %Y-%m-%d); -- 2024-05-15场景2日期加时间2024-05-15 18:30:45SELECT STR_TO_DATE(2024-05-15 18:30:45, %Y-%m-%d %H:%i:%s); -- 2024-05-15 18:30:45场景3美式斜杠日期05/15/2024SELECT STR_TO_DATE(05/15/2024, %m/%d/%Y); -- 2024-05-15注意这里是月在前别和中国的日/月/年搞混。如果是15/05/2024这种日在前格式要写成%d/%m/%Y。场景4点分日期2024.05.15SELECT STR_TO_DATE(2024.05.15, %Y.%m.%d); -- 2024-05-15场景5中文日期2024年5月15日SELECT STR_TO_DATE(2024年5月15日, %Y年%c月%d日); -- 2024-05-15中文日期解析时最好用%c而不是%m因为月份可能不带前导零%m在某些版本下对5的兼容性不如%c稳定。场景6只有年和月2024-05SELECT STR_TO_DATE(2024-05, %Y-%m); -- 2024-05-01只提供年和月时MySQL会把日部分自动补成1号。这个行为在做月份维度统计时很有用。2.3 场景7到场景12容易踩坑的进阶格式场景712小时制时间2024-05-15 06:30:45 PMSELECT STR_TO_DATE(2024-05-15 06:30:45 PM, %Y-%m-%d %h:%i:%s %p); -- 2024-05-15 18:30:45这里必须用%h12小时制配合%pAM/PM如果用成%H解析结果会直接变NULL。场景8带毫秒或微秒的时间2024-05-15 18:30:45.123456SELECT STR_TO_DATE(2024-05-15 18:30:45.123456, %Y-%m-%d %H:%i:%s.%f); -- 2024-05-15 18:30:45.123456DATETIME(6)类型才能完整保存微秒。如果目标字段是普通DATETIME毫秒部分会被舍弃。场景9时间戳字符串1715760000SELECT FROM_UNIXTIME(1715760000); -- 2024-05-15 18:30:00严格说FROM_UNIXTIME不是STR_TO_DATE的活儿但数据进来时往往是一串数字文本处理完再配合DATE()函数就能得到日期。场景10两位数年份24-05-15SELECT STR_TO_DATE(24-05-15, %y-%m-%d); -- 2024-05-15这是最需要留神的场景。MySQL对%y的解析规则是00到69映射到2000到206970到99映射到1970到1999。比如69-05-15会解析成2069-05-15不是1969。老系统里如果存在这种年份数据转换前必须确认业务含义。场景11ISO周日期2024-W20-3SELECT STR_TO_DATE(2024-W20-3, %x-W%v-%w); -- 2024-05-15%x是“用于周的年份”%v是周数%w表示星期几0是星期天1是星期一。这个格式符组合比较冷门但报表系统从海外数据源拿数据时经常遇到。场景12英文月份全名May 15, 2024SELECT STR_TO_DATE(May 15, 2024, %M %d, %Y); -- 2024-05-15%M能解析January到December也兼容Jan吗不兼容。%b才是三个字母的缩写月份。英文月份解析对大小写是敏感的may会返回NULL必须是May。这12个场景覆盖了我工作中遇到的九成输入格式。遇到新格式时我的习惯是先拿STR_TO_DATE跑一条SELECT确认结果再写进生产SQL。3. 不需要STR_TO_DATE的场景CAST、CONVERT与DATE()的正确分工3.1 CAST和CONVERT的边界在哪里CAST和CONVERT也能完成字符串到日期的转换但格式支持非常有限基本只认ISO标准格式SELECT CAST(2024-05-15 AS DATE); -- 2024-05-15 SELECT CAST(2024-05-15 18:30:45 AS DATETIME); -- 2024-05-15 18:30:45 SELECT CONVERT(2024-05-15, DATE); -- 2024-05-15超过这个范围的比如2024/05/15在严格模式下可能报错在非严格模式下部分版本能解析部分版本直接返回NULL。这种不确定性让我在项目里对CAST的定位很明确只处理已经洗干净的标准格式字符串其他一律交给STR_TO_DATE。3.2 分清“转换”和“提取”是两件事DATE()函数经常被人拿来当转换工具用但它真正的用途是从DATETIME或TIMESTAMP里提取日期部分SELECT DATE(2024-05-15 18:30:45); -- 2024-05-15这里还可以用DATE_FORMAT做反向操作把日期格式化成%Y/%m/%d这样的字符串SELECT DATE_FORMAT(2024-05-15 18:30:45, %Y/%m/%d); -- 2024/05/15我把这个关系总结成一句话字符串进日期用STR_TO_DATE日期出字符串用DATE_FORMAT规范化字符串进日期用CASTDATETIME取日期用DATE()。各司其职代码会好维护很多。4. 实战导入CSV文件、老表迁移与脏数据清洗4.1 老系统迁移的一次典型遭遇去年我接手一个某平台的数据清洗任务订单表里有个create_time_str字段VARCHAR类型存的内容长这样2024-05-15 18:30:45 2024/5/15 9:05:23 20240515 2024年5月15日四种格式混在同一个字段里。这种表千万不能直接ALTER TABLE ... MODIFY COLUMN ... DATETIMEMySQL会按默认格式解析结果就是大面积的0000-00-00或报错。正确做法是分两步走先建测试查询看看每种格式能不能被STR_TO_DATE正确解析SELECT create_time_str, CASE WHEN create_time_str REGEXP ^[0-9]{4}-[0-9]{1,2}-[0-9]{1,2} THEN STR_TO_DATE(create_time_str, %Y-%m-%d %H:%i:%s) WHEN create_time_str REGEXP ^[0-9]{4}/[0-9]{1,2}/[0-9]{1,2} THEN STR_TO_DATE(create_time_str, %Y/%m/%d %H:%i:%s) WHEN create_time_str REGEXP ^[0-9]{8}$ THEN STR_TO_DATE(create_time_str, %Y%m%d) WHEN create_time_str REGEXP ^[0-9]{4}年 THEN STR_TO_DATE(create_time_str, %Y年%c月%d日) ELSE NULL END AS parsed_time FROM temp_orders LIMIT 100;这条SQL把每种格式的解析结果都看一遍确认没有解析成NULL的漏网之鱼后再生成新的日期列最后才做数据迁移。4.2 WHERE子句里对列套函数索引失效的典型错误这是我在代码评审里见得最多的坑。有人为了对比日期字符串在WHERE条件里写成SELECT * FROM temp_orders WHERE DATE_FORMAT(create_time_str, %Y-%m-%d) 2024-05-15;如果create_time_str上建了索引这个写法直接让索引失效因为MySQL必须先对每一行做DATE_FORMAT计算才能去和常量比较全表扫描跑不掉。正确写法是直接算好区间SELECT * FROM temp_orders WHERE create_time_str 2024-05-15 00:00:00 AND create_time_str 2024-05-16 00:00:00;前提是字段里的字符串格式统一、排序规则和日期大小一致。如果字段是2024/5/15这种格式上述区间写法同样失效这时候要么先把字段洗成标准格式要么新增加一个真正的DATETIME列并建索引。4.3 给脏数据字段“留后路”的迁移方案数据量大的表不建议直接改列类型。我的习惯做法是ALTER TABLE temp_orders ADD COLUMN create_time_datetime DATETIME NULL AFTER create_time_str; UPDATE temp_orders SET create_time_datetime CASE WHEN create_time_str LIKE ____-__-__% THEN STR_TO_DATE(create_time_str, %Y-%m-%d %H:%i:%s) WHEN create_time_str LIKE ____/__/__% THEN STR_TO_DATE(create_time_str, %Y/%m/%d %H:%i:%s) ELSE NULL END; CREATE INDEX idx_create_time_datetime ON temp_orders(create_time_datetime);原始字符串列保留新列负责业务查询等运行一段时间确认无误后再清理原始列。这种做法对线上服务最友好出错随时可以回滚。5. 边界与事故NULL、空字符串、非法日期与SQL_MODE5.1 严格模式与非严格模式下的行为差异MySQL在不同sql_mode下解析非法日期的表现完全不同。非严格模式下STR_TO_DATE(2024-02-30, %Y-%m-%d)会得到2024-02-29这种被“修正”的结果而严格模式下直接报ERROR 1292或返回NULL。我见过不少人线上库和本地库sql_mode配置不一致出现“本地跑得好好的线上全报错”的灵异现象。建议在任何项目里都用严格模式SET GLOBAL sql_mode STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION;如果没法改全局设置至少在建表语句里给日期字段加上CHECK约束或者在应用层统一校验。5.2 防御性转换用CASE和字段正则兜底现实世界的数据永远比预期脏。我在过滤环节常用的方式是SELECT IF( create_time_str REGEXP ^[0-9]{4}-[0-9]{2}-[0-9]{2}[ T][0-9]{2}:[0-9]{2}:[0-9]{2}, STR_TO_DATE(create_time_str, %Y-%m-%d %H:%i:%s), NULL ) AS safe_parsed_time FROM temp_orders;先把create_time_str为NULL、空字符串和格式异常的数据全拦截成NULL后面再用COALESCE或CASE做默认值兜底。别等STR_TO_DATE返回NULL再补救那样排查成本高得多。5.3 两位数年份的69/70陷阱前面场景10提过%y的映射规则这里展开说。MySQL对两位年份的解析依据是1970-2069这个窗口70到99解析成1970到199900到69解析成2000到2069。所以SELECT STR_TO_DATE(69-05-15, %y-%m-%d); -- 2069-05-15 SELECT STR_TO_DATE(70-05-15, %y-%m-%d); -- 1970-05-15身份证号、出生日期这类数据如果只给了两位数年份转换前一定要确认业务方想要的是哪个世纪。我处理过一个社保相关的模拟数据里面全是68-03-21这种按业务规则应该是1968年但如果直接套%y全变成2068年整个统计全错。最后解决方案是先在字符串前面拼上19再按四位年份解析。6. 上线前把转换逻辑跑一遍一个完整的“体检”查询6.1 用一条SQL检查全表数据的可转换率在真正动手更新数据前我会先把全表扫一遍统计有多少行能被成功解析、有多少行会变成NULL。一条SQL就能搞定SELECT COUNT(*) AS total_cnt, SUM( CASE WHEN STR_TO_DATE(create_time_str, %Y-%m-%d %H:%i:%s) IS NOT NULL THEN 1 ELSE 0 END ) AS ok_cnt, SUM( CASE WHEN STR_TO_DATE(create_time_str, %Y-%m-%d %H:%i:%s) IS NULL THEN 1 ELSE 0 END ) AS null_cnt FROM temp_orders;如果null_cnt占比超过预期就不要急着执行全量更新先用上一节的分格式CASE WHEN把所有异常值捞出来一条条看格式差异。我遇到过最离谱的情况是一批数据里混着全角和半角空格字符串肉眼看着一模一样用长度函数一查才发现差了一个字节。6.2 转换完成后的复检与索引验证数据更新完之后复检不能只看NULL比例还要抽样对比业务上的关键日期。我的习惯是随机抽20条用原始字符串和转换后的日期做一次DATE_FORMAT回逆保证两边能对得上SELECT create_time_str, create_time_datetime, DATE_FORMAT(create_time_datetime, %Y-%m-%d %H:%i:%s) AS roundtrip_str FROM temp_orders ORDER BY RAND() LIMIT 20;这一步能发现时区偏移、日期被修正等隐蔽问题。等复检通过后再建索引索引命中情况可以用EXPLAIN确认避免出现全表扫描。6.3 一次改写SQL经历带出的经验最后分享个实际教训。有个线上统计接口原本直接查VARCHAR日期字段数据量到几十万行后接口响应从100毫秒涨到3秒多。排查后发现全表扫描在逐行做字符串格式对比。后来我把数据洗进了一个新的DATETIME列加了索引改写后的SQL响应稳定在几十毫秒。从那以后我给自己定了一条规矩能用日期类型存储的字段坚决不设计成VARCHAR历史遗留的脏字符串只做解析不参与业务计算。这套方法下来字符串日期转换很少再给我惹祸。
