Преобразование MySQL в JSON с помощью PHP: способы и примеры
Извлечение данных из MySQL в JSON на PHP
Как эффективно преобразовать результат SQL запроса в JSON с помощью PDO?
Наиболее современный и безопасный способ - использование расширения PDO (PHP Data Objects). Оно поддерживает множество баз данных, устойчиво к SQL инъекциям через подготовленные запросы и удобно интегрируется с json_encode(). Ниже приведен полный пример.
// Подключение к MySQL через PDO
$dsn = 'mysql:host=localhost;dbname=testdb;charset=utf8mb4';
$user = 'root';
$pass = '';
$options = [
PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,
];
try {
$pdo = new PDO($dsn, $user, $pass, $options);
$stmt = $pdo->query('SELECT id, name, email FROM users');
$data = $stmt->fetchAll();
$json = json_encode($data, JSON_UNESCAPED_UNICODE | JSON_PRETTY_PRINT);
header('Content-Type: application/json; charset=utf-8');
echo $json;
} catch (PDOException $e) {
echo json_encode(['error' => $e->getMessage()]);
}
Php mysql json (извлечение данных из mysql в json на php)
Типичные проблемы:
- Отсутствие драйвера PDO для MySQL - проверьте расширение
pdo_mysqlв php.ini. - Ошибки кодировки: всегда указывайте
charset=utf8mb4в DSN и используйте флагJSON_UNESCAPED_UNICODE. - Пустой результат:
json_encode([])вернет[], что валидно, но может потребоваться проверка.
Для каких целей подходит PDO?
Этот вариант универсален: подходит для любых проектов, где нужна безопасность, гибкость и поддержка разных СУБД. Используется в современных фреймворках (Laravel, Symfony) при построении API.
Как извлечь данные из MySQL в JSON с помощью расширения mysqli?
Если проект использует только MySQL и требуется более низкоуровневый контроль, можно применить mysqli.
$mysqli = new mysqli('localhost', 'root', '', 'testdb');
$mysqli->set_charset('utf8mb4');
if ($mysqli->connect_error) {
echo json_encode(['error' => $mysqli->connect_error]);
exit;
}
$result = $mysqli->query('SELECT id, name, email FROM users');
$data = [];
while ($row = $result->fetch_assoc()) {
$data[] = $row;
}
$json = json_encode($data, JSON_UNESCAPED_UNICODE | JSON_PRETTY_PRINT);
header('Content-Type: application/json; charset=utf-8');
echo $json;
$result->free();
$mysqli->close();
Характерные ошибки:
- Не забыть
set_charset()- иначе русские символы превратятся в знаки вопроса. - Обработка ошибок запроса: проверять
$mysqli->errorпослеquery(). - Утечка памяти: всегда освобождать результат и закрывать соединение.
Как сформировать вложенный JSON из связанных таблиц (один ко многим)?
Часто требуется объединить данные из двух таблиц (например, пользователь и его заказы). Один из способов - сначала получить пользователей, затем для каждого выполнить выборку заказов.
$pdo = new PDO($dsn, $user, $pass, $options);
$stmtUsers = $pdo->query('SELECT id, name FROM users');
$users = $stmtUsers->fetchAll();
foreach ($users as &$user) {
$stmtOrders = $pdo->prepare('SELECT id, product, price FROM orders WHERE user_id = ?');
$stmtOrders->execute([$user['id']]);
$user['orders'] = $stmtOrders->fetchAll();
}
unset($user);
echo json_encode($users, JSON_UNESCAPED_UNICODE | JSON_PRETTY_PRINT);
Проблемы производительности:
При большом количестве пользователей N+1 запросов может замедлить работу. Альтернатива - выполнить один JOIN и сгруппировать результат в PHP, либо использовать подзапросы и GROUP_CONCAT с последующим разбором JSON.
Как преобразовать данные с учетом типов (числа, null, даты)?
По умолчанию json_encode() преобразует NULL SQL в null, целые числа - в числа, но даты остаются строками. Для полного контроля можно вручную приводить типы.
$stmt = $pdo->query('SELECT id, price, created_at FROM products');
$data = $stmt->fetchAll(PDO::FETCH_ASSOC);
array_walk($data, function (&$row) {
$row['price'] = (float)$row['price']; // строка в число
$row['created_at'] = date('c', strtotime($row['created_at'])); // ISO 8601
});
echo json_encode($data, JSON_UNESCAPED_UNICODE);
Ошибки:
Если в базе поле price содержит нечисловые значения, приведение даст 0 или NaN. Следует проверять is_numeric().
Как генерировать JSON с пользовательскими ключами и вложенными структурами?
Иногда нужно переименовать поля или создать объект с произвольной вложенностью. Для этого используется ручное построение массива.
$stmt = $pdo->query('SELECT id, first_name, last_name FROM employees');
$employees = $stmt->fetchAll();
$result = [];
foreach ($employees as $emp) {
$result[] = [
'employeeId' => (int)$emp['id'],
'fullName' => $emp['first_name'] . ' ' . $emp['last_name'],
'profile' => [
'type' => 'staff',
'active' => true
]
];
}
echo json_encode($result, JSON_UNESCAPED_UNICODE);
Возможные трудности:
При большом объёме данных ручное конструирование может быть медленнее, чем прямая выборка. Используйте только когда структура JSON сильно отличается от схемы таблицы.
Расширенные примеры извлечения MySQL в JSON
Пример 1. Потоковая выдача большого JSON без загрузки всего в память
Когда таблица содержит миллионы строк, использование fetchAll() может привести к нехватке памяти. Вместо этого применяют итеративный вывод.
$pdo = new PDO($dsn, $user, $pass, $options);
$stmt = $pdo->query('SELECT id, name FROM large_table');
header('Content-Type: application/json; charset=utf-8');
echo '[';
$first = true;
while ($row = $stmt->fetch(PDO::FETCH_ASSOC)) {
if (!$first) echo ',';
echo json_encode($row, JSON_UNESCAPED_UNICODE);
$first = false;
}
echo ']';
Результат: выводится валидный JSON массив, память расходуется только на одну строку за раз.
Внимание: Не забудьте отключить буферизацию вывода, например, через ob_implicit_flush() или ob_end_flush().
Пример 2. Использование JSON_ARRAY и JSON_OBJECT в MySQL 5.7+
MySQL умеет формировать JSON на стороне сервера, что может быть быстрее для простых структур.
$stmt = $pdo->query("SELECT JSON_ARRAYAGG(
JSON_OBJECT('id', id, 'name', name, 'email', email)
) AS json_result FROM users");
$row = $stmt->fetch();
echo $row['json_result'];
[{"id":1,"name":"Иван","email":"ivan@example.com"},{"id":2,"name":"Мария","email":"maria@example.com"}]
Ограничение: JSON_ARRAYAGG работает только в MySQL 5.7.22+ и требует осторожности с группировкой. При больших объёмах может быть медленнее, чем обработка в PHP.
Пример 3. Генерация вложенного JSON с помощью GROUP_CONCAT и ручной сборки
$stmt = $pdo->query("
SELECT
u.id,
u.name,
CONCAT('[',
GROUP_CONCAT(
JSON_OBJECT('order_id', o.id, 'product', o.product)
SEPARATOR ','
)
, ']') AS orders_json
FROM users u
LEFT JOIN orders o ON u.id = o.user_id
GROUP BY u.id
");
$users = $stmt->fetchAll(PDO::FETCH_ASSOC);
// Преобразуем строку orders_json в реальный массив
foreach ($users as &$user) {
$user['orders'] = json_decode($user['orders_json'], true) ?? [];
unset($user['orders_json']);
}
unset($user);
echo json_encode($users, JSON_UNESCAPED_UNICODE | JSON_PRETTY_PRINT);
[
{
"id": 1,
"name": "Иван",
"orders": [
{"order_id": 10, "product": "Книга"},
{"order_id": 11, "product": "Ручка"}
]
},
{
"id": 2,
"name": "Мария",
"orders": []
}
]
Проблема: GROUP_CONCAT имеет ограничение длины (по умолчанию 1024 символа). Для больших наборов данных нужно увеличить через SET SESSION group_concat_max_len = 1000000;.
Пример 4. Обработка NULL и пустых строк
$stmt = $pdo->query('SELECT id, COALESCE(NULLIF(name, ''), 'Без имени') AS name, email FROM users');
$data = $stmt->fetchAll();
echo json_encode($data, JSON_UNESCAPED_UNICODE | JSON_UNESCAPED_SLASHES);
[{"id":1,"name":"Иван", "email":null},{"id":2,"name":"Без имени", "email":"test@test.com"}]
Здесь NULLIF заменяет пустую строку на NULL, затем COALESCE подставляет значение по умолчанию. В JSON null выводится как null, а не пустая строка.
Пример 5. Использование хранимой процедуры для генерации JSON
CREATE PROCEDURE GetUsersJson()
BEGIN
SELECT JSON_ARRAYAGG(
JSON_OBJECT('id', id, 'name', name)
) AS users FROM users;
END
$stmt = $pdo->query('CALL GetUsersJson()');
$row = $stmt->fetch();
echo $row['users'];
Этот подход удобен, когда логика формирования JSON сложна и должна быть переиспользована.