MySQL BIN() Function

Summary: in this tutorial, you will learn how to use the MySQL BIN() function to return a string that represents the binary value of a number.

Introduction to the MySQL BIN() function

The BIN() function allows you to return a binary value of a number.

Here’s the basic syntax of the BIN() function:

BIN(n)Code language: SQL (Structured Query Language) (sql)

In this syntax, n is a BIGINT number. If n is NULL, the BIN() function returns NULL.

The BIN(n) is equivalent to CONV(n, 10, 2).

MySQL BIN() function examples

Let’s take some examples of using the BIN() function.

1) Simple BIN() function example

The following example uses the BIN() function to return a string that represents the binary value of the number 3:

SELECT BIN(3);Code language: SQL (Structured Query Language) (sql)

Output:

+--------+
| BIN(3) |
+--------+
| 11     |
+--------+
1 row in set (0.00 sec)Code language: SQL (Structured Query Language) (sql)

2) Using the BIN() function with table data

First, create a new table called permissions that has two columns id and flags:

CREATE TABLE permissions(
    id INT AUTO_INCREMENT PRIMARY KEY,
    flags INT NOT NULL
);Code language: SQL (Structured Query Language) (sql)

Second, insert some rows into the permissions table:

INSERT INTO permissions(flags)
VALUES
  (3),
  (5),
  (7);Code language: SQL (Structured Query Language) (sql)

Third, use the BIN() function to query and represent the values in the flags column in binary form:

SELECT 
  id, 
  flags, 
  BIN(flags) 
FROM 
  permissions;Code language: SQL (Structured Query Language) (sql)

Output:

+----+-------+------------+
| id | flags | BIN(flags) |
+----+-------+------------+
|  1 |     3 | 11         |
|  2 |     5 | 101        |
|  3 |     7 | 111        |
+----+-------+------------+
3 rows in set (0.00 sec)Code language: SQL (Structured Query Language) (sql)

Summary

Use the MySQL BIN() function to get a string that represents the binary value of a number.

Was this tutorial helpful?