MYSQL使用函数批量新增表字段

存储过程函数批量插入表字段

公司所有表都有5个通用字段，现需要在5个通用字段之前新增3个通用字段（必须字段之前），大概600张表
问题是mysql只能用after在某字段之后新增，没办法再字段之前新增（垃圾MYSQL）
这里我使用查询information_schema表获取字段信息

初始表

CREATE TABLE `XXX` (
  `id` varchar(40) NOT NULL COMMENT 'ID',
  `create_dtme` datetime NOT NULL COMMENT '创建时间',
  `last_updtme` datetime NOT NULL COMMENT '最后更新时间',
  `create_user_id` varchar(50) NOT NULL DEFAULT '1' COMMENT '创建用户',
  `last_update_user_id` varchar(50) NOT NULL DEFAULT '1' COMMENT '更新用户',
  `cancelsign` char(1) NOT NULL DEFAULT 'n' COMMENT '删除',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='XXX表';

想要的结果表，需在create_dtme字段前新增三个字段

CREATE TABLE `XXX` (
  `id` varchar(40) NOT NULL COMMENT 'ID',
  `owner_user_id` varchar(50) NOT NULL DEFAULT '1' COMMENT '用户',
  `dorg_id` varchar(50) NOT NULL DEFAULT '1' COMMENT '组织id',
  `ddept_id` varchar(50) NOT NULL DEFAULT '1' COMMENT '部门id',
  `create_dtme` datetime NOT NULL COMMENT '创建时间',
  `last_updtme` datetime NOT NULL COMMENT '最后更新时间',
  `create_user_id` varchar(50) NOT NULL DEFAULT '1' COMMENT '创建用户',
  `last_update_user_id` varchar(50) NOT NULL DEFAULT '1' COMMENT '更新用户',
  `cancelsign` char(1) NOT NULL DEFAULT 'n' COMMENT '删除',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='XXX表';

1.创建存储过程

-- 分隔标记
delimiter $$
-- 创建存储过程
create procedure alterCur()

BEGIN
-- 声明变量
DECLARE tableName varchar(100);
DECLARE columnName varchar(100);
DECLARE done INT DEFAULT 0;
 
-- 创建游标，并设置游标所指的数据（这里设置ID不为1是因为ID为1的是总的大类）
DECLARE cur CURSOR for 
-- 这里注意information_schema中视图也会在里面，需要过滤视图
select 
c2.TABLE_NAME,
c2.COLUMN_NAME
from (
select TABLE_NAME,COLUMN_NAME,ORDINAL_POSITION from information_schema.`COLUMNS` 
where TABLE_SCHEMA = 'pasdb-sit' and COLUMN_NAME = 'create_dtme'
and TABLE_NAME not in ('视图1','视图2','视图3')
GROUP BY TABLE_NAME
) c1
LEFT JOIN information_schema.`COLUMNS` c2 on c1.TABLE_NAME = c2.TABLE_NAME and c1.ORDINAL_POSITION - 1 = c2.ORDINAL_POSITION and c2.TABLE_SCHEMA = 'pasdb-sit';
-- 游标执行完，即遍历结束。设置done的值为1
DECLARE CONTINUE HANDLER for not FOUND set done=1;

-- 开启游标
open cur;
-- 执行循环
posLoop:LOOP

-- 从游标中取出uid,pid,pcode
FETCH cur INTO tableName,columnName;

-- 如果done的值为1，即遍历结束，结束循环
        IF done=1 THEN
                LEAVE posLoop;
-- 注意，if语句需要添加END IF结束IF
END IF;
-- 以游标中取出的cid为索引，逐行更新分类表：某分类parent_ids值为它的父分类parent_ids值和它父类的id
START TRANSACTION;
set @table_name = tableName;
set @column_name = columnName;
SET @sqlcmd = concat('alter table ', @table_name, ' add column owner_user_id varchar(50) not null DEFAULT \'1\' comment \'用户\' AFTER `',@column_name,'`');
prepare stmt from @sqlcmd;
execute stmt;
deallocate prepare stmt;  

SET @sqlcmd = concat('alter table ', @table_name, ' add column dorg_id varchar(50) not null DEFAULT \'1\' comment \'组织id\' AFTER owner_user_id');
prepare stmt from @sqlcmd;
execute stmt;
deallocate prepare stmt;   

SET @sqlcmd = concat('alter table ', @table_name, ' add column ddept_id varchar(50) not null DEFAULT \'1\' comment \'部门id\' AFTER dorg_id');
prepare stmt from @sqlcmd;
execute stmt;
deallocate prepare stmt;   
-- 关闭循环
END LOOP posLoop;
-- 关闭游标
CLOSE cur;
-- 关闭分隔标记
END;

2.执行存储过程

CALL alterCur;

3.删除存储过程

DROP PROCEDURE alterCur;