替换存储在列中的逗号分隔列表中的值

-- 创建示例表
CREATE TABLE users (
  id INT,
  interests VARCHAR(255)
);

-- 插入示例数据
INSERT INTO users (id, interests)
VALUES (1, 'football,tennis,basketball'),
       (2, 'swimming,football,running');

-- 替换列表中的值
UPDATE users
SET interests = (
  SELECT GROUP_CONCAT(
    CASE
      WHEN interest = 'football' THEN 'basketball'
      ELSE interest
    END
    SEPARATOR ','
  )
  FROM (
    SELECT SUBSTRING_INDEX(SUBSTRING_INDEX(interests, ',', n), ',', -1) AS interest
    FROM users
    CROSS JOIN (
      SELECT 1 + units.i + tens.i * 10 AS n
      FROM (SELECT 0 AS i UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) AS units
      CROSS JOIN (SELECT 0 AS i UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) AS tens
    ) AS numbers
    WHERE n <= (LENGTH(interests) - LENGTH(REPLACE(interests, ',', ''))) + 1
      AND interests <> ''
  ) AS subquery
)
WHERE id = 1;