How many values allowed in: select. `field` IN (1,2,3,4.) search?

Hard to do a web search for "in"

[whoisgregg]

When doing a select using this syntax:

SELECT * FROM `table` WHERE `field` IN (1,2,3,4...)

Is there a limit to how many values can be searched? Is there a character limit to the total SELECT command? Any pointers to the right manual page for mysql [dev.mysql.com] would be appreciated. :)

[stajer]

This is a sql construct, not mysql specific. I have built queries with several several thousand items in the IN list succesfully.

But, I have run into hardware limits. You can run into "stack space optimization" problems - basically the hardware can't support the number of parameters. No hard upper limit - it is hardware dependant.

[syber]

Theoretically, there should be no limit. If the list becomes cumbersome to maintain, you could always store the values in another table and change the query to

SELECT * FROM `table`
WHERE `field` IN (SELECT 'field' FROM 'table2')

[whoisgregg]

Great feedback, thank you both. :)

Basically I am selecting rows from one table and automatically generating the list of keys for related rows in other tables. So the list will never become cumbersome (as I'll never see the list). However I had some concern that if the automatically generated list was very long and if that could cause failure that I needed to trap for that scenario.

My lists ought never be more than a few hundred items and I am running overkill hardware already, so based on what you've posted, I no longer see a pressing need to plan for failures related to long item lists.

Thanks again!

[Demaestro]

In postGres there is a limit of 10,000

I have statments where I am placing hundreds of thousands of these and I have to group them into arrays of 9,999 values and loop through those and concantinating the results set until it has run through all iterations.

I would be surprised if there was no limit. It is usually the same limit the limit on sub-selects.