MySQL STR_TO_DATE()函数处理带a.m./p.m.时间字符串问题

我之前也碰到过类似的情况，MySQL的STR_TO_DATE对带点的上午/下午后缀确实不太友好，不过只要先把格式转成它能识别的标准形式就行，给你两个靠谱的方案：

方案一：直接替换后缀（兼容所有MySQL版本） #

核心思路是把a.m.换成AM，p.m.换成PM——这是STR_TO_DATE的%p格式符能直接识别的格式，然后配合正确的格式串就能完美转换。

比如你要更新字段的话，用这条语句：

UPDATE your_table 
SET datecolumn = STR_TO_DATE(
  REPLACE(REPLACE(datecolumn, 'a.m.', 'AM'), 'p.m.', 'PM'),
  '%d/%m/%Y %h:%i:%s %p'
);

测试验证 #

拿你的两个例子测试：

-- 转换下午3点的时间
SELECT STR_TO_DATE(REPLACE(REPLACE('30/09/2010 3:33:51 p.m.', 'a.m.', 'AM'), 'p.m.', 'PM'), '%d/%m/%Y %h:%i:%s %p');
-- 返回：2010-09-30 15:33:51（正确转换为24小时制的下午3点）

-- 转换午夜12点的时间
SELECT STR_TO_DATE(REPLACE(REPLACE('30/09/2010 12:00:00 a.m.', 'a.m.', 'AM'), 'p.m.', 'PM'), '%d/%m/%Y %h:%i:%s %p');
-- 返回：2010-09-30 00:00:00（正确识别为午夜）

如果你的数据里还有大写的A.M./P.M.，可以再加一层UPPER()统一格式，让语句更鲁棒：

UPDATE your_table 
SET datecolumn = STR_TO_DATE(
  REPLACE(REPLACE(UPPER(datecolumn), 'A.M.', 'AM'), 'P.M.', 'PM'),
  '%d/%m/%Y %h:%i:%s %p'
);

方案二：用正则替换（MySQL 8.0+适用） #

如果你的MySQL版本是8.0及以上，可以用REGEXP_REPLACE一步完成后缀替换，代码更简洁：

UPDATE your_table 
SET datecolumn = STR_TO_DATE(
  REGEXP_REPLACE(datecolumn, '([ap])\\.m\\.', UPPER('\\1M')),
  '%d/%m/%Y %h:%i:%s %p'
);

这个正则会把a.m.替换成AM，p.m.替换成PM，原理和方案一一致，只是写法更优雅。

为什么你之前的尝试失败？ #

• 第一个语句的left(datecolumn(23))是语法错误，正确的截取写法应该是LEFT(datecolumn, 23)，但就算写法对了，截取会丢失上午/下午的信息，导致下午的时间无法转换成24小时制的正确值。
• 第二个语句直接忽略了a.m./p.m.，自然会出现时间错误（比如下午3点会被当成凌晨3点）。

内容的提问来源于stack exchange，提问作者Steven Cao