MySQL Prepare Statement - Maximum Length 1000 Char

问题:

I have an SQL statement which exceeds 1000 characters that I'm using through prepare method - is there anyway to extend this value marginally?

Once I use the prepare command it truncates the SQL to 1000 characters, similarly if I just select the variable holding the sql string this also is truncated in output.

In addition to the core SQL there could be any number of additional queries added on.

I suppose I could create a view and then select from the view, however views in MySQL are a little sluggish.

I know I can refine this down and replace with select tv.* etc and I have done that in my trials, however the additional queries can add significantly to the length and actually the items that are selected are least of my worries.

Is there no way to extend the 1000 limit.

回答1:

The MySQL server by default accepts a 16MB string, that limit can be extended by using max_allowed_packet.

I'm not sure what language you are using, but it's probably the database adapter of the language/framework that fails, esp. since the server doesn't truncate, it just complains with an ER_NET_PACKET_TOO_LARGE.

回答2:

So for clarification, sadly there is no cure for stupidity - turns out my variable sqlString was limited by the type varchar(1000). So obvious that I couldn't see it.