MySQL实现忽略列顺序的物品组合查询 无需枚举排列组合

方案1：动态排序拼接匹配（无需改表，适配所有场景） #

核心思路是把每行的所有物品列按固定规则（如升序）排序后拼接为字符串，和提前排序拼接好的目标集合字符串做相等匹配，天然解决顺序和重复计数问题。

3列场景适配示例（兼容MySQL 5.7及以上） #

SELECT *
FROM userdata
WHERE cID = 2
  AND CONCAT_WS(',',
    LEAST(item1, item2, item3),
    item1 + item2 + item3 - LEAST(item1, item2, item3) - GREATEST(item1, item2, item3),
    GREATEST(item1, item2, item3)
  ) = '1,2,3';

N列通用场景适配示例（兼容所有支持窗口函数/行构造的数据库，如MySQL 8.0、PostgreSQL、SQL Server） #

以7个物品列为例：

-- PostgreSQL 写法
SELECT u.*
FROM userdata u
CROSS JOIN LATERAL (
  SELECT string_agg(item::text, ',' ORDER BY item) AS sorted_items
  FROM (VALUES (item1), (item2), (item3), (item4), (item5), (item6), (item7)) AS t(item)
) s
WHERE u.cID = 2
  AND s.sorted_items = '1,2,3,4,5,6,7'; -- 替换为目标集合升序排序后的拼接字符串

如果目标集合包含重复元素，比如需要匹配[2,2,3]，只需把目标串改为'2,2,3'即可，无需修改查询逻辑。

方案2：预存计算列+索引（性能最优，适配高频查询场景） #

如果这类查询频率很高，可以把排序拼接后的结果预存为计算列并加联合索引，查询性能和普通索引查询一致：

-- 以3列场景MySQL为例添加计算列
ALTER TABLE userdata ADD COLUMN sorted_items VARCHAR(255) 
GENERATED ALWAYS AS (
  CONCAT_WS(',',
    LEAST(item1, item2, item3),
    item1 + item2 + item3 - LEAST(item1, item2, item3) - GREATEST(item1, item2, item3),
    GREATEST(item1, item2, item3)
  )
) STORED;

-- 添加联合索引
CREATE INDEX idx_cid_sorted ON userdata(cID, sorted_items);

后续查询直接走索引即可：

SELECT * FROM userdata WHERE cID = 2 AND sorted_items = '1,2,3';

特殊场景方案：位运算匹配（仅适配无重复元素、物品ID范围≤64的场景） #

如果你的物品ID最大值不超过64，且目标集合无重复元素，可以给每个物品分配唯一的2次幂位值，通过求和匹配：
比如物品1对应2^1=2、物品2对应2^2=4、物品3对应2^3=8，目标和为14：

SELECT *
FROM userdata
WHERE cID = 2
  AND (1<<item1) + (1<<item2) + (1<<item3) = 14;

这种方案运算效率最高，但适用范围有限。 #

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