StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

PO
text
Body
copied!<p>I would use <code>VARCHAR</code> for variable length data, but not with fixed length data. Because a SHA-1 value is <em>always</em> 160 bit long, the <code>VARCHAR</code> would just waste <a href="http://dev.mysql.com/doc/refman/5.1/en/storage-requirements.html" rel="noreferrer">an additional byte for the length of the fixed-length field</a>.</p> <p>And I also wouldn’t store the value the <a href="http://dev.mysql.com/doc/refman/5.1/en/encryption-functions.html#function_sha1" rel="noreferrer"><code>SHA1</code></a> 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.</p> <p>So I recommend you to use <a href="http://dev.mysql.com/doc/refman/5.0/en/binary-varbinary.html" rel="noreferrer"><code>BINARY(20)</code></a> and the <a href="http://dev.mysql.com/doc/refman/5.0/en/string-functions.html#function_unhex" rel="noreferrer"><code>UNHEX</code> function</a> to convert the <code>SHA1</code> value to binary.</p> <p>I compared storage requirements for <code>BINARY(20)</code> and <code>CHAR(40)</code>.</p> <pre><code>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 ); </code></pre> <p>With million of records <code>binary(20)</code> takes 44.56M, while <code>char(40)</code> takes 64.57M. <code>InnoDB</code> engine.</p>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload