删除重复的标签 MySQL [英] Delete duplicated tags MySQL

问题描述

我的 MySQL 数据库上有重复的标签，如下所示:

I have duplicated tags on my MySQL DB such as below:

| id  | tags                                |
+- ---+-------------------------------------+
| 3   | x,yz,z,x,x                          |
| 5   | a,b,c d,a,b,c d, d                  |
+-----+-------------------------------------+

如何执行可以删除重复标签的查询?

How can I execute a query that can remove the duplicated tags?

结果应该是:

| id  | tags                                |
+- ---+-------------------------------------+
| 3   | x,yz,z                              |
| 5   | a,b,c d, d                          |
+-----+-------------------------------------+

推荐答案

setup

create table overly_complex_tags
(
  id integer primary key not null,
  tags varchar(100) not null
);

insert into overly_complex_tags
( id, tags )
values
( 3   , 'x,yz,z,x,x'           ),
( 5   , 'a,b,c d,a,b,c d,d'    )
;

create view digits_v
as
SELECT 0 AS N 
UNION ALL 
SELECT 1 
UNION ALL 
SELECT 2 
UNION ALL 
SELECT 3 
UNION ALL 
SELECT 4 
UNION ALL 
SELECT 5 
UNION ALL 
SELECT 6 
UNION ALL 
SELECT 7 
UNION ALL 
SELECT 8 
UNION ALL 
SELECT 9
;

查询删除重复标签

update overly_complex_tags t
inner join
(
select id, group_concat(tag) as new_tags
from
(
select distinct t.id, substring_index(substring_index(t.tags, ',', n.n), ',', -1) tag
from overly_complex_tags t 
cross join
(
  select a.N + b.N * 10 + 1 n
  from digits_v a
  cross join digits_v b
  order by n
) n
where n.n <= 1 + (length(t.tags) - length(replace(t.tags, ',', '')))
) cleaned_tags
group by id
) updated_tags
on t.id = updated_tags.id
set t.tags = updated_tags.new_tags
;

输出

+----+-----------+
| id |   tags    |
+----+-----------+
|  3 | yz,z,x    |
|  5 | c d,a,d,b |
+----+-----------+

sqlfiddle

注意

上述解决方案的复杂性来自于没有正确的归一化结构.. 请注意，该解决方案使用了中间体标准化结构

the complexity of above solution comes from not having a properly normalised structure.. note that the solution uses an intermediate normalised structure

这篇关于删除重复的标签 MySQL的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！