mysql - 搜索数据库中是否存在多个对象

Question

假设我有一个包含 500 万用户的数据库，其中包含列

id(无符号整型，自动递增)、facebook_id(无符号整型)和名称(varchar)

在一个程序中，我有一个来自某人 Facebook 好友列表的可变数量的用户列表(通常范围为 500-1200 个不同的 Facebook ID)。

向数据库发送查询以返回数据库中存在相同 facebook_id 的所有用户的 facebook_id 的最有效方法是什么？

伪代码:

$friends = array(12345, 22345, 32345, 42345, 52345, ... ~1000 更多);
$q = mysql_query("从用户中选择 * ...");
$friendsAlreadyUsingApp = parseQuery($q);

Answer 1

这个主题几乎有无数的文章、博客、问答等；这个问题的本质是它看起来很简单 - 但事实并非如此。

问题的核心是参数看起来像它应该使用WHERE field IN()工作，但它没有这样做，因为参数是一个字符串，其中恰好有很多逗号。

因此，当该参数传递给 SQL 时，有必要将该单个字符串处理为多个部分，以便可以将该字段与每个部分进行比较。这就是它变得有点复杂的地方，因为并非所有数据库类型都具有相同的功能来处理这个问题。例如，MySQL 没有 MS SQL Server 提供的表变量。

所以。一个简单的方法，对于MySQL来说是这样的:

SET @param := '105,110,125,135,145,155,165,175,185,195,205';

SELECT
*
FROM Users
WHERE FIND_IN_SET(facebook_id, @param) > 0
;

FIND_IN_SET Return the index position of the first argument within the second argument

我无法判断它在数据库中的扩展程度如何，对于包含 1000 多个 id 的参数来说可能 Not Acceptable 。

因此，如果像FIND_IN_SET这样的文本处理太慢，则需要从参数中分离出每个id并将其插入到表中。这样，可以通过 INNER JOIN 使用结果表来过滤用户；但这需要一张表并进行插入，这需要时间，并且如果多个用户尝试同时使用该表，则可能会出现并发问题。

<小时/>

使用以下命令设置一个包含 10,000 个整数(1 到 10,000)的表

/* Create a table called Numbers */
CREATE TABLE `Numbers`
(
    `Number` int PRIMARY KEY
);

/* use cross joins to create 10,000 integers from 1 & store into table */
INSERT INTO Numbers (Number)
select 1 + (a.a + (10 * b.a) + (100 * c.a) + (1000 * d.a))  as N
from (select 0 as a union all select 1 union all select 2 union all select 3 union all select 4 union all select 5 union all select 6 union all select 7 union all select 8 union all select 9) as a
cross join (select 0 as a union all select 1 union all select 2 union all select 3 union all select 4 union all select 5 union all select 6 union all select 7 union all select 8 union all select 9) as b
cross join (select 0 as a union all select 1 union all select 2 union all select 3 union all select 4 union all select 5 union all select 6 union all select 7 union all select 8 union all select 9) as c
cross join (select 0 as a union all select 1 union all select 2 union all select 3 union all select 4 union all select 5 union all select 6 union all select 7 union all select 8 union all select 9) as d
;

然后可以使用此“实用程序表”将逗号分隔的参数划分为各个整数的派生表，然后在用户表的INNER JOIN中使用该表将提供所需的信息结果。

SET @param := '105,110,125,135,145,155,165,175,185,195,205';
SET @delimit := ',';

SELECT
      users.id
    , users.facebook_id
    , users.name
FROM users
      INNER JOIN (
                  SELECT
                        CAST(SUBSTRING(iq.param, n.number + 1, LOCATE(@delimit, iq.param, n.number + 1) - n.number - 1) AS UNSIGNED INTEGER) AS itemID
                  FROM (
                              SELECT
                                    concat(@delimit, @param, @delimit) AS param
                        ) AS iq
                        INNER JOIN Numbers n
                                    ON n.Number < LENGTH(iq.param)
                  WHERE SUBSTRING(iq.param, n.number, 1) = @delimit
            ) AS derived
                  ON users.facebook_id = derived.itemID
;

此查询可用作存储过程的基础，您可以更轻松地从 PHP 调用该存储过程。

See this SQLFiddle demo