Help Center/ GaussDB/ MySQL Compatibility(Centralized)/ M-compatible Mode/ Data Types/ Binary Data Types
Updated on 2025-06-30 GMT+08:00
View PDF
Share

Binary Data Types

Table 1 Binary Data Types

Data Type

Differences Compared with MySQL

BINARY[(M)]
  • Input format:
    • 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.
For other differences, see the description below the table.
NOTE:

Due to the differences between GaussDB and MySQL in BINARY fillers, 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 specific difference scenarios, see the examples in this section.

VARBINARY(M)

Character set: The default character set is the initialized character set of the database. For MySQL, the default character set is BINARY.

For other differences, see the description below the table.

TINYBLOB

For details about the differences, see the description below the table.

BLOB

For details about the differences, see the description below the table.

MEDIUMBLOB

For details about the differences, see the description below the table.

LONGBLOB

Value range: a maximum of 1 GB – 512 bytes. MySQL supports a maximum of 4 GB – 1 byte.

BIT[(M)]

Output formats:

  • GaussDB outputs a binary character string. In MySQL 5.7, the result is escaped based on the ASCII code table. If the result cannot be escaped, a character string that cannot be displayed is output. In MySQL 8.0, the hexadecimal result is output in the 0x format.
  • In MySQL 8.0 and later versions, 0 is added at the beginning of each result by default. In GaussDB, 0 is not added.

For other differences, see the description below the table.
  • For unescapable binary or hexadecimal strings, MySQL 5.7 outputs undisplayable strings; MySQL 8.0 outputs a hexadecimal result in the format of 0x; GaussDB outputs a hexadecimal result in the format of multiple \x.
  • For the TINYBLOB, BLOB, MEDIUMBLOB, and LONGBLOB types:
    • In MySQL, a default value cannot be set. However, in GaussDB, you can set a default value when creating a table column.
    • Primary key: MySQL does not support primary keys, but GaussDB does.
    • Index: MySQL supports only prefix indexes. GaussDB supports all index methods.
    • Foreign key: MySQL does not support any of these types to be the referencing column or referenced column of a foreign key, but GaussDB supports.

Example:

-- GaussDB
m_db=# CREATE TABLE test_blob(a blob);
CREATE TABLE

m_db=# INSERT INTO test_blob VALUES(0x1);
INSERT 0 1

m_db=# INSERT INTO test_blob VALUES(0x111111);
INSERT 0 1

m_db=# INSERT INTO test_blob VALUES(0x61);
INSERT 0 1

m_db=# SELECT * FROM test_blob;
      a       
--------------
 \x01
 \x11\x11\x11
 a
(3 rows)

m_db=# DROP TABLE test_blob;
DROP TABLE

-- MySQL 5.7
mysql> CREATE TABLE test_blob(a blob);
Query OK, 0 rows affected (0.02 sec)

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

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

mysql> INSERT INTO test_blob VALUES(0x61);
Query OK, 1 row affected (0.01 sec)

mysql> SELECT * FROM test_blob;
+------+
| a    |
+------+
|     |
|   |
| a    |
+------+
3 rows in set (0.00 sec)

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

-- MySQL 8.0
mysql> CREATE TABLE test_blob(a blob);
Query OK, 0 rows affected (0.08 sec)

mysql> INSERT INTO test_blob VALUES(0x1);
Query OK, 1 row affected (0.01 sec)

mysql> INSERT INTO test_blob VALUES(0x111111);
Query OK, 1 row affected (0.01 sec)

mysql> INSERT INTO test_blob VALUES(0x61);
Query OK, 1 row affected (0.01 sec)

mysql> SELECT * FROM test_blob;
+------------+
| a          |
+------------+
| 0x01       |
| 0x111111   |
| 0x61       |
+------------+
3 rows in set (0.00 sec)

mysql> DROP TABLE test_blob;
Query OK, 0 rows affected (0.04 sec)
Parent topic: Data Types