Detailed Explanation of Data Types binary and varbinary in MySQL

  • 2021-08-31 09:31:02
  • OfStack

Preface

BINARY and VARBINARY are somewhat similar to CHAR and VARCHAR, except that BINARY and VARBINARY store binary strings instead of character strings. That is to say, BINARY and VARBINARY have no concept of character set, and their sorting and comparison are based on binary values.

BINARY(N) And VARBINARY(N) N in refers to byte length, while CHAR(N) And VARCHAR(N) In N refers to the character length of yes. For BINARY(10) Its storable bytes are fixed at 10, while for CHAR(10) Which can store bytes depending 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 1 table of type BINARY(1) Because the column of the BINARY(N) N stands for bytes, while the Chinese character "I" in gbk character set needs to occupy 2 bytes, so a warning is given when inserting, prompting that the character is truncated. If SQL_MODE is in strict mode, an error will be reported directly. Looking at the contents of table t, you can see that only the first byte of the character "I" is stored in a, and the last byte is truncated. If the characters in the a column of the table t are of type CHAR, the above problems 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 with CHAR and VARCHAR is that BINARY(N) And VARBINARY(N) The N value in represents the number of bytes, not the character length; The second difference is that when comparing characters, CHAR and VARCHAR only compare the characters stored in the characters themselves, ignoring the padding characters after the characters. For BINARY and VARBINARY, because 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, the comparison is character value, so the return value of the first comparison is 1. For BINARY and VARBINARY, the comparison is binary. The 106-ary value of "a" is 61, and the 106-ary value of "a" is 612020, which is obviously different, so the return value of the second comparison is 0.

The third difference is that for an BINARY string, the padding character is 0x00, while for an CHAR, the padding character is 0x20. Perhaps because the comparison of BINARY requires, 0x00 is obviously the smallest character for comparison, as shown below:


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

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

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

Summarize


Related articles: