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

Я слышал слух, что при вставке двоичных данных (файлов и тому подобного) в 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 во время выполнения тестового кода:

Private Bytes used by the mysqld process
(источник: advefir.com)

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

Благодарю.

Ответы

Ответ 1

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

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

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

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

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

Ответ 2

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

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

  • Windows 7 (x64), PHP 5.3, MySQL 5.1
  • Ubuntu 9.10 (x64) PHP 5.2, MySQL 5.1
  • Ubuntu 10.04 (x32) PHP 5.3, MySQL 5.1

До сих пор тесты на всех трех платформах отображали те же самые значения:

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

Тест был в основном простым циклом, который экранировал или закодировал необработанные данные (изображение 2,4 MiB, полученное один раз в верхней части script), построил строку запроса и выполнил ее с помощью mysql_query или mysqli::query. - Я тестировал оба расширения. Кажется, не было никакой разницы.

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

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

Ответ 3

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

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

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

Ответ 4

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

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

в этом случае вам нужно bin2hex перед вставкой данных в mysql.

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