为什么从PHP插入二进制数据到MySQL时使用BIN2HEX？ [英] Why use bin2hex when inserting binary data from PHP into MySQL?

问题描述

我听到一个谣言，插入二进制数据（文件等等）到MySQL的时候，你应该使用 BIN2HEX（）功能，将其作为一个将十六进制codeD价值，而不是仅仅用 mysql_real_escape_string 的二进制串并使用它。

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}')";

据推测是出于性能的考虑。是与MySQL的是如何处理大型字符串与它如何处理将十六进制codeD值

It is supposedly for performance reasons. Something to do with how MySQL handles large strings vs. how it handles HEX-coded values

不过，我有一个很难证实这一点。我所有的测试表明确切的oposite;该 BIN2HEX 方法是慢〜85％，并使用〜24％，更多的内存。结果
的（我在PHP 5.3中，MySQL 5.1测试此，Win7的X64 - 使用farily简单的插入循环）的

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.)

例如，这个图显示的私有内存使用量的的mysqld 的同时测试code的正在运行的进程：

For instance, this graph shows the private memory usage of the mysqld process while the test code was running:

没有任何人有任何解释相关或reasources，澄清这一点？

Does anybody have any explainations or reasources that would clarify this?

感谢。

推荐答案

这听起来像一个都市传说给我。

This sounds like an urban legend to me.

BIN2HEX（）输入中的每个字节映射到的输出（两个的字节'A' - > '61'），所以你应该注意到了脚本的显著增加内存执行查询 - 它应该更多的使用至少尽可能多的内存要插入的二进制数据的字节长度。

bin2hex() maps each byte in the input to two bytes in the output ('a' -> '61'), so you should notice a significant memory increase of the script performing the query - it should use at least as much memory more as the byte length of the binary data to be inserted.

此外，这意味着运行 BIN2HEX（）在一个长字符串采用的多的比跑步 mysql_real_escape的字符串长（ ），其中 - 作为 MySQL的文件中解释 - 刚刚逃脱6个字符： NULL ， \\ r ， \\ n ， \\ ，，和控制-Z'。

Furthermore, this implies that running bin2hex() on a long string takes much longer than running mysql_real_escape string(), which - as explained in MySQL's documentation - just escapes 6 characters: NULL, \r, \n, \, , and 'Control-Z'.

这是PHP的一部分，现为MySQL：服务器需要做反向操作以正确存储数据。无论是倒车的功能需要几乎只要原来的操作 - 的逆函数mysql_real_escape_string（）需要更换逃脱值（ \\\\ ）以转义的人（ \\ ），而） BIN2HEX（将需要更换的每一个字节的元组的一个新的字节。

That was for the PHP part, now for MySQL: The server needs to do the reverse operation to store the data correctly. Reversing either of the functions takes almost as long as the original operation - the reverse function of mysql_real_escape_string() needs to replace escaped values (\\) with unescaped ones (\), whereas the reverse of bin2hex() would need to replace each and every byte tuple with a new byte.

由于调用 mysql_real_escape_string（）二进制数据是安全的（根据MySQL的和的 PHP的文档或即使只是考虑到操作不会做任何其他的转换比上面列出的），它将使绝对没有任何意义来执行这样一个代价高昂的操作

Since calling mysql_real_escape_string() on binary data is safe (according to MySQL's and PHP's documentation or even when just considering that the operation does not do any other conversions than the ones listed above), it would make absolutely no sense to perform such a costly operation.

这篇关于为什么从PHP插入二进制数据到MySQL时使用BIN2HEX？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！