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

But when i enter a text from my front end to insert into the table , after the size of column data reaches 233 it truncates further text added ie text beyond 233 character is not saved !!

i tried changing size of column to VARCHAR(511) but achieved no success.

i counted the nos of characters using php strlen() and it revealed 233 characters

Why is MySQL doing this , and how can i save the text ?

Run `DESCRIBE my_table`. What does it say about the desc column? — Chris Forrence, Oct 01 '12 at 11:57
Could it be a charset problem? Try to add some (latin1) characters to your string and see if 2 characters are still missing. — Clarence, Oct 01 '12 at 11:57
@GlaciesofPacis DESCRIBE `my_table` reveals `desc` is varchar(255) and NOT NULL and such .. — Nishant Jani, Oct 01 '12 at 12:01
So.. to clear it up, the strings you were saving were actually 255 or more characters in length and you didn't count commas? — N.B., Oct 01 '12 at 12:05
umm as i said i just pasted the entire text (along with commas) in the strlen() of php and it gave me 233 @N.B. — Nishant Jani, Oct 01 '12 at 12:13
@user1537158 - Hmm. I might suggest asking this problem on dba.stackexchange.com. Changing the size of the column to 511, by the way, should fail, since the character limit of char/varchar is 255. — Chris Forrence, Oct 01 '12 at 12:53

score 2 · Accepted Answer · answered Oct 01 '12 at 12:02

A VARCHAR(250) NOT NULL takes one byte to store the length n, and n bytes to store the actual string. So a column holding a value 'abc' will account for 4 bytes. In a VARCHAR(250) NULL, the nullity takes one bit, and a NULL value won't take anything else. A non-null value takes that bit, one byte for length and n bytes for data. Needing a bit here means that several nullable columns share one or more bytes to record their nullity.

Other variable length data types are similar. BLOB and TEXT types of the various sizes may have more than a single byte for length, but otherwise work pretty much the same. Numeric types as well as the fixed-length CHAR types have fixed memory requirements. I think I the CHAR will omit the length byte, as its content is padded with spaces to that fixed length.

Indices might increase the memory requirements. Multy-byte character sets (including UTF-8) might increase the memory requirements, so the above statements will only hold if your table was created for latin1 or similar. So you can deduce that VARCHAR(250) don't mean 255 caracters, it will depend on how you encode your strings

Well, this mean that abc in latin1 = varchar(3), but abc another encodage type it could be abc = varchar(5); — Weacked, Oct 01 '12 at 12:15

score 1 · Answer 2 · answered Oct 01 '12 at 11:58

1

Use mb_strlen() function to count number of characters, it is multibyte-safe, it appears that you have some special characters in your string, see here.

It depends of the version of the MySQL server and the settings, but if some string exceed the predefined length during insert or update - then MySQL will automatically truncate the exceeded part of it.

answered Oct 01 '12 at 11:58

Bud Damyanov

30,171
6
44
52

mb_strlen() gives 233 , nothing diff – Nishant Jani Oct 01 '12 at 12:15

MySQL varchar (255) limitation & Anomaly

2 Answers2

Linked