MySQL IN子句中的MySQL多列
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/44706196/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
MySQL multiple columns in IN clause
提问by nbeuchat
I have a database with four columns corresponding to the geographical coordinates x,y for the start and end position. The columns are:
我有一个数据库，其中有四列对应于开始和结束位置的地理坐标 x,y。列是：
- x0
- y0
- x1
- y1
- x0
- y0
- x1
- y1
I have an index for these four columns with the sequence x0, y0, x1, y1.
我有这四列的索引，序列为 x0, y0, x1, y1。
I have a list of about a hundred combination of geographical pairs. How would I go about querying this data efficiently?
我有一个大约一百个地理对组合的列表。我将如何有效地查询这些数据？
I would like to do something like this as suggested on this SO answerbut it only works for Oracle database, not MySQL:
我想按照this SO answer的建议做类似的事情，但它仅适用于Oracle数据库，而不适用于MySQL：
SELECT * FROM my_table WHERE (x0, y0, x1, y1) IN ((4, 3, 5, 6), ... ,(9, 3, 2, 1));
I was thinking it might be possible to do something with the index? What would be the best approach (ie: fastest query)? Thanks for your help!
我在想有可能对索引做些什么？最好的方法是什么（即：最快的查询）？谢谢你的帮助！
Notes:
笔记：
- I cannot change the schema of the database
- I have about 100'000'000 rows
- 我无法更改数据库的架构
- 我有大约 100'000'000 行
EDIT:The code as-is was actually working, however it was extremely slow and did not take advantage of the index (as we have an older version of MySQL v5.6.27).
编辑：原样的代码实际上是有效的，但是它非常慢并且没有利用索引（因为我们有一个旧版本的 MySQL v5.6.27）。
采纳答案by spencer7593
To make effective use of the index, you could rewrite the IN predicate
为了有效利用索引，你可以重写 IN 谓词
(x0, y0, x1, y1) IN ((4, 3, 5, 6),(9, 3, 2, 1))
Like this:
像这样：
( ( x0 = 4 AND y0 = 3 AND x1 = 5 AND y1 = 6 )
OR ( x0 = 9 AND y0 = 3 AND x1 = 2 AND y1 = 1 )
)
回答by Gordon Linoff
I do not understand your point. The following query is valid MySQL syntax:
我不明白你的意思。以下查询是有效的 MySQL 语法：
SELECT *
FROM my_table
WHERE (x0, y0, x1, y1) IN ((4, 3, 5, 6), ... ,(9, 3, 2, 1));
I would expect MySQL to use the composite index that you have described. But, if it doesn't you could do:
我希望 MySQL 使用您描述的复合索引。但是，如果没有，你可以这样做：
SELECT *
FROM my_table
WHERE x0 = 4 AND y0 = 3 AND x1 = 5 AND y1 = 6
UNION ALL
. . .
SELECT *
FROM my_table
WHERE x0 = 9 AND y0 = 3 AND x1 = 2 AND y1 = 1
The equality comparisons in the WHEREclause willtake advantage of an index.
WHERE子句中的相等比较将利用索引。
回答by Bill Karwin
MySQL allows row constructor comparisons like you show, but the optimizer didn't know how to use an index to help performance until MySQL 5.7.
MySQL 允许像您展示的那样进行行构造函数比较，但是优化器直到 MySQL 5.7 才知道如何使用索引来帮助提高性能。
回答by sorayadragon
You can concatenatethe four values into a string and check them like that:
您可以将四个值连接成一个字符串并像这样检查它们：
SELECT *
FROM my_table
WHERE CONCAT_WS(',', x0, y0, x1, y1) IN ('4,3,5,6', ..., '9,3,2,1');
回答by Chandan Purbia
The way you are doing is giving correct results in the mysql version on my machine. I am using v5.5.55. Maybe you are using an older one. Please check that.
您正在做的方式是在我的机器上的 mysql 版本中给出正确的结果。我正在使用v5.5.55. 也许您正在使用较旧的。请检查一下。
If you still want to solve this problem in your own version or the above mentioned solution doesn't work then only read the next solution.
如果您仍想在您自己的版本中解决此问题，或者上述解决方案不起作用，则只需阅读下一个解决方案。
I am still not clear about data types and range of all your columns here. So I am assuming that data type is integer and range is between 0 to 9. If this is the case you can easily do this as given below.
我仍然不清楚这里所有列的数据类型和范围。所以我假设数据类型是整数，范围在 0 到 9 之间。如果是这种情况，你可以很容易地做到这一点，如下所示。
select * from s1 where x0+10*x1+100*y1+1000*y2 in (4356,..., 9321);
