Which MySQL Function is Best for String Length Selection: CHAR_LENGTH() or LENGTH()?

MySQL String Length Selection

It is possible to select data based on the length of a string in MySQL. One way is to use the string_length() function, as shown in:

<code class="sql">SELECT * FROM table ORDER BY string_length(column);</code>

However, there is a more efficient function for this task: CHAR_LENGTH(). This function returns the number of characters in a string, regardless of character length (e.g., single-byte or multi-byte).

The LENGTH() function can also be used to determine the number of bytes occupied by a string. However, for multi-byte character sets, CHAR_LENGTH() is the preferred option as it returns the number of characters rather than bytes.

To illustrate the difference between LENGTH() and CHAR_LENGTH(), consider the following example:

SELECT LENGTH('abc'), CHAR_LENGTH('abc') FROM dual;

For UTF-8 character encoding, the result will be:

LENGTH: 3
CHAR_LENGTH: 3

However, if the character encoding is changed to UTF-16, the result will be:

LENGTH: 6
CHAR_LENGTH: 3

This is because UTF-16 uses two bytes to encode each character, while UTF-8 uses variable-length encoding (1 byte for ASCII characters, 2 bytes for Unicode characters). Therefore, CHAR_LENGTH() provides a more consistent and character-aware metric for string length in MySQL.

The above is the detailed content of Which MySQL Function is Best for String Length Selection: CHAR_LENGTH() or LENGTH()?. For more information, please follow other related articles on the PHP Chinese website!