How the BINARY operator works in Mariadb?

The BINARY operator in MariaDB is used to force a binary collation on the operand.

Posted on

The BINARY operator in MariaDB is used to force a binary collation on the operand. This is particularly useful when performing case-sensitive comparisons in a case-insensitive collation context.

Syntax

The syntax for the BINARY operator in MariaDB is as follows:

BINARY expression

Where expression is the string or column value you want to compare in a binary (case-sensitive) manner.

Examples

Example 1: Case-Sensitive Comparison

This example demonstrates a case-sensitive comparison using the BINARY operator.

SELECT 'A' = 'a', BINARY 'A' = 'a';

+-----------+------------------+
| 'A' = 'a' | BINARY 'A' = 'a' |
+-----------+------------------+
|         1 |                0 |
+-----------+------------------+

The output shows 1 for the first comparison, indicating true, and 0 for the second comparison, indicating true, due to the binary comparison.

Example 2: Using BINARY in WHERE Clause

To use BINARY in a WHERE clause, consider the following table and query:

DROP TABLE IF EXISTS example_table;
CREATE TABLE example_table (value VARCHAR(10));
INSERT INTO example_table VALUES ('text'), ('Text'), ('TEXT');

SELECT * FROM example_table WHERE BINARY value = 'text';

+-------+
| value |
+-------+
| text  |
+-------+

The output shows only the row with the exact case ’text'.

Example 3: BINARY with LIKE

The BINARY operator can also be used with the LIKE operator for case-sensitive pattern matching.

SELECT 'text' LIKE 't%', BINARY 'text' LIKE 'T%';

+------------------+-------------------------+
| 'text' LIKE 't%' | BINARY 'text' LIKE 'T%' |
+------------------+-------------------------+
|                1 |                       0 |
+------------------+-------------------------+

The output is 1 for the first case-insensitive LIKE, and 0 for the second case-sensitive LIKE.

Example 4: Concatenation with BINARY

When concatenating strings, BINARY can enforce a binary collation on the result.

SELECT CONCAT('A', 'b'), CONCAT(BINARY 'A', 'b');

Ab
+------------------+--------------------------------------------------+
| CONCAT('A', 'b') | CONCAT(BINARY 'A', 'b')                          |
+------------------+--------------------------------------------------+
| Ab               | 0x4162                                           |
+------------------+--------------------------------------------------+

The output is the same for both, but the second expression is treated in a binary collation context.

Below are a few functions related to the MariaDB BINARY operator:

  • MariaDB CAST() function is used to convert one data type into another.
  • MariaDB COLLATE clause is used to specify a collation for a particular query.
  • MariaDB CONCAT() function is used to concatenate two or more strings into one.

Conclusion

The BINARY operator is a powerful tool in MariaDB for enforcing case-sensitive comparisons and collations. It ensures that string comparisons and sorting take into account the exact case of the characters involved, which is essential in scenarios where case matters. Understanding how to use BINARY effectively can help maintain data integrity and provide accurate query results in your database applications.