如何编写迁移将array of string类型的template_id列转为integer类型

嘿，这个需求我太熟了——把单元素字符串数组的template_id转成整数类型对吧？我给你分不同数据库场景写实操性强的迁移脚本，都是我平时干活常用的套路：

PostgreSQL 场景（原生支持数组类型） #

PostgreSQL对数组类型的处理很直接，因为你的数组都是单元素，我们可以分四步安全完成迁移：

• 先给表加一个临时整数列，用来存转换后的数据：

  ALTER TABLE your_table ADD COLUMN temp_template_id integer;
  

• 把原数组里的字符串提取出来，转成整数存入临时列：

  UPDATE your_table SET temp_template_id = template_id[1]::integer;
  

  这里template_id[1]取数组的第一个元素（PostgreSQL数组索引从1开始），::integer是PostgreSQL的类型转换语法。
• 关键验证步骤：确认所有数据都转换成功，没有空值：

  SELECT * FROM your_table WHERE temp_template_id IS NULL;
  

  如果返回空结果，说明转换没问题。
• 最后替换原列：删除旧的数组列，把临时列重命名为template_id：

  ALTER TABLE your_table DROP COLUMN template_id;
  ALTER TABLE your_table RENAME COLUMN temp_template_id TO template_id;
  

MySQL 场景（用JSON类型存储数组） #

MySQL没有原生数组类型，通常是用JSON字段来存这类数组。转换步骤类似：

• 添加临时整数列：

  ALTER TABLE your_table ADD COLUMN temp_template_id INT;
  

• 提取JSON数组里的元素，去掉引号后转成整数：

  UPDATE your_table SET temp_template_id = CAST(JSON_UNQUOTE(JSON_EXTRACT(template_id, '$[0]')) AS UNSIGNED);
  

  解释一下：JSON_EXTRACT(template_id, '$[0]')取出数组第一个元素（JSON索引从0开始），返回的是带双引号的字符串（比如"2"），JSON_UNQUOTE去掉引号变成2，最后CAST(... AS UNSIGNED)转成无符号整数。
• 验证转换结果：

  SELECT * FROM your_table WHERE temp_template_id IS NULL;
  

• 替换原列：

  ALTER TABLE your_table DROP COLUMN template_id;
  ALTER TABLE your_table RENAME COLUMN temp_template_id TO template_id;
  

必看的安全注意事项 #

• 先备份！先备份！先备份！ 迁移前一定要给表做全量备份，避免操作失误导致数据丢失。
• 先在测试环境跑一遍脚本，确认数据转换完全符合预期后，再到生产环境执行。
• 如果template_id列有索引、外键或者触发器关联，要先删除这些依赖，迁移完成后再重新创建。
• 如果表数据量很大，建议把UPDATE语句拆成批量执行，避免锁表时间过长影响业务。

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