Зачем использовать bin2hex при вставке двоичных данных из PHP в MySQL?

Question

Я слышал слух, что при вставке двоичных данных (файлов и тому подобное) в MySQL, вы должны использовать bin2hex() функция и отправить его как HEX-кодированное значение, а не просто использовать mysql_real_escape_string на двоичной строке и использовать это.

// 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-кодированные значения

Однако мне трудно это подтвердить. Все мои тесты показывают точный опозит; что bin2hex Этот метод работает на ~85% медленнее и использует на ~24% больше памяти.
(Я тестирую это на PHP 5.3, MySQL 5.1, Win7 x64 - используя очень простой цикл вставки.)

Например, этот график показывает использование частной памяти процессом mysqld во время выполнения тестового кода:

http://atli.advefir.com/images/priv_mem_cropped.gif

У кого-нибудь есть какие-либо объяснения или источники, которые бы прояснили это?

Благодарю.

Answer 1

Для меня это звучит как городская легенда.

bin2hex() сопоставляет каждый байт на входе с двумя байтами на выходе ('a' -> '61'), поэтому вы должны заметить значительное увеличение памяти скрипта, выполняющего запрос - он должен использовать как минимум столько же памяти, сколько длина байта вставляемых двоичных данных.

Кроме того, это означает, что бег bin2hex() на длинной струне занимает гораздо больше времени, чем бег mysql_real_escape string(), который - как объяснено в документации MySQL - просто экранирует 6 символов: NULL, \r, \n, \, , и "Контроль-Z".

Это было для части PHP, теперь для MySQL: сервер должен сделать обратную операцию для правильного хранения данных. Реверсирование любой из функций занимает почти столько же времени, сколько и первоначальная операция - обратная функция mysql_real_escape_string() необходимо заменить экранированные значения (\\) с побегами (\), тогда как обратная сторона bin2hex() потребуется заменить каждый байт кортежа новым байтом.

С звонка mysql_real_escape_string() с двоичными данными безопасно (в соответствии с документацией MySQL и PHP или даже с учетом того, что операция не выполняет никаких других преобразований, кроме перечисленных выше), было бы абсолютно бессмысленно выполнять такую ​​дорогостоящую операцию.

Answer 2

Я сам проверял это и получил довольно последовательные результаты. (Хотя мои тесты немного сыры.)

Я проверил три компьютера

1. Windows 7 (x64), PHP 5.3, MySQL 5.1
2. Ubuntu 9.10 (x64) PHP 5.2, MySQL 5.1
3. Ubuntu 10.04 (x32) PHP 5.3, MySQL 5.1

До сих пор тесты на всех трех платформах показали одинаковые признаки:

• Вставка в BLOB в MyISAM происходит в 2–8 раз быстрее, чем в InnoDB. Разница в бинарных строках выше, чем в шестнадцатеричном коде. (См. Данные ниже)
• Использование HEX-кодированной строки ( bin2hex в X'...' ) в среднем использует больше памяти, чем использование экранированной двоичной строки ( mysql_real_escape_string на необработанных данных). - Это похоже на MyISAM и InnoDB.
• Двоичная строка быстрее в MyISAM, но HEX-кодированные данные быстрее в InnoDB.

---

Тест был в основном простым циклом, который экранировал или кодировал шестнадцатеричные необработанные данные (изображение размером 2,4 МБ, полученное один раз в верхней части скрипта), создавало строку запроса и выполняло ее через mysql_query или же mysqli::query функции. - Я тестировал оба расширения. Похоже, не было никакой разницы.

Я поместил результаты Ubuntu 10.04 (#3) в электронные таблицы. Результаты на машине с Ubuntu 9.10 (#2) были почти такими же, поэтому я не стал их настраивать:
(Наконец, оправдание для проверки правильности Google Docs! XD)

• Двоичная строка на MyISAM
• HEX-кодированная строка на MyISAM
• Двоичная строка на InnoDB
• Шестнадцатеричная строка на InnoDB

Эти графики показывают использование частной памяти mysqld процесс на машине Win7 (#1).

• Двоичная строка на MyISAM
• HEX-кодированная строка на MyISAM
• Двоичная строка на InnoDB
• HEX-кодированная строка на InnoDB

Answer 3

Шестнадцатеричная строка значительно длиннее соответствующей двоичной строки. Просто время переноса и его копирование в памяти PHP и MySQL может помочь.

Честно говоря, я не эксперт по базовой реализации, но не лучше ли вообще не передавать данные в SQL, а используя, например, PDOStatementпривязка параметров? Может быть, кто-то более знающий здесь может подтвердить, действительно ли это приведет к отправке данных в виде двоичной строки, вне какого-либо оператора SQL, или же PDO просто выполняет экранирование и манипулирование строкой запроса под капотом.

В любом случае, вы получаете выгоду безопасности (и простоты) прямо здесь.

Answer 4

Например, если вы столкнулись с подобной проблемой, как описано здесь: http://www.php.net/manual/en/function.mysql-real-escape-string.php

например, даже если mysql_real_escape_string кажется "бинарно-безопасным", вы не можете использовать его (в качестве примера) в сочетании с igbinary_serialize - десериализация просто не удастся.

в этом случае вам нужно bin2hex до вставки данных в MySQL.

Кроме того, обычно вы чаще читаете данные из mysql, чем вставляете:)