mysql - 如何仅选择包含 mySQL 中另一组的部分或全部的特定组

Question

我正在尝试从包含数字列表的 mySQL 表中提取特定行。

我有一个包含 2 列的表 - id 和数据。每行都有一个排序的、逗号分隔的数字记录，范围从 1 到 1000。我只想选择其中包含部分或全部特定数字的记录。我试过使用 LIKE 和 IN，还查看了 FIND_IN_SET。

t1.id       t1.data
1          2,9,569
2          2,9,991,979
3          9,569,763
4          52,57,569,763,892,897
5          763
6          2,9,10,15,151,569,771,801,888,973

如果我要查找具有一个或多个值 (2,9,569,763) 的行，我不想写:

SELECT t1.id from t1
WHERE t1.data NOT IN (1,3,4,5,6,7,8,10,11,...........,1000);

返回 3 行，t1.id = 1,3 和 5。

有没有更简单的方法？类似于(在 mySQL 中):

SELECT t1.id from t1
WHERE t1.data "only includes one or more of" (2,9,569,763);

Answer 1

Paul Spiegel 的回答给出了正确的结果，但由于使用了 FIND_IN_SET()，因此无法使用索引对其进行优化。它总是会进行表扫描，行数越多，成本就会越高。

您应该以此为线索，当您实际想要搜索该列表的离散成员时，将数字列表存储为字符串列中的逗号分隔列表是一个坏主意。

您应该做的是将列表存储为子表，每行一个成员。

CREATE TABLE mydata (
  t1id INT NOT NULL,
  member INT NOT NULL,
  PRIMARY KEY (t1id, member),
  FOREIGN KEY (t1id) REFERENCES t1(id)
);

INSERT INTO mydata VALUES
(1,2),(1,9),(1,569),
(2,2),(2,9),(2,991),(2,979),
(3,9),(3,569),(3,763),
(4,52,(4,57,(4,569,(4,763,(4,892),(4,897),
(5,763),
(6,2),(6,9),(6,10),(6,15),(6,151),(6,569),(6,771),(6,801),(6,888),(6,973);

现在您可以将原始表 t1 连接到 mydata 但排除与所需列表中的值匹配的值。

mysql> select * from t1 left join mydata on t1.id=mydata.t1id 
    and mydata.member not in (2,9,569,763);
+----+------+--------+
| id | t1id | member |
+----+------+--------+
|  1 | NULL |   NULL |
|  2 |    2 |    979 |
|  2 |    2 |    991 |
|  3 | NULL |   NULL |
|  4 |    4 |     52 |
|  4 |    4 |     57 |
|  4 |    4 |    892 |
|  4 |    4 |    897 |
|  5 | NULL |   NULL |
|  6 |    6 |     10 |
|  6 |    6 |     15 |
|  6 |    6 |    151 |
|  6 |    6 |    771 |
|  6 |    6 |    801 |
|  6 |    6 |    888 |
|  6 |    6 |    973 |
+----+------+--------+

您看到 id 1、3、5 有 NULL，因为没有值不在您指定的列表中。这些是您要返回的 ID。

mysql> select t1.id from t1 left join mydata on t1.id=mydata.t1id 
    and mydata.member not in (2,9,569,763) 
  where mydata.member is null;
+----+
| id |
+----+
|  1 |
|  3 |
|  5 |
+----+