MySQL length and char_length difference
In MySQL, length and char length functions are used to find number of characters present in string. So which one is best to use and how it differs?
I often heard CHAR_LENGTH() is multibyte safe and trustable etc... so i wanted to know what is the actual difference between these two functions. So, here is my findings:
This is because of encoding method of characters in UTF-8. As given in Wiki, "UTF-8 uses one byte for any ASCII characters, all of which have the same code values in both UTF-8 and ASCII encoding, and up to four bytes for other characters"
Just run this example in mysql, then you will have a clear idea:
Output:
+---------------------------+----------------------------------+
| length(_utf8 '©') | char_length(_utf8 '©') |
+---------------------------+----------------------------------+
| 2 | 1 |
+---------------------------+----------------------------------+
So using char_length() seems to be giving precise results.
I often heard CHAR_LENGTH() is multibyte safe and trustable etc... so i wanted to know what is the actual difference between these two functions. So, here is my findings:
LENGTH() measures string length in "bytes"
CHAR_LENGTH() measures string length in "characters"
This is because of encoding method of characters in UTF-8. As given in Wiki, "UTF-8 uses one byte for any ASCII characters, all of which have the same code values in both UTF-8 and ASCII encoding, and up to four bytes for other characters"
Just run this example in mysql, then you will have a clear idea:
select length(_utf8 '©'), char_length(_utf8 '©');
Output:
+---------------------------+----------------------------------+
| length(_utf8 '©') | char_length(_utf8 '©') |
+---------------------------+----------------------------------+
| 2 | 1 |
+---------------------------+----------------------------------+
So using char_length() seems to be giving precise results.
Comments (0)