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!