Menu
If I have a column in table with field of type VARCHAR(15) and if I try to insert data of length 16, MySQL gives an error stating
Data too long for column 'testname' at row 1
Does anyone know why VARCHAR fields in MySQL take fixed length? Also how many bytes does a VARCHAR field take per record based on the size given?
968
varchar [ ( n | max ) ] Use n to define the string size in bytes and can be a value from 1 through 8,000, or use max to indicate a column constraint size up to a maximum storage of 2^31-1 bytes (2 GB).
VARCHAR(255) stores 255 characters, which may be more than 255 bytes.
The maximum key length is 1000 bytes. This can also be changed by changing the source and recompiling. For the case of a key longer than 250 bytes, a larger key block size than the default of 1024 bytes is used. As an index is created for the key and as it is working without an index, this makes sense.
Yes, is matter when you indexing multiple columns. Prefixes can be up to 1000 bytes long (767 bytes for InnoDB tables). Note that prefix limits are measured in bytes, whereas the prefix length in CREATE TABLE statements is interpreted as number of characters.
From the MySQL 5.0 Manual:
Values in VARCHAR columns are variable-length strings. The length can be specified as a value from 0 to 255 before MySQL 5.0.3, and 0 to 65,535 in 5.0.3 and later versions. The effective maximum length of a VARCHAR in MySQL 5.0.3 and later is subject to the maximum row size (65,535 bytes, which is shared among all columns) and the character set used.
I only use VARCHAR when I'm certain that the data the column needs to hold will never exceed a certain length, and even then I'm cautious. If I'm storing a text string I tend to use one of the TEXT types.
Check out the MySQL Storage Requirements for more information on how the bytes are used.
187
If you set a column to be varchar(15) the maximum bytes allowed is 15. Thus you can't pass it more than 15 characters without modifying the column to support more than 15. If you store a 4 character string it should only use around 4 bytes out of a possible 15, whereas if you used char(15) it would have filled in the other 11 with empty bytes.
http://dev.mysql.com/doc/refman/5.0/en/char.html
( My byte calculation was probably off since it's always -1/+1 or something like that ).
35
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
