主页 / dba / 问题 / 211355
Accepted
Daniel Santos
Asked: 2018-07-05 09:24:55 +0800 CST

如何检查表中是否存在数组的所有元素？

  • 772

让我们在应用程序级别获取一个大小为 n 的 id 数组。例如 [132,3425,13,​​13,... 392] 其中 n 大于 100k 个条目。其中一些条目是重复的。

我需要检查所有这些寄存器是否都包含在包含超过 5M 条目的 MySQL 表中。

现在我一个接一个地检查，但是这个过程需要很长时间。

我怎样才能在一次检查中进行此验证，或者使此验证更有效。

我想知道它们是否都存在于桌子上，如果可能的话，知道哪些不存在于该桌子上。

mysql performance

3 个回答

  1. 我建议使用应用程序代码将数组插入到临时表中，包括一个标识字段，这样您就可以存储每条记录，即使它是重复的。（语法与 CREATE TABLE 相同，但使用 CREATE TEMPORARY TABLE）：

    CREATE TEMPORARY TABLE A (
     id BIGINT NOT NULL AUTO_INCREMENT,
     number BIGINT NOT NULL,
     PRIMARY KEY (id)
);

    然后进行查询以检查/确定大表中是否存在该数字。像这样的东西：

    --假设临时表是A，大表是B，并且您要比较的字段称为NUMBER。

    SELECT A.id, A.number as ARRAY_NUMBER,
CASE
    WHEN B.number IS NULL THEN 'not found'
    ELSE 'found'
END as FOUND_IN_TABLE
FROM A LETF OUTER JOIN B ON A.number = B.number

    祝你好运！

    • 3 
  2. CREATE TEMPORARY TABLE B_Temp (
     id BIGINT NOT NULL
     PRIMARY KEY (id)
     INDEX unid (id)
);

    我会像@AMG 的回答那样加载一个临时表。但我的查询看起来像这样：

    select id
from a
where not exists (
select *
from B_Temp
where a.id = b.id
)

    您可能希望在加载数据之前禁用 id 列上的索引，然后重新启用它以使其正常运行。如果您定期执行此操作，您可能会考虑将其设为永久表。

    • 3
  3. Best Answer

    首先计算不同数字的数量。你已经调用它了n。然后

    SELECT COUNT(*) FROM tbl
    WHERE id IN (very-long-list)

    看看是否返回n。

    相反，如果您将这些数字放在另一个表中，那么这可能是最佳选择：

    Table `nums` contains the `n` values.
ALTER TABLE tbl ADD INDEX(id);  -- if it does not already exist.
SELECT ! EXISTS ( SELECT * FROM nums LEFT JOIN tbl USING(id)
                      WHERE tbl.id IS NULL )

    这将检查每个nums.id以查看它是否在您的tbl. 更重要的是，它会在找到第一个丢失的项目时停止。输出为 1 或 0。

    （“very-long-list”是“commalist”或“comma-sparated-list”）

    • 3

相关问题