SQL迁移中字符串转Decimal类型：处理异常格式数据问题

1. 先清理异常数据 #

核心逻辑是：对包含多个小数点的异常值，保留最后一个小数点，将前面所有小数点替换为空字符串。比如把'120.45.32'转为'12045.32'。

MySQL 清理语句 #

UPDATE your_table
SET price = CONCAT(
  REPLACE(SUBSTRING(price, 1, LENGTH(price) - LOCATE('.', REVERSE(price))), '.', ''),
  SUBSTRING(price, LENGTH(price) - LOCATE('.', REVERSE(price)) + 1)
)
WHERE price LIKE '%.%.%'; -- 匹配含至少两个小数点的异常值

PostgreSQL 清理语句 #

UPDATE your_table
SET price = CONCAT(
  REPLACE(SUBSTRING(price FROM 1 TO CHAR_LENGTH(price) - POSITION('.' IN REVERSE(price))), '.', ''),
  SUBSTRING(price FROM CHAR_LENGTH(price) - POSITION('.' IN REVERSE(price)) + 1)
)
WHERE price LIKE '%.%.%';

2. 执行类型转换 #

异常数据清理完成后，再修改列类型，此时正常数值不会被错误截断：

MySQL 类型转换 #

ALTER TABLE your_table
MODIFY COLUMN price DECIMAL(10,2);

PostgreSQL 类型转换 #

ALTER TABLE your_table
ALTER COLUMN price TYPE DECIMAL(10,2) USING price::DECIMAL(10,2);

3. 迁移文件示例（以Ruby on Rails为例） #

确保先执行数据清理，再修改列类型：

class ChangePriceToDecimal < ActiveRecord::Migration[7.0]
  def up
    # 清理异常数据
    execute <<-SQL
      UPDATE products
      SET price = CONCAT(
        REPLACE(SUBSTRING(price, 1, LENGTH(price) - LOCATE('.', REVERSE(price))), '.', ''),
        SUBSTRING(price, LENGTH(price) - LOCATE('.', REVERSE(price)) + 1)
      )
      WHERE price LIKE '%.%.%'
    SQL

    # 修改列类型
    change_column :products, :price, :decimal, precision: 10, scale: 2
  end

  def down
    # 回滚操作：转回string类型
    change_column :products, :price, :string
  end
end

注意事项 #

• 执行数据更新前，务必备份数据或在测试环境验证逻辑正确性。
• 若存在其他异常格式（如含非数字字符），需额外用正则匹配过滤处理。
• 不同数据库的字符串函数语法有差异，需根据实际使用的数据库调整函数（比如SQL Server用CHARINDEX替代LOCATE）。

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