What's the main difference between length() and char_length()?
I believe it has something to do with binary and non-binary strings. Is there any practical reason to store strings as binary?
mysql> select length('MySQL'), char_length('MySQL');
+-----------------+----------------------+
| length('MySQL') | char_length('MySQL') |
+-----------------+----------------------+
| 5 | 5 |
+-----------------+----------------------+
1 row in set (0.01 sec)
Though here answer provided by @Andomar is correct, I am tempted to provide a more detailed answer.
NAME IN ENGLISH -
Suppose I create a variable @name storing my name -
SET @name = "Payel Senapati";
Now, I create a variable total_characters to store the number of characters my name occupy -
SET @total_characters = CHAR_LENGTH(@name);
SELECT @total_characters;
+-------------------+
| @total_characters |
+-------------------+
| 14 |
+-------------------+
Case 1:
I create a variable @test to store @name converted to latin1 character set -
SET @test = CONVERT(@name USING latin1);
I create a variable @total_bytes and store the length of @test in terms of bytes -
SET @total_bytes = LENGTH(@test);
SELECT @total_bytes;
+--------------+
| @total_bytes |
+--------------+
| 14 |
+--------------+
Now, latin1 character set allocated 1 byte per character.
Thus, @total_characters = @total_bytes
Case 2:
Now, in variable @test I store @name converted to ucs2 character set -
SET @test = CONVERT(@name USING ucs2);
Now, in variable @total_bytes I store the length of @test in terms of bytes -
SET @total_bytes = LENGTH(@test);
SELECT @total_bytes;
+--------------+
| @total_bytes |
+--------------+
| 28 |
+--------------+
Now, ucs2 character set allocated 2 bytes per character.
Thus, 2 * @total_characters = @total_bytes
NAME IN HINDI -
Now, I store my name in variable @name in Hindi -
SET @name = "पायल सेनापति";
Now, in variable total_characters I store the number of characters my name occupy in Hindi -
SET @total_characters = CHAR_LENGTH(@name);
SELECT @total_characters;
+-------------------+
| @total_characters |
+-------------------+
| 14 |
+-------------------+
Case 1:
Now, in variable @test I store @name converted to ucs2 character set -
SET @test = CONVERT(@name USING ucs2);
Now, in variable @total_bytes I store the length of @test in terms of bytes -
SET @total_bytes = LENGTH(@test);
SELECT @total_bytes;
+--------------+
| @total_bytes |
+--------------+
| 28 |
+--------------+
Now, ucs2 character set allocated 2 bytes per character.
Thus, 2 * @total_characters = @total_bytes
Case 2:
Now, in variable @test I store @name converted to utf32 character set -
SET @test = CONVERT(@name USING utf32);
Now, in variable @total_bytes I store the length of @test in terms of bytes -
SET @total_bytes = LENGTH(@test);
SELECT @total_bytes;
+--------------+
| @total_bytes |
+--------------+
| 56 |
+--------------+
Now, utf32 character set allocates 4 bytes per character.
Thus, 4 * @total_characters = @total_bytes
To see all character sets supported by MySQL use -
SHOW CHARACTER SET;