mysql - 删除重复的标签MySQL

我在MySQL数据库上复制了如下标记：

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

如何执行可以删除重复标记的查询？
结果应该是：

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

最佳答案

设置

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
笔记
上述解决方案的复杂性来自于没有正确的解决方案。
标准化结构。。注意，该解决方案使用一个
标准化结构