在MySQL中存储SHA1哈希值 [英] Storing SHA1 hash values in MySQL
问题描述
我有一个简单的问题,当我想把一个SHA1哈希的结果存储在一个MySQL数据库中时:
I have a simple question which occured when I wanted to store the result of a SHA1 hash in a MySQL database:
/ em>字段在其中存储哈希的结果?
How long should the VARCHAR field be in which I store the hash's result?
推荐答案
我会使用 VARCHAR
用于可变长度数据,但不具有固定长度数据。因为SHA-1值总是 160位,所以
VARCHAR
只会浪费固定长度字段长度的附加字节。
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.
我也不会将 SHA1
正在返回。因为它每个字符只使用4位,因此需要160/4 = 40个字符。但是如果你每个字符使用8位,你只需要一个160/8 = 20个字符的长字段。
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.
所以我建议你使用 BINARY(20)
和 UNHEX
函数将 SHA1
值转换为二进制。
So I recommend you to use BINARY(20)
and the UNHEX
function to convert the SHA1
value to binary.
我比较了 BINARY(20)
和 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
);
拥有百万条记录 binary(20)
需要44.56M,而 char(40)
需要64.57M。
InnoDB
引擎。
With million of records binary(20)
takes 44.56M, while char(40)
takes 64.57M.
InnoDB
engine.
这篇关于在MySQL中存储SHA1哈希值的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!