如何批量处理MySQL/MariaDB中特定前缀表的指定列？

核心思路 #

利用INFORMATION_SCHEMA查询符合前缀规则的表，动态生成批量UPDATE语句，避免手动逐个操作。

具体实现 #

1. 生成批量更新SQL语句 #

运行以下SQL，会直接输出所有目标表对应的更新命令：

SELECT CONCAT(
    'UPDATE `', TABLE_NAME, '` ',
    'SET date_created = FROM_UNIXTIME(other_date_created_column);'
) AS batch_update_sql
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_NAME LIKE 'prefix_mm_%'
  AND TABLE_TYPE = 'BASE TABLE'; -- 仅处理实体表，排除视图

• 若目标表的列名（如存储UNIX时间的列）不一致，需修改other_date_created_column为实际列名；
• 若需先验证转换结果，可将UPDATE语句替换为查询语句：

  SELECT CONCAT(
      'SELECT date_created, other_date_created_column, FROM_UNIXTIME(other_date_created_column) AS converted FROM `', TABLE_NAME, '` LIMIT 10;'
  ) AS batch_check_sql
  FROM INFORMATION_SCHEMA.TABLES
  WHERE TABLE_NAME LIKE 'prefix_mm_%'
    AND TABLE_TYPE = 'BASE TABLE';
  

2. 执行生成的SQL #

• 直接复制输出的所有UPDATE语句，在数据库客户端执行；
• 命令行环境可自动导出并执行：

  # 导出批量SQL到文件
  mysql -u 用户名 -p 数据库名 -e "SELECT CONCAT('UPDATE `', TABLE_NAME, '` SET date_created = FROM_UNIXTIME(other_date_created_column);') FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME LIKE 'prefix_mm_%' AND TABLE_TYPE='BASE TABLE';" > batch_update.sql
  # 执行批量SQL
  mysql -u 用户名 -p 数据库名 < batch_update.sql
  

关键注意事项 #

• 数据备份：批量更新前务必备份目标表，或使用事务保障可回滚：

  START TRANSACTION;
  -- 执行所有UPDATE语句
  -- 验证数据无误后提交，否则回滚
  COMMIT; -- 出错则执行 ROLLBACK;
  

• 列存在性校验：若不确定所有目标表都包含指定列，可加入列存在性过滤：

  SELECT CONCAT(
      'UPDATE `', t.TABLE_NAME, '` ',
      'SET date_created = FROM_UNIXTIME(other_date_created_column);'
  ) AS batch_update_sql
  FROM INFORMATION_SCHEMA.TABLES t
  JOIN INFORMATION_SCHEMA.COLUMNS c1 
    ON t.TABLE_NAME = c1.TABLE_NAME AND c1.COLUMN_NAME = 'date_created'
  JOIN INFORMATION_SCHEMA.COLUMNS c2 
    ON t.TABLE_NAME = c2.TABLE_NAME AND c2.COLUMN_NAME = 'other_date_created_column'
  WHERE t.TABLE_NAME LIKE 'prefix_mm_%'
    AND t.TABLE_TYPE = 'BASE TABLE';
  

• 大表优化：若表数据量较大，建议拆分批量更新，避免长时间锁表影响业务。

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