MySQL中比对两组ID集合 高性能查询匹配的InvoiceId

• 数据库Invoices表存储发票标识InvoiceId，以及逗号分隔格式的授权ID集合字段trueIDs，表样例数据如下：

+-----------+---------+
| InvoiceId | trueIDs |
+-----------+---------+
| ab12345   | 1,2,35  |
| cd1234567 | 92,28,1 |
| asdf12345 | 351,8,1 |
+-----------+---------+

• PHP侧持有用户允许访问的ID集合UserAllowed，样例集合值为：(925,28,1,99,1059,314,422,96,917,356)

当UserAllowed集合中任意一个ID存在于某条记录的trueIDs字段值中时，返回该条记录对应的InvoiceId。逐ID循环执行数据库查询的方案性能差、逻辑冗余，需要基于给出的SQL框架实现单SQL高性能查询，待补全的SQL框架如下：

SELECT
    Invoices.InvoiceId
FROM
    Invoices 
WHERE
(925,28,1,99,1059,314,422,96,917,356) ???? Invoices.trueIDs

长期优化提示：逗号分隔存储多值集合属于反范式设计，后续可将trueIDs拆分为独立的「发票-授权ID」关联表，给授权ID字段加索引后查询性能可提升1~2个数量级。以下方案针对现有表结构即可落地，无需改表，仅需单次数据库交互即可完成查询。

原生SQL没有直接判断元组内任意值存在于逗号分隔串的操作符，需要替换原有占位逻辑，通过边界+正则匹配的方式实现精确判断，同时避免ID部分误匹配（比如ID=1误命中351、11这类包含数字1但不是目标ID的值），补全后的可直接运行SQL如下：

SELECT
    Invoices.InvoiceId
FROM
    Invoices 
WHERE
    CONCAT(',', Invoices.trueIDs, ',') REGEXP CONCAT(',(925|28|1|99|1059|314|422|96|917|356),')

PHP侧生成该SQL的逻辑非常简单：拿到UserAllowed数组后，用implode('|', $userAllowed)替换正则括号内的ID拼接部分即可。

注意：不要使用LIKE模糊匹配实现该逻辑，会出现ID部分命中的错误，导致权限越权问题。

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