MariaDB BINARY Operator

In MariaDB, BINARY is a built-in operator that converts a given string to a binary string.

When comparing binary strings, MariaDB compares byte by byte rather than character by character. Spaces at both ends of the string also participate in the comparison.

MariaDB BINARY Syntax

Here is the syntax of the MariaDB BINARY function:

BINARY str

Operands

str

It is an normal string that will be converted to a binary string.

MariaDB BINARY Examples

Basic example

The following statement shows how to use the MariaDB BINARY operator to convert a normal string to a binary string:

SELECT BINARY 'Hello';

Output:

+----------------+
| BINARY 'Hello' |
+----------------+
| Hello          |
+----------------+

This example doesn’t tell much. Let’s continue.

Compare - trailing whitespace

When comparing normal strings, MariaDB ignores trailing whitespace:

SELECT
  'Hello' = 'Hello',
  'Hello' = 'Hello ';

Output:

+-------------------+--------------------+
| 'Hello' = 'Hello' | 'Hello' = 'Hello ' |
+-------------------+--------------------+
|                 1 |                  1 |
+-------------------+--------------------+

Here, 'Hello' = 'Hello ' returned 1 because MariaDB ignores trailing spaces in 'Hello '.

If you think trailing spaces are important and want a different result, use the BINARY operator:

SELECT
  'Hello' = 'Hello ',
  BINARY 'Hello' = 'Hello ',
  'Hello' = BINARY 'Hello '\G

Output:

       'Hello' = 'Hello ': 1
BINARY 'Hello' = 'Hello ': 0
'Hello' = BINARY 'Hello ': 0

or

SELECT BINARY 'Hello' = BINARY 'Hello ';

Output:

+----------------------------------+
| BINARY 'Hello' = BINARY 'Hello ' |
+----------------------------------+
|                                0 |
+----------------------------------+

Comparison - case sensitive

If you use a case-insensitive collation (eg: utf8_general_ci), MariaDB ignores case when comparing strings. Binary strings enforce case sensitivity.

First, check the current collation with the following statement:

SELECT @@collation_connection;

Output:

+------------------------+
| @@collation_connection |
+------------------------+
| utf8mb4_general_ci     |
+------------------------+

NOTE: endings with ci indicates case insensitivity.

At this point, MariaDB considers that 'hello' and 'Hello' is the same:

SELECT 'hello' = 'Hello';

Output:

+-------------------+
| 'hello' = 'Hello' |
+-------------------+
|                 1 |
+-------------------+

If you need to be case-sensitive when comparing, please use the BINARY operator, as follows:

SELECT
  BINARY 'hello' = 'Hello',
  'hello' = BINARY 'Hello',
  BINARY 'hello' = BINARY 'Hello'\G

Output:

       BINARY 'hello' = 'Hello': 0
       'hello' = BINARY 'Hello': 0
BINARY 'hello' = BINARY 'Hello': 0

Conclusion

In MariaDB, BINARY is a built-in operator that converts a given string to a binary string.