MySQL中如何将EXTRACT拼接生成的时间数字串还原为标准日期时间格式

云上老周

2026-9-23

MySQL无分隔符时间串转标准日期时间格式解决方法

方案1：调整生成逻辑（推荐）

你当前使用EXTRACT拼接的方式因为返回数值类型，个位数不会补前导零，生成的串为变长结构，反向解析难度高且存在歧义风险，建议直接用DATE_FORMAT生成固定长度的无分隔符时间串：

-- 生成固定18位无分隔符时间串
SELECT DATE_FORMAT(NOW(6), '%Y%m%d%H%i%s%f') AS output2;

生成的串所有个位数自动补前导零，例如示例时间生成的串为20211207020628783259，反向转换仅需调用一次STR_TO_DATE即可：

-- 固定长度串转标准格式
SELECT STR_TO_DATE('20211207020628783259', '%Y%m%d%H%i%s%f') AS standard_datetime;

执行后直接返回2021-12-07 02:06:28.783259格式的标准日期时间。

方案2：现有变长串转换方案

如果已经有大量你示例中这类无前置零的串需要转换，可以基于固定长度分段倒推的逻辑实现解析：

前6位固定为年月（YYYYMM）
最后6位固定为微秒
倒数第7至倒数第10位固定为分钟+秒（各占2位）
剩余中间部分为日+小时
转换示例代码如下：

SET @input_str = '202112720628783259';
-- 计算日+小时的总长度
SET @day_hour_len = LENGTH(@input_str) - 6 - 6 - 4;
SELECT 
  STR_TO_DATE(
    CONCAT(
      LEFT(@input_str, 6), -- 年月部分
      LPAD(LEFT(SUBSTRING(@input_str, 7), @day_hour_len - IF(@day_hour_len=2,1,2)), 2, '0'), -- 日补前导零
      LPAD(RIGHT(SUBSTRING(@input_str, 7), IF(@day_hour_len=2,1,2)), 2, '0'), -- 小时补前导零
      SUBSTRING(@input_str, LENGTH(@input_str) - 9, 4), -- 分+秒
      RIGHT(@input_str, 6) -- 微秒
    ),
    '%Y%m%d%H%i%s%f'
  ) AS standard_datetime;