求一个 MySQL的存储过程，给一个数据库里面所有的表都追加一个字段（表名没有规律）

发布网友发布时间：2022-12-17 02:16

共1个回答

热心网友时间：2023-09-13 07:21

方案一: 存储过程

DROP PROCEDURE IF EXISTS SP_COLUMN_ADD;
DELIMITER $$
CREATE PROCEDURE SP_COLUMN_ADD()
BEGIN

  DECLARE command VARCHAR(200);

  DECLARE founded INT DEFAULT 1;
  DECLARE column_plicated BOOLEAN DEFAULT FALSE;

  #把这个游标的语句查下,是否满足需求
  DECLARE cur_sleest CURSOR FOR
   SELECT CONCAT('ALTER TABLE ', TABLE_NAME, ' ADD COLUMN YOUR_COLUMN YOUR_TYPE;') AS SQL_COMMAND
          #eg: `ALTER TABLE TMP_SLEEST ADD COLUMN C1 INT,ADD COLUMN C2 VARCHAR(2);`
     FROM INFORMATION_SCHEMA.TABLES
    WHERE TABLE_SCHEMA="yourschema"
      AND TABLE_TYPE="BASE TABLE";

  DECLARE CONTINUE HANDLER FOR NOT FOUND SET founded = 0;
  DECLARE CONTINUE HANDLER FOR 1060 SET column_plicated = true;

  OPEN cur_sleest;

  sleet_loop : LOOP
    FETCH cur_sleest INTO command;
    IF founded = 0 THEN LEAVE sleet_loop; END IF;
    SET @sql := command;
    PREPARE stmt FROM @sql;
    EXECUTE stmt;
    DEALLOCATE PREPARE stmt;
  END LOOP sleet_loop;

  CLOSE cur_sleest;
END$$
DELIMITER ;

方案二: 扫出指令集合到本地文件,贴到命令行执行

SELECT CONCAT("ALTER TABLE ", TABLE_NAME," ADD COLUMN YOUR_COLUMN YOUR_TYPE;")
  FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_SCHEMA="yourschema"
   AND TABLE_TYPE="BASE TABLE"
  INTO OUTFILE 'D:\\add-column.sql'

不推荐存储过程, 因为可能要处理1060(plicated column)等问题, 存储过程不是很好弄,中间过程也不好跟踪, 不一定能查出哪里错了;

第二种方案直接在命令行中贴上,每一个句话正确错误一看就明了.