在 MySQL 中存储 SHA1 哈希值

2021-11-20 00:00:00 hash sha1 mysql database-design

当我想将 SHA1 散列的结果存储在 MySQL 数据库中时，我遇到了一个简单的问题:

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

VARCHAR 字段应该在多长时间内存储散列结果?

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) 的存储要求.

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
);

百万条记录 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.

相关文章