Search

BINARY 

BINARY 

– show you how to store fixed-length byte data. (1)

MySQL BINARY Data Type: Explanation in Plain English

What is the MySQL BINARY Data Type?

  • The BINARY data type in MySQL is used to store fixed-length binary data.
  • This means that every value stored in a BINARY column has the same length, and if the data is shorter than the specified length, MySQL will add padding with zero bytes (0x00) to the right to fill it up.

When to Use the BINARY Data Type?

  • The BINARY data type is ideal for storing binary data with a fixed length.
  • Common use cases include storing hashes (e.g., SHA-256) and checksums, which always have a fixed length.

How to Define a BINARY Column?

When defining a column to use the BINARY data type, you must specify its size in bytes:

sqlCopy code
column_name BINARY(size);
  • The size is the number of bytes the column will store.

Padding with Zero Bytes

  • If you insert a binary value that is shorter than the defined size, MySQL automatically adds zero bytes (0x00) to the right side to reach the required length.
    • For example, if the column size is 10 bytes but you insert only 5 bytes of data, MySQL will pad the remaining 5 bytes with 0x00.

Retrieving Data from a BINARY Column

  • When you retrieve data from a BINARY column, MySQL does not remove the zero bytes that were added during insertion. The data will include any padding that was applied.

Comparing BINARY Values

  • MySQL considers all bytes during comparisons of BINARY values, including any trailing zero bytes.
    • This means that two binary values are only considered equal if every single byte, including padding, is the same.

Differences Between Zero Bytes and Space Characters

  • Zero bytes (0x00) and space characters (0x20) are treated differently by MySQL in comparisons.
    • For example, a binary value containing 0x00 is not considered equal to one containing spaces.
    • When sorting, MySQL orders 0x00 before space characters.

Example: Using BINARY to Store SHA-256 Hashes

  1. Create a Table:
      2. sqlCopy code
CREATE TABLE binary_demo(
    id INT AUTO_INCREMENT PRIMARY KEY,
    data BINARY(32) -- 32 bytes for SHA-256 hashes
);
    1. id: Auto-incrementing primary key.
    2. data: A BINARY(32) column to store 32-byte SHA-256 hashes.
  2. Insert a SHA-256 Hash:
      3. sqlCopy code
INSERT INTO binary_demo(data)
VALUES (UNHEX(SHA2('Hello', 256)));
    1. The SHA2('Hello', 256) function generates a SHA-256 hash of the string 'Hello'.
    2. The UNHEX() function converts the hexadecimal result of the hash to binary data for storage in the BINARYcolumn.
  3. Retrieve the Data:
      4. sqlCopy code
SELECT HEX(data)
FROM binary_demo WHERE id = 1;
    1. The HEX() function converts the binary data back into its hexadecimal form for easier reading.
    2. The result would look like this:
      3. Copy code
0x185F8DB32271FE25F561A6FC938B2E264306EC304EDA518007D1764826381969

Summary in Simple Terms

  • BINARY Data Type: Stores fixed-length binary data with padding if necessary.
  • Padding with Zero Bytes: MySQL adds 0x00 to shorter binary data to match the column’s specified length.
  • No Removal of Padding: The padding remains when you retrieve the data.
  • Comparisons: All bytes, including zero bytes, are important in comparisons. 0x00 and space characters are treated differently.

This is useful for storing things like hashes or UUIDs, where the size of the data is always the same.