最近在整理一套 MySQL 字符串日期转换的实操笔记，这已经是系列里第 12 篇内容了。这一篇专门聊“把字符串格式的日期转成真正的 DATE / DATETIME”这件事。很多同学一听到“字符串日期”就说：不就是 CAST('2024-01-01' AS DATE) 吗？真有这么简单就不会有这么多坑了。实际项目里你遇到的往往是 '2024/01/01'、'20240315'、'15-MAR-24'、'2024年3月15日'，甚至是在一个字段里混了好几种格式，这种情况下 CAST 直接歇菜，必须上 STR_TO_DATE 做解析。这篇文章会把常用函数、格式规则、边界情况和性能问题全部拆开讲清楚，适合刚接触 MySQL 的新手，也适合正在做数据清洗的过来人参考，内容全部来自线上场景的实战踩坑记录。
1. 先搞清楚为什么要做字符串日期转换
很多人觉得这是个无关紧要的操作，反正都是“形如日期”的文本，显示给用户看也没问题。真在业务里跑起来就完全不同。假设订单表里有个字段叫做 create_time，但它存的是 VARCHAR，里面写了 '2024-03-15'，你查单日订单时大概率会这么干：
SELECT * FROM orders WHERE create_time = '2024-03-15';如果所有 create_time 都是规整的 'YYYY-MM-DD' ，这种查询偶尔也能跑，因为 MySQL 会自动做隐式转换，把字符串 '2024-03-15' 转成日期值再去比较。但一旦出现 '2024-3-15'、'2024/03/15'、'2024-03-15 10:30:00'，你这条 WHERE 立马失效。更可怕的是排序：按字符串排，'2024-10-01' 排在 '2024-09-02' 前面，但按日期排序，顺序反过来。如果报表里有“比大小”的逻辑，字符串排序会给出完全错误的结果。
从数据模型角度看，真正的日期类型 DATE 只有 3 字节，而 VARCHAR 哪怕存 10 个字符也要 11 字节左右；DATETIME 占 8 字节，字符串通常都是 19 字节往上。存储空间、索引大小、比较性能，统统是日期类型占优。日期类型还自带合法性校验，根本插不进去 '2024-13-45' 这种脏数据。字符串类型呢？你说它是日期它就是日期，错得悄无声息。
所以转换不是“好看不好看”的问题，而是数据质量和查询性能的基本盘。改造存量数据、接入外部系统、处理导入文件时，字符串日期转换几乎是躲不掉的第一步。
2. 核心函数逐个拆解，别只盯着 CAST
2.1 STR_TO_DATE：最灵活的解析器，但要求格式严格匹配
STR_TO_DATE 是字符串转日期的主力函数。它的逻辑是：按照你给的格式模板，把字符串拆出年、月、日、时、分、秒，然后生成一个合法的日期值。
SELECT STR_TO_DATE('2024-03-15', '%Y-%m-%d'); -- 返回 2024-03-15，类型是 DATE核心语法就两个参数：第一个是原始字符串，第二个是格式字符串。格式字符串里的 %Y、%m、%d 这些占位符，告诉 MySQL 字符串的哪一段代表哪位年份。整个格式串必须和实际字符串的结构逐字匹配，逗号就是逗号，横杠就是横杠，空格就是空格，差一位都不行。
常用格式代码我整理了一张表：
| 格式符 | 含义 | 示例 |
|---|---|---|
| %Y | 四位年份 | 2024 |
| %y | 两位年份 | 24 |
| %m | 两位月份 | 03 |
| %c | 一位或两位月份，不强制补零 | 3 或 03 |
| %d | 两位日 | 05 |
| %e | 一位或两位日，不强制补零 | 5 或 05 |
| %H | 24 小时制，两位 | 14 |
| %k | 24 小时制，不强制补零 | 14 |
| %i | 分钟，两位 | 30 |
| %s | 秒，两位 | 45 |
| %M | 英文完整月份名 | March |
| %b | 英文缩写月份名 | Mar |
| %W | 英文完整星期名 | Friday |
| %a | 英文缩写星期名 | Fri |
| %T | 时间简写，等价于 %H:%i:%s | 14:30:45 |
看到了吧，%c 和 %e 非常实用，专门处理“没有前导零”的字符串。比如要解析 '2024-3-15'，直接用 %Y-%m-%d 会返回 NULL，因为 %m 要求两位月份，'3' 只有一位。正确写法是：
SELECT STR_TO_DATE('2024-3-15', '%Y-%c-%e'); -- 返回 2024-03-15网上很多教程只教 %Y-%m-%d，但从 CSV 导入的数据经常省略前导零，实战里掌握 %c 和 %e 能救命。
2.2 CAST 和 CONVERT：快，但只认标准格式
CAST 是个老实人，只认 ISO 风格标准格式：
SELECT CAST('2024-03-15' AS DATE); -- 返回 2024-03-15 SELECT CAST('2024-03-15 14:30:45' AS DATETIME); -- 返回 2024-03-15 14:30:45CONVERT 语法稍有不同，但底层规则一致：
SELECT CONVERT('2024-03-15', DATE);CAST 能接受的字符串格式非常有限。'20240315' 这种八位整型数字是不能直接 CAST 成日期的，必须经过 STR_TO_DATE。'2024/03/15' 同样不行，因为它不是标准横杠分隔。所以 CAST 适合处理已经规整的数据，比如从一个标准接口里拿到的日期字符串，简单省事。脏一点的活，还是得交给 STR_TO_DATE。
值得注意的是，CAST 和 CONVERT 把非法字符串转成日期时，不会抛出异常，而是返回 NULL，同时给一条 Warning。这条 Warning 在命令行工具里能看到，但在业务代码的 SQL 执行结果里经常被吞掉，所以直接用 CAST 解析不安全的数据源，比较容易被“静默失败”坑到。
2.3 DATE_FORMAT：反向操作，也经常被搞混
总是有人把 DATE_FORMAT 当成字符串转日期来用，方向完全搞反了。DATE_FORMAT 是日期转字符串的，它接收一个 DATE 或 DATETIME 类型的值，按照格式输出文本：
SELECT DATE_FORMAT(NOW(), '%Y年%m月%d日'); -- 返回 2024年03月15日为什么我要在字符串转日期的文章里提它？因为实际开发里经常出现“日期转字符串之后拼接、然后再转日期”的来回折腾。比如报表需要按周分组，有人会先把日期转成 '2024-W11' 这种文本，再转回来做计算，完全没必要。理解了 DATE_FORMAT 和 STR_TO_DATE 的互逆关系，就会明白很多格式转换操作本身就是冗余的。DATE_FORMAT(NOW(), '%Y-%m-%d') 的结果可以直接 CAST 成 DATE，但更合理的做法是直接用 DATE(NOW())，少绕一圈。
3. 真实场景里的转换案例，直接照着抄
3.1 导入 CSV 文件，日期乱七八糟
外来数据经常是这个画风：既有 '2024-03-15'，又有 '03/15/2024'，还有 '15.03.2024'。这种混合格式的表，靠 ALTER TABLE 改字段类型是改不动的，因为 MySQL 看到非法字符串会报错或写入 NULL。正确流程应该是三步走。
先用 STR_TO_DATE 把每一种已知格式都解析一遍，存到临时列里：
UPDATE temp_orders SET parsed_date = STR_TO_DATE(raw_date, '%m/%d/%Y');如果一种格式搞不定，就用 CASE WHEN 逐个尝试：
UPDATE temp_orders SET parsed_date = CASE WHEN raw_date LIKE '%/%' THEN STR_TO_DATE(raw_date, '%m/%d/%Y') WHEN raw_date LIKE '%-%' THEN STR_TO_DATE(raw_date, '%Y-%m-%d') WHEN raw_date LIKE '%.%' THEN STR_TO_DATE(raw_date, '%d.%m.%Y') ELSE NULL END;这种方法本质上是“按分隔符猜格式”，风险在于美国和欧洲的日/月顺序容易颠倒。'03/15/2024' 这个字符串，按 %d/%m/%Y 解析会失败，因为 15 不是合法月份；但如果两边月份和日都在 12 以内，比如 '03/05/2024'，你根本无法从字符串本身判断它到底代表 3 月 5 日还是 5 月 3 日。所以数据源文档里必须写清楚到底用哪种格式，没有文档就只能抽样分析，最好跟上游确认，别自己猜。
导入完成之后，务必跑一条验证语句：
SELECT raw_date, parsed_date FROM temp_orders WHERE parsed_date IS NULL AND raw_date IS NOT NULL;把所有没解析出来的数据全部列出来，排查原因。这一步是数据清洗里的保命环节，跳过它，后面所有统计结果都可能带着黑洞。
3.2 处理中文日期格式
业务系统导出的字符串里经常带汉字，比如 '2024年3月15日'、'2024年03月15号'，StR_TO_DATE 完全支持这种自定义格式。只需要把格式模板写成汉字：
SELECT STR_TO_DATE('2024年3月15日', '%Y年%c月%e日'); -- 返回 2024-03-15这里月份和日用了 %c 和 %e，因为源数据里 '3月' 和 '15日' 都没有前导零。如果源数据是 '2024年03月15日'，用 %Y年%m月%d日 也能匹配。关键是汉字部分必须逐字出现在格式字符串里，MySQL 会把这个汉字当成普通文本去匹配。
中文格式的坑主要在“号”这个字上。'2024年3月15号' 这种写法很普遍，格式串里就要写 %Y年%c月%e号，不能写“日”。另外有些导出表格里日期和时间之间用了中文字符的空格或者其他不可见字符，先用 TRIM(raw_date) 再解析，或者直接在格式串里写好空位。
3.3 字符串存的是整数或连续数字
有些老系统为了省空间，把日期存成 INT，比如 20240315。这种数字转日期，STR_TO_DATE 要先把它当成字符串处理一下：
SELECT STR_TO_DATE(CAST(20240315 AS CHAR), '%Y%m%d');更简洁的写法是用函数：
SELECT STR_TO_DATE('20240315', '%Y%m%d');如果字段本身是 INT 类型，直接用：
SELECT STR_TO_DATE(CAST(date_int AS CHAR), '%Y%m%d') FROM my_table;顺带提醒一句：如果数字里带了两位年份，比如 240315 代表 2024-03-15，MySQL 解析时会把 00-69 之间的两位年份映射为 2000-2069，70-99 映射为 1970-1999，参考的是 MySQL 的年份规则，不是你的业务规则。遇到跨世纪的身份证有效期、会员卡到期日这类场景，必须头脑清醒，建议先手动拼上四位年份再解析：
SELECT STR_TO_DATE(CONCAT('20', '240315'), '%Y%m%d');3.4 日期时间混合字符串转到时间戳
前端传过来的参数经常是 '2024-03-15 14:30:45'，要存成 DATETIME 很简单：
SELECT STR_TO_DATE('2024-03-15 14:30:45', '%Y-%m-%d %H:%i:%s');但有些日志系统会把日期和时间用字母 T 连接，形如 '2024-03-15T14:30:45Z'。这是 ISO 8601 的标准格式，MySQL 不能直接识别 T 和 Z，需要手动处理：
SELECT STR_TO_DATE(REPLACE(REPLACE('2024-03-15T14:30:45Z', 'T', ' '), 'Z', ''), '%Y-%m-%d %H:%i:%s');先把 T 替换成空格，再去掉尾部的 Z，解析出来的是服务器本地时区的 DATETIME，不是 UTC 时间。如果业务上需要准确时区，比如跨境订单、全球部署的系统，这 Z 不能直接删，得结合业务时区做换算。
另外有一类字符串是日期和时间之间带中文符号，比如 '2024年03月15日 14:30:45'，照样可以在格式里写：
SELECT STR_TO_DATE('2024年03月15日 14:30:45', '%Y年%m月%d日 %H:%i:%s');只要格式模板和字符串长度一致、符号一致，STR_TO_DATE 就能稳定工作。
3.5 解析英文月份名称
英文月份的完整名称和缩写，在外部系统数据里很常见。比如 'March 15, 2024' 或者 '15-Mar-2024'。格式串用 %M 表示完整月份名，%b 表示缩写月份名：
SELECT STR_TO_DATE('March 15, 2024', '%M %e, %Y'); -- 返回 2024-03-15 SELECT STR_TO_DATE('15-Mar-2024', '%d-%b-%Y'); -- 返回 2024-03-15这类解析对字母大小写不敏感，'march' 和 'MAR' 都能匹配。但需要注意，MySQL 的 %M 和 %b 默认按英文环境识别，如果你把系统语言改成中文或使用了某些自定义 locale，行为可能出现意外。绝大多数情况下保持默认英文识别就好。
如果字符串里面月份是数字和英文混着来，比如 '2024 Mar 15'，格式串就是 '%Y %b %e'，这不是魔法，就是按照实际文本结构一点点抠出来的。
3.6 UNIX 时间戳转日期
严格说这不是字符串日期转换，但很多从接口拿到的数据，日期就是一个纯数字时间戳，比如 1710489600。这种数值转日期必须用 FROM_UNIXTIME：
SELECT FROM_UNIXTIME(1710489600); -- 返回 2024-03-15 16:00:00，结果取决于服务器时区如果只想取日期部分：
SELECT DATE(FROM_UNIXTIME(1710489600));有些系统的时间戳精确到毫秒，数值有 13 位，那得先除以 1000 再转换：
SELECT FROM_UNIXTIME(1710489600123 / 1000);我自己就遇到过三次这种毫秒时间戳，第一次没除 1000，日期直接算到了 1970 年，当时吓得以为是服务器时钟坏了，排查半天才发现是精度问题。遇到 13 位或 16 位的大整数时间戳，一律先看单位，再动手。
4. 转换结果是 NULL 的常见原因和排查思路
STR_TO_DATE 在解析失败时不会报错，只会返回 NULL 并产生一个 Warning。这在海量数据清洗里既是福也是祸：好处是单条脏数据不会让整个批处理崩掉，坏处是你可能压根没发现数据是脏的。所以在转换逻辑上线前，一定要把“返回 NULL 的原始字符串”单独捞出来看，这就是排查的入口。
4.1 格式串和字符串不对应
最常见的失败原因是格式串里少了分隔符。比如字符串是 '2024-03-15'，格式串却写成 '%Y-%m%d'，月份后面少了横杠，解析直接失败。这种错误在写长格式串时特别容易犯，尤其是日期时间混合类型 '2024-03-15 14:30:45'，格式串里最容易漏写中间那个空格。我的习惯是先把格式串和原始字符串逐字符对齐，空格就是空格，冒号就是冒号，缺一不可。
4.2 日期本身不合法
字符串格式完全匹配，但因为月份或日期超出范围，解析也会失败。比如 '2024-13-01'、'2024-02-30'，这些在日期类型里根本不存在。STR_TO_DATE 遇到这种字符串直接返回 NULL，同时警告信息里会写类似“Incorrect datetime value”的字样。在严格 SQL 模式下，直接 INSERT 这种转换结果还会直接报错，连 NULL 都不给。所以导入数据前先做一轮合法性检查比较稳妥。
4.3 字符串里带不可见字符
CSV 文件里常见的坑是 \r 回车符，尤其是 Windows 导出的文件，每一行结尾都带 \r\n。你看着每一行都是 '2024-03-15'，但字符串实际是 '2024-03-15\r'，STR_TO_DATE 匹配到末尾多了一个 \r 就返回 NULL。排查方法很简单：
SELECT HEX(raw_date) FROM temp_orders LIMIT 5;看到 0D 就说明回车符混进去了，先做 TRIM 或者 REPLACE 再解析：
UPDATE temp_orders SET parsed_date = STR_TO_DATE(REPLACE(REPLACE(raw_date, '\r', ''), '\n', ''), '%Y-%m-%d');HTML 上传文件里还可能混入全角空格（U+3000），肉眼完全看不出来，处理思路一样，先替换再解析。这类问题我排查的次数最多，一个 \r 能让整批数据“看起来没问题但全是 NULL”，非常折磨人。
4.4 时区偏移量带来的解析失败
字符串结尾带 '+08:00' 或 '+00:00' 也常常让 STR_TO_DATE 返回 NULL。MySQL 不认这种时区后缀，必须手动剥掉，之后再结合业务做时区换算：
SELECT CONVERT_TZ( STR_TO_DATE(LEFT('2024-03-15T14:30:45+08:00', 19), '%Y-%m-%dT%H:%i:%s'), '+08:00', '+00:00' );将带 +08:00 的时间字符串先截出主体，解析成 DATETIME，再用 CONVERT_TZ 转成标准 UTC 或业务库的本地时间。这个方法在跨时区日志解析里很常用，比手工加减 8 小时可靠得多。
5. 索引、性能与存储：别让隐式转换毁掉查询
5.1 隐式转换吃掉索引
我已经数不清看到过多少条这样的 SQL：
SELECT * FROM orders WHERE DATE(create_time) = '2024-03-15';如果 create_time 是 DATETIME 类型并建了索引，这个查询基本用不上索引，因为函数 DATE() 包裹了索引列，MySQL 只能对全表每个值套上函数再比较，索引失效。正确写法是：
SELECT * FROM orders WHERE create_time >= '2024-03-15 00:00:00' AND create_time < '2024-03-16 00:00:00';反过来，如果 create_time 是 VARCHAR 类型，查询写：
WHERE create_time >= '2024-03-15 00:00:00'MySQL 要把每一行的字符串隐式转成日期再去比较，照样全表扫描。所以真正治本的办法，是把列定义改成 DATETIME，并让写入时全部用真实日期类型，而不是指望查询时靠字段类型救你。
5.2 已有字符串日期表的迁移思路
面对一张已经跑了好几年、线上不能停的表，直接 ALTER 改字段类型容易引发长时间锁表，非常冒险。我推荐的安全迁移步骤是：先加一个 DATETIME 类型的影子列，比如 create_time_new，然后分批用 UPDATE 把解析结果填充进去，每批加 WHERE 条件限制主键范围，避免一次性 UPDATE 全表导致锁范围过大。再把代码里的查询和写入逻辑切换到新列，最后观察一段时间确认无误，再删掉旧列、重命名新列。整个过程可以不停服完成，风险可控。
ALTER TABLE orders ADD COLUMN create_time_new DATETIME NULL; UPDATE orders SET create_time_new = STR_TO_DATE(create_time, '%Y-%m-%d %H:%i:%s') WHERE id BETWEEN 1 AND 100000; -- 分批继续执行，直到没有 NULL 且所有行都被覆盖 ALTER TABLE orders DROP COLUMN create_time; ALTER TABLE orders RENAME COLUMN create_time_new TO create_time;这套流程的核心原则是：数据转换必须可回滚，千万别在一个事务里把几千万行一次性改完，出问题哭都来不及。
5.3 字段类型怎么选：DATE、DATETIME 还是 TIMESTAMP
既然要转成日期类型，那就顺手把字段类型选对。只要日期，不要时间：用 DATE，占 3 字节。需要精确到时分秒：用 DATETIME，占 8 字节，不随数据库时区变化。带时区语义的时间点，比如订单创建时间、日志时间：用 TIMESTAMP，占 4 字节，但会自动根据 time_zone 变量换算，适合需要按会话时区展示的场景。一个常见误区是觉得“TIMESTAMP 比 DATETIME 更准确”，其实两者精度一样，只是 TIMESTAMP 有 2038 年上限问题，存储范围远小于 DATETIME。如果你在维护一个长期系统，存“未来到 2038 年之后”的业务时间，请选 DATETIME，别选 TIMESTAMP。
6. 作为实际业务操作的最终心得：在与 12 个字符串日期格式转换“战斗”之后，一些习惯与想法
处理完了数据库里那些字符串日期格式来自自定义，跨越 CSV、日志、旧系统。这些字符串日期格式转换看似简单，但在这里我需要记住一些养成“对外界透明”的习惯。判断、转换、验证、替换：这四步缺一不可。
我习惯在 SQL 脚本里留一个验证步骤：
SELECT raw_date, STR_TO_DATE(raw_date, '%Y-%m-%d %H:%i:%s') AS parsed_date FROM temp_orders WHERE STR_TO_DATE(raw_date, '%Y-%m-%d %H:%i:%s') IS NULL;这样能在一堆数据里快速定位所有格式异常的行。线上跑批处理之前，先用一个旧分区或者临时表跑完看警告数量和 NULL 数量，心里有底再动全量。我的经验是：不管上游说得多肯定，数据里永远有“意外”，曾经接过一份文档里标着“百分百标准 ISO 格式”的文件，结果里面混着零宽空格，前缀表达式，尾字符。永远对数据保持怀疑，永远把校验当成正式逻辑的一部分。
字符串日期转换还有一个常见认知盲区：开发者常常在本机 MySQL 和线上 MySQL 之间测试现象不同，问题极少不是因为函数本身用错，而是因为双方 sql_mode 或 time_zone 配置不一样。STR_TO_DATE 的 NULL 行为、TIMESTAMP 的转换结果都会受影响。跨环境部署前，把 sql_mode 和 time_zone 也纳入检查清单，这一点被绝大多数教程忽略了。
如果让我给出一个最核心的行动建议，那就是：新表永远不要用 VARCHAR 存日期，存量已经在用的一定要尽早启动迁移。短期的“省事”让你当时不用改代码，但每个月的月报、每个季度的对账、每一次数据仓抽取里，字符串日期就像埋在 C 代码里的隐式类型转换，不知道什么时候就炸一次。这次把所有能踩的坑都踩了一遍，写下来也是给自己以后避雷用。数据清洗没有一劳永逸，但把基础类型做对了，后面至少能少折腾一半。
