上周帮同事排查一个线上问题Meta赔了一整天。他写了一条查询条件里用了STR_TO_DATE(create_time, %Y-%m-%d) 2024-01-01结果该查出来的数据一条都没有日志里也没有报错。我过去一看问题出在他建表时create_time已经是DATETIME类型又套了一层STR_TO_DATE转完跟字符串比较语义直接乱了。这不是他一个人的问题——我见过太多人搞不清楚MySQL里字符串和日期类型到底什么时候自动转、什么时候要手动转、函数用了之后索引还灵不灵。这篇内容我准备把MySQL里和日期时间转换相关的函数彻底捋一遍重点讲透STR_TO_DATE()顺带把DATE_FORMAT()、CAST()、CONVERT()、UNIX_TIMESTAMP()这套转换家族一起讲清楚。还会专门聊聊隐式类型转换是怎么回事为什么有些日期条件会导致全表扫描甚至排序错误。无论你是刚学MySQL的新手还是被线上慢查询折磨过的老手这篇都值得收藏。1. 为什么日期时间转换会成为一个通用痛点1.1 一个每天都在发生的典型场景先说一个我几乎每周都能遇到的场景。业务系统里有一个导入功能用户上传Excel或者CSV前端拿到的是2024/01/15 14:30:00这种格式后端拿到之后直接拼成SQL去更新数据库。如果目标列恰好是VARCHAR类型那这个字符串就原封不动存进去了。等到后面要做月度报表、按天统计、时间区间筛选的时候VARCHAR列上的日期比较就开始出幺蛾子了要么排序顺序不对要么就是2024-1-5排在2024-10-3后面让你怀疑人生。反过来还有一种场景。接口对接时对方传了一个时间戳类似1705300000这种纯数字你要转成可读的DATETIME格式展示在页面上。或者页面上提交了一个日期字符串你要存进DATETIME列。这些场景背后都有一个共同点——MySQL不会因为字符串长得像日期就自动帮你把类型和格式都处理妥当。类型、格式、语义这三件事你至少得搞明白两件。1.2 MySQL日期时间类型的最小认知做转换之前得先知道自己到底在往哪个容器里装东西。MySQL里和日期时间相关的核心类型有五个DATE只存年月日格式YYYY-MM-DD范围从1000-01-01到9999-12-31。TIME只存时分秒格式HH:MM:SS可以带小数秒范围支持到838:59:59这种超出一天的值主要用于表示时间间隔。DATETIME在DATE基础之上加上时分秒格式YYYY-MM-DD HH:MM:SS不跟时区走存什么就是什么。TIMESTAMP也存年月日时分秒但它的实际存储是UTC时间展示时会根据会话时区转换。范围比DATETIME窄很多只能到2038年左右。YEAR只说年份两位或者四位。我用一个生活化的比喻来解释DATE是一个只有天刻度的日历盒子DATETIME是带时分秒的钟表TIMESTAMP则是那个会自动按照所在时区调时间的智能手表。你要是把2024-01-15下午两点塞进日历盒子它只会保留2024-01-15时间部分直接丢弃你要是把一个超出2038年的时间塞进智能手表它直接罢工报错。1.3 先把转换方向想清楚日期时间转换不外乎下面三个方向搞混了就会出现同事那种啼笑皆非的SQL字符串 → 日期类型这是写入端的核心需求典型的操作就是STR_TO_DATE()和CAST() AS DATE。日期类型 → 字符串这是展示端和报表端的核心需求典型操作是DATE_FORMAT()。时间戳 ↔ 日期接口对接和跨系统同步时用得最多典型操作是FROM_UNIXTIME()和UNIX_TIMESTAMP()。一开始觉得自己写SQL没问题的人往往就是因为在字符串和日期类型之间横跳的时候吃了暗亏。接下来我们从最核心的STR_TO_DATE()开始。2. STR_TO_DATE核心拆解格式串决定了你的生死2.1 基本语法与返回类型判断STR_TO_DATE()的语法非常简单STR_TO_DATE(str, format)str是你的原始字符串format是格式串MySQL会按照格式串去解析字符串解析成功就返回一个日期或日期时间值解析失败就返回NULL。这里有一个关键点STR_TO_DATE()的返回类型由格式串决定不是由字符串决定。这句话怎么理解直接看例子-- 格式串只有年月日返回 DATE 类型 SELECT STR_TO_DATE(2024-01-15, %Y-%m-%d); -- 结果是一个 DATE2024-01-15 -- 格式串包含时分秒返回 DATETIME 类型 SELECT STR_TO_DATE(2024-01-15 14:30:00, %Y-%m-%d %H:%i:%s); -- 结果是一个 DATETIME2024-01-15 14:30:00这个特性非常实用。你想得到一个DATE类型就只写年月日的格式串你想要一个DATETIME就补上时分秒。MySQL不会因为字符串里恰好带了14:30:00就自动帮你识别它完全以格式串为准。2.2 格式串占位符全集这些写法千万不能记混接下来是这篇文章的命根子格式串占位符。我整理了一份核心对照表中文环境下最常用的就这几个占位符含义对应示例%Y四位年份2024%y两位年份24%m月份两位数字带前导零01、12%c月份数字不带前导零1、12%M月份完整英文名January%b月份英文缩写Jan%d日两位数字带前导零05、25%e日不带前导零5、25%H小时24小时制两位14%h小时12小时制两位02%i分钟两位30%s秒两位45%f微秒六位123456%pAM或PMAM%T完整时间等价于%H:%i:%s14:30:45%r12小时制完整时间等价于%h:%i:%s %p02:30:45 PM有一个最容易踩的坑是%i。接触过不少老手下意识会把分钟写成%m或者%M。醒醒%m是月份%M是英文月份名分钟是%i没有第二个写法。你写%m解析分钟MySQL会默认把字符串当成月份解析比如STR_TO_DATE(14:30, %H:%m)得到的根本不是14点30分而是14点零?月结果要么是NULL要么是一个语义完全错误的日期。%H和%h也容易被忽略。如果你用%h去解析14:30:00大概率返回NULL因为12小时制里根本没有14点。遇到上午下午混合的字符串必须%h %p组合上阵SELECT STR_TO_DATE(2024-01-15 02:30:45 PM, %Y-%m-%d %h:%i:%s %p); -- 正确解析为 2024-01-15 14:30:452.3 匹配失败的残酷真相它不报错只返回NULL这是新手最容易崩溃的地方也是很多线上问题的元凶。STR_TO_DATE()解析失败的时候不会报错而是安静地返回一个NULL。举个例子SELECT STR_TO_DATE(2024-01-15 14:30:00, %Y-%m-%d); -- 结果是 NULL为什么因为字符串里包含14:30:00这段内容但格式串里没有对应的占位符去承接它。MySQL的解析逻辑是用格式串逐位吞掉字符串格式串结束之后字符串还有剩余对不起解析失败返回NULL。反过来也一样格式串里有%H:%i:%s但字符串里根本没有时间部分也是NULLSELECT STR_TO_DATE(2024-01-15, %Y-%m-%d %H:%i:%s); -- 结果还是 NULL更隐蔽的一个情况是格式串写错了但肉眼看不出来。比如你想解析2024-01-15格式串写成了%Y-%m-%e——%e可以直接承接不带前导零的数字但前面的连字符已经吞掉了第一个分隔符字符串里的05这种带前导零的日子%e只解析数字5剩下一个0没人接又变成NULL。还有一类和sql_mode直接相关的坑。当会话开启NO_ZERO_DATE或NO_ZERO_IN_DATE时字符串里的0000-00-00、2024-00-15这种值会被STR_TO_DATE()直接拒绝返回NULL。生产环境的sql_mode一般都比较严格这类问题排查起来特别考验耐心因为直接看SQL感觉没问题查数据就是查不到。我把常见失败原因整理成了一个小表问题现象根本原因排查方向返回NULL但看不出哪里错字符串与格式串没有完全对应逐位对比字符串和占位符注意时间和日期部分是否齐全返回的日期月份和预期不一致把%i误写成%m分钟被当成月份特别注意%i才是分钟12小时制字符串解析失败用了%H去解析带AM/PM的字符串换用%h并配合%p00开头的日期字段解析失败开启了严苛的sql_mode检查会话或全局sql_mode配置2.4 常见业务模板从导入Excel到接口报文光知道语法还不行得会套用。我平时用得最多的是下面几个模板Excel或CSV常见格式2024/1/5 14:30斜杠分隔、月日不补零SELECT STR_TO_DATE(2024/1/5 14:30, %Y/%c/%e %H:%i);接口报文常见格式20240115143045纯数字连在一起SELECT STR_TO_DATE(20240115143045, %Y%m%d%H%i%s);带毫秒的日志时间2024-01-15 14:30:45.123456SELECT STR_TO_DATE(2024-01-15 14:30:45.123456, %Y-%m-%d %H:%i:%s.%f);月度数据只想取年月SELECT STR_TO_DATE(202401, %Y%m); -- 返回 2024-01-01最后这个例子每次说都有人吃惊STR_TO_DATE()解析只有年月的字符串时返回的日期会把日默认补成01。这是MySQL的默认行为格式串里没有的日期部分全部取最小值。3. 转换函数全家桶DATE_FORMAT、CAST、CONVERT与时间戳体系3.1 DATE_FORMATSTR_TO_DATE的镜像操作STR_TO_DATE()把字符串变成日期DATE_FORMAT()正好反着来把日期按照格式串变成字符串。这两兄弟用的格式串占位符是同一套体系所以上面的表格同样适用于DATE_FORMAT()。SELECT DATE_FORMAT(NOW(), %Y-%m-%d %H:%i:%s); -- 输出类似 2024-01-15 14:30:45 SELECT DATE_FORMAT(NOW(), %Y%m%d); -- 输出类似 20240115日常开发里最常用的场景是报表的按天、按月分组SELECT DATE_FORMAT(pay_time, %Y-%m) AS month, SUM(amount) FROM orders GROUP BY DATE_FORMAT(pay_time, %Y-%m);有一点必须提醒不要在WHERE条件里对列做DATE_FORMAT包裹。比如你想查询2024年1月15日当天的订单写成WHERE DATE_FORMAT(create_time, %Y-%m-%d) 2024-01-15逻辑上没错但create_time列上的索引直接失效MySQL得把全表每一行都格式化一遍再比较。正确姿势是把常量包一层让列保持原样。这一点后面还会细讲。3.2 CAST和CONVERT轻量转型工具如果说STR_TO_DATE()是带格式说明书的重型解析器CAST()和CONVERT()就是可以快速套用的轻量转换器。-- 字符串转 DATE SELECT CAST(2024-01-15 AS DATE); -- 结果: 2024-01-15 -- 字符串转 DATETIME SELECT CAST(2024-01-15 14:30:00 AS DATETIME); -- 结果: 2024-01-15 14:30:00 -- DATETIME 转 DATE时间部分被截断 SELECT CAST(2024-01-15 14:30:00 AS DATE); -- 结果: 2024-01-15CONVERT()写法稍微奇特点老版本常用逗号分隔的写法SELECT CONVERT(2024-01-15, DATE); -- 结果: 2024-01-15这两个的局限很明显只能处理MySQL能识别的标准格式2024/1/5这种带斜杠且不补零的直接解析失败。所以带有非标准格式的字符串我还是推荐STR_TO_DATE()。值得留意的是CAST(2024-01-15 AS DATETIME)这种写法结果会自动补上00:00:00SELECT CAST(2024-01-15 AS DATETIME); -- 结果: 2024-01-15 00:00:003.3 时间戳体系UNIX_TIMESTAMP与FROM_UNIXTIME时间戳转换在系统对接时很常用。UNIX_TIMESTAMP()把一个日期时间转成秒级时间戳FROM_UNIXTIME()把时间戳还原成日期时间。-- 日期时间转时间戳 SELECT UNIX_TIMESTAMP(2024-01-15 14:30:00); -- 结果类似 1705300200 -- 时间戳转日期时间 SELECT FROM_UNIXTIME(1705300200); -- 结果: 2024-01-15 14:30:00需要警惕的是时区问题。UNIX_TIMESTAMP()和FROM_UNIXTIME()默认使用会话时区在业务库和报表库时区不一致的环境里同一个时间戳转换出来的时间可能差好几个小时。排查这类问题的时候先执行SELECT NOW(), session.time_zone, global.time_zone;看一眼两边时区是否统一。毫秒时间戳在Java和Go接口里很常见MySQL 8.0里可以这样处理-- 毫秒转日期时间先除以1000再用 FROM_UNIXTIME 转 SELECT FROM_UNIXTIME(1705300200123 / 1000);MySQL 8.0也支持直接转微秒时间戳的函数TIMEDIFF()配合MICROSECOND()这类函数处理经常绕实用主义一点的做法是直接换算。3.4 一张表理清选择策略我做了个选型表格遇到具体场景直接查使用场景推荐函数理由非标准格式字符串转日期如2024/1/5STR_TO_DATE()格式串完全可控解析能力强标准格式字符串直接转日期CAST()或CONVERT()语法简单一次到位日期按指定格式输出字符串DATE_FORMAT()和STR_TO_DATE()共用一套格式串日期转时间戳UNIX_TIMESTAMP()一行搞定注意时区时间戳转日期FROM_UNIXTIME()秒级毫秒级都适用DATETIME仅取日期部分CAST(dt AS DATE)或DATE(dt)语法直观支持索引优化4. 隐式类型转换你没写转换函数MySQL就自己乱猜4.1 VARCHAR列和日期类型比较的真实语义有一些坑不是因为你写了转换函数反而恰恰是你什么都没写MySQL自动做了隐式类型转换然后转换结果把你坑了。这是整个日期时间主题里最隐蔽的一层。最经典的一句是WHERE date_str 2024-01-15其中date_str是一个VARCHAR列里面存的是2024-01-15这样的字符串。它真的没问题吗不一定。如果所有字符串都规规整整是2024-01-15这种格式比较起来结果是对的。如果里面出现了一条2024-1-5字符串比较时后面的2024-01-15明显大于2024-1-5因为字符0的ASCII码大于-一条完全属于1月5日的数据就被排除掉了。更夸张的是数字和日期列的混比。假设你的列是DATE类型然后你写了WHERE create_time 20240115这个数字会先被转成20240115字符串再和日期列比较。MySQL会尝试把整个表达式中的字符串转成数字或者日期结果往往和你预想的不在一辆车上。这种写法我见到一次就要提醒一次日期列永远不要跟一个裸数字比较要么写成2024-01-15要么显式CAST。4.2 为什么函数套列会让索引失效索引失效问题在MySQL里是性能杀手。-- 错误示范在索引列上套函数 SELECT * FROM orders WHERE DATE_FORMAT(create_time, %Y-%m-%d) 2024-01-15; -- 正确示范保留索引列常量套函数 SELECT * FROM orders WHERE create_time STR_TO_DATE(2024-01-15, %Y-%m-%d) AND create_time STR_TO_DATE(2024-01-16, %Y-%m-%d);第一句MySQL为了比较每一行的格式化结果不得不对create_time的每个值都执行一次DATE_FORMAT()索引字段的值本身在比较前就被改写了这导致基于B树的排序查找完全失效只能走全表扫描。第二句索引列create_time本身没有穿过任何函数外套常量那边包一层STR_TO_DATE()完全不影响索引结构优化器可以直接命中索引。这个逻辑其实可以推广到任何函数只要索引列出现在函数参数里这个索引基本就废了。不光是DATE_FORMATLEFT()、YEAR()、MONTH()、SUBSTRING()全都一样。治本的办法是设计上避免对列的包裹查询按照上面那种边界区间写法或者新建冗余的date列在写入时就生成好查询直接用等值条件。4.3 排序VARCHAR日期列的连环车祸热搜词里有mysql排序我多说一嘴。如果日期存成VARCHAR按它排序会遇到典型的字符串排序问题SELECT * FROM orders ORDER BY create_time DESC;VARCHAR排序按字符逐位比较2024-9-1在2024-10-1之后因为字符9比1大。你期望的倒序当然是10月1日在前实际却变成了9月1日在前。这种错乱非常隐蔽而且往往在数据量大了之后才被用户发现。解决思路有两个阶段。临时方案是对排序列做转换SELECT * FROM orders ORDER BY STR_TO_DATE(create_time, %Y-%m-%d) DESC;长期方案是彻底改造表结构把这个列改成DATE或DATETIME。我的建议很直接只要业务字段语义上是个日期就绝对不要用VARCHAR去存。一个字符串列上所有关于日期的比较、排序、区间查询都是反模式你后面要花十倍的时间去补坑。5. 实战排坑实录从数据清洗到区间查询的完整案例5.1 场景一批量导入历史数据时清洗脏格式有一次接到一个任务要把一张老系统导出的csv导入新库。老系统导出的日期千奇百怪有2024/1/5 14:30有2024.01.05有空字符串甚至有#N/A。表结构里目标列是DATETIME。我的导入思路是先用一个临时表接收原始数据然后用STR_TO_DATE()做清洗转换转换失败的记录用CASE兜底UPDATE temp_raw_data SET cleaned_date CASE WHEN raw_date IS NULL OR raw_date THEN NULL WHEN raw_date #N/A THEN NULL ELSE STR_TO_DATE(raw_date, %Y/%c/%e %H:%i) END;这个过程中的核心体验就是转换失败不要慌先统计有多少NULL。我习惯先跑一遍查询把转换失败的样本捞出来看看格式长什么样再针对性调整格式串。一步到位直接写格式串然后全量导入大概率会翻车。5.2 场景二按天统计报表的正确打开方式统计每天订单量很多人一开始会写SELECT LEFT(create_time, 10) AS day, COUNT(*) FROM orders GROUP BY LEFT(create_time, 10);这个写法性能糟糕一天的窄区间算还好跑全月报表时就很吃力。更标准的写法是直接对DATE类型列分组SELECT DATE(create_time) AS day, COUNT(*) FROM orders GROUP BY DATE(create_time);或者按天区间分组SELECT DATE_FORMAT(create_time, %Y-%m-%d) AS day, COUNT(*) FROM orders GROUP BY DATE_FORMAT(create_time, %Y-%m-%d);无论是LEFT()还是DATE_FORMAT()在GROUP BY里对列做加工都会让分组统计走不上索引。报表场景数据量达到百万级时建议干脆在表里冗余一个day_date DATE列写入时同步生成分组直接走这个列排序和过滤都轻松。5.3 场景三时间区间查询的边界条件统计某个用户在某一天的所有操作记录2024年1月15日全天。比较自然的想法是SELECT * FROM operation_log WHERE user_id 123 AND create_time 2024-01-15 AND create_time 2024-01-15;这里有一个细节2024-01-15和DATETIME列比较时MySQL会把它转成2024-01-15 00:00:00。所以这个条件的右边界其实只覆盖到了15号零点整那一秒15号当天23点59分的数据全部查询不到。正确姿势是左闭右开SELECT * FROM operation_log WHERE user_id 123 AND create_time 2024-01-15 00:00:00 AND create_time 2024-01-16 00:00:00;很多人迷迷糊糊写 2024-01-15数据量一大就开始漏数据。左闭右开这个习惯养成了能省掉不少深夜排查的时间。5.4 面试与日常开发中的高频观察点最后结合我这些年面试候选人和带新人的经验说几个高频考点和容易翻车的地方。第一STR_TO_DATE()返回类型判定是必问的。%Y-%m-%d格式串返回DATE%Y-%m-%d %H:%i:%s返回DATETIME。第二%i的分钟含义几乎每个新人都得踩一次。第三字符串和格式串不匹配返回NULL而不是报错这也非常经典。第四索引列上套函数导致索引失效属于性能调优的高频场景。日常开发里我有一个坚持了很久的习惯所有的日期字符串在代码层就统一成YYYY-MM-DD HH:MM:SS再进SQL不在SQL层临时拼格式。这样可以最大限度减少STR_TO_DATE()这种转换函数出现在业务查询里SQL简单干净索引也能安安心心用上。如果你现在正被某个日期查询折腾得焦头烂额建议先按这个顺序排查先确认列的真实类型再确认字符串的字面格式最后检查一下格式串占位符和索引列有没有被函数包裹。很多时候答案就藏在这三步里。上面这些内容希望你在写下一个日期条件的时候就想起来而不是等线上出了事故再来翻。
