How to Store a SHA-256 Hash in MySQL: CHAR(64) vs BINARY(32)

A SHA-256 hash is 256 bits, which is exactly 32 bytes raw or 64 hexadecimal characters as a string. MySQL has no dedicated hash column type, so you pick how to represent those 32 bytes: CHAR(64) (the readable hex string, 64 bytes), VARCHAR(64) (almost never the right call), or BINARY(32) (the raw bytes, half the size and the one to reach for). Below is the comparison table plus working DDL and insert/select for each, the SHA2() function, the indexing implications, and how to index a long column by its SHA-256 with a generated column.

Short answer: for a new table, store the raw 32 bytes in BINARY(32) and convert with UNHEX() on insert and HEX() on read. Use CHAR(64) only when you debug at the SQL prompt enough that a human-readable hex string is worth doubling the storage. This is the same BINARY(N) versus CHAR(2N) trade-off as storing an MD5 hash in MySQL, just with 32-byte values instead of 16.

What is a SHA-256 hash and how is it stored in MySQL?

SHA-256 (Secure Hash Algorithm 2, 256-bit variant) is a fixed-size 256-bit value produced by hashing arbitrary input. It is always 32 bytes in raw binary form, or 64 hexadecimal characters when stringified (d04b98f48e8f8bcc15c6ae5ac050801cd6dcfd428fb5f9e65c4e16e7807340fa for SHA2('hash', 256)). MySQL stores those 32 bytes however you tell it to: as ASCII hex in a CHAR(64) or VARCHAR(64) column (64 bytes per row, human-readable), or as raw bytes in a BINARY(32) column (32 bytes per row, half the storage and half the index size).

MySQL's built-in SHA2() function does the hashing for you: SHA2('hash', 256) returns the lowercase 64-character hex string above (the second argument is the bit length, one of 224, 256, 384, 512, or 0 which means 256). You then store that string, or wrap it in UNHEX() to get the raw 32 bytes. You can equally compute the digest in your application and hand MySQL the finished value; the storage choices are identical either way.

Everything here applies to MariaDB as well: SHA2(), UNHEX()/HEX(), the column types, and the indexing all behave the same, so the way you store a SHA-256 hash in MariaDB is the way you store it in MySQL (the indexed generated-column example below needs MariaDB 10.2+).

Jump to:

• Storage comparison table
• Method 1: BINARY(32)
• Method 2: CHAR(64)
• Method 3: VARCHAR(64)
• Index a large column by its SHA-256
• Indexing implications
• SHA-256 vs MD5 vs bcrypt
• FAQ

Storage comparison table

The "Raw hash bytes" column is the stored value alone times a billion rows, not the full on-disk index size (an InnoDB secondary index entry also carries the primary-key value and per-record overhead). What it shows cleanly is the relative cost: BINARY(32) is half of CHAR(64), and that 2× difference carries straight through to the real index, roughly a 32 GB gap in the secondary index on a billion-row table, plus less RAM spent keeping it hot in the buffer pool.

Method 1: BINARY(32), the recommended default

Half the storage of CHAR(64), half the index size, no charset issues.

CREATE TABLE sha_binary (
  sha256 BINARY(32) NOT NULL,
  INDEX idx_sha256 (sha256)
) ENGINE=InnoDB;

INSERT INTO sha_binary (sha256)
VALUES (UNHEX('d04b98f48e8f8bcc15c6ae5ac050801cd6dcfd428fb5f9e65c4e16e7807340fa'));

SELECT HEX(sha256) FROM sha_binary;
-- D04B98F48E8F8BCC15C6AE5AC050801CD6DCFD428FB5F9E65C4E16E7807340FA

Two helpers do all the conversion:

• UNHEX('hex_string') packs the 64-char hex string to its 32-byte binary form on insert.
• HEX(binary_col) expands it back to an uppercase hex string on read.

For lookups by hash, wrap the parameter in UNHEX() so you compare bytes to bytes:

SELECT * FROM sha_binary WHERE sha256 = UNHEX(?);

Or let MySQL do the hashing and store the raw bytes in one step:

INSERT INTO sha_binary (sha256) VALUES (UNHEX(SHA2('hash', 256)));

In application code, work with bytes directly: bytes.fromhex(hex) in Python, Buffer.from(hex, 'hex') in Node.js, hex2bin($hex) in PHP. For 99% of new tables, BINARY(32) is the right answer.

Method 2: CHAR(64)

Store the hex string as fixed-length ASCII. Readable at the SQL prompt, double the bytes.

CREATE TABLE sha_char (
  sha256 CHAR(64) CHARACTER SET ascii COLLATE ascii_bin NOT NULL,
  INDEX idx_sha256 (sha256)
) ENGINE=InnoDB;

