5

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?

  • 1
    After inserting some strings into this table, try using SELECT LENGTH(`desc`), CHAR_LENGTH(`desc`) FROM my_table; Are there differences? – ypercubeᵀᴹ Oct 2 '12 at 8:13
  • @ypercube i changed varchar(255) to text , now its working , but i still dont know why varchar(255) dint – user1537158 Oct 3 '12 at 7:35
  • 1
    Please read this: MySQL Charset/Collate It's a rather long post but the "Best practise" paragraph should point you to where the problem lies in your case. You probably missed one of those settings (replacing 'utf-8' with 'latin1') and your application was sending data in utf8 and not latin. – ypercubeᵀᴹ Oct 3 '12 at 7:43
  • If you enter umlauts in latin1 column it will be stored in two bytes. That would explain – akuzminsky Feb 18 '14 at 18:29
2

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%';
0

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.

  • So, you are saying that you managed to accomplish the opposite of what the OP experienced? Squeezing more than 255 characters in a varchar(255) column? – ypercubeᵀᴹ Feb 15 '14 at 18:15
0

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.

-2

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.