Mysql存储过程批量新增字段

一、创键批量新增字段的存储过程

# 删除已存在的存储过程
DROP PROCEDURE IF EXISTS add_field_proc;
#创键存储过程
DELIMITER ;;
CREATE PROCEDURE add_field_proc(IN n INT)
BEGIN
    DECLARE i INT DEFAULT(1);
    WHILE (i <= n) DO
        # 拼接sql
        SET @sqlStr:=CONCAT("alter table tbl_mall_order",i," add (
                  `qd_at` int(11) DEFAULT '0' COMMENT '支付金额',
                  `pay_at` int(11) DEFAULT '0' COMMENT '卡付金额',
                  `pay_tp` char(2) DEFAULT '01' COMMENT ' 01: 全卡付,02 :券点, 03: 全点 04, 免费'
                 )");
        # 批量更新字段值
        #SET @indexStr = LPAD('1', 2, 0);
        #SET @sqlStr:=CONCAT("update tbl_qd_user_sub_acct",@indexStr," SET total_at = 120,balance_at = 120,exp_at = 0");
        # 打印sql
        #SELECT @sqlStr;
        #SET @sqlStr:=CONCAT("alter table tbl_mall_order",i, " add column `pavilion_id` varchar(8) NOT NULL DEFAULT '' COMMENT '地域馆id';");
        #SET @sqlStr:=CONCAT("alter table tbl_task_user_bonus",i, " drop column open_id");
        #用PREPARE将该字符串转化为MySQL脚本中的Prepared Statements(简写stmt) 
        PREPARE stmt from @sqlStr;
        #执行stmt即可获得输出结果
        EXECUTE stmt;
        #通过DEALLOCATE释放该Prepared Statements
        DEALLOCATE PREPARE stmt;  
        SET i = i + 1;
    END WHILE;  
END
;;
DELIMITER ;

二、使用示例

# 循环添加60张表字段
CALL add_field_proc(60)