基于MySQL多列值创建新列的实现方法

场景说明 #

当指定列的值为1时，将列名拼接成逗号分隔的字符串作为新列的值，比如genre_drama和genre_comedy均为1时，新列值为genre_drama, genre_comedy。

方案1：查询时临时生成新列 #

如果不需要永久存储结果，仅在查询时生成，用CONCAT_WS结合CASE语句即可实现：

SELECT
  *,
  CONCAT_WS(', ',
    CASE WHEN genre_drama = 1 THEN 'genre_drama' END,
    CASE WHEN genre_comedy = 1 THEN 'genre_comedy' END,
    CASE WHEN genre_action = 1 THEN 'genre_action' END
    -- 按需求继续添加其他需要判断的列
  ) AS combined_genres
FROM your_table;

• CONCAT_WS(', ', ...)：以逗号加空格作为分隔符，自动忽略NULL值，避免出现多余的分隔符
• CASE语句：仅当对应列值为1时返回列名字符串，否则返回NULL，不会被拼接进结果

方案2：永久新增列并手动更新 #

如果需要将结果永久存储在表中：

1. 先添加新列：

ALTER TABLE your_table ADD COLUMN combined_genres VARCHAR(255);

2. 批量更新列值：

UPDATE your_table
SET combined_genres = CONCAT_WS(', ',
    CASE WHEN genre_drama = 1 THEN 'genre_drama' END,
    CASE WHEN genre_comedy = 1 THEN 'genre_comedy' END,
    CASE WHEN genre_action = 1 THEN 'genre_action' END
    -- 其他列同理添加
  );

注意：后续原列值变化时，新列不会自动同步，需要重新执行UPDATE语句

方案3：使用生成列自动同步（MySQL 5.7+） #

如果希望新列随原列值变化自动更新，可以用MySQL的生成列（Generated Column）：

ALTER TABLE your_table
ADD COLUMN combined_genres VARCHAR(255)
GENERATED ALWAYS AS (
  CONCAT_WS(', ',
    CASE WHEN genre_drama = 1 THEN 'genre_drama' END,
    CASE WHEN genre_comedy = 1 THEN 'genre_comedy' END,
    CASE WHEN genre_action = 1 THEN 'genre_action' END
    -- 其他列
  )
) STORED; -- 可选STORED（存储实际值）或VIRTUAL（查询时计算）

内容的提问来源于stack exchange，提问作者Ryan Park