TechEarl

In this guide lets look at some of the ways we can store a MD5 hash in a mysql table, focusing on best and efficient methods.

First lets quickly understand the basic characteristics of a MD5 hash.

The MD5 or 5th iteration of the message-digest algorithm is a 128-bit (16-byte) hash value. Generally seen as a 32 digit hexadecimal number, it can be interpreted as 32 alpha-numeric characters.

The MD5 hash of the word “hash” would look like

0800fc577294c34e0b28ad2839435945

So how is this value stored in a MySQL table.

Storing a MD5 hash in a CHAR(32) field

Since the hash is 32 char length of alpha-numeric ASCII characters the most commonly used method is a CHAR(32) field.

CREATE TABLE `md5_test_char` (
	`md5` CHAR(32) NOT NULL,
	INDEX `idx_md5` (`md5`)
)
ENGINE=InnoDB;

If you are wondering why we are not using VARCHAR(32), as we already know the size of the field will always be 32 ASCII characters long and also storing it in a CHAR field will save some storage space. Also as I have been enphasizing this field does not need any special collation such as UTF8 as it is plain ASCII.

Inserting a value into our created table would look like this.

mysql> INSERT INTO md5_test_char (`md5`) VALUES ('0800fc577294c34e0b28ad2839435945');
Query OK, 1 row affected (0.31 sec)

And we can see how our value looks line in the database. It looks like the same hash we inserted.

mysql> select * from md5_test_char;
+----------------------------------+
| md5                              |
+----------------------------------+
| 0800fc577294c34e0b28ad2839435945 |
+----------------------------------+
1 row in set (0.00 sec)

Storing a MD5 hash in a BINARY(16) field

CREATE TABLE `md5_test_binary` (
	`md5` BINARY(16) NOT NULL,
	INDEX `idx_md5` (`md5`)
)
ENGINE=InnoDB;

mysql> INSERT INTO md5_test_binary (`md5`) VALUES (unhex('0800fc577294c34e0b28ad2839435945'));
Query OK, 1 row affected (0.31 sec)

As you can see the value we have inserted has been converted to binary and we see a particular representation of it although it is slightly distorted due to display difficulties of such characters on the web.

mysql> SELECT * FROM md5_test_binary;
+------------------+
| md5              |
+------------------+
| ⁿWrö├N♂(¡(9CYE   |
+------------------+
1 row in set (0.00 sec)

Storing a MD5 hash using two BIGINT fields

An unsigned BIGINT field can store a value up to 18446744073709551615.

This is the maximum value of a 16 digit hexadecimal string in decimal ie

Hex ffffffffffffffff = Decimal 18446744073709551615

So we can divide our md5 string into two parts calling them either left and right or high and low and store each value converted to decimals in our fields.

CREATE TABLE `md5_test_bigint` (
  `md5_high` BIGINT UNSIGNED NOT NULL,
  `md5_low` BIGINT UNSIGNED NOT NULL,
  INDEX `idx_md5`(`md5_high`, `md5_low`)
)
ENGINE = InnoDB;

mysql> INSERT INTO `md5_test_bigint` (`md5_high` , `md5_low` ) VALUES( CONV(LEFT('0800fc577294c34e0b28ad2839435945',16),16,10) , CONV(RIGHT('0800fc577294c34e0b28ad2839435945',16),16,10));
Query OK, 1 row affected (0.07 sec)

Lets do a SELECT query to see how are MD5 hash is stored in the table:

mysql> SELECT * FROM md5_test_bigint;
+--------------------+--------------------+
| md5_high           | md5_low            |
+--------------------+--------------------+
| 576738204818129742 | 804082921756645701 |
+--------------------+--------------------+
1 row in set (0.07 sec)

Getting back the MD5 Hash from the BIGINT fields:

mysql> SELECT CONCAT(LPAD(CONV(`md5_high`,10,16),16,'0'),LPAD(CONV(`md5_low`,10,16),16,'0')) AS 'MD5' FROM md5_test_bigint;
+----------------------------------+
| MD5                              |
+----------------------------------+
| 0800FC577294C34E0B28AD2839435945 |
+----------------------------------+
1 row in set (0.07 sec)