Practical Limit on Length of MySQLi Query String

I'm building an export application to run against a table with about 20,000 rows.  Each row needs to be written to the output file, then the row will be marked by changing the "exported" column from default 'N' to 'Y' so that we know which rows have been exported.

The idea of running 20,000 update queries makes me itch, so I was thinking along the lines of using a single query with a clause like WHERE id IN (1,2,3,4,5 ... 20000)

It seems like this query string might be nearly 100,000 characters.  

Is there a practical limit on the length of the query string?  Or any other "gotcha" lurking in this idea?

Is there a "best practice" way of doing something like this?

Response time is not a big issue; this will probably run once a day.

Thanks and regards, ~Ray
LVL 111
Ray PaseurAsked:
Who is Participating?
mankowitzConnect With a Mentor Commented:

Never thought I'd be answering one of your questions, but here goes:

Look at the max_allowed_packet variable to see how big a query you can submit. However, my guess is that you could probably make a query like WHERE id < 20000 in many cases.

SHOW VARIABLES LIKE 'max_allowed_packet';

Open in new window

Ray PaseurAuthor Commented:
Great!  That's exactly what I was looking for.

stdClass Object
    [Variable_name] => max_allowed_packet
    [Value] => 33554432
Ray PaseurAuthor Commented:
Many thanks! ~Ray
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.