use orgs; DROP PROCEDURE IF EXISTS CreateTable; DELIMITER // CREATE PROCEDURE `CreateTable`(IN tableName varchar(100)) BEGIN -- 定义一个变量来存放机构id DECLARE id int(11); -- 定义遍历数据结束标志 DECLARE done BOOLEAN DEFAULT 0; DECLARE curid CURSOR FOR select orgId as id From common.t_org_info where `status`=2; -- 当REPEAT由于没有更多的行供循环而不能继续时出现这个条件,将done设置为1,此时REPEAT终止 DECLARE CONTINUE HANDLER FOR NOT FOUND SET done=1; OPEN curid; read_loop:LOOP FETCH curid into id; IF done THEN LEAVE read_loop; END IF; -- call AsyncReportDataByOrgId(id); Set @TableStr = CONCAT(tableName,id); select COUNT(1) into @CountNum from information_schema.tables where table_schema='orgs'and table_name = @TableStr; IF @CountNum = 0 THEN -- select @TableStr; Set @TableStr2 = CONCAT('table_template.',tableName,'template'); -- select @TableStr2; Set @STMT= CONCAT('CREATE TABLE ',@TableStr, ' As SELECT * FROM ',@TableStr2,' WHERE 1=0;'); select @STMT; -- Set @STMT=CONCAT(' show Create Table ', @TableStr); PREPARE ShowCreateTable From @STMT; EXECUTE ShowCreateTable ; DEALLOCATE PREPARE ShowCreateTable; END IF; END LOOP; CLOSE curid; END // call CreateTable('t_assessment_'); -- 查询org 表格获取所有机构Id。 -- 自动字段脚本 use orgs; DROP PROCEDURE IF EXISTS AddColumn; DELIMITER // CREATE PROCEDURE `AddColumn`(IN tableName varchar(100),IN columnName varchar(100),IN createScript varchar(500)) BEGIN -- 定义一个变量来存放机构id DECLARE id int(11); -- 定义遍历数据结束标志 DECLARE done BOOLEAN DEFAULT 0; DECLARE curid CURSOR FOR select orgId as id From common.t_org_info where `status`=2; -- 当REPEAT由于没有更多的行供循环而不能继续时出现这个条件,将done设置为1,此时REPEAT终止 DECLARE CONTINUE HANDLER FOR NOT FOUND SET done=1; OPEN curid; read_loop:LOOP FETCH curid into id; IF done THEN LEAVE read_loop; END IF; Set @TableStr = CONCAT(tableName,id); select COUNT(1) into @CountNum from information_schema.columns where table_schema='orgs'and table_name = @TableStr and column_name=columnName ; -- select @CountNum; IF @CountNum = 0 THEN -- select createScript; -- select @TableStr; Set @STMT= REPLACE( REPLACE(createScript,'<table>',@TableStr),'<column>',columnName); -- select @STMT; -- Set @STMT=CONCAT(' show Create Table ', @TableStr); PREPARE ShowCreateTable From @STMT; EXECUTE ShowCreateTable ; DEALLOCATE PREPARE ShowCreateTable; END IF; END LOOP; CLOSE curid; END // call AddColumn('t_activity_','extra','ALTER TABLE <table> ADD COLUMN `<column>` json NOT NULL COMMENT \'这里是备注\';');