maximum character size that a mysql query can handle

Question

I am trying to run this query but there are no values being retrieved , I tried to find out the length of characters till which values are returned. Length was 76 characters.

Any suggestions?

SELECT tokenid FROM tokeninfo where tokenNumber = 'tUyXl/Z2Kpua1AvIjcY5tMG+KlEhnt+V/YfnszF5m1+q8ngYvw%L3ZKrq2Kmtz5B8z7fH5BGQXTWAoqFNY8buAhTzjyLFUS64juuvVVzI7Af5UAVOj79JcjKgdNV4KncdcqaijPQAmy9fP1w9ITj7NA==%';

Try this: `SELECT @str:=tokenNumber FROM tokeninfo ORDER BY LENGTH(tokenNumber) DESC LIMIT 1; SELECT tokenid FROM tokeninfo WHERE tokenNumber:=@str; SELECT LENGTH(@str);` — Eugen Rieck, Aug 22 '14 at 12:09
The % in the string will be considered wildcards, is that your intent? It would not break your match. However in some cases + also has the meaning the previous character may appear 1 or more times.. that would break your search if you are looking for that string exactly. — Rob, Aug 22 '14 at 12:09
[Use](http://stackoverflow.com/questions/1069415/limit-on-the-where-col-in-condition) [the](http://stackoverflow.com/questions/1869753/maximum-size-for-a-sql-server-query-in-clause-is-there-a-better-approach) [search](http://stackoverflow.com/questions/7345122/is-there-a-limit-to-how-long-a-sql-query-can-be-in-sql-server?lq=1) [bar](http://stackoverflow.com/questions/24149337/maximum-size-of-mssql-query-executed-from-php?lq=1) — Touk, Aug 22 '14 at 12:10

score 0 · Answer 1 · answered Aug 22 '14 at 12:09

The problem is not the length of the characters you select, but in the characters, which are stored in database field itself. Check the tokenNumber field in your database schema - if it is varchar, or blob or whatever type, what is the length, etc...

You can insert/select pretty much more than 76 characters in any database, but you can get less that 76, as in your case, it depend on how you handle the field they are stored in.

A quick way to see the tokeninfo table properties is to run this query:

SHOW COLUMNS FROM tokeninfo;

If the data types differ from what you expect them to be based on a CREATE TABLE statement, note that MySQL sometimes changes data types when you create or alter a table. The conditions under which this occurs are described in Section 13.1.10.3, Silent Column Specification Changes.

score 0 · Answer 2 · answered Aug 22 '14 at 12:09

the max size would be limited by the variable max_allowed_packet
so, if you do a

show variables like 'max_allowed_packet'

it will show you the limit.  By default, it is set to
1047552 bytes.

If you want to increase that, add a line to the server's
my.cnf file, in the [mysqld] section :

set-variable = max_allowed_packet=2M

and restart mysql server.

maximum character size that a mysql query can handle

2 Answers2