存储过程 为指定的数据库所有表增加若干字段

为指定数据库添加所有表增加若干字段

1.项目背景

公司项目需要将所有表增加修改时间、修改人、逻辑删除标注字段，有138张表。如果手动修改需要累出血，所以使用存储过程修改

2.存储过程源码

-- 判断是否有这个存储过程 有的话删除
DROP PROCEDURE IF EXISTS insert_multi;
-- 创建存储过程 参数为数据库名
CREATE  PROCEDURE insert_multi(IN dbname VARCHAR(200))
BEGIN

-- 声明判断标准位
DECLARE done INT DEFAULT 0;
-- 声明表名
DECLARE tableName VARCHAR(200);
-- 查询所有表名放入游标
DECLARE cur CURSOR FOR SELECT table_name FROM information_schema.TABLES WHERE table_schema = dbname;
-- 改变判断位
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;
-- 打开游标
OPEN cur;

REPEAT
-- 将cur的表名放入tableName
FETCH cur INTO tableName;
-- 将表名放入临时变量
SET @newname = tableName;
IF NOT done THEN
-- 查询字段是否已经存在
SET @SQL = CONCAT("SELECT count(*) into @count FROM information_schema.columns WHERE table_name='",@newname,"' and COLUMN_NAME='deleted_flag'  and table_schema='",dbname,"'");
PREPARE tmpstmt FROM @SQL;
EXECUTE tmpstmt ;
-- 如果不存在
IF  @count =0
THEN
-- 进行修改操作
 SET @SQL = CONCAT('alter table `',@newname,'` add deleted_flag TINYINT;');
PREPARE tmpstmt FROM @SQL;
EXECUTE tmpstmt;
END IF;

SET @SQL = CONCAT("SELECT count(*) into @count FROM information_schema.columns WHERE table_name='",@newname,"'AND COLUMN_NAME='creator_name' and table_schema='",dbname,"'");
PREPARE tmpstmt FROM @SQL;
EXECUTE tmpstmt ;

IF   @count =0
THEN
SET @SQL = CONCAT('alter table `',@newname,'` add creator_name varchar(50);');
PREPARE tmpstmt FROM @SQL;
EXECUTE tmpstmt;
END IF;


SET @SQL = CONCAT("SELECT count(*) into @count FROM information_schema.columns WHERE table_name='",@newname,"' AND COLUMN_NAME='updater_name' and table_schema='",dbname,"'");
PREPARE tmpstmt FROM @SQL;
EXECUTE tmpstmt;

IF   @count =0
THEN
SET @SQL = CONCAT('alter table `',@newname,'` add updater_name varchar(50);');
PREPARE tmpstmt FROM @SQL;
EXECUTE tmpstmt;
END IF;

SET @SQL = CONCAT("SELECT count(*) into @count FROM information_schema.columns WHERE table_name='",@newname,"' AND COLUMN_NAME='update_date' and table_schema='",dbname,"'");
PREPARE tmpstmt FROM @SQL;
EXECUTE tmpstmt;
IF   @count =0
THEN
SET @SQL = CONCAT('alter table `',@newname,'` add update_date datetime;');
PREPARE tmpstmt FROM @SQL;
EXECUTE tmpstmt;
END IF;

SET @SQL = CONCAT("SELECT count(*) into @count FROM information_schema.columns WHERE table_name='",@newname,"' AND COLUMN_NAME='updater' and table_schema='",dbname,"'");
PREPARE tmpstmt FROM @SQL;
EXECUTE tmpstmt ;
IF   @count =0
THEN
SET @SQL = CONCAT('alter table `',@newname,'` add updater BIGINT;');
PREPARE tmpstmt FROM @SQL;
EXECUTE tmpstmt;
END IF;

DEALLOCATE PREPARE tmpstmt;

END IF;
UNTIL done END REPEAT;
CLOSE cur;
END

call insert_multi('库名');