MySQL 5.7特殊Datetime格式转换失败，求正确str_to_date格式

你的时间格式2024-08-09 20:57:31.4477里，.4477是小数部分的秒，MySQL中对应这个部分的格式符是%f（表示微秒，最多支持6位，输入不足6位时会自动补零兼容）。你之前错误使用的%P/%p（用于匹配AM/PM标识）、%s（用于匹配整数秒）都不符合当前时间的结构，这才导致转换报错。

正确的格式串 #

针对你的时间格式，str_to_date应使用的格式串为：

'%Y-%m-%d %H:%i:%s.%f'

修正后的UPDATE语句 #

UPDATE `table` 
SET `column1` = 
    CASE 
        WHEN `column1` = '' THEN NULL 
        ELSE DATE_FORMAT(STR_TO_DATE(`column1`, '%Y-%m-%d %H:%i:%s.%f'), '%m/%d/%Y')
    END;

额外说明 #

如果column1本身就是DATETIME类型（而非字符串存储的时间值），可以跳过STR_TO_DATE转换，直接用DATE_FORMAT处理：

UPDATE `table` 
SET `column1` = 
    CASE 
        WHEN `column1` IS NULL THEN NULL 
        ELSE DATE_FORMAT(`column1`, '%m/%d/%Y')
    END;

注意：若原列是DATETIME类型，将其更新为%m/%d/%Y格式的字符串可能引发类型不匹配问题，建议将结果存储到另一个字符串类型的列中，或确保业务场景允许该类型转换。

内容的提问来源于stack exchange，提问作者John Beasley