MySQL varchar(255) limitation & Anomaly

Question

I have defined a column in my MySQL database as :

ALTER TABLE `my_table` CHANGE `desc` `desc` VARCHAR( 255 ) 
    CHARACTER SET latin1 COLLATE latin1_swedish_ci NOT NULL

However, when I enter text that is longer than 233 characters from my front end the data is truncated.

Changing the size of the column to varchar(511) has made no difference.

I counted the number of characters using PHP's strlen() function and it revealed 233 characters.

Why is MySQL doing this, and how can I save the full string?

Answer 1

MySQL is not cutting it at 233. The problem is likely in your save method which cuts it to 233 before the data even reaches MySQL.

Also, don't forget that 233 limit is not character limit, and as some character might need more han 1 byte to be stored, you might see less than 233 characters.

Also please make sure that data in MySQL server is really stored as latin1, this can be accomplished with:

show variables like 'char%';

Answer 2

Which Version of MySQL you are using?. Mine is MySQL 5.5.16 and it is saving full text.

I Created sample table "my_table" with 2 columns id and desc(varchar(100).

After that I execute your alter query and after successfully execution of query I added two records with more than 255 character.

My record is showing 255 characters.

I assume that either your MySQL version is different or your front end is displaying only 233 characters.

Try to add records directory in your database and check the result.

Answer 3

UTF-8 needs two bytes for umlauted letters; latin1 does not.

What were you ALTERing from. Quite likely, that is the wrong variant of ALTER.

See this for common character set problems (including truncation) and their causes.

For further discussion, provide

SHOW CREATE TABLE ...;
SHOW VARIABLES LIKE 'char%';
sample of data giving trouble

Here are fixes (ALTERs) for the data in various cases.

Answer 4

255 is used because it's the largest number of characters that can be counted with an 8-bit number. It maximizes the use of the 8-bit count, without frivolously requiring another whole byte to count the characters above 255.

When used this way, VarChar only uses the number of bytes + 1 to store your text, so you might as well set it to 255, unless you want a hard limit (like 50) on the number of characters in the field.