How MySQL LENGTH() function measures the string length?

MySQL LENGTH() function measures the string length in ‘bytes’ which means that it is not multibyte safe. The difference of the result between multi-byte safe functions, like CHAR_LENGTH() or CHARACTER_LENGTH(), and LENGTH() function especially relevant for Unicode, in which most of the characters are encoded in two bytes or relevant for UTF-8 where the number of bytes varies. For example, if a string contains four 2-bytes characters then LENGTH() function will return 8, whereas CHAR_LENGTH() or CHARACTER_LENGTH() function will return 4. It is demonstrated in the example below −

Example

mysql> Select LENGTH('tutorialspoint');
+--------------------------+
| LENGTH('tutorialspoint') |
+--------------------------+
|                       14 |
+--------------------------+
1 row in set (0.00 sec)

The above result set shows that the length of string ‘tutorialspoint’ is 14 because it is yet not converted to Unicode character. The following query converts it into Unicode character −

mysql> SET @A = CONVERT('tutorialspoint' USING ucs2);
Query OK, 0 rows affected (0.02 sec)

After converting the string in Unicode, it gives the result 28 instead of 14 because in Unicode a single character takes 2-bytes as shown below −

mysql> Select LENGTH(@A);
+------------+
| LENGTH(@A) |
+------------+
|         28 |
+------------+
1 row in set (0.00 sec)

But CHAR_LENGTH() gives the result as 14 because it is multi-byte safe function as shown below −

mysql> Select CHAR_LENGTH(@A);
+-----------------+
| CHAR_LENGTH(@A) |
+-----------------+
|              14 |
+-----------------+
1 row in set (0.00 sec)