转载： /weixin_42159024/article/details/81488788
实现功能：
升级数据库。通过遍历所有数据库，对每个数据库进行升级，通过新建表、字段升级。这里主要通过数据库名.表名来实现在同一个存储过程中切换不同数据库。
drop procedure if exists addColumn;
drop procedure if exists updateDataBase;
delimiter $$;
--创建存储过程：新增字段，传入参数：数据库名、表名、字段名、字段类型(int、VARCHAR...)
create procedure 'addColumn'(in dataName VARCHAR(100),in tableName VARCHAR(100),in columnName VARCHAR(100),in columnType VARCHAR(100))
BEGIN
DECLARE column_num int;
set @sqlstr=concat("select count(*) into @column_num from information_schema.columns where table_schema='",dataName,"' and table_name='",tableName,"' and column_name='",columnName,"'");
PREPARE sqlstr FROM @sqlstr;
EXECUTE sqlstr;
if @column_num=0 then
set @sqlstr=concat("alter table ",dataName,".",tableName," add column ",columnName,columnType);
PREPARE sqlstr FROM @sqlstr;
EXECUTE sqlstr;
end if;
END
$$
delimiter$$;
--创建存储过程：更新数据库，通过创建表及字段来升级数据库
drop procedure if exists updateDataBase;
delimiter$$;
create procedure 'updateDataBase'()
LANGUAGE SQL
NOT DETERMINISTIC
CONTAINS SQL
SQL SECURITY DEFINER
COMMENT ''
BEGIN
DECLARE dataName VARCHAR(100);
DECLARE s int DEFAULT 0;
--将以ticket_开头的数据库遍历
DECLARE consume CURSOR FOR select distinct table_schema as dataName from information_schema.'TABLES' where table_schema like 'ticket_%';
DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET s=1;
OPEN consume;
FETCH consume into dataName;
while s<>1 DO
call addColumn(dataName,'tbl_dc_neinfo','isPassed',' varchar(128) DEFAULT NULL');--调用存储过程，在表tbl_dc_neinfo中新建字段isPassed varchar(128) DEFAULT NULL
--新建表，在不同的数据库中
set @sql_create_table=concat(
"CREATE TABLE IF NOT EXISTS ",dataName,"tbl_dc_neinfo",
"(
'OID' bigint(20) NOT NULL AUTO_INCREMENT,
'字段2' 类型,
PRIMARY KEY('OID')
)ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8");
PREPARE sql_create_table FROM @sql_create_table;
EXECUTE sql_create_table;
FETCH consume INTO dataName;
end WHILE;
CLOSE consume;
END
$$;
delimiter$$;
call updateDataBase();
drop procedure if exists updateDataBase;
drop procedure if exists addColumn;
mysql存储过程遍历新增_MySQL存储过程：内部调用存储过程 存储过程实现遍历数据库建表以及修改字段...
