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 ;
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 ;