string – MySQL – length() vs char_length()

The Question :

219 people think this question is useful

What’s the main difference between length() and char_length()?

I believe it has something to do with binary and non-binary strings. Is there any practical reason to store strings as binary?

mysql> select length('MySQL'), char_length('MySQL');
+-----------------+----------------------+
| length('MySQL') | char_length('MySQL') |
+-----------------+----------------------+
|               5 |                    5 |
+-----------------+----------------------+
1 row in set (0.01 sec)

The Question Comments :
  • Yes, there is practical reasons to store binary strings when they actually are binary strings, e.g. compressed.

The Answer 1

356 people think this answer is useful

LENGTH() returns the length of the string measured in bytes.
CHAR_LENGTH() returns the length of the string measured in characters.

This is especially relevant for Unicode, in which most characters are encoded in two bytes. Or UTF-8, where the number of bytes varies. For example:

select length(_utf8 '€'), char_length(_utf8 '€')
--> 3, 1

As you can see the Euro sign occupies 3 bytes (it’s encoded as 0xE282AC in UTF-8) even though it’s only one character.

The Answer 2

19 people think this answer is useful

varchar(10) will store 10 characters, which may be more than 10 bytes. In indexes, it will allocate the maximium length of the field – so if you are using UTF8-mb4, it will allocate 40 bytes for the 10 character field.

Add a Comment