mysql删除以逗号隔开的字符串中的重复字符

SELECT * FROM products WHERE id = 1;

• 1.

id | name      | tags
---|-----------|-----------------
1  | 产品名称  | 苹果,香蕉,苹果,橙子

• 1.
• 2.
• 3.

SELECT
  id,
  name,
  GROUP_CONCAT(DISTINCT SUBSTRING_INDEX(SUBSTRING_INDEX(tags, ',', n.n), ',', 1)) AS unique_tags
FROM
  products,
  (SELECT 1 AS n UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4) n
GROUP BY
  id,
  name;

• 1.
• 2.
• 3.
• 4.
• 5.
• 6.
• 7.
• 8.
• 9.
• 10.

SELECT
  id,
  name,
  REPLACE(
    SUBSTRING_INDEX(
      SUBSTRING_INDEX(
        REPLACE(tags, ',', ', ') , ', ', ','), ', ', 1
      ), ', ', ','
    ) AS unique_tags
FROM
  products
WHERE
  id = 1;

• 1.
• 2.
• 3.
• 4.
• 5.
• 6.
• 7.
• 8.
• 9.
• 10.
• 11.
• 12.
• 13.

SELECT
  id,
  name,
  REGEXP_REPLACE(tags, ',(,|$)', ',') AS unique_tags
FROM
  products
WHERE
  id = 1;

• 1.
• 2.
• 3.
• 4.
• 5.
• 6.
• 7.
• 8.

-- 方法一：使用GROUP_CONCAT和DISTINCT
SELECT
  id,
  name,
  GROUP_CONCAT(DISTINCT SUBSTRING_INDEX(SUBSTRING_INDEX(tags, ',', n.n), ',', 1)) AS unique_tags
FROM
  products,
  (SELECT 1 AS n UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4) n
WHERE
  id = 1
GROUP BY
  id,
  name;

-- 方法二：使用REPLACE和SUBSTRING_INDEX
SELECT
  id,
  name,
  REPLACE(
    SUBSTRING_INDEX(
      SUBSTRING_INDEX(
        REPLACE(tags, ',', ', ') , ', ', ','), ', ', 1
      ), ', ', ','
    ) AS unique_tags
FROM
  products
WHERE
  id = 1;

-- 方法三：使用正则表达式
SELECT
  id,
  name,
  REGEXP_REPLACE(tags, ',(,|$)', ',') AS unique_tags
FROM
  products
WHERE
  id = 1;

• 1.
• 2.
• 3.
• 4.
• 5.
• 6.
• 7.
• 8.
• 9.
• 10.
• 11.
• 12.
• 13.
• 14.
• 15.
• 16.
• 17.
• 18.
• 19.
• 20.
• 21.
• 22.
• 23.
• 24.
• 25.
• 26.
• 27.
• 28.
• 29.
• 30.
• 31.
• 32.
• 33.
• 34.
• 35.
• 36.
• 37.
• 38.