企业官网建设流程全解析

上周帮同事排查一个线上问题，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'); -- 结果是一个 DATE：2024-01-15 -- 格式串包含时分秒，返回 DATETIME 类型 SELECT STR_TO_DATE('2024-01-15 14:30:00', '%Y-%m-%d %H:%i:%s'); -- 结果是一个 DATETIME：2024-01-15 14:30:00

这个特性非常实用。你想得到一个DATE类型，就只写年月日的格式串；你想要一个DATETIME，就补上时分秒。MySQL不会因为字符串里恰好带了"14:30:00"就自动帮你识别，它完全以格式串为准。

2.2 格式串占位符全集：这些写法千万不能记混

接下来是这篇文章的命根子，格式串占位符。我整理了一份核心对照表，中文环境下最常用的就这几个：

有一个最容易踩的坑是%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:45

2.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，但字符串里根本没有时间部分，也是NULL：

SELECT 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感觉没问题，查数据就是查不到。

我把常见失败原因整理成了一个小表：

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.123456：

SELECT 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_FORMAT：STR_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-15

CONVERT()写法稍微奇特点，老版本常用逗号分隔的写法：

SELECT CONVERT('2024-01-15', DATE); -- 结果: 2024-01-15

这两个的局限很明显：只能处理MySQL能识别的标准格式，2024/1/5这种带斜杠且不补零的，直接解析失败。所以带有非标准格式的字符串，我还是推荐STR_TO_DATE()。

值得留意的是CAST('2024-01-15' AS DATETIME)这种写法，结果会自动补上00:00:00：

SELECT CAST('2024-01-15' AS DATETIME); -- 结果: 2024-01-15 00:00:00

3.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 一张表理清选择策略

我做了个选型表格，遇到具体场景直接查：

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_FORMAT，LEFT()、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简单干净，索引也能安安心心用上。

如果你现在正被某个日期查询折腾得焦头烂额，建议先按这个顺序排查：先确认列的真实类型，再确认字符串的字面格式，最后检查一下格式串占位符和索引列有没有被函数包裹。很多时候答案就藏在这三步里。上面这些内容，希望你在写下一个日期条件的时候就想起来，而不是等线上出了事故再来翻。