SQL存储过程当索引存在时删除，当列存在时删除，避免删除报错

ゝ一纸荒年。 2022-11-26 03:59 331阅读 0赞

USE `gsm2`;
    -- drop index 的存储过程：当存在时删除，因为 drop index 不支持 if exists
    DROP PROCEDURE IF EXISTS drop_index_if_exists;
    DELIMITER //
    create procedure drop_index_if_exists(IN p_tablename varchar(200), IN p_idxname VARCHAR(200))
    begin
      DECLARE str VARCHAR(250);
      set @str=concat(' drop index ',p_idxname,' on ',p_tablename); 
      select count(*) into @cnt from information_schema.statistics where table_name=p_tablename and index_name=p_idxname and TABLE_SCHEMA = 'gsm2';
      if @cnt >0 then 
        PREPARE stmt FROM @str;
        EXECUTE stmt ;
      end if;
    END
    //
    DELIMITER ;
    
    -- drop 列 的存储过程：当存在时删除，因为 drop column 不支持 if exists
    DROP PROCEDURE IF EXISTS drop_column_if_exists;
    DELIMITER //
    create procedure drop_column_if_exists(IN p_tablename varchar(200), IN p_columnname VARCHAR(200))
    begin
      DECLARE str VARCHAR(250);
      set @str=concat(' alter table ',p_tablename,' drop column ',p_columnname); 
      select count(*) into @cnt from information_schema.columns where table_name=p_tablename and column_name=p_columnname and TABLE_SCHEMA = 'gsm2' ;
      if @cnt >0 then 
        PREPARE stmt FROM @str;
        EXECUTE stmt ;
      end if;
    END
    //
    DELIMITER ;