本文主要是介绍存储过程批量删除多个子表中的重复数据，希望对大家解决编程问题提供一定的参考价值，需要的开发者们随着小编来一起学习吧！
``````
use database;
set names utf8;-- 删除客户表子表中的重复数据
DELIMITER //
drop procedure if exists delFieldPro//
CREATE PROCEDURE delFieldPro()
BEGIN DECLARE tableName varchar(100) default ''; DECLARE done INT DEFAULT 0; DECLARE taskCursor CURSOR FOR select table_name from information_schema.tables where table_schema='uc_ipcc_cloud_ai' and table_name like 'tb_customer_%' and table_name not like 'tb_customer_contact%' ; DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1; OPEN taskCursor; REPEAT FETCH taskCursor INTO tableName; IF not done THEN set @sql0=concat('delete from tb_tmp;') ;PREPARE stmt0 from @sql0; execute stmt0;set @sql=concat('insert into tb_tmp select t.id from ( select max(id) id,customer_nbr,count(1) cu from ',tableName,' group by customer_nbr having cu>1) t') ;PREPARE stmt from @sql; execute stmt;set @sql1=concat('delete from ',tableName,' where id in (select id from tb_tmp)');PREPARE stmt1 from @sql1; execute stmt1;END IF; UNTIL done END REPEAT; CLOSE taskCursor;
END
//
DELIMITER ;
CALL delFieldPro();-- 给客户子表添加唯一索引
DELIMITER //
drop procedure if exists addUniqueIndexPro//
CREATE PROCEDURE addUniqueIndexPro()
BEGIN DECLARE tableName varchar(100) default ''; DECLARE done INT DEFAULT 0; DECLARE taskCursor CURSOR FOR select table_name from information_schema.tables where table_schema='database' and table_name like 'tb_customer_%' and table_name not like 'tb_ucpaas_customer_contact%' ; DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1; OPEN taskCursor; REPEAT FETCH taskCursor INTO tableName; IF not done THEN set @sql0=concat('ALTER TABLE ',tableName,' ADD unique(customer_nbr);') ;PREPARE stmt0 from @sql0; execute stmt0; END IF; UNTIL done END REPEAT; CLOSE taskCursor;
END
//
DELIMITER ;
CALL addUniqueIndexPro();-- 找出客户表中含有重复号码的表
DELIMITER //
drop procedure if exists proc_find_repeatNum;
create procedure proc_find_repeatNum (out sum varchar(1000))
BEGINDECLARE tableName varchar(100) default ''; DECLARE done INT DEFAULT 0; DECLARE _num int DEFAULT 0;DECLARE taskCursor CURSOR FOR select table_name from information_schema.tables where table_schema='database' and table_name like 'tb_customer_%' and table_name not like 'tb_ucpaas_customer_contact%' ; DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1; OPEN taskCursor; REPEAT FETCH taskCursor INTO tableName; IF not done THEN set @x ='';set @sql0=concat('select count(1) into @tnum from (select customer_nbr,count(1) cu from ',tableName,' group by customer_nbr having cu>1) t;') ;PREPARE stmt0 from @sql0; execute stmt0; deallocate prepare stmt0;SET _num = @tnum;if(_num > 0)then SET sum= concat(tableName,',');END IF;END IF; UNTIL done END REPEAT; CLOSE taskCursor;
end
//
DELIMITER ;
call proc_find_repeatNum(@sum);
-- 查询结果集
select @sum;---删除某张表的重复数据
select count(1) from (select customer_nbr,count(1) cu from tb_customer_ddd92456d085c9dd4976b59c90e48de6 group by customer_nbr having cu>1) t;
delete from tb_tmp;
insert into tb_tmp select t.id from ( select max(id) id,customer_nbr,count(1) cu from tb_customer_ddd92456d085c9dd4976b59c90e48de6 group by customer_nbr having cu>1) t ;
delete from tb_customer_ddd92456d085c9dd4976b59c90e48de6 where id in (select id from tb_tmp);``````
-- 删除多余索引
DELIMITER //
drop procedure if exists delUniqueIndexPro//
CREATE PROCEDURE delUniqueIndexPro()
BEGIN DECLARE tableName varchar(100) default ''; DECLARE done INT DEFAULT 0; DECLARE taskCursor CURSOR FOR select table_name from information_schema.tables where table_schema='uc_ipcc_cloud_ai' and table_name like 'tb_ucpaas_customer_%' and table_name not like 'tb_ucpaas_customer_contact%' ; DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1; OPEN taskCursor; REPEAT FETCH taskCursor INTO tableName; IF not done THEN IF EXISTS (SELECT * FROM information_schema.statistics WHERE table_schema='uc_ipcc_cloud_ai' AND table_name = tableName AND index_name = 'customer_nbr_2') THENset @sql0=concat('ALTER TABLE ',tableName,' drop index customer_nbr_2 ;') ;PREPARE stmt0 from @sql0; execute stmt0; END IF;IF EXISTS (SELECT * FROM information_schema.statistics WHERE table_schema='uc_ipcc_cloud_ai' AND table_name = tableName AND index_name = 'customer_nbr_3') THENset @sql0=concat('ALTER TABLE ',tableName,' drop index customer_nbr_3;') ;PREPARE stmt0 from @sql0; execute stmt0; END IF;IF EXISTS (SELECT * FROM information_schema.statistics WHERE table_schema='uc_ipcc_cloud_ai' AND table_name = tableName AND index_name = 'customer_nbr_4') THENset @sql0=concat('ALTER TABLE ',tableName,' drop index customer_nbr_4;') ;PREPARE stmt0 from @sql0; execute stmt0; END IF;IF EXISTS (SELECT * FROM information_schema.statistics WHERE table_schema='uc_ipcc_cloud_ai' AND table_name = tableName AND index_name = 'idx_customer_nbr') THENset @sql0=concat('ALTER TABLE ',tableName,' drop index idx_customer_nbr;') ;PREPARE stmt0 from @sql0; execute stmt0; END IF; END IF;UNTIL done END REPEAT; CLOSE taskCursor;
END
//
DELIMITER ;
CALL delUniqueIndexPro();
这篇关于存储过程批量删除多个子表中的重复数据的文章就介绍到这儿，希望我们推荐的文章对编程师们有所帮助！
