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.
A SHA1 hash is 40 chars long!
Reference taken from this blog:
Below is a list of hashing algorithm along with its require bit size:
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)
);
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).
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.
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With