Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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?

share|improve this question
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 at 18:29

3 Answers 3

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%';
share|improve this answer

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.

share|improve this answer
    
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 at 18:15

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.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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