Вставка нескольких записей в базу данных MySQL с помощью PHP

Вставка записей по одной означает один сетевой запрос к серверу базы данных на каждую строку — медленно и расточительно, если нужно загрузить сотни или тысячи строк. Объединение их в один оператор INSERT позволяет MySQL разобрать, спланировать и зафиксировать весь пакет за один раз, что значительно быстрее.

В этой главе показаны три способа вставки нескольких строк с помощью расширения mysqli в PHP:

1. Один оператор INSERT ... VALUES (...), (...) — простейший и самый быстрый способ пакетной вставки.
2. Подготовленный запрос в цикле по данным — защищён от SQL-инъекций, идеален когда значения поступают от пользователей.
3. mysqli_multi_query() — выполнение нескольких отдельных операторов, объединённых в одну строку.

Если вы ещё не подключались к базе данных, начните с раздела Подключение к MySQL с помощью PHP. Для вставки одной строки см. Вставка данных в MySQL.

Установка соединения

Во всех примерах ниже предполагается открытое соединение mysqli в переменной $conn. Создадим его с помощью mysqli_connect() с ранней остановкой при ошибке:

<?php
$servername = "localhost";
$username   = "username";
$password   = "password";
$dbname     = "database_name";

// Create connection
$conn = mysqli_connect($servername, $username, $password, $dbname);

// Check connection
if (!$conn) {
    die("Connection failed: " . mysqli_connect_error());
}
echo "Connected successfully";
?>

Способ 1: один INSERT с несколькими наборами значений

Самый быстрый способ добавить много строк — один оператор INSERT INTO, перечисляющий несколько наборов значений через запятую. MySQL вставляет их все за одну операцию:

<?php
$sql = "INSERT INTO users (firstname, lastname, email)
        VALUES ('John', 'Doe', '[email protected]'),
               ('Mary', 'Moe', '[email protected]'),
               ('Julie', 'Dooley', '[email protected]')";

if (mysqli_query($conn, $sql)) {
    // mysqli_affected_rows() reports how many rows were inserted
    $count = mysqli_affected_rows($conn);
    echo "$count records inserted successfully.";
} else {
    echo "Error inserting records: " . mysqli_error($conn);
}
?>

mysqli_query() принимает два аргумента — соединение и строку SQL — и возвращает true при успехе или false при ошибке. После успешной вставки mysqli_affected_rows() сообщает, сколько строк было фактически записано (в данном случае 3).

Используйте этот способ, когда данные доверенные (жёстко закодированные или уже очищенные). Поскольку значения конкатенируются непосредственно в строку SQL, он небезопасен для необработанного пользовательского ввода — это исправляет Способ 2.

Способ 2: подготовленный запрос в цикле

Когда значения поступают из формы, API или любого ненадёжного источника, создайте запрос с помощью подготовленного оператора, чтобы данные передавались отдельно от SQL. Это блокирует SQL-инъекции и позволяет повторно использовать один скомпилированный оператор для каждой строки:

<?php
$users = [
    ['John',  'Doe',    '[email protected]'],
    ['Mary',  'Moe',    '[email protected]'],
    ['Julie', 'Dooley', '[email protected]'],
];

// Prepare once with placeholders
$stmt = mysqli_prepare($conn, "INSERT INTO users (firstname, lastname, email) VALUES (?, ?, ?)");

// Bind PHP variables to the placeholders ("sss" = three strings)
mysqli_stmt_bind_param($stmt, "sss", $firstname, $lastname, $email);

foreach ($users as [$firstname, $lastname, $email]) {
    mysqli_stmt_execute($stmt);   // runs with the current variable values
}

echo count($users) . " records inserted safely.";
mysqli_stmt_close($stmt);
?>

Строка типов "sss" сообщает MySQL, что три связанных параметра являются строками. Используйте i для целых чисел, d для дробных чисел с плавающей запятой и b для больших объектов. Подробнее см. Подготовленные запросы PHP MySQL.

Для максимальной скорости при тысячах строк оберните цикл в транзакцию, чтобы MySQL фиксировал данные один раз, а не после каждого выполнения:

<?php
mysqli_begin_transaction($conn);
foreach ($users as [$firstname, $lastname, $email]) {
    mysqli_stmt_execute($stmt);
}
mysqli_commit($conn);
?>

Способ 3: mysqli_multi_query()

Описанные выше способы вставляют данные в одну таблицу одним оператором. Когда нужно выполнить несколько различных операторов одновременно — например, вставку в две таблицы — используйте mysqli_multi_query(), который выполняет несколько SQL-операторов, разделённых точкой с запятой, из одной строки:

<?php
$sql  = "INSERT INTO users (firstname, lastname) VALUES ('John', 'Doe');";
$sql .= "INSERT INTO users (firstname, lastname) VALUES ('Mary', 'Moe');";
$sql .= "INSERT INTO logs (action) VALUES ('bulk import')";

if (mysqli_multi_query($conn, $sql)) {
    echo "Multiple statements executed successfully.";
} else {
    echo "Error: " . mysqli_error($conn);
}
?>

Важно: mysqli_multi_query() принимает произвольные операторы, поэтому он рискован при любом пользовательском вводе — никогда не формируйте его строку из ненадёжных данных. Для обычной пакетной вставки предпочтительнее Способ 1 или Способ 2.

Получение ID вставленных записей

После вставки mysqli_insert_id() возвращает идентификатор AUTO_INCREMENT, сгенерированный для последней вставленной строки. При вставке нескольких строк возвращается ID первой строки пакета; последующие строки следуют по порядку. Подробнее см. Получение ID последней вставленной записи.

Закрытие соединения

mysqli автоматически закрывает соединение по завершении скрипта, но хорошей практикой является явное освобождение соединения после завершения работы:

<?php
mysqli_close($conn);
?>

Какой способ выбрать?

Заключение

Пакетная вставка превращает множество медленных запросов в одну быструю операцию. Используйте одиночный INSERT с несколькими значениями, когда данные доверенные; подготовленный запрос в цикле (обёрнутый в транзакцию для больших пакетов) — когда данные от пользователей; и mysqli_multi_query() только тогда, когда действительно нужно выполнить различные операторы вместе. В любом случае проверяйте входные данные и проверяйте mysqli_error(), чтобы ошибки не проходили незамеченными.

Практика