mysql 通过存储过程添加分表和分表的字段

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 \'这里是备注\';');