In MySQL, if I create a new VARCHAR(32)
field in a UTF-8 table does it means I can store 32 bytes of data in that field or 32 chars (multi-byte)?
This answer showed up at the top of my google search results but wasn't correct.
The confusion is probably due to different versions of MySQL being tested.
Version 4 counts bytes
Version 5 counts characters
Here is the quote from the official MySQL 5 documentation:
MySQL interprets length specifications in character column definitions in character units. (Before MySQL 4.1, column lengths were interpreted in bytes.) This applies to CHAR, VARCHAR, and the TEXT types.
Interestingly (I hadn't thought about it) the max length of a varchar column is affected by utf8 as follows:
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. For example, utf8 characters can require up to three bytes per character, so a VARCHAR column that uses the utf8 character set can be declared to be a maximum of 21,844 characters.
it would let you store 32 multi-byte chars
To save space with UTF-8, use VARCHAR instead of CHAR. Otherwise, MySQL must reserve three bytes for each character in a CHAR CHARACTER SET utf8 column because that is the maximum possible length. For example, MySQL must reserve 30 bytes for a CHAR(10) CHARACTER SET utf8 column.
http://dev.mysql.com/doc/refman/5.0/en/charset-unicode.html
CHAR
and when I do it's not intended to store multi-byte chars, so I'm safe. What about VARCHAR
, are you sure the limit is defined in multi-byte chars and not on single-byte chars?
32 multibytes data for varchar(32)
with collation utf8_unicode_ci
, I just tested with XAMPP.
1234567890123456789012345678901234567890
Get truncated to:
12345678901234567890123456789012
Keep in mind that these are not regular ASCII chars.
utf8
, but then you get broken Unicode support in MySQL. You should use utf8mb4
encoding instead, because there are max. 4 bytes in a utf-8 char, not 3 as in MySQL's variant of utf8...
It is better to use "char" for high-frequent update tables because the total data length of the row will be fixed and fast. Varchar columns make row data sizes dynamic. That's not good for MyISAM, but I don't know about InnoDB and others. For example, if you have a very narrow "type" column, it may be better to use char(2) with latin1 charset to only claim minimal space.
CHAR
. For InnoDB, so many other things are going on that the "dynamic/fixed row size" debate is essentially irrelevant.
CHAR
.
If you connect to the database using latin1 encoding (for example with PHP) to save an PHP UTF8 string in an MySQL UTF8 column, you will have a double UTF8 encoding.
If the UTF8 string $s
is 32 characters long but 64 bytes long and the column is VARCHAR(32)
UTF8, the double encoding will convert the string $s
to a 64 characters long UTF8 string that will be truncated in the database to its 32 first characters corresponding to the 32 first bytes of $s
. You may end up thinking that MySQL 5 behaves like MySQL 4 but it is in fact a second cause for the same effect.
Success story sharing
utf8mb4
) can store "💩💩💩💩💩💩💩💩💩💩" (10 piles of poo), that's 10 characters but 40 bytes.