Introduction to MySQL BINARY Data Type

BINARY is a commonly used data type in MySQL for storing binary data.

Syntax

The syntax for BINARY data type is as follows:

BINARY(M)

where M represents the number of bytes to store, ranging from 1 to 255.

Use Cases

BINARY data type is typically used for storing binary data such as encryption keys, hash values, images, audio, video files, etc.

Examples

Here are two examples of using BINARY data type:

CREATE TABLE users (
  user_id INT UNSIGNED NOT NULL AUTO_INCREMENT,
  username VARCHAR(255) NOT NULL,
  password BINARY(64) NOT NULL,
  PRIMARY KEY (user_id)
);

INSERT INTO users (username, password) VALUES
  ('alice', UNHEX(SHA2('mypassword', 256))),
  ('bob', UNHEX(SHA2('secret', 256)));

In the above example, we create a table called users with columns for user ID, username, and password. The password column uses BINARY data type because the passwords are hashed binary data. We insert two example records into the table, each containing a username and a password. When inserting the password, we use the built-in SHA2 function in MySQL to hash the password, and use the UNHEX function to convert the hash value into binary data.

Next, let’s use BINARY data type to store image data:

CREATE TABLE images (
  image_id INT UNSIGNED NOT NULL AUTO_INCREMENT,
  image BINARY(1024) NOT NULL,
  PRIMARY KEY (image_id)
);

INSERT INTO images (image) VALUES
  (LOAD_FILE('/path/to/image1.jpg')),
  (LOAD_FILE('/path/to/image2.png')),
  (LOAD_FILE('/path/to/image3.gif'));

In the above example, we create a table called images with columns for image ID and image data. The image data column uses BINARY data type because the images are binary data. We insert three example records into the table, each containing an image data. When inserting the image data, we use the built-in LOAD_FILE function in MySQL to load binary data from files.

Conclusion

In this article, we introduced the BINARY data type in MySQL. BINARY is commonly used for storing binary data such as encryption keys, hash values, images, audio, video files, etc. When using BINARY data type, it’s important to be mindful of the number of bytes being stored and the source of the binary data.