INSERT INTO sha_char (sha256)
VALUES ('d04b98f48e8f8bcc15c6ae5ac050801cd6dcfd428fb5f9e65c4e16e7807340fa');

CHAR(64) reserves exactly 64 bytes per row in latin1 or ascii. Don't use a multibyte charset for hash columns. utf8mb4 would reserve 256 bytes per row for the same 64-char hex string. Add CHARACTER SET ascii COLLATE ascii_bin if your table default is utf8mb4. The binary collation also makes comparison case-sensitive, which is what you want (lowercase hex shouldn't match uppercase).

Method 3: VARCHAR(64)

Same content as CHAR(64) plus a 1-byte length prefix, so 65 bytes per row. There is almost never a reason to pick it: a SHA-256 hex string is always exactly 64 characters, so the variable-length machinery buys nothing. The only honest use is a column that might later hold a different fixed-length hash (say you migrate to SHA-512's 128 hex chars), and even then a schema change is the cleaner answer.

Index a large column by its SHA-256

The most common reason to put a SHA-256 in MySQL is to make a long column searchable without indexing the whole thing. You can't B-tree index a TEXT column, and a prefix index on a long VARCHAR is useless when the values diverge near the end (URLs, file paths, document bodies). Store the SHA-256 of the long value in an indexed generated column and look it up by equality:

CREATE TABLE documents (
  id          BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
  body        TEXT NOT NULL,
  body_sha256 BINARY(32) AS (UNHEX(SHA2(body, 256))) VIRTUAL,
  INDEX idx_body_sha256 (body_sha256)
) ENGINE=InnoDB;

-- exact-match lookup by content, without scanning the TEXT column
SELECT id FROM documents WHERE body_sha256 = UNHEX(SHA2(?, 256));

SHA2(body, 256) produces the 64-char hex string and UNHEX() packs it to BINARY(32), so the index is the compact one from Method 1. Use VIRTUAL, not STORED: InnoDB materializes a virtual generated column in the secondary-index records only, never in the row, so the table stays small, and adding the column plus its index is an in-place change rather than a full table rebuild. MySQL's optimizer will even use idx_body_sha256 for a WHERE clause that repeats the expression UNHEX(SHA2(body, 256)) exactly, without naming the column. (Same generated-column pattern covered for 16-byte digests in the MD5 storage guide.)

Indexing implications

All three methods support B-tree indexes, and point-query performance (WHERE sha256 = ?) is essentially identical. Where they diverge is index size and therefore buffer-pool efficiency:

The smaller BINARY(32) index stays hot in the buffer pool longer, which means fewer disk reads on RAM-constrained servers. SHA-256 digests are random by design, so if the hash is the primary key, every insert lands in a random B-tree page, causing page splits and write amplification (the same problem random UUIDs have). On high-write tables, make the hash a secondary index and keep an auto-increment BIGINT as the primary key.

SHA-256 vs MD5 vs bcrypt

SHA-256 is the right tool for integrity and fingerprinting: file checksums, content-addressable storage, deduplication, digital-signature inputs, audit hashes. It is collision-resistant in a way MD5 is not (MD5 has been broken since 2004), so for anything where an adversary controls the input, SHA-256 over MD5.

SHA-256 is the wrong tool for password storage, for the opposite reason it's good at integrity: it's fast. A fast hash lets an attacker who steals your table try billions of password guesses per second on a GPU. Passwords need a deliberately slow, salted, purpose-built hash: bcrypt, argon2id, or scrypt, applied at the application layer and stored in a VARCHAR(255) column whose value already encodes the algorithm, cost, and salt. Storing a bare SHA2(password, 256) is only marginally better than storing MD5, which is to say: not good enough.

What to do next

• For the 16-byte equivalent and the full four-method breakdown, see How to Store an MD5 Hash in MySQL.
• For every MySQL data type with exact byte sizes, see MySQL Data Types and Sizes.
• For the conversion functions and DDL on one page, the MySQL Cheat Sheet covers HEX, UNHEX, and SHA2.

FAQ

See also

• How to store an MD5 hash in MySQL for the 16-byte sibling of this decision, with the same BINARY(N) versus CHAR(2N) math.
• Storing an MD5 hash in PostgreSQL when you need the equivalent in Postgres, where bytea and encode/decode replace BINARY and HEX/UNHEX.
• Picking the right MySQL data type and size for the byte cost of every column type, not just hash columns.
• The MySQL cheat sheet for SHA2, UNHEX, and HEX syntax alongside the rest of the day-to-day SQL.
• Storing a bcrypt password hash in MySQL for the credentials case where SHA-256 is the wrong tool and a slow, salted hash belongs instead.
• Running MySQL in Docker to spin up a throwaway instance and try the BINARY(32) round-trip yourself.