MYSQL去重最终版

DELIMITER ;


DROP PROCEDURE IF EXISTS P_DeRep_data ;


DELIMITER &&


CREATE PROCEDURE P_DeRep_data 
(


IN p_Rep_table VARCHAR(50),
IN p_Key_Columns VARCHAR(255), #唯一字段列表
IN p_Rep_Columns VARCHAR(255), #重复字段列表 --未列出的字段代表不需要关系。1-->N。前面为主要字段
IN p_Rep_Columns_Keep VARCHAR(255) #重复字段保留值，min max
)
top:BEGIN #定义存储过程顶部以方便跳出存储过程
 # declare p_condition varchar(255) Default '';  默认值的定义方法
# 创建表
DECLARE p_Auto_Column VARCHAR(50);
DECLARE var_sql VARCHAR(4000);




IF  NOT EXISTS (SELECT 1 FROM information_schema.`TABLES` 
WHERE table_schema=DATABASE() AND table_name =p_Rep_table) THEN 
SELECT '数据表不存在';
LEAVE top; #强制跳出
END IF ;


SELECT @p_Auto_Column:=column_name FROM information_schema.`columns`  
WHERE table_schema=DATABASE() AND Extra='auto_increment' AND table_name =p_Rep_table;






SET @Group_List='';
SET @Group2_List='';
SET @Agg_List='';
SET @Join_List='';
SET @Where_List='';
SET @Full_Delete='';
# 有id的






SELECT 
 @Group_List:=CONCAT(@Group_List,',',COLUMN_NAME) #有无id都一样
,@Group2_List:=CONCAT(@Group2_List,',B.',COLUMN_NAME) #有无id都一样
,@Join_List:=CONCAT(@Join_List,' and A.',COLUMN_NAME,'=B.',COLUMN_NAME)
,@Right_Null:=NULLIF(@p_Auto_Column,COLUMN_NAME)
,@Full_Delete:=CONCAT(@Full_Delete,' and ',COLUMN_NAME,'='',',COLUMN_NAME,',''')
FROM 
(
SELECT a.`COLUMN_NAME`
,FIND_IN_SET(a.`COLUMN_NAME`, p_Key_Columns) AS iOrder
 FROM information_schema.`COLUMNS` a
WHERE table_schema=DATABASE() AND table_name=p_Rep_table
ORDER BY iOrder
) AS a
WHERE a.iOrder>=1
;




SELECT 
 @Agg_List:=CONCAT(@Agg_List,',',(SUBSTRING(p_Rep_Columns_Keep,(iOrder-1)*4+1,3)),'(',COLUMN_NAME,') as ',COLUMN_NAME)#有无id都一样
,@Join_List:=(CASE WHEN iOrder=1 THEN CONCAT(@Join_List,' and A.',COLUMN_NAME,'=B.',COLUMN_NAME) ELSE @Join_List END)
,@Where_List:=(CASE WHEN iOrder>1 THEN CONCAT(@Where_List,' and A.',COLUMN_NAME,'=B.',COLUMN_NAME) ELSE @Where_List END)
FROM 
(
SELECT a.`COLUMN_NAME`
,FIND_IN_SET(a.`COLUMN_NAME`, p_Rep_Columns) AS iOrder
 FROM information_schema.`COLUMNS` a
WHERE table_schema=DATABASE() AND table_name=p_Rep_table
ORDER BY iOrder
) AS a
WHERE a.iOrder>=1
;


SET @Group_List=RIGHT(@Group_List,(CHAR_LENGTH(@Group_List)-1));
SET @Group2_List=RIGHT(@Group2_List,(CHAR_LENGTH(@Group2_List)-1));
SET @Join_List=RIGHT(@Join_List,(CHAR_LENGTH(@Join_List)-4));
SET @Full_Delete=RIGHT(@Full_Delete,(CHAR_LENGTH(@Full_Delete)-4));


IF @p_Auto_Column IS  NOT NULL THEN 


SET @Where_List=CONCAT(' where ',RIGHT(@Where_List,(CHAR_LENGTH(@Where_List)-4)));
SET @p_sql=CONCAT('
delete B2
from 
(select Max(B.',@p_Auto_Column,') as ',@p_Auto_Column,'
from 
(select ',@Group_List,@Agg_List,' ',' 
from ',p_Rep_table,' group by ',@Group_List,') as A right join ',p_Rep_table,' as B
on ',@Join_List,' ',@Where_List,'
group by ',@Group2_List,') as A2
right join ',p_Rep_table,' as B2
on A2.',@p_Auto_Column,'=B2.',@p_Auto_Column,'
where A2.',@Right_Null,' is null ;'
);


PREPARE stmt FROM @p_sql;
EXECUTE stmt; 


ELSE 
#基础去重。非完全重复的去重
SET @p_sql=CONCAT('
delete B
from 
(select ',@Group_List,@Agg_List,' ',' 
from ',p_Rep_table,' group by ',@Group_List,') as A right join ',p_Rep_table,' as B
on ',@Join_List,'
',@Where_List,'
where A.',@Right_Null,' is null ;'
);
PREPARE stmt FROM @p_sql;
EXECUTE stmt; 






# select @p_sql;
# 可删除全部重复数据的存储过程体
SET @p_sql2=CONCAT('
DECLARE var_sql VARCHAR(1000);
DECLARE var_Where VARCHAR(1000);
DECLARE var_Limit_num VARCHAR(1000);
DECLARE var_table VARCHAR(1000) DEFAULT ''',p_Rep_table,''';
DECLARE var_done INT DEFAULT FALSE;
DECLARE cur_delete_rep_full CURSOR FOR
SELECT CONCAT(''delete from ',p_Rep_table,' where ',@Full_Delete,' limit '',COUNT(1)-1,'';'')
FROM ',p_Rep_table,' GROUP BY ',@Group_List,' HAVING COUNT(1)>1;
 
DECLARE CONTINUE HANDLER FOR NOT FOUND SET var_done = TRUE;


IF EXISTS (SELECT STATUS,bo_id 
FROM ',p_Rep_table,' GROUP BY ',@Group_List,'
HAVING COUNT(1)>1) THEN


 OPEN cur_delete_rep_full;
 read_loop:LOOP
   FETCH cur_delete_rep_full INTO var_sql;
   IF var_done THEN
     LEAVE read_loop;
   END IF;


   SET @p_sql=var_sql;
   PREPARE stmt FROM @p_sql;
   EXECUTE stmt; 
   END LOOP;
 CLOSE cur_delete_rep_full;
END IF ;


');




# 使用临时表保存数据去重条件
DROP TEMPORARY TABLE IF EXISTS full_dep_condition;


CREATE TEMPORARY TABLE full_dep_condition(id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,cSql VARCHAR(1000));






SET @p_sql3=CONCAT('
insert into full_dep_condition(cSql)
SELECT CONCAT(''delete from ',p_Rep_table,' where ',@Full_Delete,' limit '',COUNT(1)-1,'';'')
FROM ',p_Rep_table,' GROUP BY ',@Group_List,' HAVING COUNT(1)>1;');






PREPARE stmt FROM @p_sql3;
EXECUTE stmt; 


SELECT @m:=MIN(id),@n:=MAX(id) FROM full_dep_condition;


WHILE @m<=@n
DO 

SET @p_sql4=(SELECT cSQL FROM full_dep_condition WHERE id=@m);
PREPARE stmt FROM @p_sql4;
EXECUTE stmt; 


SET @m=@m+1;
END WHILE;
END IF;






END;


&&


DELIMITER ;