MySQL中mediumtext列datetime字符串转Unix时间戳的更新问题

问题根源 #

你的value列中存储的datetime字符串是被双引号包裹的（比如"2023-10-05 14:30:00"），直接调用UNIX_TIMESTAMP(value)时，MySQL无法识别带引号的字符串为合法的datetime格式，会返回NULL，导致UPDATE操作看似没有效果。

解决方案 #

先通过REPLACE()函数移除字符串中的双引号，再进行Unix时间戳转换，同时优化正则表达式，精确匹配合法的datetime格式避免误操作：

1. 先测试转换结果（推荐）

SELECT 
  value,
  UNIX_TIMESTAMP(REPLACE(value, '"', '')) AS converted_unix
FROM thetable
WHERE value REGEXP '^"2[0-9]{3}-[0-9]{2}-[0-9]{2} [0-9]{2}:[0-9]{2}:[0-9]{2}"$';

2. 执行更新操作

UPDATE thetable
SET value = UNIX_TIMESTAMP(REPLACE(value, '"', ''))
WHERE value REGEXP '^"2[0-9]{3}-[0-9]{2}-[0-9]{2} [0-9]{2}:[0-9]{2}:[0-9]{2}"$';

说明 #

• 优化后的正则表达式^"2[0-9]{3}-[0-9]{2}-[0-9]{2} [0-9]{2}:[0-9]{2}:[0-9]{2}"$会精确匹配"YYYY-MM-DD HH:MM:SS"格式的字符串，避免误转换其他带引号的数字字符串。
• 执行UPDATE前务必备份数据，或先用SELECT语句验证转换结果，确保符合预期。

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