MySQL 5.7中以数组存储关联ID是否为合适的表关联方式？

嘿，这个问题问到点子上了——在MySQL 5.7里用数组（比如[5,2,12]这种格式）存储演员ID来关联影片和演员，既不恰当，也完全不符合关系型数据库的设计原则。下面给你掰扯清楚为什么，以及正确的做法是什么：

为什么这种方式不可取？ #

• 违反第一范式（1NF）：关系型数据库的核心要求之一是列的原子性——每个列的值都应该是不可再拆分的最小单元。数组是复合值，明显违背了这个规则，会彻底破坏数据的结构化特性。
• 查询与更新效率极低：
  • 如果你想找某个演员参演的所有影片，只能用FIND_IN_SET(actor_id, movie.actor_ids)或者正则匹配这种操作，这类查询完全没法利用索引，数据量稍微大一点就会慢到让人崩溃。
  • 要是需要移除某个演员的ID，你得做字符串替换操作，不仅麻烦，还很容易出错——比如不小心把12改成1，或者误删了其他演员的ID。
• 数据完整性无法保障：没法通过外键约束来确保数组里的演员ID确实存在于演员表中，很容易出现无效的ID值，导致数据不一致的问题。
• 统计聚合操作困难：比如统计某演员的参演影片数量、按演员分组统计影片数这类需求，用数组存储的话，写出来的SQL会极其复杂，而且性能极差。

正确的做法：使用多对多关联表 #

影片和演员是典型的多对多关系——一部影片有多个演员，一个演员可以参演多部影片。在关系型数据库里，处理这种关系的标准方案是新增一张关联表（中间表），把多对多关系拆成两个一对多关系。

举个具体的表结构例子：

1. 演员表actors：

   CREATE TABLE actors (
       actor_id INT PRIMARY KEY AUTO_INCREMENT,
       name VARCHAR(100) NOT NULL,
       birthday DATE
   );
   

2. 影片表movies：

   CREATE TABLE movies (
       movie_id INT PRIMARY KEY AUTO_INCREMENT,
       title VARCHAR(200) NOT NULL,
       release_date DATE
   );
   

3. 关联表movie_actors：

   CREATE TABLE movie_actors (
       movie_id INT NOT NULL,
       actor_id INT NOT NULL,
       PRIMARY KEY (movie_id, actor_id),
       FOREIGN KEY (movie_id) REFERENCES movies(movie_id),
       FOREIGN KEY (actor_id) REFERENCES actors(actor_id)
   );
   

举个实用的查询例子 #

比如要找演员ID为5的所有参演影片，SQL会非常简洁高效：

SELECT m.title, m.release_date
FROM movies m
JOIN movie_actors ma ON m.movie_id = ma.movie_id
WHERE ma.actor_id = 5;

这种查询可以利用movie_actors表上的联合索引，性能拉满，而且外键约束能确保关联的ID都是有效的，更新操作也简单——比如移除某个演员和影片的关联，只需要删除movie_actors表中对应的行即可。

就算你考虑用MySQL 5.7支持的JSON类型来存数组，也依然不如关联表靠谱——JSON的查询索引支持有限，在处理多对多关联的场景下，完全发挥不出关系型数据库的优势。

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