保存的十六进制值作为MySQL二进制 [英] Storing hexadecimal values as binary in MySQL

问题描述

我在想我如何存储我的数据库密码：适当咸SHA1字符串在CHAR（40）字段。然而，由于存在字符数据实际上只是一个十六进制再一个160位数的presentation，我想这可能是更好的将其存储为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.

我们遇到的最大问题是不断的，烦人的，有二进制数据在控制台中显示出来（键入每次选择*您听到一百万声），你必须经常做选择HEX（）或插入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.

最后，如果你混搭（错误）二进制和十六进制/ 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屋！