combining a WHERE and IN clause with two fields

I have a table 'goods' with 3 columns (id, name, price). I need to get data by list of ids and price range.

I'm using this query:

SELECT id, name, price FROM goods
WHERE (id, price)
IN ((1,10), (3,20), (2,10))
ORDER BY FIELD(id, 1,3,2)

Everything is just fine while I'm using strict price values.

In fact, I need to do query like:

SELECT id, name, price FROM goods
WHERE (id, price)
IN ((1, BETWEEN 0 AND 100), (2, BETWEEN 50 AND 150), (n, BETWEEN m AND k))
ORDER BY FIELD(id, 1,3,n)

I found expression (1, BETWEEN 0 AND 100) will never return the result I need, while 开发者_如何学JAVA(1,10) will.

I'm surely can exclude records by price range in php, but I'm trying to find more efficient way to do it in mysql query using indexes. What should I do?

You can't use BETWEEN like that. Try changing your IN expression to a series of ORs instead:

SELECT id, name, price
FROM goods
WHERE
    (id = 1 AND price BETWEEN 0 AND 100) OR
    (id = 2 AND price BETWEEN 50 AND 150) OR
    (id = n AND price BETWEEN m AND k)
ORDER BY FIELD(id, 1,3, n)