Inserting binary string to MySQL BINARY column

[anson5]

I'm planning on storing RIPEMD-160 passphrase hash's raw binary data in MySQL as BINARY(20).  Right now, I'm not sure if one of the following INSERT approach is more preferrable and more efficient:

 

Approach (1) - Using mysqli::real_escape_string()

=================================================

 

$binhash = $mysqli->real_escape_string(hash('ripemd160', $passphrase, true));
$mysqli->query("INSERT INTO testtbl (Passphrase) VALUES ('" . $binhash . "')");

 

This approach saves hash() from converting raw binary data to hexadecimal format, but need to process and escape the binary string.

 

Approach (2) - Using UNHEX() on MySQL

=====================================

 

$hexhash = hash('ripemd160', $passphrase);
$mysqli->query("INSERT INTO testtbl (Passphrase) values (UNHEX('" . $hexhash . "'))");

 

This approach needs hash() to convert binary data to hexadecimal but no need for escaping the string and it put extra load on MySQL to convert the hexadecimal back to binary.

 

[otuatail]

This is a 40 charecter string. I would sugest you store this information in this format.

 

Desmond.

 

[anson5]

There are two reasons for storing hash in binary format:

[*]It saves half the storage size, for a 160-bit hash, it's BINARY(20) vs CHAR(40)

[*]Operations on BINARY is faster according to benchmarks

 

So my question will be which one will be faster:

[*]mysqli->real_escape_string() on a 20 byte binary string (note that I used hash($algo, $passwd, true) to output raw binary data), or

[*]telling hash() to output hexadecimal character string (conversion from binary needed) plus UNHEX on MySQL?