MySQL: BINARY Function

This tutorial explains how to use the MySQL BINARY function with syntax and examples.

Description

The MySQL BINARY function converts a value to a binary string.

Syntax

The syntax for the BINARY function in MySQL is:

BINARY value

Parameters or Arguments

value

The value to convert to a binary string.

Note

• The BINARY function is shorthand for CAST(value AS BINARY).
• See also CONVERT function.

Applies To

The BINARY function can be used in the following versions of MySQL:

• MySQL 5.7, MySQL 5.6, MySQL 5.5, MySQL 5.1, MySQL 5.0, MySQL 4.1, MySQL 4.0, MySQL 3.23.3

Example

Let's look at some MySQL BINARY function examples and explore how to use the BINARY function in MySQL.

For example:

mysql> SELECT BINARY('AODBA.com'); 
Output: 'AODBA.com'

mysql> SELECT BINARY('T'); 
Output: 'T'

Using the BINARY function to cast a value to a binary string is one way to force a byte-by-byte comparison of strings, rather than a character-by-character comparison. Let's investigate this further.

For example:

mysql> SELECT 'AODBA' = 'AODBA';
Output: 1

If we ran the example above, MySQL would perform a character-by-character comparison of 'AODBA' and 'AODBA' and return 1 (because on a character-by-character basis, 'AODBA' and 'AODBA' are equivalent).

However, if we modified the example by adding the BINARY function as follows, changing the comparison to byte-by-byte instead of character-by-character:

mysql> SELECT BINARY 'AODBA' = 'AODBA';
Output: 0

MySQL would perform a byte-by-byte comparison of 'AODBA' and 'AODBA' and would return 0 (because on a byte-by-byte basis, 'AODBA' and 'AODBA' are NOT equivalent.)