MySQL:如何在WHERE子句中对具有多对的SELECT行进行批量处理 [英] MySQL: How to bulk SELECT rows with multiple pairs in WHERE clause
问题描述
比方说，我有一个表，email_phone_notes看起来像这样:
Let's say I have a table, email_phone_notes that looks like this:
+-----------------------+--------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-----------------------+--------------+------+-----+---------+-------+
| email | varchar | NO | PRI | NULL | |
| phone | varchar | NO | PRI | NULL | |
| notes | text | NO | | 0 | |
+-----------------------+--------------+------+-----+---------+-------+
因此，每个电子邮件/电话组合都是唯一的，但是您可能会有多个电子邮件地址，其中包含不同的电话号码，反之亦然.这有点做作，但它反映了我的情况.
So, each email/phone combination is unique, but you could have several email addresses with different phone numbers and vice versa. This is a little contrived but it mirrors my scenario.
我想这样查询:
SELECT * FROM email_phone_notes WHERE email = 'foo@bar.com' AND phone = '555-1212';
但是，我想一次做多对，所以我不必进行几个SELECT查询.将两对保持在一起也很重要，因为我不想退回不需要的错误的电话/电子邮件组合.
But, I'd like to do multiple pairs at once so I don't have to make several SELECT queries. It's also important to keep the pairs together because I don't want to return an errant phone/email combination that wasn't requested.
我可以做这样的事情，但是对于数百个值的查询来说，查询可能会很长.
I could do something like this, but for the possibility of several hundred values the query will be really long.
SELECT * FROM email_phone_notes WHERE (
(email='foo@bar.com' && phone='555-1212') ||
(email='test@test.com' && phone='888-1212') ||
...
是否有更优雅的解决方案，还是应该坚持呢?谢谢！
Is there a more elegant solution, or should I stick with this? Thanks!
推荐答案
如果您追求优雅的SQL，则可以使用行构造器:
If you're after elegant SQL, you could use row constructors:
SELECT * FROM email_phone_notes WHERE (email, phone) IN (
('foo@bar.com' , '555-1212'),
('test@test.com', '888-1212')
-- etc.
);
但是，这根本不是索引友好的，也不建议在任何大的表上使用.取而代之的是，您可以将具有所需对的表具体化，然后将其与表连接:
However, that's not at all index-friendly and would not be recommended on a table of any significant size. Instead, you could materialise a table with your desired pairs and join that with your table:
SELECT * FROM email_phone_notes NATURAL JOIN (
SELECT 'foo@bar.com' AS email, '555-1212' AS phone
UNION ALL
SELECT 'test@test.com', '888-1212'
-- etc.
) t;
或者预先填充(临时)表:
Or else pre-populate a (temporary) table:
CREATE TEMPORARY TABLE foo (PRIMARY KEY (email, phone)) Engine=MEMORY
SELECT email, phone FROM email_phone_notes WHERE FALSE
;
INSERT INTO foo
(email, phone)
VALUES
('foo@bar.com' , '555-1212'),
('test@test.com', '888-1212')
-- etc.
;
SELECT * FROM email_phone_notes NATURAL JOIN foo;
这篇关于MySQL:如何在WHERE子句中对具有多对的SELECT行进行批量处理的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
