MYSQL 批量增加删除字段

1、批量增加字段

DROP PROCEDURE IF EXISTS gcloud.batchAddFiled;

CREATE PROCEDURE gcloud.batchAddFiled (
    schema_name VARCHAR(100)
)
BEGIN
    DECLARE target_table_name VARCHAR(100) DEFAULT '';
    DECLARE sql_script text DEFAULT '';
    DECLARE done INT DEFAULT 0;
    DECLARE curl CURSOR FOR
        SELECT table_name
        FROM information_schema.TABLES
        WHERE table_schema = schema_name;
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;
    OPEN curl;
    REPEAT 
        FETCH curl INTO target_table_name;
        IF NOT done
        AND target_table_name <> 'COM_PUNISH'
        AND target_table_name <> 'AUTH_EMPLOYEE'
        AND target_table_name <> 'AUTH_LOG' THEN
            SET @before_filed = (
                SELECT COLUMN_NAME
                FROM information_schema.COLUMNS
                WHERE TABLE_NAME = target_table_name
                    AND TABLE_SCHEMA = schema_name
                ORDER BY ORDINAL_POSITION DESC
                LIMIT 9, 1
            );
            SET @SQL = concat('alter table ',schema_name,'.`',target_table_name);
            SET @SQL = concat(@SQL, '` add `MERCHANT_ID` bigint(20) ');
            #纯粹是因为简书的代码显示区太窄才分2行
            IF @before_filed IS NOT NULL THEN
                SET @SQL = concat(@SQL, ' AFTER `', @before_filed, '`;');
            ELSE 
                SET @SQL = concat(@SQL, ';');
            END IF;
            PREPARE stmt FROM @SQL;
            EXECUTE stmt;
            SET sql_script = CONCAT(sql_script, @SQL, '\n');
        END IF;
    UNTIL done
    END REPEAT;
    CLOSE curl;
    SELECT CONCAT('This Sql EXECUTE done.\n', sql_script);
END;

2、批量删除字段

DROP PROCEDURE IF   EXISTS gcloud.batchRemoveFiled;

CREATE PROCEDURE gcloud.`batchRemoveFiled` ( schema_name VARCHAR ( 100 ) ) BEGIN
    DECLARE target_table_name VARCHAR(100) DEFAULT '';
    DECLARE     oneAddr VARCHAR ( 100 ) DEFAULT '';
    DECLARE     sql_script text DEFAULT '';
    DECLARE     done INT DEFAULT 0;
    DECLARE     curl CURSOR FOR SELECT      table_name  FROM        information_schema.TABLES   WHERE       table_schema = schema_name;
    DECLARE     CONTINUE HANDLER FOR NOT FOUND      SET done = 1 ;
    OPEN curl;
    REPEAT
            FETCH curl INTO target_table_name;
        IF
            NOT done 
            AND target_table_name <> 'COM_PUNISH' 
            AND target_table_name <> 'AUTH_EMPLOYEE' 
            AND target_table_name <> 'AUTH_LOG' THEN
            
            #查找位置
            
                SET @before_filed = (
                SELECT COLUMN_NAME
                FROM information_schema.COLUMNS
                WHERE TABLE_NAME = target_table_name
                    AND TABLE_SCHEMA = schema_name
                ORDER BY ORDINAL_POSITION DESC
                LIMIT 9, 1
            );
                        
                        IF @before_filed = 'MERCHANT_ID' THEN
                            set @sql=concat('alter table ',schema_name,'.`',target_table_name,'` drop column `MERCHANT_ID` ');
                            PREPARE stmt FROM @SQL;
                            EXECUTE stmt;
                            SET sql_script = CONCAT( sql_script, @SQL, '\n' );
                            
                        END IF;

        END IF;
        UNTIL done 
    END REPEAT;
    CLOSE curl;
    SELECT
        CONCAT( 'This Sql EXECUTE done.\n', sql_script );

END ;

3、使用方法

call gcloud.batchRemoveFiled('test');

call gcloud.batchAddFiled('test');