Help Center> GaussDB> Service Overview> Compatibility with MySQL Databases> Primary/Standby Instances> MySQL Compatibility in M-Compatible Mode> Data Types> Binary Data Types
Updated on 2024-05-14 GMT+08:00
View PDF

Binary Data Types

Table 1 Binary data types

No.

MySQL

GaussDB

Difference

1

BINARY[(M)]

Supported, with differences
  • Input formats:
    • GaussDB does not support escape characters, but MySQL supports.
    • 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.
    NOTE:

    Due to the differences between GaussDB and MySQL in BINARY fillers and \0j 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:
    • GaussDB does not support escape characters, but MySQL supports.
    • 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 format: 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.

3

TINYBLOB

Supported, with differences
  • Input formats:
    • GaussDB does not support escape characters, but MySQL supports.
    • 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.

4

BLOB

Supported, with differences
  • Input formats:
    • GaussDB does not support escape characters, but MySQL supports.
    • 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.

5

MEDIUMBLOB

Supported, with differences
  • Input formats:
    • GaussDB does not support escape characters, but MySQL supports.
    • 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.

6

LONGBLOB

Supported, with differences
  • Value range: a maximum of 1 GB. MySQL supports a maximum of 4 GB minus 1 byte.
  • Input formats:
    • GaussDB does not support escape characters, but MySQL supports.
    • 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.

7

BIT[(M)]

Supported, with differences
  • Output format: All outputs are output 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.

Example:

-- GaussDB
m_db=# create table test(a binary(10));
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));
CREATE TABLE
m_db=# INSERT INTO test2 VALUES(0x80008000);
INSERT 0 1
m_db=# SELECT hex(a) FROM test2;
         hex
----------------------
 80202020202020202020
(1 row)

-- 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)
Parent topic: Data Types