Mysqli: работа с MySQL в PHP от А до Я

Раздел: Программирование на PHP -> Расширения PHP

Основное решение: объектно-ориентированный стиль с подготовленными запросами

Расширение mysqli (MySQL Improved) предоставляет интерфейс для взаимодействия с серверами MySQL версии 4.1.3 и выше. Наиболее эффективным подходом считается использование объектно-ориентированного API с подготовленными запросами (prepared statements). Этот метод защищает от SQL-инъекций и повышает производительность при многократном выполнении однотипных запросов.

Как безопасно подключиться к MySQL и выполнить запрос с параметрами?

// Создание объекта mysqli
$mysqli = new mysqli('localhost', 'user', 'password', 'database');

// Проверка соединения
if ($mysqli->connect_errno) {
    die('Ошибка подключения: ' . $mysqli->connect_error);
}

// Подготовка запроса
$stmt = $mysqli->prepare('SELECT id, name FROM users WHERE email = ?');

// Привязка параметра (s - string)
$stmt->bind_param('s', $email);
$email = 'user@example.com';

// Выполнение
$stmt->execute();

// Получение результата
$result = $stmt->get_result();
while ($row = $result->fetch_assoc()) {
    echo $row['id'] . ' - ' . $row['name'] . "\n";
}

// Закрытие
$stmt->close();
$mysqli->close();

Php управление com (управление com-объектами в php)

1 - Иван Иванов

Php modules (установка и использование модулей php)

Типичные проблемы:

  • Ошибка подключения - проверьте имя хоста, логин, пароль, порт (по умолчанию 3306). Используйте mysqli_report(MYSQLI_REPORT_ERROR | MYSQLI_REPORT_STRICT); для преобразования ошибок в исключения.
  • Неверная кодировка - после подключения выполните $mysqli->set_charset('utf8mb4');.
  • Утечка памяти - всегда освобождайте результат ($stmt->free_result()) и закрывайте запросы.

Вариант: процедурный стиль mysqli

Процедурный API использует функции с префиксом mysqli_. Он удобен для быстрых скриптов без ООП, но менее структурирован.

$link = mysqli_connect('localhost', 'user', 'password', 'database');
if (!$link) {
    die('Ошибка: ' . mysqli_connect_error());
}

$query = 'SELECT * FROM products WHERE price > ?';
$stmt = mysqli_prepare($link, $query);
mysqli_stmt_bind_param($stmt, 'd', $price);
$price = 100.50;
mysqli_stmt_execute($stmt);
$result = mysqli_stmt_get_result($stmt);
while ($product = mysqli_fetch_assoc($result)) {
    echo $product['name'] . ' - ' . $product['price'] . "\n";
}

mysqli_stmt_close($stmt);
mysqli_close($link);

Php mysqli extension (расширение mysqli в php)

При процедурном стиле легко забыть передать ссылку на соединение в каждую функцию. Также не забывайте проверять возвращаемые значения на false.

Вариант: выполнение простых запросов без подготовки

Если запрос не содержит пользовательских данных, можно использовать query(). Этот метод выполняет запрос напрямую и возвращает объект mysqli_result.

$mysqli = new mysqli('localhost', 'user', 'password', 'database');
$result = $mysqli->query('SELECT COUNT(*) AS cnt FROM users');
if ($result) {
    $row = $result->fetch_assoc();
    echo 'Всего пользователей: ' . $row['cnt'];
    $result->free();
}
Всего пользователей: 42

Никогда не используйте query() с подстановкой пользовательских данных - это прямой путь к SQL-инъекции. Для динамических значений применяйте подготовленные запросы.

Вариант: множественные запросы через multi_query

multi_query() позволяет выполнить несколько SQL-команд, разделённых точкой с запятой, за один вызов. Используется при импорте данных или выполнении пакета изменений.

$mysqli = new mysqli('localhost', 'user', 'password', 'database');
$sql = 'UPDATE users SET status = 1 WHERE id = 1;';
$sql .= 'UPDATE users SET status = 0 WHERE id = 2;';
if ($mysqli->multi_query($sql)) {
    do {
        if ($result = $mysqli->store_result()) {
            $result->free();
        }
    } while ($mysqli->more_results() && $mysqli->next_result());
}
if ($mysqli->errno) {
    echo 'Ошибка: ' . $mysqli->error;
}

Опасность множественных запросов - возможность SQL-инъекции при конкатенации пользовательских данных. Также невозможно использовать подготовленные запросы с multi_query().

Вариант: транзакции в mysqli

Транзакции обеспечивают атомарность изменений. В mysqli транзакции управляются методами begin_transaction(), commit() и rollback().

