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

问题描述

我听说在将二进制数据(文件等)插入 MySQL 时，应该使用 bin2hex() 函数并将其作为 HEX 编码值发送，而不是仅仅使用 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 如何处理大字符串与它如何处理 HEX 编码值有关

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

但是，我很难确认这一点.我所有的测试都表明完全相反.bin2hex 方法的速度慢了约 85%，并且使用了约 24% 的内存.
(我在 PHP 5.3、MySQL 5.1、Win7 x64 上测试这个 - 使用非常简单的插入循环.)

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 进程的私有内存使用情况:

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

_{(来源:advefir.com)}

有没有人有任何解释或资源可以澄清这一点?

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 string() 花费 much 更长的时间，即MySQL 的文档 中解释 - 只是转义6 个字符:NULL、 、 、、、 和'Control-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, , , , , 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屋！