怎样用脚本批量修改数据库表结构？

本文目录导读：

1. ⚠️ 重要安全提示
2. 场景一：MySQL / MariaDB
3. 场景二：PostgreSQL
4. 场景三：SQL Server
5. 推荐的更安全的方式（非脚本）
6. 总结步骤

批量修改数据库表结构（例如为所有表添加字段、修改字段类型、更改字符集等）通常需要结合元数据查询和动态 SQL来实现。

最常用的语言是 MySQL/MariaDB、PostgreSQL 和 SQL Server 的存储过程或脚本,以下是几种主流数据库的批量修改脚本示例。

⚠️ 重要安全提示

1. 绝对要在测试库先跑！ 生产环境执行前，务必在 staging 环境验证。
2. 先备份！ 执行修改前对目标数据库做完整备份或快照。
3. 锁表风险：ALTER TABLE 在大型表上可能导致长时间锁表，建议在低峰期或使用在线 DDL 工具（如 pt-online-schema-change for MySQL）。
4. 小心 INFORMATION_SCHEMA：查询时注意过滤掉系统库（mysql、information_schema、performance_schema、sys 等）。

MySQL / MariaDB

批量修改表的字符集（例如改为 utf8mb4）

-- 1. 先生成所有 ALTER 语句
SELECT CONCAT(
    'ALTER TABLE `', TABLE_SCHEMA, '`.`', TABLE_NAME, 
    '` CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;'
) AS '-- 请先检查，然后取消注释最后一行执行'
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_SCHEMA = 'your_db_name'       -- 替换为你的数据库名
  AND TABLE_TYPE = 'BASE TABLE';
-- 2. 确认无误后，复制上面生成的语句批量执行
-- 或者使用存储过程动态执行（不推荐直接在生产用，除非很熟悉）

存储过程版（MySQL）：

DELIMITER $$
CREATE PROCEDURE batch_alter_tables()
BEGIN
  DECLARE done INT DEFAULT 0;
  DECLARE tbl_name VARCHAR(255);
  DECLARE cur CURSOR FOR 
    SELECT TABLE_NAME 
    FROM INFORMATION_SCHEMA.TABLES 
    WHERE TABLE_SCHEMA = 'your_db_name' AND TABLE_TYPE = 'BASE TABLE';
  DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;
  OPEN cur;
  read_loop: LOOP
    FETCH cur INTO tbl_name;
    IF done THEN LEAVE read_loop; END IF;
    SET @sql = CONCAT('ALTER TABLE `your_db_name`.`', tbl_name, '` CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;');
    PREPARE stmt FROM @sql;
    EXECUTE stmt;
    DEALLOCATE PREPARE stmt;
  END LOOP;
  CLOSE cur;
END$$
DELIMITER ;
-- 调用
CALL batch_alter_tables();

批量给所有表添加字段（created_at）

-- 生成语句
SELECT CONCAT(
    'ALTER TABLE `', TABLE_SCHEMA, '`.`', TABLE_NAME, 
    '` ADD COLUMN `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP;'
) AS '-- 批处理语句'
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_SCHEMA = 'your_db_name'
  AND TABLE_TYPE = 'BASE TABLE';

PostgreSQL

PostgreSQL 无法直接在同一个事务中遍历执行 ALTER TABLE 的非标准操作，通常通过 DO 块或 PL/pgSQL 函数实现。

批量修改表字段类型（例如将所有 text 改为 varchar(500)）

DO $$
DECLARE
    rec RECORD;
    sql_text TEXT;
BEGIN
    FOR rec IN 
        SELECT table_schema, table_name, column_name
        FROM information_schema.columns
        WHERE table_schema = 'public'      -- 指定 schema
          AND data_type = 'text'           -- 目标旧类型
          AND table_name NOT LIKE 'pg_%'   -- 过滤系统表
          AND table_name NOT LIKE 'sql_%'
    LOOP
        sql_text := format(
            'ALTER TABLE %I.%I ALTER COLUMN %I TYPE VARCHAR(500);',
            rec.table_schema, rec.table_name, rec.column_name
        );
        -- 打印日志（推荐）
        RAISE NOTICE 'Executing: %', sql_text;
        -- 执行
        EXECUTE sql_text;
    END LOOP;
END $$;

批量添加字段（如果有则跳过）

DO $$
DECLARE
    rec RECORD;
BEGIN
    FOR rec IN 
        SELECT table_schema, table_name
        FROM information_schema.tables
        WHERE table_schema = 'public' 
          AND table_type = 'BASE TABLE'
          -- 排除已存在该字段的表
          AND table_name NOT IN (
              SELECT table_name 
              FROM information_schema.columns 
              WHERE column_name = 'new_col' 
                AND table_schema = 'public'
          )
    LOOP
        EXECUTE format(
            'ALTER TABLE %I.%I ADD COLUMN new_col INTEGER DEFAULT 0;',
            rec.table_schema, rec.table_name
        );
    END LOOP;
END $$;

SQL Server

批量添加 not null 默认值字段

-- 使用游标循环
DECLARE @TableName NVARCHAR(255)
DECLARE @SchemaName NVARCHAR(50) = 'dbo'
DECLARE @SQL NVARCHAR(MAX)
DECLARE table_cursor CURSOR FOR 
SELECT TABLE_SCHEMA, TABLE_NAME
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_TYPE = 'BASE TABLE'
  AND TABLE_CATALOG = 'YourDatabaseName'  -- 指定数据库
  AND TABLE_NAME NOT LIKE 'sys%'
OPEN table_cursor
FETCH NEXT FROM table_cursor INTO @SchemaName, @TableName
WHILE @@FETCH_STATUS = 0
BEGIN
    -- 构造 ALTER 语句
    SET @SQL = 'ALTER TABLE [' + @SchemaName + '].[' + @TableName + '] 
                ADD [is_active] BIT NOT NULL DEFAULT 1;'
    PRINT @SQL   -- 强烈建议先打印审查
    -- EXEC sp_executesql @SQL   -- 确认后取消注释执行
    FETCH NEXT FROM table_cursor INTO @SchemaName, @TableName
END
CLOSE table_cursor
DEALLOCATE table_cursor

批量修改字段长度（例如所有 varchar(50) 改为 varchar(100)）

SELECT 
    'ALTER TABLE [' + TABLE_SCHEMA + '].[' + TABLE_NAME + '] 
     ALTER COLUMN [' + COLUMN_NAME + '] VARCHAR(100) ' + 
     CASE WHEN IS_NULLABLE = 'NO' THEN 'NOT NULL' ELSE 'NULL' END + ';' AS AlterStatement
FROM INFORMATION_SCHEMA.COLUMNS
WHERE DATA_TYPE = 'varchar' 
  AND CHARACTER_MAXIMUM_LENGTH = 50
  AND TABLE_SCHEMA = 'dbo'
  AND TABLE_NAME NOT LIKE 'sys%';

推荐的更安全的方式（非脚本）

对于可能影响性能的 DDL 操作，脚本不是最佳选择,更推荐以下工具：

总结步骤

1. 查询元数据：使用 INFORMATION_SCHEMA 获得要修改的表/字段列表。
2. 生成 SQL 字符串：通过 CONCAT 或 FORMAT 构造 ALTER TABLE 语句。
3. 审查输出：永远不要直接执行动态 SQL，先 PRINT 或 SELECT 出所有生成的语句,人工快速扫一眼。
4. 分批执行：如果表非常多（几百个），建议分批次（每次 20-30 个表）执行,避免长时间占用元数据锁。
5. 记录日志：在执行过程中记录成功和失败的表,便于事后修复。