Binary Data Types

**Table 1** Binary data types
No.	MySQL	GaussDB	Difference
1	BINARY[(M)]	Supported, with differences	Input formats: After a binary or hexadecimal character string is entered, GaussDB outputs a hexadecimal character string, and MySQL escapes the character string based on the ASCII code table. If the character string cannot be escaped, the output is empty. If the length of the inserted string is less than the target length, the padding character is 0x20 in GaussDB and 0x00 in MySQL. Character set: The default character set is the initialized character set of the database. For MySQL, the default character set is BINARY. Output formats: When the JDBC protocol is used, a space at the end of the BINARY type is displayed as a space, and that in MySQL is displayed as \x00. In loose mode, if characters (such as Chinese characters) of the BINARY type exceed n bytes, the excess characters will be truncated. MySQL retains the first n bytes. However, garbled characters are displayed in the output. In MySQL 8.0 and later versions, results starting with 0x are returned by default. In GaussDB, results in the format of "\x...\x...\x..." are returned. NOTE: Due to the differences between GaussDB and MySQL in BINARY fillers and \0 truncation, GaussDB and MySQL have different performance in scenarios such as operator comparison calculation, character string-related system function calculation, index matching, and data import and export. For details about the difference scenarios, see the examples in this section.
2	VARBINARY(M)	Supported, with differences	Input formats: After a binary or hexadecimal character string is entered, GaussDB outputs a hexadecimal character string, and MySQL escapes the character string based on the ASCII code table. If the character string cannot be escaped, the output is empty. Character set: The default character set is the initialized character set of the database. For MySQL, the default character set is BINARY. Output formats: When the JDBC protocol is used, a space at the end of the BINARY type is displayed as a space, and that in MySQL is displayed as \x00. In MySQL 8.0 and later versions, results starting with 0x are returned by default. In GaussDB, results in the format of "\x...\x...\x..." are returned.
3	TINYBLOB	Supported, with differences	Input formats: Default value: When creating a table column, you can set a default value in the syntax. MySQL does not allow you to set a default value. After a binary or hexadecimal character string is entered, GaussDB outputs a hexadecimal character string, and MySQL escapes the character string based on the ASCII code table. If the character string cannot be escaped, the output is empty. Primary key: In MySQL, the TINYBLOB type does not support primary keys, but GaussDB supports. Index: In MySQL, the TINYBLOB type does not support other index methods except prefix indexes. GaussDB supports these index methods. Foreign key: In MySQL, the TINYTEXT type cannot be used as the referencing column or referenced column of a foreign key, but GaussDB supports this operation. Output formats: In MySQL 8.0 and later versions, results starting with 0x are returned by default. In GaussDB, results in the format of "\x...\x...\x..." are returned.
4	BLOB	Supported, with differences	Input formats: Default value: When creating a table column, you can set a default value in the syntax. MySQL does not allow you to set a default value. After a binary or hexadecimal character string is entered, GaussDB outputs a hexadecimal character string, and MySQL escapes the character string based on the ASCII code table. If the character string cannot be escaped, the output is empty. Primary key: In MySQL, the BLOB type does not support primary keys, but GaussDB supports. Index: In MySQL, the BLOB type does not support other index methods except prefix indexes. GaussDB supports these index methods. Foreign key: In MySQL, the TINYTEXT type cannot be used as the referencing column or referenced column of a foreign key, but GaussDB supports this operation. Output formats: In MySQL 8.0 and later versions, results starting with 0x are returned by default. In GaussDB, results in the format of "\x...\x...\x..." are returned.
5	MEDIUMBLOB	Supported, with differences	Input formats: Default value: When creating a table column, you can set a default value in the syntax. MySQL does not allow you to set a default value. After a binary or hexadecimal character string is entered, GaussDB outputs a hexadecimal character string, and MySQL escapes the character string based on the ASCII code table. If the character string cannot be escaped, the output is empty. Primary key: In MySQL, the MEDIUMBLOB type does not support primary keys, but GaussDB supports. Index: In MySQL, the MEDIUMBLOB type does not support other index methods except prefix indexes. GaussDB supports these index methods. Foreign key: In MySQL, the TINYTEXT type cannot be used as the referencing column or referenced column of a foreign key, but GaussDB supports this operation. Output formats: In MySQL 8.0 and later versions, results starting with 0x are returned by default. In GaussDB, results in the format of "\x...\x...\x..." are returned.
6	LONGBLOB	Supported, with differences	Value range: a maximum of 1 GB. MySQL supports a maximum of 4 GB minus 1 byte. Input formats: Default value: When creating a table column, you can set a default value in the syntax. MySQL does not allow you to set a default value. After a binary or hexadecimal character string is entered, GaussDB outputs a hexadecimal character string, and MySQL escapes the character string based on the ASCII code table. If the character string cannot be escaped, the output is empty. Primary key: In MySQL, the LONGBLOB type does not support primary keys, but GaussDB supports. Index: In MySQL, the LONGBLOB type does not support other index methods except prefix indexes. GaussDB supports these index methods. Foreign key: In MySQL, the TINYTEXT type cannot be used as the referencing column or referenced column of a foreign key, but GaussDB supports this operation. Output formats: In MySQL 8.0 and later versions, results starting with 0x are returned by default. In GaussDB, results in the format of "\x...\x...\x..." are returned.
7	BIT[(M)]	Supported, with differences	Output formats: All outputs are displayed as binary character strings. MySQL escapes the character string based on the ASCII code table. If the character string cannot be escaped, the output is empty. In MySQL 8.0 and later versions, 0 is added at the beginning of each result by default. In GaussDB, 0 is not added.

