如何有效地返回具有多组值要匹配的WHERE条件的行？

Question

我有一个表格，列如下：

id, name, surname

我有以下sql查询：

SELECT `id` from `table` WHERE name = 'John' AND surname = 'Smith';

由于我有一个名称和姓氏的列表，我想检查它们是否存在于表中，所以我想进行一个查询并返回id (如果一个集合的名称和姓氏都匹配)或一个空的id (或零)，以指示名称和姓氏的组合没有匹配。

所以我的基本观点是：

SELECT `id` from `table` WHERE name = 'John' AND surname = 'Smith';
SELECT `id` from `table` WHERE name = 'Claudia' AND surname = 'Janssen';
SELECT `id` from `table` WHERE name = 'Eduart' AND surname = 'Allen';
SELECT `id` from `table` WHERE name = 'Don' AND surname = 'Diablo';
SELECT `id` from `table` WHERE name = 'Esther' AND surname = 'Smith';

(其中许多查询)

我认为IN语句不起作用，因为如果在表中找到同名的人和同姓的人，它也会返回id。重要的是要找到这个组合。

我的问题是：如何按照上面5个单一查询的规定高效地执行mysql查询？

Answer 1

您可以使用OR

SELECT id
FROM table
WHERE (name = 'John' and surname 'Smith')
OR (name = 'Claudia' and surname 'Janssen')
OR (name = 'Eduart' and surname 'Allen')
...

请确保使用括号，因为AND和OR之间的优先级。

您也可以使用UNION来完成它，如果在name和surname列上有索引，那么它可能会更高效，因为OR条件可能不使用它们。

SELECT id from table WHERE name = 'John' and surname 'Smith'
UNION
SELECT id from table WHERE name = 'Claudia' and surname 'Janssen'
UNION
SELECT id from table WHERE name = 'Eduart' and surname 'Allen'
UNION
...

Answer 2

这就是你要找的吗？

SELECT `id` 
FROM   `table` 
WHERE  (`name` = 'John' AND `surname` = 'Smith') 
        OR (`name` = 'Claudia' AND `surname` = 'Janssen') 
        OR (`name` = 'Eduart' AND `surname` = 'Allen') 
        OR (`name` = 'Don' AND `surname` = 'Diablo') 
        OR (`name` = 'Esther' AND `surname` = 'Smith');

Answer 3

您还可以尝试将这两个部分连接起来，并进行如下搜索：

SELECT id, CONCAT_WS(",", surname, name) AS FullName
FROM table1
HAVING FullName IN (
  "hand,bob", 
  "smith,steve", 
  "brown,rob", 
  "green,tom"
)

或

SELECT id, CONCAT_WS(",", surname, name) AS FullName
FROM table1
WHERE CONCAT_WS(",", surname, name) IN (
  "hand,bob", 
  "smith,steve", 
  "brown,rob", 
  "green,tom"
)

工作小提琴：http://sqlfiddle.com/#!2/2463e6