Help Center/ GaussDB/ Service Overview/ Compatibility with MySQL Databases/ Distributed Instances/ MySQL Compatibility in M-compatible Mode/ Data Types/ Binary Data Types
Updated on 2024-11-11 GMT+08:00
View PDF
Share

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=# 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 test;
DROP TABLE
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> 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 test;
Query OK, 0 rows affected (0.00 sec)
mysql> DROP TABLE test2;
Query OK, 0 rows affected (0.00 sec)
Parent topic: Data Types