Storing SHA1 hash values in MySQL

Solution 1:

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.

Solution 2:

A SHA1 hash is 40 chars long!

Solution 3:

Reference taken from this blog:

Below is a list of hashing algorithm along with its require bit size:

• MD5 = 128-bit hash value.
• SHA1 = 160-bit hash value.
• SHA224 = 224-bit hash value.
• SHA256 = 256-bit hash value.
• SHA384 = 384-bit hash value.
• SHA512 = 512-bit hash value.

Created one sample table with require CHAR(n):

CREATE TABLE tbl_PasswordDataType
(
    ID INTEGER
    ,MD5_128_bit CHAR(32)
    ,SHA_160_bit CHAR(40)
    ,SHA_224_bit CHAR(56)
    ,SHA_256_bit CHAR(64)
    ,SHA_384_bit CHAR(96)
    ,SHA_512_bit CHAR(128)
); 
INSERT INTO tbl_PasswordDataType
VALUES 
(
    1
    ,MD5('SamplePass_WithAddedSalt')
    ,SHA1('SamplePass_WithAddedSalt')
    ,SHA2('SamplePass_WithAddedSalt',224)
    ,SHA2('SamplePass_WithAddedSalt',256)
    ,SHA2('SamplePass_WithAddedSalt',384)
    ,SHA2('SamplePass_WithAddedSalt',512)
);