Detailed explanation of binary and varbinary data types in MySQL

Preface

BINARY and VARBINARY are somewhat similar to CHAR and VARCHAR types, except that BINARY and VARBINARY store binary strings rather than character strings. In other words, BINARY and VARBINARY do not have the concept of character sets, and their sorting and comparison are all based on binary values.

The N in BINARY（N） and VARBINARY（N） refers to the byte length, while the N in CHAR（N） and VARCHAR（N） refers to the character length. For BINARY（10） , the number of bytes that can be stored is fixed at 10, while for CHAR（10） , the number of bytes that can be stored depends on the character set.

Let’s look at the following example.

mysql> CREATE TABLE t (
 -> a BINARY(1)
 ->)ENGINE=InnoDB CHARSET=GBK;
Query OK, 0 rows affected (0.02 sec)

mysql> SET NAMES GBK;
Query OK, 0 rows affected (0.00 sec)

MySQL> INSERT INTO t SELECT 'i';
Query OK, 1 row affected, 1 warning (0.01 sec)
Records: 1 Duplicates: 0 Warnings: 1

mysql> SHOW WARNINGS\G;
*************************** 1. row ***************************
 Level: Warning
 Code: 1265
Message: Data truncated for column 'a' at row 1
1 row in set (0.00 sec)

mysql> SELECT a,HEX(a) FROM t\G;
*************************** 1. row ***************************
 a:
HEX(a): CE

Table t contains a column of type BINARY（1） . Because N in BINARY（N） represents bytes, and the Chinese character "我" in the gbk character set requires 2 bytes, a warning is given during insertion, indicating that the character is truncated. If SQL_MODE is strict mode, an error will be reported directly. Looking at the contents of table t, we can find that only the first byte of the character "I" is stored in a, and the second byte is truncated. If the character type in column a of table t is CHAR, the above problem will not occur at all, for example:

mysql> CREATE TABLE t (
 -> a CHAR(1)
 ->)ENGINE=InnoDB CHARSET=GBK;
Query OK, 0 rows affected (0.02 sec)

mysql> INSERT INTO t SELECT 'I';
Query OK, 1 row affected, 1 warning (0.01 sec)
Records: 1 Duplicates: 0 Warnings: 0

mysql> SELECT a,HEX(a) FROM t\G;
*************************** 1. row ***************************
 a: I HEX (a): CED2
1 row in set (0.00 sec)

The first difference between BINARY and VARBINARY compared to CHAR and VARCHAR is that the N value in BINARY（N） and VARBINARY（N） represents the number of bytes, not the length of characters. The second difference is that when CHAR and VARCHAR compare characters, they only compare the characters stored in the characters themselves, ignoring the padding characters after the characters. For BINARY and VARBINARY, since they are compared according to binary values, the results will be very different. For example:

mysql> SELECT
 -> HEX('a'),
 -> HEX('a '),
 -> 'a'='a '\G; 
*************************** 1. row ***************************
HEX('a'): 61
HEX('a '): 612020
'a'='a': 1
1 row in set (0.00 sec)

mysql> SELECT
 -> HEX(BINARY('a')),
 -> HEX(BINARY('a ')),
 -> BINARY('a') = BINARY('a ')\G; 
*************************** 1. row ***************************
  HEX(BINARY('a')): 61
 HEX(BINARY('a ')): 612020
BINARY('a') = BINARY('a '): 0
1 row in set (0.00 sec)

For CHAR and VARCHAR, character values ​​are compared, so the return value of the first comparison is 1. For BINARY and VARBINARY, the comparison is on binary values. The hexadecimal value of "a" is 61, and the hexadecimal value of "a " is 612020, which are obviously different. Therefore, the return value of the second comparison is 0.

The third difference is that for BINARY strings, the fill character is 0x00, while the fill character for CHAR is 0x20. This may be because of the BINARY comparison requirement. 0x00 is obviously the minimum character for comparison. The example is as follows:

mysql> CREATE TABLE t ( a BINARY(3));
Query OK, 0 rows affected (0.00 sec)

mysql> INSERT INTO t SELECT 'a';
Query OK, 1 row affected (0.00 sec)
Records: 1 Duplicates: 0 Warnings: 0

mysql> SELECT a,HEX(a) FROM t\G;
*************************** 1. row ***************************
 a: a
HEX(a): 610000
1 row in set (0.00 sec)

Summarize

The above is the full content of this article. I hope that the content of this article can bring some help to your study or work. If you have any questions, you can leave a message to communicate. Thank you for your support of 123WORDPRESS.COM.