How does the MySQL LENGTH() function measure string length?

The MySQL LENGTH() function measures string length in "bytes", which means it is not multibyte safe. The difference in results between multibyte-safe functions such as CHAR_LENGTH() or CHARACTER_LENGTH() and the LENGTH() function is particularly relevant to Unicode, where most characters are encoded in two bytes, or to UTF-8, The number of bytes varies. For example, if the string contains four 2-byte characters, the LENGTH() function returns 8, and the CHAR_LENGTH() or CHARACTER_LENGTH() function returns 4. As shown in the following example -

Example

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

The above result set shows that the length of the string "tutorialspoint" is 14 because it has not been converted to Unicode characters. The following query converts it to Unicode characters -

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

After converting the string to Unicode, the result is 28 instead of 14 because in Unicode a single character occupies 2 bytes as shown below-

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

But CHAR_LENGTH() gives result of 14 as 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)

The above is the detailed content of How does the MySQL LENGTH() function measure string length?. For more information, please follow other related articles on the PHP Chinese website!