CODEWITHSUNDEEP
mysql
Gregory Magarshak
Gregory Magarshak

Reputation: 2217

How does MySQL interpret VARCHAR field size?

Suppose a field is declared thusly:

a VARCHAR(255)

How many characters can be stored in it, is it 255 or 256? And how much space is used?

Should we use a power of 2 and then subtract 1, or it doesn’t matter?

Upvotes: 2

Views: 780

Answers (2)

MontyPython
MontyPython

Reputation: 3012

In MySQL, varchar(255) would use 255 bytes (maximum) to store the data and 1 byte to store metadata (length information) about that data. Essentially, it would be 2^8. Now, how many characters can you store in 255 bytes depends on what character set you are using.

The number of bytes do you need to address & store a character depends upon how many characters does the character set have in total.

Upvotes: -1

Michael - sqlbot
Michael - sqlbot

Reputation: 179442

A VARCHAR(255) can store up to 255 characters, regardless of the number of bytes per character required by the character set encoding.

The storage requirement is the length of the actual data stored (not the maximum), plus 1 or 2 bytes to store the length of the data -- 1 byte is used unless the maximum possible length in bytes > 255... so a VARCHAR(255) COLLATE utf8mb4 uses 2 bytes to store the length, while a VARCHAR(255) COLLATE ascii_general_ci uses 1 byte to store the length. Either column can store not more than 255 characters.

Declare the column size as appropriate for the data being stored. Using 255 is common, but usually a red flag of sloppy design, since it's rare that this particular value meaningfully represents the maximum appropriate length of a column.

By contrast, a CHAR(255) COLLATE utf8mb4 always consumes 255 × 4 (the maximum possible) bytes per column per row, and 0 bytes to store the length, since the stored length does not vary. These columns are rarely appropriate, except when the column is always a known length and the character set is single-byte, such as a UUID, which would be CHAR(36) COLLATE ascii_general_ci.

https://dev.mysql.com/doc/refman/5.7/en/storage-requirements.html#data-types-storage-reqs-strings

Upvotes: 3

Related Questions