-- ----------------------------

-- Procedure structure for batchAddField

-- ----------------------------

DROP PROCEDURE IF EXISTS `batchAddField`;

DELIMITER ;;

CREATE DEFINER=`root`@`%` PROCEDURE `batchAddField`(IN `tableName` varchar(100),IN `fieldName` varchar(100),IN `fieldType` varchar(100),IN `defaultValue` varchar(1000))

BEGIN

    #数据库名称

  DECLARE schemaName VARCHAR(100); 

  #声明结束标识

    DECLARE end_flag int DEFAULT 0;

    #声明游标 curosr ,查找所有“MyDB_”开头的数据库

    DECLARE curosr CURSOR FOR select  TABLE_SCHEMA   from information_schema.`TABLES`  WHERE TABLE_SCHEMA like 'MyDB_%' and table_name = tableName;

  #设置终止标志

    DECLARE CONTINUE HANDLER FOR NOT FOUND SET end_flag=1;

    #打开游标

    OPEN curosr;

    #遍历游标

    loop_label:  LOOP

        FETCH curosr INTO schemaName;#获取当前游标指针记录，取出值赋给自定义的变量

        IF end_flag > 0 THEN

            LEAVE  loop_label;

        END IF;

        SET @sqlStr=CONCAT('alter table ',schemaName,'.',tableName,' add column `',fieldName,'`  ',fieldType,' DEFAULT ''',defaultValue,''' ');

    PREPARE stmt from @sqlStr;

    EXECUTE stmt;

    END LOOP;

  #关闭游标

    close curosr;

END

;;

DELIMITER ;

batchAddField('Users','score','int(8)','0') //添加int型字段score默认值：0

batchAddField('Users','memo','varchar(1000)','blank') //添加varchar型字段memo默认值：blank