BINARY operator

OceanBase Database supports the BINARY operator for forcibleconversion.

Syntax

Syntax of the BINARYoperator:

BINARY expr

The BINARY operator converts a string specified by expr into a binary string, that is, a string with the binary character set andcollation. In string comparison, BINARY isusually used to compare the binary values of strings, rather than to compare strings character by character. Trailing zeros need to be considered during comparison. Sample code:

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

Binary string conversion

In OceanBase Database, you can convert a string expression into a binary string in the following three ways:

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

Pay attention that the BINARY operator in the expression and the BINARY data type defined in the character column have different effects. For a column defined by using the BINARY attribute, the database assigns the default character set and the binary (_bin) collation of this character set to the table. Each non-binary character set has a binary collation. For example, if the default character set of a table is GBK, the following two column definitions are equivalent:

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

If you use CHARACTER SET binary in the definition of a CHAR, VARCHAR, or TEXT column, this column is treated as the corresponding binary string data type. The definitions in the following example are equivalent:

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

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

TEXT CHARACTER SET binary
<=>
BLOB