SQL BINARY

What does the SQL BINARY keyword do?

BINARY defines or casts fixed-length byte strings and forces case-sensitive comparisons in MySQL.

Welcome to the Galaxy, Guardian!
You'll be receiving a confirmation email

Follow us on twitter :)

Oops! Something went wrong while submitting the form.

Compatible dialects for SQL BINARY: MySQL 4.1+, MariaDB, SQL Server (data type only), Oracle (keyword unsupported, use RAW), PostgreSQL (unsupported), SQLite (ignored, handled as BLOB).

SQL BINARY Full Explanation

In MySQL the BINARY keyword serves three related purposes: 1) Data type definition - BINARY(n) creates a fixed-length byte string column that always stores exactly n bytes, padding with 0x00 if the inserted value is shorter. 2) Type cast - the unary operator BINARY expr converts a value to the BINARY string type, making subsequent comparisons byte-wise and case-sensitive according to the binary collation of the connection. 3) Comparison modifier - using BINARY before literals or in predicates (e.g. LIKE BINARY) overrides the column's collation so the comparison is evaluated with a binary (case-sensitive) collation. Unlike VARBINARY, BINARY is fixed length; unlike CHAR, its content is not interpreted as text, so character set and collation are always 'binary'. For large unbounded binary data use BLOB. In SQL Server and the SQL standard BINARY exists only as a data type, not as a cast operator. PostgreSQL and SQLite represent raw bytes with other types (bytea, BLOB) and ignore the BINARY keyword.

SQL BINARY Syntax

-- Column definition
column_name BINARY(n)

-- Cast operator
SELECT BINARY expr;

-- Force case-sensitive pattern match
SELECT * FROM users WHERE name LIKE BINARY 'Alice';

SQL BINARY Parameters

n (integer) - Mandatory for the data type. Specifies the fixed length (0-255 in MySQL) of the binary string. Must be a positive, non-zero integer.

Example Queries Using SQL BINARY

-- 1. Create a table with a binary hash column
CREATE TABLE files (
  id INT PRIMARY KEY,
  sha256 BINARY(32) NOT NULL
);

-- 2. Pad or truncate automatically
INSERT INTO files VALUES (1, UNHEX('aabb'));  -- stored as 0xAABB0000… up to 32 bytes

-- 3. Case-sensitive search regardless of column collation
SELECT *
FROM customers
WHERE BINARY email = 'Admin@Example.com';

-- 4. Cast to BINARY before hashing
SELECT SHA2(BINARY 'Galaxy',256);

Expected Output Using SQL BINARY

Table is created with a fixed 32-byte column.
Value shorter than 32 bytes is right-padded with 0x00; longer values are truncated.
Rows match only when email bytes are identical, including letter case.
String literal is treated as raw bytes before being fed to SHA2, ensuring deterministic hashing.

Use Cases with SQL BINARY

Store fixed-size hashes (MD5, SHA-256) efficiently.
Guarantee exact, case-sensitive comparisons on text columns that use case-insensitive collations.
Optimize join or index lookups where variable-length VARBINARY would incur extra storage overhead.
Convert textual data to raw bytes prior to cryptographic or compression functions.

Common Mistakes with SQL BINARY

Using BINARY(n) when data length varies – prefer VARBINARY or BLOB.
Forgetting that values longer than n bytes are silently truncated, potentially corrupting hashes.
Assuming BINARY stores human-readable text – it stores uninterpreted bytes.
Thinking the cast operator supports length; BINARY expr always produces a VARBINARY of the expression length.

First Introduced In

MySQL 4.1

Frequently Asked Questions

What is the difference between BINARY and VARBINARY?

BINARY(n) always stores exactly n bytes, padding with 0x00. VARBINARY(n) stores only the bytes you insert plus a small length prefix. Use BINARY for fixed-size data like hashes, VARBINARY for variable-length data.

Does BINARY make comparisons case-sensitive?

Yes. When you cast a value with BINARY or write LIKE BINARY, MySQL uses a binary collation, so 'Galaxy' and 'galaxy' are different.

How large can a BINARY column be?

Up to 255 bytes in MySQL. For larger data consider TINYBLOB, BLOB, MEDIUMBLOB, or LONGBLOB.

Can I use BINARY in PostgreSQL or SQLite?

No. PostgreSQL uses the bytea type and SQLite treats all blobs as BLOB; the BINARY keyword is ignored or unsupported.