CODEWITHSUNDEEP
mysql
Oleg alex
Oleg alex

Reputation: 99

Mysql byte length

ive came across a website who state that the following:

Ex: CountryCode CHAR(3) CHARSET utf8

We are asking for a column with 3 characters exactly. The required storage for this column will be such that any 3-letter name must fit in. This means (3 characters) times (3 bytes per character) = 9 bytes of storage. So CHAR and utf8 together may be less than ideal. VARCHAR behaves better: it only requires as many bytes per character as described above. So the text "abc" will only require 3 bytes

Do i need for the text 'abc'(with utf8 and char(3)) 3 bytes or 9 bytes?! Thanks

Upvotes: 1

Views: 471

Answers (1)

Marc B
Marc B

Reputation: 360562

MySQL's internal structure places CHAR fields directly within the table structure, e.g. A simple table like:

create table foo (
   id int
   name char(3)
);

would produce an on-disk record that looks like

xxxxccccccccc
^^^^-- 4 bytes of int storage space
    ^^^^^^^^^ 9 bytes of utf-8 char space

Since MySQL has no way of knowing in advance what kind of text you'll be storing in that char field, it HAS to assume worst-case, and allocates as much space as 3 chars of 'absolutely the longest possible' utf-8 text might take. If it didn't, then an overly long string would overflow the on-disk storage and start scribbling on an adjacent record.

varchar, on the other hand, only has a small 'stub' data section in the table's raw data, and the varchar's contents are stored elsewhere. That means that your varchar(3) will always occupy the same amount of table-space storage, no matter WHAT kind of character set you're using.

Upvotes: 2

Related Questions