Postgres+Prisma中如何将重复行数据(JSON)存入单条SQL记录？

直接说结论：用JSON数组存演员列表不是通用的最佳实践，绝大多数业务场景下，更推荐用关联表的方式来存储。下面拆解两种方案的优劣和适用场景：

一、JSON数组方案的优缺点 #

优点 #

• 上手快：不用额外建表，Prisma里直接定义Json类型，表单提交的数组直接存，代码层面少写很多关联逻辑
• 单条查询省事：拿整个cast list的时候，一次就能把所有演员数据都捞出来，不用做多表join

缺点 #

• 数据容易出问题：数据库没法帮你校验talentId是否真的存在于talent表里，万一存了无效ID，后期排查麻烦；Prisma也没法自动做外键校验
• 查询灵活性极差：如果要找某个演员参与的所有cast list，或者统计某个角色的演员数量，JSON数组的查询语句会非常绕，性能也拉胯（Postgres对JSON的索引优化很有限）
• 维护麻烦：要改某个演员的角色或备注，得先把整个JSON数组取出来，修改完再存回去，并发更新的时候容易冲突
• 没法用Prisma的关联特性：想顺便拿到talent表的详细信息？得自己手动查完再拼接，没法用Prisma的include直接关联

二、推荐的关联表方案（行业通用最佳实践） #

建两个表就行：

1. cast_list：存你说的created_by、project、comment、shared_with这些字段
2. cast_list_item：专门存每个演员的关联信息，包含cast_list_id（关联cast_list的主键）、talent_id（关联talent的主键）、role、comment

对应的Prisma Schema大概是这样：

model CastList {
  id          Int              @id @default(autoincrement())
  createdBy   String
  project     String
  comment     String?
  sharedWith  String[]
  castItems   CastListItem[]
}

model CastListItem {
  id          Int              @id @default(autoincrement())
  castListId  Int
  talentId    Int
  role        String
  comment     String?
  castList    CastList         @relation(fields: [castListId], references: [id], onDelete: Cascade)
  talent      Talent           @relation(fields: [talentId], references: [id])
}

model Talent {
  id          Int              @id @default(autoincrement())
  // 这里加talent表的其他字段，比如姓名、联系方式之类的
  castItems   CastListItem[]
}

这个方案的好处 #

• 数据靠谱：数据库外键约束能确保talentId一定是有效的，不会出现脏数据
• 查询想怎么查就怎么查：找某个演员的所有参演列表、统计某个角色的数量，用Prisma或者SQL写起来都很直观，性能也有保障
• 维护方便：改单个演员的信息，只需要更新对应的cast_list_item记录，不会影响其他演员，并发冲突的概率极低
• 完美适配Prisma：用include: { castItems: { include: { talent: true } } }就能一次性拿到完整的cast list和对应的演员详情，代码简洁得很

三、什么时候可以考虑用JSON数组？ #

如果你的业务场景完全符合以下条件，那用JSON数组也没问题：

• 几乎不需要单独查询或修改数组里的单个演员信息
• 演员列表只是cast list的附属数据，不会单独被其他业务逻辑引用
• 对数据完整性要求不高（比如能接受偶尔出现无效ID，后续靠业务代码校验）

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