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

wen 实用脚本 2026-07-24 3

本文目录导读：

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

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

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

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

⚠️ 重要安全提示

绝对要在测试库先跑！ 生产环境执行前，务必在 staging 环境验证。
先备份！ 执行修改前对目标数据库做完整备份或快照。
锁表风险：ALTER TABLE 在大型表上可能导致长时间锁表，建议在低峰期或使用在线 DDL 工具（如 pt-online-schema-change for MySQL）。
小心 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%';

场景	推荐工具	理由
MySQL	`pt-online-schema-change` (Percona Toolkit)	不锁表，在线修改
PostgreSQL	`pgroll` 或直接使用 `ALTER TABLE ... USING`	支持在线且可回滚
SQL Server	使用 SSMS 的“生成脚本”向导，或使用第三方工具（如 ApexSQL）	可视化勾选，避免手写语法错误