PHP расширение mysqli - подготовленные выражения

• Обзор
• Асинхронные запросы
• Подготовленные выражения
• Хранимые процедуры
• Множественные запросы

Подготовленное выражение (ПВ) — это выражение (SQL-запрос) с параметрами используемое для многократного выполнения с высокой эффективностью. При этом эффективность проявляется в ситуации когда запрос нужно выполнять более одного раза.

MySQL сервер поддерживает интерфейс к ПВ на уровне SQL. Но, согласно, документации этот вариант менее эффективен, чем использование специализированного интерфейса (mysqli в случае с PHP).

В mysqli выполнение ПВ состоит из двух пунктов: подготовка и выполнение. При подготовке SQL-выражение, с маркерами в виде знаков вопроса, куда будут подставляться значения, отправляется серверу БД. Сервер проверяет синтаксис и инициализируется:

$stmt = $mysqli->prepare('INSERT INTO `test` (`id`) VALUES (?)')
    OR exit('Prepare failed: (' . $mysqli->errno . ') ' .
    $mysqli->error);

При вызове метода prepare(), создаётся объект класса mysqli_stmt, представляющий собой подготовленное выражение.

На стадии выполнения клиент привязывает значения к параметрам и отправляет их серверу БД. Сервер уже выполняет SQL-запрос с подставленными параметрами:

$id  = 1;
$val = 'value';
 
$stmt->bind_param('is', $id, $val)
    OR exit('Binding failed: (' . $stmt->errno . ') ' .
    $stmt->error);
 
$stmt->execute()
    OR exit('Execute failed: (' . $stmt->errno . ') ' .
    $stmt->error);

При этом метод bind_param() принимает минимум два параметра. Первый указывает на типы передаваемых значений всех остальных параметров, по одному символу на параметр:

• i - integer
• d - double
• s - string
• b – blob (двоичные строки)

Остальные параметры bind_param() передаются по ссылке и привязывают переменную PHP к маркеру в запросе (в порядке очереди). При этом специальные символы будут экранированы сервером БД. Отправка параметра происходит только при выполнении метода execute().

Помимо параметров, можно также привязывать PHP переменные к результатам запроса:

$stmt = $mysqli->prepare('SELECT `id`, `value` FROM `test` WHERE `id` = "1"')
    OR exit('Prepare failed: (' . $mysqli->errno . ') ' .
    $mysqli->error);
 
$stmt->execute()
    OR exit('Execute failed: (' . $stmt->errno . ') ' .
    $stmt->error);
 
$stmt->bind_result($col1, $col2)
    OR exit('Binding results failed: (' . $stmt->errno . ') ' .
    $stmt->error);
 
while ($stmt->fetch())
{
    echo 'col1 = "' . $col1 . '"; col2 = "' . $col2 . '"<br />';
}

Подготовленное выражение может быть использовано многократно, при этом выражение не пересылается серверу БД и не проверяется повторно:

$stmt = $mysqli->prepare('INSERT INTO `test` (`id`, `value`) VALUES (?, ?)')
    OR exit('Prepare failed: (' . $mysqli->errno . ') ' .
    $mysqli->error);
 
$id    = 0;
$value = '';
 
$stmt->bind_param('is', $id, $value)
    OR exit('Binding failed: (' . $stmt->errno . ') ' .
    $stmt->error);
 
for ($id = 1; $id < 5; $id++)
{
    $value = 'val ' . $id;
 
    $stmt->execute()
        OR exit('Execute failed: (' . $stmt->errno . ') ' .
        $stmt->error);
}

Каждое ПВ занимает ресурсы сервера, поэтому после использования их необходимо закрывать.

$stmt->close();

В заключение простой тест для сравнения ПВ с традиционными запросами в mysqli. Время замерялось с помощью функции microtime() от создания объекта до закрытия соединения. В тесте запускалось по 1000 запросов:

INSERT INTO `test` (`id`, `value`) VALUES (?, ?)

Результат следующий:

ПВ

13.664777994156

13.61216211319

13.906296014786

13.861138105392

обычный подход

15.933166027069

16.207018136978

16.945013999939

17.206493139267