MySQL如何将字符串类型日期列转换为Timestamp类型？

你的原SQL语句无效的原因是：MODIFY COLUMN仅用于修改列的数据类型，不能直接在列名位置使用STR_TO_DATE函数完成数据转换——必须先将文本数据转换为MySQL可识别的日期格式，再修改列类型。

以下是两种可行的解决方案：

方法一：直接更新数据后修改列类型 #

先将文本列的数据转换为合法的日期值，再修改列的类型为TIMESTAMP：

-- 第一步：将文本数据转换为TIMESTAMP兼容的格式
UPDATE mytable 
SET `Start Date` = STR_TO_DATE(`Start Date`, "%m/%d/%Y %H:%i"),
    `End Date` = STR_TO_DATE(`End Date`, "%m/%d/%Y %H:%i");

-- 第二步：修改列数据类型为TIMESTAMP
ALTER TABLE mytable
MODIFY COLUMN `Start Date` TIMESTAMP,
MODIFY COLUMN `End Date` TIMESTAMP;

方法二：通过临时列安全转换（推荐） #

如果担心直接更新出错，可先创建临时列存储转换后的数据，验证无误后再替换原列：

-- 1. 添加临时列
ALTER TABLE mytable
ADD COLUMN `Start Date Temp` TIMESTAMP,
ADD COLUMN `End Date Temp` TIMESTAMP;

-- 2. 将原列数据转换后存入临时列
UPDATE mytable
SET `Start Date Temp` = STR_TO_DATE(`Start Date`, "%m/%d/%Y %H:%i"),
    `End Date Temp` = STR_TO_DATE(`End Date`, "%m/%d/%Y %H:%i");

-- 3. 验证临时列数据正确后，删除原列并重命名临时列
ALTER TABLE mytable
DROP COLUMN `Start Date`,
DROP COLUMN `End Date`,
CHANGE COLUMN `Start Date Temp` `Start Date` TIMESTAMP,
CHANGE COLUMN `End Date Temp` `End Date` TIMESTAMP;

注意事项 #

• 确保STR_TO_DATE的格式符与你现有文本的格式完全匹配：如果文本包含秒数，需将格式符改为%m/%d/%Y %H:%i:%s。
• 若存在格式错误的无效数据，UPDATE会报错，可通过以下方式处理：

  -- 仅更新有效数据，无效数据设为NULL
  UPDATE mytable
  SET `Start Date` = CASE 
                      WHEN STR_TO_DATE(`Start Date`, "%m/%d/%Y %H:%i") IS NOT NULL 
                      THEN STR_TO_DATE(`Start Date`, "%m/%d/%Y %H:%i") 
                      ELSE NULL 
                    END,
      `End Date` = CASE 
                    WHEN STR_TO_DATE(`End Date`, "%m/%d/%Y %H:%i") IS NOT NULL 
                    THEN STR_TO_DATE(`End Date`, "%m/%d/%Y %H:%i") 
                    ELSE NULL 
                  END;
  

• 执行操作前请备份数据表，避免数据丢失。

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