MySQL BINARY function – A Complete Guide

By /
Mysql Binary Function

In this tutorial, we will study the MySQL BINARY function and go through some examples to understand its primary use.

MySQL BINARY function is used to convert the expression to the binary string. A binary string is a string that has binary collation and binary character-set. Note that this function won’t convert the value or string into binary code. If you are looking for the function which converts numbers into binary (base 2), then click here for detailed information about it.

Also, MySQL BINARY function and BINARY data type are two different points, don’t get confused between those!

So, you might be thinking, what’s the use of the BINARY function if we already have BIN() function. The answer is simple. When we search anything in MySQL, we generally use “like” or “=” operators, right?

But it returns the result, which is case-insensitive. That means, if you search for “mysql,” it will also return true for “MYSQL” as well.

To avoid a case-insensitive search, you have two options. First, change collation from ci ( case insensitive) to cs (case sensitive), but in most cases, this is not preferred because of inconsistency in collation. The other way is to use the BINARY function.

The MySQL BINARY function allows comparing two values byte-by-byte. Hence it is used to achieve case-insensitive search. Let’s see how!

MySQL BINARY function Syntax

The MySQL BINARY function has very straightforward syntax. Check below:

BINARY value;Code language: SQL (Structured Query Language) (sql)

Where,

  • BINARY is a function name
  • value is the parameter to be passed

You can convert the value into a binary string using the CONVERT() or CAST() function. See the following syntax.

CONVERT(value, BINARY);
CAST(value as BINARY);Code language: SQL (Structured Query Language) (sql)

Note that all three syntaxes stated above produce identical results. You can select any of those. Now, let’s see some examples of the BINARY function to understand how it works.

Examples of MySQL BINARY Function

Let’s see simple examples of the BINARY function first.

SELECT BINARY "mysqlcode.com";Code language: SQL (Structured Query Language) (sql)
Mysql Binary Function Example
Mysql Binary Function Example
SELECT BINARY 10;Code language: SQL (Structured Query Language) (sql)
Mysql Binary Function Example
Mysql Binary Function Example

Here, a parameter that we pass as well as the result string both look identical. But, using BINARY is the way to cast or convert the string into a binary string and make a comparison byte-by-byte instead of character-by-character. Sounds confusing? Check the example below.

First, we will compare two strings, out of which one is uppercase, whereas the other one is lowercase, and check the result. Because we are only comparing two strings, we should get the result in a boolean form, i.e., 1 or 0.

SELECT "MYSQLCODE.COM" = "mysqlcode.com" as comparison; Code language: SQL (Structured Query Language) (sql)

Here, the keyword “comparison” is used as an alias for the new column that gets generated for output.

Compare Strings In Mysql
Compare Strings In Mysql

MySQL assumes these two strings as identical irrespective of letter casing because it compares those in a character-by-character manner and, thus, output as 1.

But what if we wanted to get the result as true only and only if two strings are identical based on letter casing? In that case, we can use the BINARY function. Take a look below!

SELECT BINARY "MYSQLCODE.COM" = "mysqlcode.com" as comparison;Code language: SQL (Structured Query Language) (sql)
Compare Strings In Mysql Using Binary Example
Compare Strings In Mysql Using Binary Example

Here, a byte-by-byte comparison is performed by the MySQL between these two strings. Because these two strings are not equivalent(on a byte-by-byte basis), it results as 0. Now, we will try the BINARY function example with two similar uppercase strings again and check the result.

SELECT BINARY "MYSQLCODE.COM" = "MYSQLCODE.COM" as comparison;Code language: SQL (Structured Query Language) (sql)
Compare Strings In Mysql Using Binary Example
Compare Strings In Mysql Using Binary Example

Now, as expected, we got the result as 1 because both strings are identical in a byte-by-byte manner. This way, you can implement your search query with the column values for case-sensitive situations.

Conclusion

We have studied the BINARY function here. It is a super easy function to implement a case-sensitive search operation in your query. However, official MySQL documentation says the BINARY function is deprecated as of now for the MySQL version 8.0.27.

There are high chances that it would be removed entirely in the future version. So, better to use the CAST() or CONVERT() function to achieve the same.

References

MySQL official documentation for cast functions and operators (includes BINARY function)