CODEWITHSUNDEEP
phpmysqlvarchar
Nishant Jani
Nishant Jani

Reputation: 1993

MySQL varchar (255) limitation & Anomaly

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 ?

Upvotes: 1

Views: 3819

Answers (2)

Weacked
Weacked

Reputation: 970

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

Upvotes: 2

Bud Damyanov
Bud Damyanov

Reputation: 31829

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.

Upvotes: 1

Related Questions