mysql 批量select,MySQL：如何在WHERE子句中对具有多对的SELECT行进行批量处理

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.

I'd like to do a query like this:

SELECT * FROM email_phone_notes  WHERE email = 'foo@bar.com' AND phone = '555-1212';

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!

解决方案

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;