"in"中有多少个值? SQL子句中的子句过多? [英] How many values in an "in" clause is too many in a SQL query?

问题描述

我有一个SQL查询，该查询在其WHERE子句中使用数组的值:

I have a SQL query that uses the values of an array in its WHERE clause:

 $ids = array 
         ( 
           [0] => 1 
           [1] => 2 
           [2] => 5 
         ) 

 $ids = join(',',$ids);   
 $sql = "SELECT * FROM cats WHERE id IN ($ids)"; 

我的问题是ID太多?
会影响速度吗?

My question is how many ids are too many?
Will it effect the speed?

谢谢

推荐答案

Oracle的限制为1000，这是我之前遇到的限制. MySQL似乎并不介意.最好的解决方案是不要对如此大的数据集使用IN子句.身份证从哪里来?如果来自同一数据库，则查看是否可以使用子查询来代替，该子查询基于一个参数进行搜索，例如userid查找链接的id.由于MySQL可以使用索引在内部进行联接，因此这将更加高效.

Oracle has a limit of 1000, which I've hit before. MySQL doesn't seem to mind. The best solution is not to use an IN clause for that large a dataset though. Where do the ids come from? If from the same DB, then see if you can use a subquery instead that searches based on one parameter e.g. userid to find the linked ids. This will be far more efficient as MySQL can do a join internally using indexes.

这篇关于"in"中有多少个值? SQL子句中的子句过多?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！