Are There Any Limits on Length of String in MySQL

Are there any limits on length of string in mysql?

String, in general, should be used for short text. For example, it is a VARCHAR(255) under MySQL.

Text uses the larger text from the database, like, in MySQL, the type TEXT.

For information on how this works and the internals in MySQL and limits and such, see the other answer by Pekka.

If you are requesting, say, a paragraph, I would use text. If you are requesting a username or email, use string.

Maximum length for MySQL type text

See for maximum numbers:
http://dev.mysql.com/doc/refman/5.0/en/storage-requirements.html

TINYBLOB, TINYTEXT       L + 1 bytes, where L < 2^8    (255 Bytes)
BLOB, TEXT               L + 2 bytes, where L < 2^16   (64 Kilobytes)
MEDIUMBLOB, MEDIUMTEXT   L + 3 bytes, where L < 2^24   (16 Megabytes)
LONGBLOB, LONGTEXT       L + 4 bytes, where L < 2^32   (4 Gigabytes)

L is the number of bytes in your text field. So the maximum number of chars for text is 2¹⁶-1 (using single-byte characters). Means 65 535 chars(using single-byte characters).

UTF-8/MultiByte encoding: using MultiByte encoding each character might consume more than 1 byte of space. For UTF-8 space consumption is between 1 to 4 bytes per char.

MySQL column length maxes out at 1026 characters

Before calling the SELECT query, you can set the maximum value for group_concat_max_len to the maximum possible value, for this particular session:

SET SESSION group_concat_max_len = @@max_allowed_packet;

From Docs:

The result is truncated to the maximum length that is given by the
group_concat_max_len system variable, which has a default value of
1024. The value can be set higher, although the effective maximum length of the return value is constrained by the value of
max_allowed_packet.

limit length of string, set max length of string column

That is what varchar() is for:

create table user(
    id int unsigned auto_increment not null,
    username varchar(16) not null,
    primary key (id)
);

What is the maximum length of string for CONCAT in MySQL (Aurora-MySql)?

text cannot be larger than 2^16 bytes or 65,535. Depending on the character set, characters can take up multiple bytes, but if it's simple UTF-8 it's probably one byte per character.

You could use mediumtext (2^24 bytes) or longtext (2^32 bytes), but really your table needs to be redesigned. Storing lists as comma separated strings is extremely inefficient, complex, and error prone. Instead, use a many-to-many join table.

create thing_stuff (
  thing_id int not null references thing(id),
  stuff_id int not null references stuff(id)
);

-- Thing 1 has Stuff 2 and 5.
insert into thing_stuff (thing_id, stuff_id) values
  (1, 2), (1, 5);

-- Get all the Stuff associated with Thing 1.
select stuff.*
from stuff s
join thing_stuff ts on ts.stuff_id = s.id
where thing_id = 1;

-- Thing 1 no longer has Stuff 5.
delete from thing_stuff
where thing_id = 1 and stuff_id = 5;

String length limit to minimum characters with MYSQL

try something like this,

SELECT  IF(CHAR_LENGTH(subjects) > 35, CONCAT(LEFT(subjects,35), '...'), subjects),
        ...
FROM
(
    SELECT  GROUP_CONCAT( DISTINCT s.subject_name SEPARATOR ', ') AS subjects,
            ...
    FROM    tableName
    GROUP   BY ...
) s

• SQLFiddle Demo

I'd rather use CHAR_LENGTH than LENGTH when getting length of the character because...

• CHAR_LENGTH vs LENGTH

Why/how am I able to exceed the configured length of a varchar in MySQL? [my mistake, I can't]

The answer is that I can't. Misread the column types because that column is varchar(255) when the application builds the schema in PostgreSQL. It's longtext* in MySQL, which explains why I was able to get past the 190 characters. I tried inserting my 230 char test string into the varchar(190) column and it throws an error, as expected.

Need more coffee.

*not sure why longtext, when the application GUI limits input to 255 characters, but I'll need to ask the people who built it.

---

Related Topics