/*
Hash functions such as MD5() or SHA1() return a string of hex digits. More efficient storage and comparisons can be obtained by converting the hex representation to binary using UNHEX() and storing the result in a BINARY(N) column. 

Each pair of hex digits requires one byte in binary form, so the value of N depends on the length of the hex string. N is 16 for an MD5() value and 20 for a SHA1() value. For SHA2(), N ranges from 28 (SHA-224) to 64 (SHA2-512) depending on the argument specifying the desired bit length of the result. 

The size penalty for storing the hex string in a CHAR column is at least two times, up to eight times if the value is stored in a column that uses the utf8 character set (where each character uses 4 bytes). 

Storing the string also results in slower comparisons because of the larger values and the need to take character set collation rules into account.

Good article on Difference between Hashing and Encryption algorithms:
http://stackoverflow.com/questions/4948322/fundamental-difference-between-hashing-and-encryption-algorithms
*/

DROP TABLE IF EXISTS sha2_tbl;
CREATE TABLE IF NOT EXISTS sha2_tbl 
(
-- Step 1. use binary data type to store necessary number of bytes associated with hash type (here, SHA2-512).
-- Each pair of hex digits requires one byte in binary form, value of N depends on length of hex string.
pwd BINARY(64), 
lname varchar(30)
);

INSERT INTO sha2_tbl 
(pwd, lname) 

-- Step 2. unhex (put into binary data type) values returned by SHA2 hash function.
VALUES
(UNHEX(sha2('test1', 512)), 'Smith'),
(UNHEX(sha2('test2', 512)), 'Jones');

show warnings;

select lname from sha2_tbl
where pwd=UNHEX(sha2('test2', 512));

select length(pwd) from sha2_tbl;

-- Test byte lengths of various hash values:

-- md5
select md5('test'); -- value returned as string of 32 hex digits
select LENGTH(md5('test')) as num_bytes_md5;

-- sha1
select sha1('test'); -- value returned as string of 40 hex digits
select LENGTH(sha1('test')) as num_bytes_sha1;

-- SHA2: 224, 256, 384, and 512:

-- SHA-224
select sha2('test', 224); -- value returned as string of 56 hex digits
select LENGTH(sha2('test', 224)) as num_bytes_sha2_224;

-- SHA-256 (0 is the same as using 256)
select sha2('test', 0); -- value returned as string of 64 hex digits
select LENGTH(sha2('test', 0)) as num_bytes_sha2_0;

-- SHA-256
select sha2('test', 256); -- value returned as string of 64 hex digits
select LENGTH(sha2('test', 256)) as num_bytes_sha2_256;

-- SHA-384
select sha2('test', 384); -- value returned as string of 96 hex digits
select LENGTH(sha2('test', 384)) as num_bytes_sha2_384;

-- SHA-512
select sha2('test', 512); -- value returned as string of 128 hex digits
select LENGTH(sha2('test', 512)) as num_bytes_sha2_512;

-- simple SSN hash example:
update person
set per_ssn=unhex(sha2('111111111', 512))
where per_id=1;

-- find person, based upon hashed SSN
select per_id from person where per_ssn=unhex(sha2('111111111',512));