-- 动态执行传入sql
CREATE DEFINER=`root`@`localhost` PROCEDURE `executeSql`(in sqlstr VARCHAR(500))
BEGIN
  SET @query = CONCAT(sqlstr);
  PREPARE stmt FROM @query; -- 准备查询
  EXECUTE stmt; -- 执行查询
  DEALLOCATE PREPARE stmt; -- 释放查询
END



-- 调用：
SET @query = 'select * from table';
call executeSql(@query);

-- 传入项目id, 拼装相关清理数据的 sql
-- 依赖 上边的 executeSql
CREATE DEFINER=`root`@`localhost` PROCEDURE `clearByProjectId`(in pid VARCHAR(100))
BEGIN
  SET @pid = LOWER(pid);
  SET @query = CONCAT('DELETE FROM ', CONCAT(@pid,'producttype;'));
  call executeSql(@query);

  SET @query = CONCAT('UPDATE ', CONCAT(@pid,'projectarchives'),' SET endBatch = 0;');
  call executeSql(@query);
  
END


-- 调用： 
-- 清理项目下所有数据，替换项目ID执行即可
call clearByProjectId('Q7CCE2409953F4BD6B4F253997EA15237');

-- 清理所有项目数据，读取项目表，循环拼装清理sql, 并执行
-- 依赖 上边的 executeSql
CREATE DEFINER=`root`@`localhost` PROCEDURE `clearAll`()
BEGIN

  DECLARE done INT DEFAULT FALSE;
  DECLARE proId VARCHAR(100);
  DECLARE proName VARCHAR(100);
  
  
    
  DELETE from log; -- 清理日志
  DELETE from outcontroldatarule; -- 清理失控数据
  
  -- 循环项目，清理所有项目相关数据
  DECLARE pro CURSOR FOR SELECT id,name FROM project;
  OPEN pro;
  read_loop: LOOP
    FETCH pro INTO proId, proName;
    IF done THEN
      LEAVE read_loop;
    END IF;
    
    SET @pid = proId;
    
    SET @query = CONCAT('DELETE FROM ', CONCAT(@pid,'producttype;'));
    call executeSql(@query);

    
    SET @query = CONCAT('UPDATE ', CONCAT(@pid,'projectarchives'),' SET endBatch = 0;');
    call executeSql(@query);
 
  END LOOP;
  CLOSE pro;

END


-- 调用： 
-- 清理项目下所有数据
call clearAll()

-- 循环项目，修改项目表结构
CREATE DEFINER=`root`@`localhost` PROCEDURE `updateTable`()
BEGIN

  DECLARE done INT DEFAULT FALSE;
  DECLARE proId VARCHAR(100);
  DECLARE proName VARCHAR(100);
  DECLARE pro CURSOR FOR SELECT id,name FROM project;
  
  OPEN pro;
  read_loop: LOOP
    FETCH pro INTO proId, proName;
    IF done THEN
      LEAVE read_loop;
    END IF;
    
     -- IF NOT EXISTS 后可以增加 IF NOT EXISTS，但要高版本
    SET @query = CONCAT('ALTER TABLE `', proId,'projectdata` ADD COLUMN `volumeNumber` varchar(100) DEFAULT NULL;');
    call executeSql(@query);
    
 
  END LOOP;
  CLOSE pro;
END

-- 调用：
call updateTable();

SET @row_number = 0;
SELECT
  (@row_number:=@row_number + 1) AS num,
  id
FROM
  qb7c16daf71ab4ce880fe64baa3494554projectdata;