$mysqli->begin_transaction(MYSQLI_TRANS_START_READ_WRITE);
try {
    $mysqli->query('UPDATE accounts SET balance = balance - 100 WHERE id = 1');
    $mysqli->query('UPDATE accounts SET balance = balance + 100 WHERE id = 2');
    $mysqli->commit();
} catch (Exception $e) {
    $mysqli->rollback();
    echo 'Транзакция отменена: ' . $e->getMessage();
}

Следите за поддержкой транзакций движком таблиц (InnoDB, не MyISAM). При возникновении ошибки в середине транзакции автоматического отката не происходит - необходим явный вызов rollback().

Вариант: получение всех строк результата в виде массива

Метод fetch_all() возвращает все строки результата в виде вложенного массива. Это удобно, когда нужно сразу обработать весь набор данных.

$result = $mysqli->query('SELECT id, name FROM users LIMIT 3');
$rows = $result->fetch_all(MYSQLI_ASSOC);
foreach ($rows as $row) {
    echo $row['id'] . ': ' . $row['name'] . "\n";
}
1: Иван
2: Мария
3: Петр

При больших объёмах данных fetch_all() может потреблять много памяти. В таких случаях лучше использовать итеративный fetch_assoc().

Расширенные примеры использования mysqli

Как работать с хранимыми процедурами и OUT-параметрами?

Хранимые процедуры могут возвращать значения через OUT-параметры. В mysqli для этого используется метод bind_param() с указанием типа 's', 'i', 'd' и передачей переменной по ссылке.

Пример процедуры:

Пример 
DELIMITER //
CREATE PROCEDURE get_user_name(IN user_id INT, OUT name VARCHAR(100))
BEGIN
 SELECT name INTO name FROM users WHERE id = user_id;
END //
DELIMITER ;

Код PHP:

Пример 
$mysqli = new mysqli('localhost', 'user', 'password', 'database');
$stmt = $mysqli->prepare('CALL get_user_name(?, ?)');
$stmt->bind_param('is', $id, $name);
$id = 1;
$stmt->execute();
// После выполнения OUT-параметр доступен
$stmt->bind_result($name);
$stmt->fetch();
echo 'Имя пользователя: ' . $name;
$stmt->close();
Имя пользователя: Иван

В некоторых версиях MySQL после вызова хранимой процедуры может остаться неиспользованный результат. Вызовите $stmt->free_result() или $mysqli->next_result(), чтобы избежать ошибок при последующих запросах.

Как настроить асинхронные запросы?

Асинхронные запросы позволяют не блокировать выполнение скрипта во время ожидания ответа от БД. Используйте MYSQLI_ASYNC флаг и опрос результатов.

Пример 
$mysqli = new mysqli('localhost', 'user', 'password', 'database');
$mysqli->query('SELECT SLEEP(2)', MYSQLI_ASYNC);
// Делаем другую работу
sleep(1);
echo 'Ожидание...';
// Проверяем готовность
$links = $errors = $reject = [$mysqli];
if (mysqli_poll($links, $errors, $reject, 1)) {
    $result = $mysqli->reap_async_query();
    if ($result) {
        print_r($result->fetch_assoc());
        $result->free();
    }
}
Ожидание...
Array ( [SLEEP(2)] => 0 )

Асинхронный режим доступен не во всех сборках PHP. Убедитесь, что используется родной драйвер mysqlnd. Также требуется корректная обработка таймаутов.

Как выполнить массовую вставку с помощью подготовленных запросов?

При многократной вставке одинаковой структуры данных подготовленные запросы позволяют менять только параметры, экономя ресурсы сервера.

Пример 
$data = [
    ['user1@example.com', 'User1'],
    ['user2@example.com', 'User2'],
    ['user3@example.com', 'User3'],
];

$stmt = $mysqli->prepare('INSERT INTO users (email, name) VALUES (?, ?)');
$stmt->bind_param('ss', $email, $name);

foreach ($data as $row) {
    $email = $row[0];
    $name = $row[1];
    $stmt->execute();
}
echo 'Добавлено строк: ' . $stmt->affected_rows;
$stmt->close();
Добавлено строк: 3

При очень большом количестве записей (сотни тысяч) лучше использовать LOAD DATA INFILE или пакетные вставки с одним запросом INSERT INTO ... VALUES (...), (...).

Как использовать mysqli::options для настройки соединения?

Перед открытием соединения можно задать опции через mysqli_options() или метод options(). Например, установить таймаут или режим авто-коммита.

Пример 
$mysqli = new mysqli();
$mysqli->options(MYSQLI_OPT_CONNECT_TIMEOUT, 10);
$mysqli->options(MYSQLI_INIT_COMMAND, 'SET NAMES utf8mb4');
$mysqli->real_connect('localhost', 'user', 'password', 'database');
// Далее обычная работа

Некоторые опции (например, MYSQLI_SET_CHARSET_NAME) могут не поддерживаться старыми версиями libmysqlclient. Всегда проверяйте документацию вашего окружения.

Расширение mysqli в PHP - comments

En
Php mysqli extension (php)