MySQL BIT_LENGTH() function is multi-byte safe or not?

MySQLMySQLi Database

Just like LENGTH() function, MySQL BIT_LENGTH() function is not a multi-byte safe function. As we know that the difference of the result between multi-byte safe functions, like CHAR_LENGTH() or CHARACTER_LENGTH(), and BIT_LENGTH() function especially relevant for Unicode, in which most of the characters are encoded in two bytes or relevant for UTF-8 where the number of bytes varies. It is demonstrated in the example below −

Example

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

The above result set shows that the bit length of string ‘tutorialspoint’ is 112 because it is yet not converted to Unicode character. The following query converts it into Unicode character −

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

After converting the string in Unicode, it gives the result 224 instead of 112 because in Unicode a single character takes 2-bytes as shown below −

mysql> Select BIT_LENGTH(@A);
+----------------+
| BIT_LENGTH(@A) |
+----------------+
| 224            |
+----------------+
1 row in set (0.00 sec)
raja
Published on 21-Feb-2018 11:30:26
Advertisements