OceanBase AI Database supports the BINARY coercion operator.
Syntax of the BINARY operator
The syntax for the BINARY operator is as follows:
BINARY expr
The BINARY operator converts the expression expr into a binary string (a string with a binary character set and binary collation). In string comparison, BINARY is used to compare the binary values of strings rather than comparing them character by character, and trailing spaces are taken into account. 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
Description of binary string conversion
In OceanBase AI Database, you can convert a string expression to a binary string using one of the following three methods:
CONVERT(expr USING BINARY)
CAST(expr AS BINARY)
BINARY expr
Note that the BINARY operator in expressions has a different effect from the BINARY data type used in character column definitions. For a column defined with the BINARY attribute, the database assigns the table's default character set and its binary (_bin) collation. Every non-binary character set has a _bin collation. For example, if the table's default character set is GBK, then the following two column definitions are equivalent:
CHAR(5) BINARY
<=>
CHAR(5) CHARACTER SET gbk COLLATE gbk_bin
Using CHARACTER SET binary in the definition of a CHAR, VARCHAR, or TEXT column causes that column to be treated as the corresponding binary string data type. The definitions in the following examples are equivalent:
CHAR(5) CHARACTER SET binary
<=>
BINARY(5)
VARCHAR(10) CHARACTER SET binary
<=>
VARBINARY(10)
TEXT CHARACTER SET binary
<=>
BLOB