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)?
MySQL VARCHAR Lengths and UTF-8
Viewed 65610
5 Answers
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.
32 multibytes data for varchar(32) with collation utf8_unicode_ci, I just tested with XAMPP.
１２３４５６７８９０１２３４５６７８９０１２３４５６７８９０１２３４５６７８９０
Get truncated to:
１２３４５６７８９０１２３４５６７８９０１２３４５６７８９０１２
Keep in mind that these are not regular ASCII chars.
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.
Related