MySQL查询：如何按多列筛选匹配指定值集的行及统计

完全可以通过MySQL直接实现需求，且能解决PHP处理时的高内存、慢响应问题，以下是针对不同长度外部列表的查询语句：

外部列表含3个值（示例：['mirror', 'seat', 'door']） #

查询匹配行 #

SELECT part1, part2, part3
FROM parts
WHERE 
  ('mirror' IN (part1, part2, part3))
  AND ('seat' IN (part1, part2, part3))
  AND ('door' IN (part1, part2, part3));

统计匹配行数 #

SELECT COUNT(*) AS match_count
FROM parts
WHERE 
  ('mirror' IN (part1, part2, part3))
  AND ('seat' IN (part1, part2, part3))
  AND ('door' IN (part1, part2, part3));

外部列表含2个值（示例：['mirror', 'door']） #

查询匹配行 #

SELECT part1, part2, part3
FROM parts
WHERE 
  ('mirror' IN (part1, part2, part3))
  AND ('door' IN (part1, part2, part3));

统计匹配行数 #

SELECT COUNT(*) AS match_count
FROM parts
WHERE 
  ('mirror' IN (part1, part2, part3))
  AND ('door' IN (part1, part2, part3));

外部列表含1个值（示例：['antenna']） #

查询匹配行 #

SELECT part1, part2, part3
FROM parts
WHERE 'antenna' IN (part1, part2, part3);

统计匹配行数 #

SELECT COUNT(*) AS match_count
FROM parts
WHERE 'antenna' IN (part1, part2, part3);

性能优化建议 #

由于表中数据量较大，为避免全表扫描、提升查询速度，可为三列分别建立普通索引：

CREATE INDEX idx_part1 ON parts(part1);
CREATE INDEX idx_part2 ON parts(part2);
CREATE INDEX idx_part3 ON parts(part3);

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