Limit on number of values in mysql in clause

HI,
On this page :
http://dev.mysql.com/doc/refman/5.7/en/comparison-operators.html#function_in
its mentioned that :
The number of values in the IN list is only limited by the max_allowed_packet value.
what does this means ??
On my mysql i have the following value of this variable :
mysql> show variables like 'max_allowed_packet'
    -> ;
+--------------------+---------+
| Variable_name      | Value   |
+--------------------+---------+
| max_allowed_packet | 4194304 |
+--------------------+---------+
1 row in set (0.01 sec)

Open in new window

This is the default 4MB.
I dont understand the relation between the number of value and 4MB.

What limit does mysql produce on the number of values in the IN clause ?

Thanks
Rohit BajajAsked:
Who is Participating?

Improve company productivity with a Business Account.Sign Up

x
 
Ferruccio AccalaiConnect With a Mentor Senior developer, analyst and customer assistance Commented:
When you create an IN list you generally write strings that are buffered, so the limit is to have a buffer size not larger than max_allowed_packet.
Anyway there's not a sure limit number for arguments passed in the IN list: it depends on the size of the sent packet that is limited by max_allowed_packet.
http://dev.mysql.com/doc/refman/5.7/en/server-system-variables.html#sysvar_max_allowed_packet
0
 
Rohit BajajAuthor Commented:
So is it like If i am using a query : select * from note where id in (id1, id2,....)
And say each id is a string of 10 chars
so each id is approx 10 bytes and if the number of elements in the list is 1000. Then total buffer size will be = 10000 bytes = 10 KB ??
which is smaller than 4 MB so it is fine ?

Also the link mentions something about if you are using large columns...
So for queries like select * from note where a column type is TEXT this max_allowed_packet comes into play ???
If i have to move from TEXT to LONGTEXT do i need to change max_allowed_packet ??
0
 
Ferruccio AccalaiSenior developer, analyst and customer assistance Commented:
@will be = 10000 bytes = 10 KB ??
which is smaller than 4 MB so it is fine ?

more or less yes.

To better understand how datas are stored  in blob or longtext field take a look here http://dev.mysql.com/doc/refman/5.7/en/blob.html and
also the Data Type Storage Requirements here http://dev.mysql.com/doc/refman/5.7/en/storage-requirements.html
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.