Java开发中如何在数据库内比较整数列表实现匹配查询

现有如下Java实体定义：

public class Drink {
    Long id;
    String name;
    List<Integer> ingredients; // 存储配料对应的编号
}

示例数据：id = 1, name = Mojito, ingredients = [5,7,3,8]
核心需求：根据传入的配料列表查询匹配的饮品，需要评估两种实现方案的合理性。

方案1：全量拉取所有饮品数据到Java内存，遍历完成列表比对 #

• 适用场景极窄：仅当全库饮品数据量长期稳定在千条以内、业务无增长预期时，可以临时应急使用
• 缺陷非常明显：
  • 性能随数据量线性下降：全表扫描拉取数据的数据库IO开销、网络传输开销、内存遍历开销都会随着饮品数据增长持续升高，接口延迟会越来越高
  • 资源浪费严重：每次查询都要传输不需要的数据，并发上来后很容易打满数据库带宽、占满应用堆内存触发频繁GC
  • 无扩展性：只要业务数据量上涨就必须重构，投入产出比极低，不推荐作为正式实现方案

方案2：数据库层面完成整数列表比对 #

这个方案是生产环境的标准可行方案，但是要注意表结构的设计合理性，不要直接把整数列表序列化为逗号分隔字符串、JSON串存在单个字段中做模糊匹配，这类写法无法利用索引，性能极差。

推荐实现方式（关系型数据库通用） #

按照数据库范式拆成两张表存储，通过关联查询实现匹配，性能好、易扩展：

1. 饮品主表drink，存储饮品核心属性：

   CREATE TABLE drink (
       id BIGINT PRIMARY KEY COMMENT '饮品ID',
       name VARCHAR(255) NOT NULL COMMENT '饮品名称'
   );
   

2. 饮品-配料关联表drink_ingredient，存储每个饮品对应的配料编号，给配料ID建索引加速查询：

   CREATE TABLE drink_ingredient (
       id BIGINT PRIMARY KEY AUTO_INCREMENT COMMENT '主键',
       drink_id BIGINT NOT NULL COMMENT '关联饮品ID',
       ingredient_id INT NOT NULL COMMENT '配料编号',
       INDEX idx_ingredient(ingredient_id),
       UNIQUE KEY uk_drink_ingredient(drink_id, ingredient_id)
   );
   

根据不同的匹配规则，查询写法如下：

• 需求1：查询包含传入所有配料的饮品（比如传入[3,5,7,8]，匹配到包含这四个配料的Mojito，允许饮品有额外配料）
  传入配料个数为4，对应SQL：

  SELECT d.* FROM drink d
  INNER JOIN drink_ingredient di ON d.id = di.drink_id
  WHERE di.ingredient_id IN (3,5,7,8)
  GROUP BY d.id
  HAVING COUNT(DISTINCT di.ingredient_id) = 4;
  

• 需求2：查询配料列表和传入参数完全一致的饮品（饮品配料不多不少，和传入列表完全相等）
  对应SQL：

  SELECT d.* FROM drink d
  INNER JOIN drink_ingredient di ON d.id = di.drink_id
  GROUP BY d.id
  HAVING 
    -- 匹配到的传入配料个数等于传入总个数
    SUM(CASE WHEN di.ingredient_id IN (3,5,7,8) THEN 1 ELSE 0 END) = 4
    -- 饮品自身总配料个数等于传入总个数
    AND COUNT(DISTINCT di.ingredient_id) = 4;
  

如果非要用单字段存储列表（比如用PostgreSQL的数组类型、MySQL的JSON类型），也可以通过数据库内置函数实现匹配，比如MySQL可以用JSON_CONTAINS函数判断JSON数组的包含关系，但这类方案很难建立高效索引，数据量上涨后性能远不如拆关联表的方案，不推荐使用。

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