In MariaDB, the BINARY operator casts the string following it to a binary string.

The BINARY operator enables you to do a column comparison byte by byte rather than character by character. This causes the comparison to be case sensitive even if the column isn’t defined as BINARY or BLOB. It also means that leading/trailing spaces become significant.

Example

Here’s a simple example:

SELECT BINARY 'Cat';

Result:

+--------------+
| BINARY 'Cat' |
+--------------+
| Cat          |
+--------------+

This example doesn’t really demonstrate how the BINARY operator can affect string comparisons. The following examples do.

Leading/Trailing Spaces

As mentioned, leading and trailing spaces are significant when comparing strings byte by byte (i.e. with BINARY).

First, here’s a string comparison without any leading or trailing spaces:

SELECT 
    'Cat' = 'Cat',
    BINARY 'Cat' = 'Cat';

Result:

+---------------+----------------------+
| 'Cat' = 'Cat' | BINARY 'Cat' = 'Cat' |
+---------------+----------------------+
|             1 |                    1 |
+---------------+----------------------+

We get the same return value whether we use BINARY or not.

But here’s what happens when we add trailing spaces to one of the strings:

SELECT 
    'Cat' = 'Cat ',
    BINARY 'Cat' = 'Cat ',
    'Cat' = BINARY 'Cat ',
    BINARY 'Cat' = BINARY 'Cat ';

Result:

+----------------+-----------------------+-----------------------+------------------------------+
| 'Cat' = 'Cat ' | BINARY 'Cat' = 'Cat ' | 'Cat' = BINARY 'Cat ' | BINARY 'Cat' = BINARY 'Cat ' |
+----------------+-----------------------+-----------------------+------------------------------+
|              1 |                     0 |                     0 |                            0 |
+----------------+-----------------------+-----------------------+------------------------------+

The first one returns 1 whereas the others (i.e. the ones with BINARY) return 0.

Just to be sure, let’s compare strings that both have trailing spaces:

SELECT
    'Cat ' = BINARY 'Cat ',
    BINARY 'Cat ' = BINARY 'Cat ';

Result:

+------------------------+-------------------------------+
| 'Cat ' = BINARY 'Cat ' | BINARY 'Cat ' = BINARY 'Cat ' |
+------------------------+-------------------------------+
|                      1 |                             1 |
+------------------------+-------------------------------+

Bear in mind that we’re only talking about leading and trailing spaces here. This concept doesn’t apply to spaces in the middle of a string.

For example, two spaces in a string is not equal to one space – even without using the BINARY operator:

SELECT 'Homer Jay' = 'Homer  Jay';

Result:

+----------------------------+
| 'Homer Jay' = 'Homer  Jay' |
+----------------------------+
|                          0 |
+----------------------------+

In this case the first string contains one space and the second contains two spaces. That’s enough for them to be unequal, even when using a character by character comparison.

Case Sensitivity

The BINARY operator forces a case sensitive comparison, even if the collation is case insensitive.

For example, here’s my connection collation:

SELECT @@collation_connection;

Result:

+------------------------+
| @@collation_connection |
+------------------------+
| utf8_general_ci        |
+------------------------+

The ci at the end means case insensitive.

Here’s an example of a string comparison of uppercase vs lowercase strings:

SELECT 
    'cat' = 'CAT',
    BINARY 'cat' = 'CAT';

Result:

+---------------+----------------------+
| 'cat' = 'CAT' | BINARY 'cat' = 'CAT' |
+---------------+----------------------+
|             1 |                    0 |
+---------------+----------------------+

The first comparison returned 1, because my collation is case insensitive. The second one returned 0, because we use the BINARY operator.

The results you get with such string comparisons (without the BINARY operator) will depend on your collation. Using a case sensitive collation will return the same result that we got with BINARY.

Here’s an example to demonstrate:

SELECT 
    _latin1'cat' COLLATE latin1_general_ci = 'CAT' AS 'ci',
    _latin1'cat' COLLATE latin1_general_cs = 'CAT' AS 'cs',
    BINARY 'cat' = 'CAT';

Result:

+------+------+----------------------+
| ci   | cs   | BINARY 'cat' = 'CAT' |
+------+------+----------------------+
|    1 |    0 |                    0 |
+------+------+----------------------+

Here, I explicitly specified the character set and collation at the string level. The syntax for doing that goes like this:

[_charset_name]'string' [COLLATE collation_name]

We can see that the case insensitive collation returned a different result to the case sensitive collation. And the case sensitive collation returned the same result as we get when using the BINARY operator.