BINARY conversion operators

seekdb 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:

SELECT 'aa' = 'AA';
+-------------+
| 'aa' = 'AA' |
+-------------+
|           1 |
+-------------+
1 row in set (0.001 sec)

SELECT BINARY 'aa' = 'AA';
+--------------------+
| BINARY 'aa' = 'AA' |
+--------------------+
|                  0 |
+--------------------+
1 row in set (0.001 sec)

SELECT 'aa' = 'aa ';
+--------------+
| 'aa' = 'aa ' |
+--------------+
|            1 |
+--------------+
1 row in set (0.000 sec)

SELECT BINARY 'aa' = 'aa ';
+---------------------+
| BINARY 'aa' = 'aa ' |
+---------------------+
|                   0 |
+---------------------+
1 row in set (0.000 sec)

Notes on binary string conversion

In seekdb, you can convert a string expression to a binary string using the following three methods:

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

Note that the BINARY operator in an expression has different effects from the BINARY data type in a character column definition. For columns defined with the BINARY attribute, the database assigns the default character set of the table and the corresponding binary (_bin) collation. Each non-binary character set has a _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

Using CHARACTER SET binary in the definition of a CHAR, VARCHAR, or TEXT column causes the column to be treated as a binary string data type. The following example demonstrates this:

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

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

TEXT CHARACTER SET binary
<=>
BLOB