Storing SHA1 hash values in MySQL

niklasfi_2275 0
Viewed 116979

I have a simple question which occured when I wanted to store the result of a SHA1 hash in a MySQL database:

How long should the VARCHAR field be in which I store the hash's result?

sha1 hash database-design mysql
7 Answers

I would use VARCHAR for variable length data, but not with fixed length data. Because a SHA-1 value is always 160 bit long, the VARCHAR would just waste an additional byte for the length of the fixed-length field.

And I also wouldn’t store the value the SHA1 is returning. Because it uses just 4 bit per character and thus would need 160/4 = 40 characters. But if you use 8 bit per character, you would only need a 160/8 = 20 character long field.

So I recommend you to use BINARY(20) and the UNHEX function to convert the SHA1 value to binary.

I compared storage requirements for BINARY(20) and CHAR(40).

CREATE TABLE `binary` (
    `id` int unsigned auto_increment primary key,
    `password` binary(20) not null
);
CREATE TABLE `char` (
    `id` int unsigned auto_increment primary key,
    `password` char(40) not null
);

With million of records binary(20) takes 44.56M, while char(40) takes 64.57M. InnoDB engine.

Gumbo_531140

A SHA1 hash is 40 chars long!

user_651360

Output size of sha1 is 160 bits. Which is 160/8 == 20 chars (if you use 8-bit chars) or 160/16 = 10 (if you use 16-bit chars).

user_698020

So the length is between 10 16-bit chars, and 40 hex digits.

In any case decide the format you are going to store, and make the field a fixed size based on that format. That way you won't have any wasted space.

user_39780
Related