U0400023: GaussDB does not support BINARY

Description

Database Type and Version

• Source database type and version: MySQL 5.5, 5.6, 5.7, and 8.0
• Target database type and version: all GaussDB versions

Syntax Example

This error is reported when BINARY is used in a SELECT statement to achieve case-sensitivity.

The default character set and collation rule for creating MySQL tables are utf8mb4 and utf8mb4_0900_ai_ci respectively, and data is case insensitive during queries. You can also specify the BINARY keyword during queries, so that MySQL can perform case-sensitive string comparison in binary mode. For example:

CREATE TABLE test_table (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(50)
);
INSERT INTO test_table (name) VALUES ('John Doe');
INSERT INTO test_table (name) VALUES ('john doe');
Case-sensitive search
SELECT * FROM test_table WHERE BINARY name = 'John Doe';
Case-insensitive search
SELECT * FROM test_table WHERE name = 'John Doe';

Suggestion

In GaussDB, queries are case sensitive by default, and BINARY of MySQL has the same effect. However, the default behaviors of MySQL and GaussDB are different.

To keep the default case-sensitive behavior consistent between GaussDB and MySQL, you can use either of the following methods:

1. GaussDB Centralized V2.0-8.0 and later is compatible with MySQL, so they have the same default case sensitivity behavior.

2. When creating a centralized GaussDB database compatible with B or distributed GaussDB database compatible with MySQL, specify UTF-8 and set GUC parameters b_format_dev_version to s1 and b_format_version to 5.7. In this case, the default behaviors of MySQL and GaussDB are the same.

When b_format_dev_version is set to s1, only the case sensitivity of table data queries in GaussDB is the same as that in MySQL, but constant character string matching is case sensitive. In GaussDB V2.0-8.100, b_format_dev_version can be set to s2. In this case, constant string comparison is case insensitive. For details, see GaussDB Developer Guide.

UGO does not convert BINARY. Whether a query is case sensitive depends on the GaussDB database settings. You are advised to fully evaluate and configure the database before migration. If you only need to make a single query case-sensitive, specify a case-sensitive collation rule. For example, the following statement is equivalent to BINARY of MySQL:

SELECT * FROM test_table WHERE name = 'John Doe' COLLATE utf8mb4_bin;

Figure 1 COLLATE utf8mb4_bin