MySQL中逗号分隔字段的替代方案：多作曲家数据存储优化

针对你遇到的曲目与多作曲家关联的存储问题，最合规且可扩展的方案是采用多对多关联的范式化存储结构，彻底替代逗号分隔字符串或多字段的不良设计：

具体表结构设计 #

1. 保留原tracks表：只保留核心字段id（主键），移除原composer字段（若需兼容旧数据可保留，但不再用于存储多作曲家信息）。
2. 新建composers表：存储唯一的作曲家信息，避免重复数据：

   CREATE TABLE composers (
       id INT PRIMARY KEY AUTO_INCREMENT,
       name VARCHAR(255) NOT NULL UNIQUE
   );
   

   • id作为主键唯一标识每个作曲家，name设为唯一约束防止同一作曲家的重复或不一致录入。
3. 新建track_composers关联表：建立曲目与作曲家的多对多关联：

   CREATE TABLE track_composers (
       track_id INT NOT NULL,
       composer_id INT NOT NULL,
       PRIMARY KEY (track_id, composer_id),
       FOREIGN KEY (track_id) REFERENCES tracks(id) ON DELETE CASCADE,
       FOREIGN KEY (composer_id) REFERENCES composers(id) ON DELETE CASCADE
   );
   

   • 联合主键(track_id, composer_id)避免同一曲目重复关联同一作曲家；外键约束保证数据一致性，删除曲目或作曲家时会自动清理关联记录。

数据存储示例 #

比如原tracks表中id=1的曲目对应作曲家"贝多芬,莫扎特"，现在的存储方式：

• 在composers表插入两条记录：(1, '贝多芬')、(2, '莫扎特')
• 在track_composers表插入两条关联记录：(1, 1)、(1, 2)

这种方案的优势 #

• 无限扩展性：不管曲目关联多少个作曲家，只需在关联表新增记录即可，完全不用担心字段不够的问题。
• 查询高效灵活：比如要查询某作曲家的所有曲目，直接通过关联查询实现，性能远优于逗号分隔字段的LIKE匹配：

  SELECT t.* FROM tracks t
  JOIN track_composers tc ON t.id = tc.track_id
  JOIN composers c ON tc.composer_id = c.id
  WHERE c.name = '贝多芬';
  

• 数据一致性强：修改作曲家姓名只需在composers表更新一次，所有关联曲目都会同步生效，不会出现同一作曲家姓名拼写不一致的情况。
• 便于统计分析：轻松实现诸如"统计每个作曲家的曲目数量""查询有3个及以上作曲家的曲目"这类需求。

对比原方案的问题 #

• 逗号分隔字符串：无法高效精准查询，只能用LIKE '%作曲家%'，不仅性能差，还可能误匹配包含该名字的其他字符串（比如"贝多芬二世"会被误识别）；同时无法单独统计每个作曲家的关联数据。
• 新增composer2/composer3等字段：扩展性极差，一旦出现超过预设数量的作曲家就需要修改表结构；查询时要遍历多个字段，逻辑复杂且浪费存储空间（大部分曲目只会关联少量作曲家，空字段占比高）。

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