Example:

-- GaussDB
m_db=# CREATE TABLE test(a BINARY(10)) DISTRIBUTE BY REPLICATION;
CREATE TABLE
m_db=# INSERT INTO test VALUES(0x8000);
INSERT 0 1
m_db=# SELECT hex(a) FROM test;
         hex
----------------------
 80202020202020202020
(1 row)

m_db=# SELECT * FROM test WHERE hex(a) = 80000000000000000000;
 a
---
(0 rows)
m_db=# DROP TABLE test;
DROP TABLE

m_db=# CREATE TABLE test2(a BINARY(10)) DISTRIBUTE BY REPLICATION;
CREATE TABLE
m_db=# INSERT INTO test2 VALUES(0x80008000);
INSERT 0 1
m_db=# SELECT hex(a) FROM test2;
         hex
----------------------
 80202020202020202020
(1 row)

m_db=# DROP TABLE test2;
DROP TABLE

-- MySQL
mysql> CREATE TABLE test(a BINARY(10));
Query OK, 0 rows affected (0.01 sec)

mysql> INSERT INTO test VALUES(0x8000);
Query OK, 1 row affected (0.00 sec)

mysql> SELECT hex(a) FROM test;
+----------------------+
| hex(a)               |
+----------------------+
| 80000000000000000000 |
+----------------------+
1 row in set (0.00 sec)

mysql> SELECT * FROM test WHERE hex(a) = 80000000000000000000;
+------------+
| a          |
+------------+
| ▒           |
+------------+
1 row in set (0.00 sec)

mysql> DROP TABLE test;
Query OK, 0 rows affected (0.00 sec)

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

mysql> INSERT INTO test2 VALUES(0x80008000);
Query OK, 1 row affected (0.00 sec)

mysql> SELECT hex(a) FROM test2;
+----------------------+
| hex(a)               |
+----------------------+
| 80008000000000000000 |
+----------------------+
1 row in set (0.00 sec)

mysql> DROP TABLE test2;
Query OK, 0 rows affected (0.00 sec)

Parent topic: Data Types

Previous topic: String Data Types

Next topic: JSON