MySQL IN clause: maximum number of arguments

Question

Say you have the following query:

SELECT * FROM table1 WHERE table1.id IN (1, 2, 3, 4, 5, ..., 999999) 

What is a reasonable maximum for the number of elements in an IN clause? I use Sphinx to generate full-text search results and insert identifiers into a MySQL query. Is this an acceptable way to do this?

Answer 1

You can also have an IN clause to take query results, for example:

 SELECT * FROM table1 WHERE table1.id IN ( SELECT id from table2 ) 

Thus, you do not need to generate a text string with all possible values.

In mysql, you should be able to put as many values ​​in an IN clause as you want, only with a limit on the value of "max_allowed_packet".

http://dev.mysql.com/doc/refman/5.0/en/comparison-operators.html#function_in http://dev.mysql.com/doc/refman/5.0/en/server-system-variables.html #sysvar_max_allowed_packet

Answer 2

From my experience, the maximum values ​​are 1000 values ​​in IN ('1',....,'1000') , I have 1300 values ​​in my Excel worksheet, I put them all in IN, MySQL returns only 1000.