在 MySQL 中存储 SHA1 哈希值
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/614476/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Storing SHA1 hash values in MySQL
提问by niklasfi
I have a simple question which occured when I wanted to store the result of a SHA1 hash in a MySQL database:
当我想将 SHA1 散列的结果存储在 MySQL 数据库中时，出现了一个简单的问题：
How long should the VARCHARfield be in which I store the hash's result?
我存储散列结果的VARCHAR字段应该多长时间？
回答by Gumbo
I would use VARCHARfor variable length data, but not with fixed length data. Because a SHA-1 value is always160 bit long, the VARCHARwould just waste an additional byte for the length of the fixed-length field.
我会VARCHAR用于可变长度数据，但不会用于固定长度数据。因为 SHA-1 值总是160 位长，所以对于固定长度字段的长度VARCHAR只会浪费一个额外的字节。
And I also wouldn't store the value the SHA1is 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.
而且我也不会存储SHA1返回的值。因为它每个字符只使用 4 位，因此需要 160/4 = 40 个字符。但是，如果您使用每个字符 8 位，则只需要一个 160/8 = 20 个字符的长字段。
So I recommend you to use BINARY(20)and the UNHEXfunctionto convert the SHA1value to binary.
因此，我建议您使用BINARY(20)和将值转换为二进制的UNHEX函数SHA1。
I compared storage requirements for BINARY(20)and CHAR(40).
我比较了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
);
With million of records binary(20)takes 44.56M, while char(40)takes 64.57M.
InnoDBengine.
百万条记录binary(20)需要44.56M，而char(40)需要64.57M。
InnoDB引擎。
回答by schmilblick
A SHA1 hash is 40 chars long!
一个 SHA1 哈希是 40 个字符长！
回答by Anvesh
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.
- MD5 = 128 位哈希值。
- SHA1 = 160 位哈希值。
- SHA224 = 224 位哈希值。
- SHA256 = 256 位哈希值。
- SHA384 = 384 位哈希值。
- SHA512 = 512 位哈希值。
Created one sample table with require CHAR(n):
创建了一个带有 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)
);
回答by inazaruk
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).
sha1 的输出大小为 160 位。即 160/8 == 20 个字符（如果您使用 8 位字符）或 160/16 = 10（如果您使用 16 位字符）。
回答by Douglas Leeder
So the length is between 10 16-bit chars, and 40 hex digits.
所以长度介于 10 个 16 位字符和 40 个十六进制数字之间。
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.
在任何情况下，决定您要存储的格式，并根据该格式使字段具有固定大小。这样你就不会浪费任何空间。
回答by Keith Harty
You may still want to use VARCHAR in cases where you don't always store a hash for the user (i.e. authenticating accounts/forgot login url). Once a user has authenticated/changed their login info they shouldn't be able to use the hash and should have no reason to. You could create a separate table to store temporary hash -> user associations that could be deleted but I don't think most people bother to do this.
如果您不总是为用户存储哈希（即验证帐户/忘记登录 url），您可能仍然希望使用 VARCHAR。一旦用户对他们的登录信息进行了身份验证/更改，他们就不能使用哈希，也没有理由这样做。您可以创建一个单独的表来存储临时哈希 -> 可以删除的用户关联，但我认为大多数人不会这样做。
回答by Francesco Casula
If you need an index on the sha1 column, I suggest CHAR(40) for performance reasons. In my case the sha1 column is an email confirmation token, so on the landing page the query enters only with the token. In this case CHAR(40) with INDEX, in my opinion, is the best choice :)
如果您需要 sha1 列上的索引，出于性能原因，我建议使用 CHAR(40) 。在我的情况下，sha1 列是电子邮件确认令牌，因此在登录页面上，查询仅输入令牌。在这种情况下，CHAR(40) 和 INDEX，在我看来，是最好的选择:)
If you want to adopt this method, remember to leave $raw_output = false.
如果要采用这种方法，记得保留$raw_output = false。
