CODEWITHSUNDEEP
mysqlpasswordsmd5
Eugene Yarmash
Eugene Yarmash

Reputation: 150109

Best practices for efficiently storing md5 hashes in mysql

Possible field types:

BINARY(16)
CHAR(32)
BIGINT + BIGINT

How do I decide which one to use?

Upvotes: 30

Views: 20657

Answers (2)

theking2
theking2

Reputation: 2852

Is it not clear if you mean efficient storage or performance wise. Btw. don't use it to store passwords.

But I used this for a project.

Two generated BIGINT columns

Add these two columns to your table.

`md5_l` bigint(20) UNSIGNED GENERATED ALWAYS AS (conv(left(md5(`text`),16),16,10)) STORED,
`md5_r` bigint(20) UNSIGNED GENERATED ALWAYS AS (conv(right(md5(`text`),16),16,10)) STORED,

It might or might not help to create a PK on these two columns though, as it probably concatenates two string representations and hashes the result. It would kind of defeat your purpose and a full scan might be quicker but that depends on the situation and specifically on the number of columns and records.

Select

Don't try to read these bigints in languages such as php as it doesn't have unsigned integers (and might convert to float), just stay in sql realm and do something like:

select email into result from `address`
    where url_md5_l=conv(left(md5(the_email),16),16,10)
      and url_md5_r=conv(right(md5(the_email),16),16,10) limit 1;

MD5 does collide and is not longer a save way of storing passwords. For other less critical data it is ok.

Upvotes: 0

Josh Davis
Josh Davis

Reputation: 28730

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)

Upvotes: 45

Related Questions