Адаптация PHP для взаимодействия с MySQL восьмой версии

Расширенные примеры работы PHP с MySQL 8

Использование CTE с рекурсией (иерархические данные)

<?php
// Создаём таблицу категорий с родительскими ссылками
$pdo->exec('CREATE TABLE categories (
    id INT PRIMARY KEY,
    name VARCHAR(50),
    parent_id INT,
    FOREIGN KEY (parent_id) REFERENCES categories(id)
) ENGINE=InnoDB');

// Вставляем данные (пример древовидной структуры)
$categories = [
    [1, 'Электроника', null],
    [2, 'Компьютеры', 1],
    [3, 'Ноутбуки', 2],
    [4, 'Планшеты', 2],
    [5, 'Мониторы', 2],
    [6, 'Смартфоны', 1],
];
$stmt = $pdo->prepare('INSERT INTO categories (id, name, parent_id) VALUES (?, ?, ?)');
foreach ($categories as $row) {
    $stmt->execute($row);
}

// Рекурсивный CTE для получения всех подкатегорий 'Компьютеры'
$sql = "WITH RECURSIVE subcat AS (
    SELECT id, name, parent_id
    FROM categories
    WHERE id = 2
    UNION ALL
    SELECT c.id, c.name, c.parent_id
    FROM categories c
    INNER JOIN subcat s ON c.parent_id = s.id
)
SELECT * FROM subcat";

$result = $pdo->query($sql);
echo 'Подкатегории:';
while ($row = $result->fetch()) {
    echo ' - ' . $row['name'] . ' (id=' . $row['id'] . ')<br>';
}
?>

Подкатегории:
 - Компьютеры (id=2)
 - Ноутбуки (id=3)
 - Планшеты (id=4)
 - Мониторы (id=5)

Оконные функции: скользящее среднее

<?php
$pdo->exec('CREATE TABLE daily_sales (
    sale_date DATE,
    revenue DECIMAL(10,2)
) ENGINE=InnoDB');

// Вставляем данные за несколько дней
$data = [
    ['2025-01-01', 100.00],
    ['2025-01-02', 120.00],
    ['2025-01-03', 110.00],
    ['2025-01-04', 130.00],
    ['2025-01-05', 105.00],
];
$stmt = $pdo->prepare('INSERT INTO daily_sales (sale_date, revenue) VALUES (?, ?)');
foreach ($data as $d) {
    $stmt->execute($d);
}

// Скользящее среднее за 3 дня (центрированное)
$sql = "SELECT sale_date, revenue,
         AVG(revenue) OVER (ORDER BY sale_date ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING) AS moving_avg
         FROM daily_sales
         ORDER BY sale_date";

$result = $pdo->query($sql);
echo 'Дата | Выручка | Скользящее среднее<br>';
while ($row = $result->fetch()) {
    echo $row['sale_date'] . ' | ' . $row['revenue'] . ' | ' . round($row['moving_avg'], 2) . '<br>';
}
?>

Дата | Выручка | Скользящее среднее
2025-01-01 | 100.00 | 110.00
2025-01-02 | 120.00 | 110.00
2025-01-03 | 110.00 | 120.00
2025-01-04 | 130.00 | 115.00
2025-01-05 | 105.00 | 117.50

Транзакции с PDO и проверка целостности

<?php
try {
    $pdo->beginTransaction();
    $pdo->exec('UPDATE accounts SET balance = balance - 100 WHERE id = 1');
    $pdo->exec('UPDATE accounts SET balance = balance + 100 WHERE id = 2');
    $pdo->commit();
    echo 'Перевод средств выполнен успешно';
} catch (PDOException $e) {
    $pdo->rollBack();
    echo 'Ошибка транзакции: ' . $e->getMessage();
}
?>

Использование SSL-соединения с MySQL 8 через PDO

<?php
$dsn = 'mysql:host=mysql-server;dbname=secure_db;charset=utf8mb4';
$options = [
    PDO::MYSQL_ATTR_SSL_CA    => '/path/to/ca-cert.pem',
    PDO::MYSQL_ATTR_SSL_CERT  => '/path/to/client-cert.pem',
    PDO::MYSQL_ATTR_SSL_KEY   => '/path/to/client-key.pem',
    PDO::MYSQL_ATTR_SSL_VERIFY_SERVER_CERT => true,
];
$pdo = new PDO($dsn, 'user', 'pass', $options);
?>

Внимание: для работы SSL на сервере MySQL должна быть включена поддержка SSL, а пути к сертификатам должны быть доступны для чтения веб-сервером.

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

<?php
// При эмуляции prepares = false, PDO возвращает колонки с реальными типами MySQL (INT, DECIMAL, DATETIME и т.д.)
// Это удобно, когда нужно точно знать тип данных без дополнительного парсинга.

$stmt = $pdo->query('SELECT id, price, created_at FROM orders LIMIT 1');
$meta = $stmt->getColumnMeta(0); // 'id'
echo 'Тип колонки id: ' . $meta['native_type'] . '<br>'; // например, 'LONGLONG'
?>

Тип колонки id: LONGLONG

Пакетная вставка с использованием JSON-функций MySQL 8

<?php
// Вставка нескольких записей в одну строку через JSON_ARRAY
$orders = [
    ['product_id' => 1, 'quantity' => 2],
    ['product_id' => 3, 'quantity' => 5],
];
$json = json_encode($orders);
$sql = "INSERT INTO order_details (order_id, items)
         VALUES (1, JSON_ARRAY($json))"; // упрощённо, на практике нужно экранировать
// Вместо этого можно использовать подготовленный запрос с передачей JSON-строки
$stmt = $pdo->prepare('INSERT INTO events (payload) VALUES (?)');
$stmt->execute([json_encode(['type' => 'page_view', 'time' => date('c')])]);
?>