Storing hexadecimal values as binary in MySQL

Question

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.

What's your opinion?

Answer 1

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.

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.

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.