MySQL VARCHAR size limit

问题:

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?

回答1:

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 ).

回答2:

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.

回答3:

Small extra local note. The number of bytes used will depend on the encoding scheme in use. 1 byte per character in latin1 encoding, but up to 3 in UTF8. See link in mlambie's answer for details.

回答4:

If you look here it should tell you everything about varchar you want to know: http://dev.mysql.com/doc/refman/5.0/en/char.html

Basically, depending on the length you chose it will use 1 or two bytes to track the length of the current string in that column, so it will store the number of bytes for the data you put in, plus one or two bytes.

So, if you put in 'abc' then it will be 4 or 5 bytes used for that column in that row.

If you used char(15) then even 'abc' would take up 15 bytes, as the data is the right-padded to use up the full 15 bytes.