Best practices for efficiently storing md5 hashes in mysql

Question

Possible field types:

BINARY(16)
CHAR(32)
BIGINT + BIGINT

How do I decide which one to use?

Answer 1

An MD5 hash is typically expressed as a 32 digit hex number. CHAR(32) will work just fine.

Answer 2

None.

Don't use MD5, it's a dead hash. Use SHA-2 or above, and store is as a series of hex characters (64 characters). It's very nice to read, in this fashion, and it's the standard approach.

Answer 3

im no good at MySql but a hash is nomaly a byte array so BINARY(16) should be the best, but it might be harder to write custom sql questions against Binary if thats needed?

Answer 4

If the column is indexed and you know what you're doing, BINARY(16) for performance reasons.

Otherwise, CHAR(32) is fine. Make sure the column uses the ascii charset though. (ascii_bin for example)