What is the difference between the MySQL LENGTH() and CHAR_LENGTH() functions?

Both these functions are string functions and return the number of characters present in the string. But they differ in that the CHAR_LENGTH() function measures the string length in "characters" while the LENGTH() function measures the string length in "bytes". In other words, we can say that the CHAR_LENGTH() function is multibyte safe, i.e. it ignores whether a character is single byte or multibyte. For example, if a string contains four 2-byte characters, LENGTH().

This function will return 8, while the CHAR_LENGTH() function will return 4. In this sense, we can say that CHAR_LENGTH() gives more precise results than the LENGTH() function.

This difference is particularly relevant to Unicode, where most characters are encoded in two bytes, or to UTF-8, where the number of bytes varies. The following example demonstrates -

Example

In the following example, first, the string 'Gaurav' is converted to ucs2 strong> i.e. Unicode, saving 2 bytes Character, character set. We can then observe the difference in the result set, i.e. LENGTH() returns the length in bytes, while CHAR_LENGTH() returns the length in characters.

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

mysql> Select Char_length(@A), LENGTH(@A);

+-----------------+------------+
| Char_length(@A) | LENGTH(@A) |
+-----------------+------------+
| 6               | 12         |
+-----------------+------------+

1 row in set (0.03 sec)

Now, in the example below, we are using special characters in UTF-8, the number of bytes in the character set is different. We can then observe the differences from the result set.

mysql> SET @B = CONVERT('©' USING utf8);
Query OK, 0 rows affected (0.15 sec)

mysql> Select CHAR_LENGTH(@B);

+-----------------------+
| CHAR_LENGTH(@B)       |
+-----------------------+
| 1                     |
+-----------------------+

1 row in set (0.00 sec)

mysql> Select LENGTH(@B);

+---------------+
| LENGTH(@B)    |
+---------------+
| 2             |
+---------------+

1 row in set (0.00 sec)

The above is the detailed content of What is the difference between the MySQL LENGTH() and CHAR_LENGTH() functions?. For more information, please follow other related articles on the PHP Chinese website!