MySQL BINARY Operator

In MySQL, the BINARY operator converts a regular string to a binary string. Binary strings use the binary character set and binary collation. BINARY Operators are often used to compare strings byte-by-byte rather than character-by-character.

`BINARY` Syntax

Here is the syntax of MySQL BINARY operators:

BINARY expr

BINARY expr is equivalent to CONVERT(expr, BINARY) and CAST(expr AS BINARY) functions.

Parameters

expr: Required. A value or expression that needs to be converted to a binary string.

Return value

MySQL BINARY returns the binary string converted from the parameter.

If you do not provide parameters for BINARY, MySQL will return an error.

`BINARY` Examples

Basic usage

This example shows how to use the BINARY operator to convert 'Hello' to a binary string:

SELECT BINARY 'Hello';

+--------------------------------+
| BINARY 'Hello'                 |
+--------------------------------+
| 0x48656C6C6F                   |
+--------------------------------+

Note that in the mysql client, binary strings are printed in hexadecimal by default.

compare strings

The BINARY operator is often used in string comparisons. For example:

SELECT 'hello' = 'HELLO';

+-------------------+
| 'hello' = 'HELLO' |
+-------------------+
|                 1 |
+-------------------+

Here, due to the character set and collation used, comparing strings is case-insensitive and 'hello' = 'HELLO' returned 1. If we want to compare strings case-sensitively, we can use BINARY to convert the strings to binary and compare them byte by byte. As follows:

SELECT BINARY 'hello' = 'HELLO';

+--------------------------+
| BINARY 'hello' = 'HELLO' |
+--------------------------+
|                        0 |
+--------------------------+

The result is obvious.

The BINARY operator also cause trailing whitespace to become important in strings comparisons. See the example below:

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

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

Here,

'a' = 'a ' returned 1, which indicates that trailing spaces in 'a ' are omited.
BINARY 'a' = 'a ' returned 0, which indicates that trailing spaces in 'a ' are not omited.

BINARY Syntax