BINARY conversion operators

OceanBase Database supports the BINARY conversion operator.

Syntax of the BINARY operator

The syntax of the BINARY operator is as follows:

BINARY expr

The BINARY operator converts the expression expr to a binary string (a string with the binary character set and binary collation). In string comparisons, BINARY is often used to compare the binary values of strings rather than character by character, and trailing spaces are considered in the comparison. Here is an example:

obclient> SELECT 'aa' = 'AA';
+-------------+
| 'aa' = 'AA' |
+-------------+
|           1 |
+-------------+
1 row in set

obclient> SELECT BINARY 'aa' = 'AA';
+--------------------+
| BINARY 'aa' = 'AA' |
+--------------------+
|                  0 |
+--------------------+
1 row in set

obclient> SELECT 'aa' = 'aa ';
+--------------+
| 'aa' = 'aa ' |
+--------------+
|            1 |
+--------------+
1 row in set

obclient> SELECT BINARY 'aa' = 'aa ';
+---------------------+
| BINARY 'aa' = 'aa ' |
+---------------------+
|                   0 |
+---------------------+
1 row in set

Notes on binary string conversion

In OceanBase Database, you can use the following three methods to convert a string expression to a binary string:

CONVERT(expr USING BINARY)
CAST(expr AS BINARY)
BINARY expr

Note that the BINARY operator in an expression has a different effect from the BINARY data type in a character column definition. For a column defined with the BINARY attribute, the database assigns the default character set of the table and the binary (_bin) collation of that character set. Each non-binary character set has a corresponding _bin collation. For example, if the default character set of the table is gbk, the following two column definitions are equivalent:

CHAR(5) BINARY
<=>
CHAR(5) CHARACTER SET gbk COLLATE gbk_bin

If you specify CHARACTER SET binary in the definition of a CHAR, VARCHAR, or TEXT column, the column is considered to have the corresponding binary string data type. The following examples are equivalent:

CHAR(5) CHARACTER SET binary
<=>
BINARY(5)

VARCHAR(10) CHARACTER SET binary
<=>
VARBINARY(10)

TEXT CHARACTER SET binary
<=>
BLOB