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?
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.
I compared storage requirements for
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.