Binary and varbinary Data Types in MySQL, binaryvarbinary

Binary and varbinary Data Types in MySQL, binaryvarbinary

Preface

The BINARY and VARBINARY types are similar to those of the CHAR and VARCHAR types. The difference is that BINARY and VARBINARY stores BINARY strings instead of character strings. That is to say, BINARY and VARBINARY do not have the character set concept, so they are sorted and compared based on BINARY values.

BINARY（N）AndVARBINARY（N）N indicates the length of the byte, whileCHAR（N）AndVARCHAR（N）N indicates the character length. ForBINARY（10）Which can store 10 bytes at a fixed speed.CHAR（10）The bytes that can be stored depends on the character set.

Let's take a look at the example below.

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: 1265Message: Data truncated for column 'a' at row 11 row in set (0.00 sec)

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

Table t contains a typeBINARY（1）BecauseBINARY（N）N indicates the byte, while the Chinese character "I" in the gbk character set occupies 2 bytes. Therefore, a warning is given during insertion, prompting that the character is truncated. If SQL _MODE is in strict mode, an error is reported. View the contents of table t, you can find that only the first byte of the character "I" is stored in a, and the last byte is truncated. If the character type in column a of table t is CHAR, no such problem occurs. 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 (): CED21 row in set (0.00 sec)

BINARY and VARBINARY compare CHAR and VARCHAR. The first difference is thatBINARY（N）AndVARBINARY（N）The n value in represents the number of bytes rather than the character length. The second difference is that when comparing CHAR and VARCHAR, only the characters stored in the character itself are compared, the padding character after the character is ignored. For BINARY and VARBINARY, the result is very different because it is compared by BINARY value. For example:

mysql> SELECT -> HEX('a'), -> HEX('a '), -> 'a'='a '\G; *************************** 1. row ***************************HEX('a'): 61HEX('a '): 612020'a'='a ': 11 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 ')): 612020BINARY('a')= BINARY('a '): 01 row in set (0.00 sec)

For CHAR and VARCHAR, the character value is compared, so the first comparison returns 1. For BINARY and VARBINARY, the BINARY value is compared. The hexadecimal value of "a" is 61, and that of "a" is 612020, which is obviously different, therefore, the return value of the second comparison is 0.

The third difference is that for BINARY strings, the padding character is 0x00, And the padding character for CHAR is 0x20. It may be because BINARY is required for comparison. 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: aHEX(a): 6100001 row in set (0.00 sec)

Summary

The above is all the content of this article. I hope the content of this article will help you in your study or work. If you have any questions, please leave a message, thank you for your support.