MySQL: Choose Data type and length of the Secure Hash Algorithm (SHA)

Generally, we use the MD5 and SHA password encryption algorithm in MySQL Application.

The MD5 and SHA password encryption algorithm is very famous for different application. The MySQL also provides a different function to use this method.

When we use the hashing algorithm, we should also create a fixed length data type to store encrypted string or password.
Whatever our input is, hashing algorithm always produces a result of the same length.
We can use CHAR or BINARY data type to store encrypted password.

We should choose hashing algorithm base on our requirements because there are different version available with the different bit size.
Generally, SHA-224 is recommended.
Below is a list of hashing algorithm along with its require bit size.

• MD5 = 128-bit hash value.
• SHA1 = 160-bit hash value.
• SHA224 = 224-bit hash value.
• SHA256 = 256-bit hash value.
• SHA384 = 384-bit hash value.
• SHA512 = 512-bit hash value.

Below is a small demonstration to create CHAR fixed length data type for different bit hash value.

First, create sample table and data with CHAR fixed length data type.

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22

CREATE TABLE tbl_PasswordDataType (     ID INTEGER     ,MD5_128_bit CHAR(32)     ,SHA_160_bit CHAR(40)     ,SHA_224_bit CHAR(56)     ,SHA_256_bit CHAR(64)     ,SHA_384_bit CHAR(96)     ,SHA_512_bit CHAR(128) );   INSERT INTO tbl_PasswordDataType VALUES ( 1 ,MD5('Anvesh') ,SHA1('Anvesh') ,SHA2('Anvesh',224) ,SHA2('Anvesh',256) ,SHA2('Anvesh',384) ,SHA2('Anvesh',512) );

Now check the size of this columns:

1 2 3 4 5 6 7 8

SELECT     LENGTH(MD5_128_bit) AS Length_MD5128     ,LENGTH(SHA_160_bit) AS Length_SHA160     ,LENGTH(SHA_224_bit) AS Length_SHA224     ,LENGTH(SHA_256_bit) AS Length_SHA256     ,LENGTH(SHA_384_bit) AS Length_SHA384     ,LENGTH(SHA_512_bit) AS Length_SHA512 FROM tbl_PasswordDataType

The Result: