MySQL 常用的 procedure

循环执行

DROP PROCEDURE idata;
delimiter ;;
create procedure idata()
begin
  declare i int;
  set i=1;
  while(i<=100000)do
    insert into t values(i, i, i);
    set i=i+1;
  end while;
end;;
delimiter ;
call idata();

删除索引

-- drop index 的存储过程：当存在时删除，因为 drop index 不支持 if exists
DROP PROCEDURE IF EXISTS drop_index_if_exists;
DELIMITER //
create procedure drop_index_if_exists(IN p_db varchar(200),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 = p_db;
  if @cnt >0 then 
    PREPARE stmt FROM @str;
    EXECUTE stmt ;
  end if;
END
//
DELIMITER ;

删除列

-- drop column 的存储过程：当存在时删除，因为 drop column 不支持 if exists
DROP PROCEDURE IF EXISTS drop_column_if_exists;
DELIMITER //
create procedure drop_column_if_exists(IN p_db varchar(200),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 = p_db;
  if @cnt >0 then 
    PREPARE stmt FROM @str;
    EXECUTE stmt ;
  end if;
END
//
DELIMITER ;

条件不满足立刻返回

DELIMITER //
create procedure check_duplicate()
label:begin
  select count(*) into @cnt from (select count(*) from dc_fb_form_tables group by `season_uuid`, `team_uuid`, `round_uuid`, `group_uuid` having count(*) > 1 order by null) t; 
  select concat('check dc_fb_form_tables count is ', @cnt,',') ;
  if @cnt > 0 then
    select 'fail';
    LEAVE label;
  end if;
  
  select count(*) into @cnt from (select count(*) from dc_fb_injuries group by `person_uuid`, `start_date` having count(*) > 1 order by null) t; 
  select concat('check dc_fb_injuries count is ', @cnt,',') ;
  if @cnt > 0 then
    select 'fail';
    LEAVE label;
  end if;
  
  select count(*) into @cnt from (select count(*) from dc_fb_match_event group by `match_uuid`, `event_type`, `code`, `person_uuid`, `team_uuid`, `minute` having count(*) > 1 order by null) t; 
  select concat('check dc_fb_match_event count is ', @cnt,',') ;
  if @cnt > 0 then
    select 'fail';
    LEAVE label;
  end if;
  
END
//
DELIMITER ;