Why use bin2hex when inserting binary data from PHP into MySQL?
- by Atli
I heard a rumor that when inserting binary data (files and such) into MySQL, you should use the bin2hex() function and send it as a HEX-coded value, rather than just use mysql_real_escape_string on the binary string and use that.
// That you should do
$hex = bin2hex($raw_bin);
$sql = "INSERT INTO `table`(`file`) VALUES (X'{$hex}')";
// Rather than
$bin = mysql_real_escape_string($raw_bin);
$sql = "INSERT INTO `table`(`file`) VALUES ('{$bin}')";
It is supposedly for performance reasons. Something to do with how MySQL handles large strings vs. how it handles HEX-coded values
However, I am having a hard time confirming this. All my tests indicate the exact oposite; that the bin2hex method is ~85% slower and uses ~24% more memory.
(I am testing this on PHP 5.3, MySQL 5.1, Win7 x64 - Using a farily simple insert loop.)
For instance, this graph shows the private memory usage of the mysqld process while the test code was running:
Does anybody have any explainations or reasources that would clarify this?
Thanks.
