在 MySQL 中将十六进制值存储为二进制 [英] Storing hexadecimal values as binary in MySQL

问题描述

我正在考虑如何在我的数据库中存储密码:在 CHAR(40) 字段中适当地加盐 SHA1 字符串.但是，由于其中的字符数据实际上只是 160 位数字的十六进制表示，我认为将其存储为 BINARY(20) 可能会更好.

I was thinking about how I'm storing passwords in my database : appropriately salted SHA1 strings in a CHAR(40) field. However, since the character data in there is actually just a hex representation of a 160 bit number, I thought it might be better to store it as BINARY(20).

CREATE TABLE users (
    password BINARY(20)
    /* snip */
);

INSERT INTO users (password) VALUES (UNHEX(SHA1('mypassword'));

在我看来，这种方法的一个好处是它将该字段的大小减半，但我可以想象可能也有一些缺点.

As I see it, one benefit of this approach is that it halves the size of that field, but I can imagine there's probably some downsides too.

你有什么意见?

推荐答案

我们在数据库中为大量不同的 id 使用二进制来节省空间，因为我们的大部分数据都是由这些 id 组成的.由于您似乎不需要节省空间(因为它只是密码，而不是其他一些大型项目)，我看不出有任何理由在这里使用二进制.

We used binary for a ton of different ids in our database to save space, since the majority of our data consisted of these ids. Since it doesn't seem like you need to save space (as it's just passwords, not some other huge scale item), I don't see any reason to use binary here.

我们遇到的最大问题是控制台中不断显示二进制数据(每次键入 select * 都会听到一百万声哔声)，而且您必须始终执行 select HEX() 或 insert UNHEX()，这是一种痛苦.

The biggest problem we ran into was constantly, annoyingly, having binary data show up in the console (everytime you type select * you hear a million beeps), and you have to always do select HEX() or insert UNHEX(), which is a pain.

最后，如果你混合匹配(错误地)二进制和 HEX/UNHEX 并加入这个值，你可能会匹配你从未打算匹配的记录.

Lastly, if you mix and match (by mistake) binary and HEX/UNHEX and join on this value, you could match records you never intended to.

这篇关于在 MySQL 中将十六进制值存储为二进制的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！