CODEWITHSUNDEEP
mysql
Shiv_k
Shiv_k

Reputation: 89

Wrong max length for char datatype in MySQL

When character encoding is latin1 (single-byte character set), below SQL statement execute without error.

create table chartype  (chardata char(255));

But When character encoding is UTF-8 (3 byte for each character) , 

create table chartype  (chardata char(255));

this statement should throw error but it is executed without any error. Max length for char datatype is 255 bytes, for UTF-8 encoding it should allow only below statement

create table chartype  (chardata char(85));

85*3=255 bytes , so 85 is max length for UTF-8 character set

Please clarify me.

Upvotes: 1

Views: 365

Answers (2)

Bill Karwin
Bill Karwin

Reputation: 562230

http://dev.mysql.com/doc/refman/5.7/en/storage-requirements.html says in part:

For a VARCHAR column that stores multibyte characters, the effective maximum number of characters is less. For example, utf8mb3 characters can require up to three bytes per character, so a VARCHAR column that uses the utf8mb3 character set can be declared to be a maximum of 21,844 characters.

The same applies to CHAR or TEXT, or any other data type that supports character sets.

Upvotes: 0

tadman
tadman

Reputation: 211540

When you say CHAR(255) you're creating a fixed length field that can accommodate 255 characters. This is distinct from bytes. UTF-8 characters vary in length from 1 to 4 bytes depending on which character you're talking about but the default in MySQL is to accommodate only 3-byte length characters.

To handle the full range of Unicode characters you need to use utf8mb4 encoding.

Be sure to use VARCHAR in preference to CHAR as CHAR is fixed length and creates a lot of wasted space.

Upvotes: 1

Related Questions