PHP: полный мануал с примерами, функциями и ООП

Когда свойства-документы дополняют таблицы

Для каталога могут понадобиться свойства разных товаров, а интеграция может прислать массив позиций в одном документе. MySQL умеет хранить JSON, читать конкретные пути и превращать вложенные элементы в строки SQL. Задача главы — получить нужную карточку или предпросмотр прямо запросом, сохранив понятные типы и ошибки.

Представьте карточку товара с обязательными графами и отдельным листом характеристик. JSON здесь похож на этот лист, но это лишь модель назначения: он не заменяет таблицы и не описывает буквально устройство хранения MySQL. Ссылки на покупателей, товары и позиции остаются реляционными связями. Их основу объясняет проектирование таблиц и связей.

Перед этой главой нужны первый SELECT, параметры PDO и понимание объекта/массива JSON. Если сами фигурные и квадратные скобки пока незнакомы, начните с многомерных массивов и JSON в PHP. Здесь изучается работа сервера MySQL с документом, а не повторяется PHP-перебор массива.

Запустить самостоятельный пример и увидеть результат

Комплект рассчитан на PHP 8.4+ с pdo_mysql/mysqlnd и MySQL 8.4. Helper использует PDO::connect и Pdo\Mysql; рассматриваемые SQL-возможности зависят от сервера. Контрольный стенд — PHP 8.5.11 / MySQL 8.4.13. Подготовьте отдельную локальную базу по инструкции запуска MySQL.

Задайте PHPMAN_MYSQL_HOST=127.0.0.1, PHPMAN_MYSQL_PORT, PHPMAN_MYSQL_DATABASE, PHPMAN_MYSQL_USER, PHPMAN_MYSQL_PASSWORD в терминале. На стенде порт 33070. Учебному пользователю нужны SELECT, INSERT, UPDATE, CREATE TEMPORARY TABLES. Все таблицы этой главы временные, с префиксом mysql113_json_; постоянные данные и настройки сервера не меняются.

Каждый ZIP самостоятельный: example.php, jd113-helper.php и README.txt. Распакуйте один пример отдельно, выполните php example.php, откройте result.html. Рядом сохраняются result.json и metrics.json с версиями, результатами проверок и хешами. Helper подключается с native prepare и исключениями, а конкретные SQL и входные данные находятся в example.php. Это запуск PHP CLI и настоящего MySQL; он не проверяет отправку HTTP-формы или нагрузку рабочего сервиса.

Путь, JSON-значение и SQL-значение

Путь начинается с $. Точка выбирает поле JSON-объекта, [0] — первый элемент JSON-массива, [*] — все элементы там, где функция допускает wildcard. Ключ с точкой в самом имени требует JSON path с кавычками вокруг ключа. Это самостоятельный язык путей: имя колонки, путь и значение параметра не взаимозаменяемы. В примерах пути фиксированы; параметры передают ID, порог или целый документ.

Сохранить свойства товара и увидеть, что проверяет колонка JSON

У чайника есть обычные поля каталога: ID, название и цена. Дополнительные характеристики отличаются у разных видов товаров. Сохраним марку, цвет и мощность в JSON-колонке, а для карточки прочитаем только нужные свойства средствами SQL.

Что поступает на вход

Что получится после полного запуска

Короткая запись основного действия

SELECT name,
       JSON_EXTRACT(attributes, '$.brand') AS json_brand,
       attributes->>'$.brand' AS plain_brand
FROM mysql113_json_products
WHERE id = ?;

Этот фрагмент объясняет SQL на уже созданных данных. Символ ? — значение, которое передаётся при execute; полная программа ниже создаёт собственные таблицы и выполняет весь сценарий.

JSON — формат значения, а JSON-колонка — место его хранения в MySQL. В CREATE TABLE запись attributes JSON NOT NULL задаёт тип колонки; это отличается от VARCHAR с произвольным текстом. При вставке в JSON MySQL разбирает документ. Неполная запись {broken вызывает PDOException с SQLSTATE 22032 и кодом MySQL 3140. Программа ловит именно эту ожидаемую ошибку и затем читает таблицу: ID 2 не сохранён.

Путь $.brand начинается с корня документа $; точка выбирает свойство brand. Это синтаксис JSON path внутри SQL-строки, не обращение к PHP-переменной. JSON_EXTRACT возвращает JSON-значение: строковая марка имеет вид "Север". Оператор attributes->>'$.brand' снимает JSON-кавычки и даёт обычный текст Север. Он соответствует JSON_UNQUOTE(JSON_EXTRACT(...)); удалять кавычки через replace не нужно.

Параметр ? в WHERE получает ID товара при execute. В полном примере json_encode только готовит документ для передачи, а извлечение brand/color делает сервер. Кодирование PHP-массива и обработка ошибок JSON отдельно разобраны в главе о JSON в PHP. Native prepare и окружение подключения объясняются в PDO_MYSQL: подключение и параметры.

Проверка [] даёт JSON_VALID=1 и JSON_TYPE=ARRAY. Это корректный JSON, но товару по нашему договору нужен объект характеристик. Синтаксическая допустимость не подтверждает наличие brand, его длину и разрешённый тип power_w. Полная программа показывает эту границу; [] проверяется отдельно и не вставляется как второй товар.

Границы и ошибки

ID, цена, остаток, связи заказа с покупателем и ограничения уникальности остаются обычными колонками и таблицами. JSON уместен для дополнительных неоднородных свойств и исходного документа, но сам по себе не создаёт внешний ключ для вложенного sku. MySQL может нормализовать представление документа: порядок ключей и пробелы не являются договором сохранения исходного текста. Native JSON и JSON_TABLE требуют серверной поддержки MySQL; версия PHP не добавляет их в старый сервер.

<?php
declare(strict_types=1);
require __DIR__.'/jd113-helper.php';
$db=jd113Connect();
$runtime=['php'=>PHP_VERSION,'client'=>$db->getAttribute(PDO::ATTR_CLIENT_VERSION),'server'=>$db->getAttribute(PDO::ATTR_SERVER_VERSION)];
$details=[];
$db->exec('CREATE TEMPORARY TABLE mysql113_json_products (id INT PRIMARY KEY,name VARCHAR(50) NOT NULL,price_cents INT NOT NULL,attributes JSON NOT NULL) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4');
$insert=$db->prepare('INSERT INTO mysql113_json_products (id,name,price_cents,attributes) VALUES (?,?,?,?)');
$document=json_encode(['brand'=>'Север','color'=>'синий','power_w'=>1500],JSON_UNESCAPED_UNICODE|JSON_THROW_ON_ERROR);
$insert->execute([1,'Чайник',299000,$document]);
$failure=null;
try {$insert->execute([2,'Повреждённый документ',100,'{broken']);}
catch(PDOException $error) {$failure=['state'=>$error->errorInfo[0]??'', 'code'=>(int)($error->errorInfo[1]??0)];}
$insert->closeCursor();
$product=jd113Rows($db,"SELECT id,name,price_cents,JSON_TYPE(attributes) AS document_type,JSON_EXTRACT(attributes,'$.brand') AS quoted_brand,attributes->>'$.brand' AS brand,attributes->>'$.color' AS color FROM mysql113_json_products ORDER BY id");
$shape=jd113Rows($db,"SELECT JSON_VALID('[]') AS valid_json,JSON_TYPE(CAST('[]' AS JSON)) AS document_type")[0];
$r=$product[0];
$rows=[['Сохранённый товар',(int)$r['id'],$r['name'],$r['brand'],$r['color'],$r['document_type']],['Повреждённый JSON',2,null,null,null,$failure===null?'Неожиданно принят':'Отклонён: '.$failure['state'].' / '.$failure['code']],['Документ []',null,null,null,null,'Допустимый JSON: '.$shape['document_type'].'; это не объект свойств']];
$checks=['one_product_only'=>count($product)===1,'properties_read_by_sql'=>$r['brand']==='Север'&&$r['color']==='синий'&&(int)$r['price_cents']===299000,'json_and_text_differ'=>$r['quoted_brand']==='"Север"'&&$r['document_type']==='OBJECT','invalid_document_rejected'=>$failure===['state'=>'22032','code'=>3140],'valid_array_is_not_object'=>(int)$shape['valid_json']===1&&$shape['document_type']==='ARRAY'];
$details=['stored_json_brand'=>$r['quoted_brand'],'plain_brand'=>$r['brand'],'invalid_document'=>$failure,'valid_array_checked_but_not_inserted'=>true];
$db=null;
jd113Report('mysql113-json-store-document', ['Шаг', 'ID', 'Название', 'Марка', 'Цвет', 'Результат'], $rows, $checks, $runtime, $details);
echo 'mysql113-json-store-document: verified result.html and result.json
';

mysql113-json-store-document: verified result.html and result.json

Открыть фактический HTML-результат · Скачать результат JSON

Попробуйте сами. Добавьте в объект свойство volume_ml со значением 1700 и в SELECT выражение JSON_VALUE(attributes, '$.volume_ml' RETURNING UNSIGNED). Получится число 1700; brand и color останутся прежними. Если ожидается именно число, дополнительно проверьте исходный JSON_TYPE, а не полагайтесь на возможность преобразования строки.

Отличить отсутствующее поле, два вида null и тип значения

Поставщик может не прислать мощность, прислать явное null, число, строку с числом или false. Для формы редактирования это разные события: «не менять поле», «очистить», «установить значение» и «отклонить неподходящий тип». Сначала увидим, какие сведения сохраняются после чтения.

Что поступает на вход

Что получится после полного запуска

Короткая запись основного действия

SELECT JSON_CONTAINS_PATH(payload, 'one', '$.value') AS has_key,
       JSON_TYPE(JSON_EXTRACT(payload, '$.value')) AS value_type,
       JSON_VALUE(payload, '$.value') AS scalar_value
FROM mysql113_json_states
ORDER BY id;

Этот фрагмент объясняет SQL на уже созданных данных. Символ ? — значение, которое передаётся при execute; полная программа ниже создаёт собственные таблицы и выполняет весь сценарий.

У отсутствующего пути JSON_EXTRACT возвращает SQL NULL. У существующего JSON null результат — JSON-значение null; JSON_TYPE называет его строкой NULL, а наличие пути равно 1. Если сама колонка SQL NULL, у неё вообще нет документа: и проверка пути возвращает SQL NULL. В отчёте SQL NULL показан заглавными буквами, текстовое представление JSON null — строчными.

JSON_CONTAINS_PATH принимает документ, режим one или all и один либо несколько путей. one достаточно одного существующего пути; all требует все перечисленные. Здесь путь единственный. Проверка наличия не проверяет пригодность значения. У false наличие равно 1, JSON_TYPE равен BOOLEAN; ложное логическое значение нельзя подменять отсутствием поля.

Без RETURNING функция JSON_VALUE возвращает VARCHAR(512). Поэтому и JSON-число 1500, и JSON-строка "1500" дают скалярный текст 1500. JSON null превращается в SQL NULL, как и отсутствующий путь при стандартном NULL ON EMPTY. Если требуется различать эти случаи, сохраняйте информацию о наличии и исходном типе до такой нормализации. Не проверяйте наличие через нестрогое условие if ($value): оно также смешивает ноль, пустоту и false.

Полный пример создаёт шесть строк, а затем одним SELECT выводит шесть независимых признаков. Таблица показывает фактические результаты сервера; PHP только приводит числовые флаги к int и записывает отчёт. Правила SQL NULL и трёхзначной логики продолжаются в контрактах MySQL.

Границы и ошибки

JSON_TYPE сообщает тип, но не проверяет диапазон числа, длину строки или права пользователя менять поле. Строка "null", JSON null и отсутствие значения — три разные формы. JSON_VALUE предназначен для скаляра; попытка извлечь объект или массив регулируется ON ERROR. Невалидный JSON или неверный path — отдельная ошибка, которую ON ERROR у JSON_VALUE не превращает в безусловно допустимый документ.

<?php
declare(strict_types=1);
require __DIR__.'/jd113-helper.php';
$db=jd113Connect();
$runtime=['php'=>PHP_VERSION,'client'=>$db->getAttribute(PDO::ATTR_CLIENT_VERSION),'server'=>$db->getAttribute(PDO::ATTR_SERVER_VERSION)];
$details=[];
$db->exec('CREATE TEMPORARY TABLE mysql113_json_states (id INT PRIMARY KEY,label VARCHAR(50) NOT NULL,payload JSON NULL) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4');
$insert=$db->prepare('INSERT INTO mysql113_json_states (id,label,payload) VALUES (?,?,?)');
foreach([[1,'Нет ключа','{}'],[2,'JSON null','{"value":null}'],[3,'SQL NULL',null],[4,'Число','{"value":1500}'],[5,'Строка','{"value":"1500"}'],[6,'Логическое false','{"value":false}']] as $row){$insert->execute($row);}
$insert->closeCursor();
$data=jd113Rows($db,"SELECT label,payload IS NULL AS sql_null,JSON_CONTAINS_PATH(payload,'one','$.value') AS has_key,JSON_TYPE(JSON_EXTRACT(payload,'$.value')) AS value_type,JSON_EXTRACT(payload,'$.value') AS extracted,JSON_VALUE(payload,'$.value') AS scalar_value FROM mysql113_json_states ORDER BY id");
$rows=[];
foreach($data as $r){$rows[]=[$r['label'],(int)$r['sql_null'],$r['has_key']===null?null:(int)$r['has_key'],$r['value_type'],$r['extracted'],$r['scalar_value']];}
$checks=['missing_has_no_key'=>$rows[0]===['Нет ключа',0,0,null,null,null],'json_null_is_present'=>$rows[1]===['JSON null',0,1,'NULL','null',null],'sql_null_is_no_document'=>$rows[2]===['SQL NULL',1,null,null,null,null],'number_and_string_distinguished'=>$rows[3]===['Число',0,1,'INTEGER','1500','1500']&&$rows[4]===['Строка',0,1,'STRING','"1500"','1500'],'false_is_not_missing'=>$rows[5]===['Логическое false',0,1,'BOOLEAN','false','false']];
$details=['json_value_default_return_type'=>'VARCHAR(512)','sql_null_display'=>'NULL','json_null_display'=>'null','row_count'=>count($rows)];
$db=null;
jd113Report('mysql113-json-missing-null-types', ['Случай', 'Колонка SQL NULL?', 'Ключ есть?', 'JSON_TYPE', 'JSON_EXTRACT', 'JSON_VALUE'], $rows, $checks, $runtime, $details);
echo 'mysql113-json-missing-null-types: verified result.html and result.json
';

mysql113-json-missing-null-types: verified result.html and result.json

Открыть фактический HTML-результат · Скачать результат JSON

Попробуйте сами. Добавьте {"value":0} и {"value":""}. Оба пути существуют: has_key=1. Типы будут INTEGER и STRING; JSON_VALUE даст текст 0 и пустую строку. Такая проверка показывает, почему для формы нельзя использовать только truthy/falsy.

Практический вопрос: Почему JSON null не превращается в nullable integer сам собой. У автора JSON null после извлечения и снятия кавычек приводил к ошибке целочисленной generated column. Наши отдельные сценарии показывают отсутствие пути, JSON null и SQL NULL, затем типизированное извлечение с явным договором. Обсуждение задаёт проблему; правила проверены по официальной документации, пример написан отдельно.

Отобрать мощность числом и получить готовую страницу из SQL

Экран должен показать два самых мощных устройства от 1000 Вт. Получать все документы, декодировать каждый в PHP и сортировать большой массив не требуется: MySQL извлечёт числовое значение, отфильтрует и ограничит ответ.

Что поступает на вход

Что получится после полного запуска

Короткая запись основного действия

SELECT id, name,
       JSON_VALUE(attributes, '$.power_w' RETURNING SIGNED) AS power_w
FROM mysql113_json_devices
WHERE JSON_VALUE(attributes, '$.power_w' RETURNING SIGNED) >= ?
ORDER BY power_w DESC, id ASC
LIMIT 2;

Этот фрагмент объясняет SQL на уже созданных данных. Символ ? — значение, которое передаётся при execute; полная программа ниже создаёт собственные таблицы и выполняет весь сценарий.

RETURNING SIGNED задаёт целочисленный результат JSON_VALUE. Сравнение становится числовым: 2200 больше 1500 и 900. Ввод порога передаётся отдельным параметром с PDO::PARAM_INT. ORDER BY сначала сортирует мощность по убыванию, а равные значения — по ID по возрастанию. Второй критерий нужен, чтобы страница с двумя строками имела определённый состав.

Короткий запрос показывает прямое извлечение. В полном варианте таблица добавляет вычисляемую колонку power_w INT GENERATED ALWAYS AS (...) STORED. Значение формируется из attributes, а не заполняется отдельным PHP-присваиванием. На него создан индекс power_page(power_w DESC, id ASC): направления соответствуют смешанной сортировке страницы. Реальный запрос обращается к той же типизированной колонке. Программа проверяет совпадение ответа прямого выражения и колонки, состав и направления индекса.

NULL ON EMPTY оставляет неизвестную мощность неизвестной; JSON null также даёт SQL NULL. Такие строки не проходят условие >= 1000. NULL ON ERROR задаёт политику для неподходящего извлекаемого значения; это не сертификат качества входных данных. Строка "1500" успешно преобразуется в 1500 — пример проверяет это отдельно. Если договор разрешает только JSON-число, нужно проверять JSON_TYPE и диапазон на входе.

STORED-значение и индекс занимают место и требуют обновления при изменении исходного документа. Часто важная постоянная характеристика заслуживает обычной типизированной колонки с явными ограничениями. Generated column полезна, когда документ уже является выбранной формой хранения, а определённый путь регулярно участвует в SQL-фильтре. Базовые индексы и отбор для экрана разобраны в практике SQL.

Границы и ошибки

Наличие индекса не доказывает выбор этого индекса оптимизатором, отсутствие filesort или ускорение. Здесь нет нагрузочного замера и требования к конкретному EXPLAIN. Для рабочего объёма проверяют реальный план и распределение данных. Извлечение неизвестного числа в 0 без явного договора исказит фильтр. Преобразование значения также не проверяет допустимые единицы измерения.

<?php
declare(strict_types=1);
require __DIR__.'/jd113-helper.php';
$db=jd113Connect();
$runtime=['php'=>PHP_VERSION,'client'=>$db->getAttribute(PDO::ATTR_CLIENT_VERSION),'server'=>$db->getAttribute(PDO::ATTR_SERVER_VERSION)];
$details=[];
$db->exec("CREATE TEMPORARY TABLE mysql113_json_devices (id INT PRIMARY KEY,name VARCHAR(50) NOT NULL,attributes JSON NOT NULL,power_w INT GENERATED ALWAYS AS (JSON_VALUE(attributes,'$.power_w' RETURNING SIGNED NULL ON EMPTY NULL ON ERROR)) STORED,INDEX power_page(power_w DESC,id ASC)) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4");
$insert=$db->prepare('INSERT INTO mysql113_json_devices (id,name,attributes) VALUES (?,?,?)');
foreach([[1,'Мини','{"power_w":900}'],[2,'Дом','{"power_w":1500}'],[3,'Профи','{"power_w":2200}'],[4,'Без мощности','{}'],[5,'Лампа','{"power_w":120}'],[6,'Тихий','{"power_w":"1500"}'],[7,'Не определено','{"power_w":null}']] as $row){$insert->execute($row);}
$insert->closeCursor();
$query=$db->prepare('SELECT id,name,power_w FROM mysql113_json_devices WHERE power_w>=? ORDER BY power_w DESC,id ASC LIMIT 2');
$query->bindValue(1,1000,PDO::PARAM_INT);$query->execute();$data=$query->fetchAll();$query->closeCursor();
$rows=[];foreach($data as $r){$rows[]=[(int)$r['id'],$r['name'],(int)$r['power_w']];}
$direct=jd113Rows($db,"SELECT id FROM mysql113_json_devices WHERE JSON_VALUE(attributes,'$.power_w' RETURNING SIGNED NULL ON EMPTY NULL ON ERROR)>=? ORDER BY JSON_VALUE(attributes,'$.power_w' RETURNING SIGNED NULL ON EMPTY NULL ON ERROR) DESC,id ASC LIMIT 2",[1000]);
$indexRows=$db->query("SHOW INDEX FROM mysql113_json_devices WHERE Key_name='power_page'")->fetchAll();
$nullable=jd113Rows($db,'SELECT id,power_w FROM mysql113_json_devices WHERE id IN (4,6,7) ORDER BY id');
$checks=['ready_page'=>$rows===[[3,'Профи',2200],[2,'Дом',1500]],'same_scalar_semantics'=>array_map('intval',array_column($direct,'id'))===[3,2],'index_has_scalar_and_tiebreaker'=>array_column($indexRows,'Column_name')===['power_w','id']&&array_column($indexRows,'Collation')===['D','A'],'missing_and_json_null_stay_null'=>$nullable[0]['power_w']===null&&$nullable[2]['power_w']===null,'numeric_string_is_converted'=>(int)$nullable[1]['power_w']===1500];
$details=['index_columns'=>array_column($indexRows,'Column_name'),'index_directions'=>array_column($indexRows,'Collation'),'selected_ids'=>array_column($rows,0),'page_limit'=>2,'performance_not_measured'=>true,'converted_string_id'=>6];
$db=null;
jd113Report('mysql113-json-typed-filter', ['ID', 'Товар', 'Мощность, Вт'], $rows, $checks, $runtime, $details);
echo 'mysql113-json-typed-filter: verified result.html and result.json
';

mysql113-json-typed-filter: verified result.html and result.json

Открыть фактический HTML-результат · Скачать результат JSON

Попробуйте сами. Измените LIMIT 2 на LIMIT 3. Третьим будет ID 6, Тихий, 1500 Вт: при равной мощности ID 2 стоит раньше ID 6. Если оставить LIMIT 2, но задать порог 2000, останется только Профи.

Изменить доставку, сохранить соседние поля и вставить массив правильно

Для заказа 42 меняется способ доставки и появляются две маркировки. Адрес и признак хрупкости должны сохраниться. Заодно нужно удалить устаревший ключ, а не скачать документ, заменить его случайно неполным массивом и потерять соседние свойства.

Что поступает на вход

Что получится после полного запуска

Короткая запись основного действия

UPDATE mysql113_json_delivery
SET payload = JSON_REMOVE(
    JSON_SET(payload, '$.labels', CAST(? AS JSON)),
    '$.legacy_code'
)
WHERE id = ?;

Этот фрагмент объясняет SQL на уже созданных данных. Символ ? — значение, которое передаётся при execute; полная программа ниже создаёт собственные таблицы и выполняет весь сценарий.

JSON_SET получает документ и пары path/value. Существующий указанный путь заменяется, подходящий отсутствующий ключ добавляется; соседние свойства остаются. Функция возвращает новый JSON-документ. Чтобы сохранить его, UPDATE присваивает результат обратно payload и выбирает конкретную строку через WHERE id = ?. Без UPDATE отдельный SELECT JSON_SET только вычисляет значение.

JSON-текст, пришедший как обычный SQL-параметр, имеет строковый тип. Поэтому первый UPDATE намеренно создаёт labels типа STRING с содержимым ["Хрупкое","Верх"]. JSON_LENGTH для скаляра равен 1; это не количество символов. Второй UPDATE использует CAST(? AS JSON), и тот же текст становится настоящим массивом: JSON_TYPE=ARRAY, JSON_LENGTH=2. Для отдельного строкового свойства такой CAST не нужен; он меняет смысл значения, а не делает параметр «более безопасным».

JSON_REMOVE возвращает документ без указанного ключа. Удаление отсутствующего ключа оставляет документ прежним. Это отличается от JSON_SET с SQL NULL как значением: в примере появляется существующий comment с JSON null. Три состояния — удалить, присвоить null, присвоить строку — надо выбирать по договору формы или API.

Пары JSON_SET применяются слева направо; допустимы конкретные пути без wildcard. Не рассчитывайте, что отсутствующие промежуточные объекты любой глубины будут построены автоматически. В этом примере address уже существует и остаётся неизменным. Полная программа декодирует окончательный документ только для проверки сохранности соседних свойств; редактирование выполняет SQL. Конкурентное редактирование и версия строки рассматриваются в безопасном изменении данных.

Границы и ошибки

Передача параметра защищает границу SQL-значения, но не проверяет, какие свойства пользователь имеет право менять. Пути здесь фиксированы автором; произвольные пути от клиента не принимаются. SQL NULL вместо всего исходного документа не превращается автоматически в объект. Возможность частичного внутреннего обновления JSON зависит от условий MySQL: пример не обещает отсутствие полной записи документа на диск.

<?php
declare(strict_types=1);
require __DIR__.'/jd113-helper.php';
$db=jd113Connect();
$runtime=['php'=>PHP_VERSION,'client'=>$db->getAttribute(PDO::ATTR_CLIENT_VERSION),'server'=>$db->getAttribute(PDO::ATTR_SERVER_VERSION)];
$details=[];
$db->exec('CREATE TEMPORARY TABLE mysql113_json_delivery (id INT PRIMARY KEY,payload JSON NOT NULL) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4');
$insert=$db->prepare('INSERT INTO mysql113_json_delivery VALUES (?,?)');
$insert->execute([42,'{"method":"pickup","address":{"city":"Москва"},"legacy_code":"old","fragile":false}']);$insert->closeCursor();
$snapshot="SELECT payload->>'$.method' AS method,payload->>'$.address.city' AS city,JSON_TYPE(JSON_EXTRACT(payload,'$.labels')) AS labels_type,JSON_LENGTH(JSON_EXTRACT(payload,'$.labels')) AS labels_count,JSON_CONTAINS_PATH(payload,'one','$.legacy_code') AS has_legacy,JSON_TYPE(JSON_EXTRACT(payload,'$.comment')) AS comment_type FROM mysql113_json_delivery WHERE id=?";
$rows=[];
$capture=function(string $step)use($db,$snapshot,&$rows):void{$r=jd113Rows($db,$snapshot,[42])[0];$rows[]=[$step,$r['method'],$r['city'],$r['labels_type'],$r['labels_count']===null?null:(int)$r['labels_count'],(int)$r['has_legacy'],$r['comment_type']];};
$capture('До изменения');
$labels=json_encode(['Хрупкое','Верх'],JSON_UNESCAPED_UNICODE|JSON_THROW_ON_ERROR);
$wrong=$db->prepare("UPDATE mysql113_json_delivery SET payload=JSON_SET(payload,'$.method',?,'$.labels',?,'$.comment',NULL) WHERE id=?");
$wrong->execute(['courier',$labels,42]);$wrong->closeCursor();$capture('JSON передан как строка');
$correct=$db->prepare("UPDATE mysql113_json_delivery SET payload=JSON_REMOVE(JSON_SET(payload,'$.labels',CAST(? AS JSON)),'$.legacy_code') WHERE id=?");
$correct->execute([$labels,42]);$correct->closeCursor();$capture('После CAST и удаления ключа');
$stored=jd113Rows($db,'SELECT payload FROM mysql113_json_delivery WHERE id=?',[42])[0]['payload'];
$decoded=json_decode($stored,true,512,JSON_THROW_ON_ERROR);
$checks=['string_is_not_array'=>$rows[1][3]==='STRING'&&$rows[1][4]===1,'array_is_inserted'=>$rows[2][3]==='ARRAY'&&$rows[2][4]===2&&$decoded['labels']===['Хрупкое','Верх'],'other_properties_preserved'=>$decoded['address']===['city'=>'Москва']&&$decoded['fragile']===false,'method_changed'=>$decoded['method']==='courier','key_removed'=>!array_key_exists('legacy_code',$decoded),'json_null_added'=>array_key_exists('comment',$decoded)&&$decoded['comment']===null];
$details=['final_document'=>$decoded,'row_id'=>42,'json_decoded_only_for_postcondition'=>true];
$db=null;
jd113Report('mysql113-json-patch-properties', ['Шаг', 'Способ', 'Город', 'Тип labels', 'Размер labels', 'legacy_code есть?', 'Тип comment'], $rows, $checks, $runtime, $details);
echo 'mysql113-json-patch-properties: verified result.html and result.json
';

mysql113-json-patch-properties: verified result.html and result.json

Открыть фактический HTML-результат · Скачать результат JSON

Попробуйте сами. Удалите comment через JSON_REMOVE(payload, '$.comment'). После этого JSON_CONTAINS_PATH вернёт 0. Если вместо удаления записать строку "null", путь останется, но JSON_TYPE будет STRING. Маркировки и адрес в обоих случаях должны остаться прежними.

Практический вопрос: Почему JSON_SET сохраняет массив как строку с кавычками. Массив был передан как SQL-строка и стал одним строковым JSON-значением. Наш пример сравнивает реальные типы STRING и ARRAY, исправляет передачу через CAST и проверяет сохранность соседних свойств. Обсуждение задаёт проблему; правила проверены по официальной документации, пример написан отдельно.

Развернуть позиции JSON в строки и найти товары по артикулу

Поставщик прислал заказ с массивом позиций, а названия и цены находятся в реляционном каталоге. Нужен понятный предпросмотр: какой артикул найден, сколько он стоит и какая строка не может быть принята. Неизвестный артикул нельзя незаметно выбросить из заказа.

Что поступает на вход

Что получится после полного запуска

Короткая запись основного действия

SELECT jt.line_no, jt.sku, jt.qty, p.name
FROM mysql113_json_orders AS o
JOIN JSON_TABLE(o.payload, '$.items[*]' COLUMNS (
    line_no FOR ORDINALITY,
    sku VARCHAR(20) PATH '$.sku',
    qty INT PATH '$.qty'
)) AS jt ON TRUE
LEFT JOIN mysql113_json_catalog AS p
  ON p.sku COLLATE utf8mb4_bin = jt.sku COLLATE utf8mb4_bin
WHERE o.id = ?
ORDER BY jt.line_no;

Этот фрагмент объясняет SQL на уже созданных данных. Символ ? — значение, которое передаётся при execute; полная программа ниже создаёт собственные таблицы и выполняет весь сценарий.

JSON_TABLE превращает выбранную часть документа в набор строк для текущего SQL-запроса. Он не создаёт постоянную таблицу и не записывает позиции заказа. Первый аргумент o.payload берётся из уже указанной таблицы orders; путь $.items[*] означает каждый элемент массива items. COLUMNS описывает, какие SQL-колонки извлечь из каждого элемента. Псевдоним jt обязателен.

FOR ORDINALITY нумерует полученные элементы с 1. Это отличается от индекса JSON-массива: items[0] — первый элемент. VARCHAR(20) PATH '$.sku' извлекает артикул, INT PATH '$.qty' — количество. В полной программе ERROR ON EMPTY и ERROR ON ERROR явно останавливают извлечение при отсутствующем обязательном поле и несовместимой форме. Это ещё не все правила бизнеса: положительность количества и допустимость исходного типа проверяются отдельно.

JOIN JSON_TABLE ... ON TRUE подключает строки документа, затем LEFT JOIN каталога сохраняет и ненайденный UNKNOWN. Обычный INNER JOIN скрывал бы такую позицию: таблица выглядела бы успешной, но заказ потерял бы одну строку. У SKU в каталоге первичный ключ, поэтому одна позиция не размножается из-за нескольких совпавших товаров. Количество умножается на цену в копейках на стороне SQL; для UNKNOWN и цена, и сумма остаются NULL.

У строкового столбца JSON_TABLE и столбца каталога могут оказаться разные правила сравнения, даже при общей кодировке utf8mb4. Тогда равенство может завершиться ошибкой смешения collations. В этом примере SKU каталога явно объявлен с utf8mb4_bin, и обе стороны условия JOIN получают тот же COLLATE. Для нашего договора артикулов регистр значим: TEA и tea — разные значения. Допустимый формат и пробелы в артикуле проверяются отдельно; collation не заменяет эту проверку. Подробное сравнение правил — в главе о текстовых сравнениях MySQL.

Две известные суммы — 70000 и 49000 копеек. Их сумма 119000 является только промежуточным итогом найденных позиций, не стоимостью полного заказа. Пока unknown_skus содержит UNKNOWN, программа ничего не импортирует. Окончательная запись позиций должна отдельно фиксировать согласованные цены и выполняться в транзакции; текущая цена каталога не подменяет историю уже оформленного заказа. Связанные таблицы и границы JOIN дополняет разбор связанных данных для экранов.

Границы и ошибки

Ошибка JSON_TABLE относится к запросу, а не автоматически только к одной плохой строке. Программа здесь работает с известным учебным массивом. Обычная связь JOIN с верхним пустым массивом даст ноль дочерних строк; сохранение родителя требует осознанного LEFT JOIN или NESTED PATH, как в следующем примере. Если позиции постоянно редактируются и ссылаются на товары, обычная таблица order_items с внешним ключом обычно яснее JSON-массива.

<?php
declare(strict_types=1);
require __DIR__.'/jd113-helper.php';
$db=jd113Connect();
$runtime=['php'=>PHP_VERSION,'client'=>$db->getAttribute(PDO::ATTR_CLIENT_VERSION),'server'=>$db->getAttribute(PDO::ATTR_SERVER_VERSION)];
$details=[];
$db->exec('CREATE TEMPORARY TABLE mysql113_json_orders (id INT PRIMARY KEY,payload JSON NOT NULL) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4');
$db->exec('CREATE TEMPORARY TABLE mysql113_json_catalog (sku VARCHAR(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin PRIMARY KEY,name VARCHAR(50) NOT NULL,price_cents INT NOT NULL) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4');
$db->exec("INSERT INTO mysql113_json_catalog VALUES ('TEA','Чай',35000),('HONEY','Мёд',49000)");
$insert=$db->prepare('INSERT INTO mysql113_json_orders VALUES (?,?)');
$insert->execute([42,'{"items":[{"sku":"TEA","qty":2},{"sku":"HONEY","qty":1},{"sku":"UNKNOWN","qty":3}]}']);$insert->closeCursor();
$data=jd113Rows($db,<<<'SQL'
SELECT jt.line_no,jt.sku,p.name,jt.qty,p.price_cents,jt.qty*p.price_cents AS line_cents
FROM mysql113_json_orders AS o
JOIN JSON_TABLE(o.payload,'$.items[*]' COLUMNS(
    line_no FOR ORDINALITY,
    sku VARCHAR(20) PATH '$.sku' ERROR ON EMPTY ERROR ON ERROR,
    qty INT PATH '$.qty' ERROR ON EMPTY ERROR ON ERROR
)) AS jt ON TRUE
LEFT JOIN mysql113_json_catalog AS p ON p.sku COLLATE utf8mb4_bin=jt.sku COLLATE utf8mb4_bin
WHERE o.id=?
ORDER BY jt.line_no
SQL,[42]);
$rows=[];$knownSubtotal=0;$unknown=[];
foreach($data as $r){$price=$r['price_cents']===null?null:(int)$r['price_cents'];$line=$r['line_cents']===null?null:(int)$r['line_cents'];$status=$r['name']===null?'Нет в каталоге: импорт запрещён':'Товар найден';$rows[]=[(int)$r['line_no'],$r['sku'],$r['name'],(int)$r['qty'],$price,$line,$status];if($line!==null){$knownSubtotal+=$line;}else{$unknown[]=$r['sku'];}}
$checks=['original_order_kept'=>array_column($rows,0)===[1,2,3]&&array_column($rows,1)===['TEA','HONEY','UNKNOWN'],'known_prices_and_totals'=>$rows[0][5]===70000&&$rows[1][5]===49000&&$knownSubtotal===119000,'unknown_is_not_dropped'=>$unknown===['UNKNOWN']&&$rows[2][2]===null&&$rows[2][5]===null,'document_unchanged'=>(int)jd113Rows($db,"SELECT JSON_LENGTH(payload,'$.items') AS n FROM mysql113_json_orders WHERE id=?",[42])[0]['n']===3];
$details=['known_subtotal_cents'=>$knownSubtotal,'unknown_skus'=>$unknown,'ready_to_import'=>$unknown===[],'positions_inserted'=>0];
$db=null;
jd113Report('mysql113-json-positions-join', ['Позиция', 'SKU', 'Товар', 'Количество', 'Цена, коп.', 'Сумма, коп.', 'Результат'], $rows, $checks, $runtime, $details);
echo 'mysql113-json-positions-join: verified result.html and result.json
';

mysql113-json-positions-join: verified result.html and result.json

Открыть фактический HTML-результат · Скачать результат JSON

Попробуйте сами. Добавьте UNKNOWN в каталог с ценой 10000 копеек. Его сумма станет 30000, список unknown_skus опустеет, а сумма всех трёх позиций станет 149000. Условие ready_to_import изменится, но программа всё ещё остаётся предпросмотром и не вставляет бизнес-позиции.

Показать ошибки вложенных позиций и выбрать строгий отказ

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

Что поступает на вход

Что получится после полного запуска

Короткая запись основного действия

SELECT jt.has_qty, jt.qty
FROM JSON_TABLE('[{"qty":2}, {}, {"qty":{"bad":1}}]', '$[*]'
    COLUMNS (
        has_qty INT EXISTS PATH '$.qty',
        qty INT PATH '$.qty' NULL ON EMPTY NULL ON ERROR
    )
) AS jt;

Этот фрагмент объясняет SQL на уже созданных данных. Символ ? — значение, которое передаётся при execute; полная программа ниже создаёт собственные таблицы и выполняет весь сценарий.

Внешний путь $.packages[*] задаёт строку посылки. Внутренний NESTED PATH '$.items[*]' отсчитывается от неё, а не снова от корня всего документа. Когда вложенных совпадений нет, остаётся строка родителя с NULL в дочерних колонках. Поэтому P2 и P3 видны. Родительский has_items показывает 1 для пустого существующего массива и 0 для отсутствующего ключа.

has_qty INT EXISTS PATH отвечает только о наличии поля. qty INT PATH пытается получить SQL-число. NULL ON EMPTY даёт NULL при отсутствии значения; NULL ON ERROR — при ошибке преобразования, например объекте вместо числа. JSON null также становится SQL NULL. Чтобы эти случаи не слились, пример дополнительно получает JSON_TYPE по конкретным номерам посылки и позиции. Динамический путь строится только из внутренних числовых ordinality: из номера 1 получается индекс массива 0.

В таблице A имеет количество 2. Для B выводится «Нет поля qty»; C имеет исходный тип OBJECT; D — JSON NULL. Это объяснение фактического состояния, а не решение «везде подставить 0». Затем два отдельных коротких запроса включают строгие ERROR ON EMPTY и ERROR ON ERROR. Отсутствующее qty отклоняется с 22035 / 3665, объект — с 2203F / 3666. Эти ожидаемые PDOException пойманы; другие ошибки не выдаются за успешный импорт.

ON EMPTY записывается до ON ERROR. Важная граница: числовые преобразования и усечение могут сопровождаться warning; некоторые предупреждения возникают независимо от ON ERROR. Например, дробное число нельзя объявить подходящим целым количеством только потому, что INT-колонка смогла его преобразовать. Поэтому правило примера проверяет исходный INTEGER, положительность и отсутствие NULL. Для внешнего документа нужны также пределы количества, длины SKU, обязательность объекта и массива, допустимые размеры всего входа.

Здесь два известных состояния items — [] и отсутствие; программа не объявляет проверенными произвольные items: null, строку или объект. EXISTS PATH не доказывает, что значение является массивом. Прежде чем переносить схему на чужой API, задайте проверку формы packages/items и политику для каждой непредусмотренной формы. Загрузку с исправлением и журналом отказов продолжает глава о восстановлении импорта.

Границы и ошибки

Предпросмотр специально не записывает бизнес-таблицы. Его режим NULL ON ERROR сохраняет проблемные строки для объяснения, а не разрешает их принять. Несколько соседних NESTED PATH имеют собственную семантику расширения и не означают произвольный декартов продукт. В этом уроке одна ветка вложения, чтобы сначала увидеть связь «посылка → её позиции». Синтаксически неверный JSON и неверный JSON path должны обрабатываться отдельно от отсутствующего qty.

<?php
declare(strict_types=1);
require __DIR__.'/jd113-helper.php';
$db=jd113Connect();
$runtime=['php'=>PHP_VERSION,'client'=>$db->getAttribute(PDO::ATTR_CLIENT_VERSION),'server'=>$db->getAttribute(PDO::ATTR_SERVER_VERSION)];
$details=[];
$db->exec('CREATE TEMPORARY TABLE mysql113_json_imports (id INT PRIMARY KEY,payload JSON NOT NULL) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4');
$document='{"packages":[{"code":"P1","items":[{"sku":"A","qty":2},{"sku":"B"},{"sku":"C","qty":{"bad":1}},{"sku":"D","qty":null}]},{"code":"P2","items":[]},{"code":"P3"}]}';
$insert=$db->prepare('INSERT INTO mysql113_json_imports VALUES (?,?)');$insert->execute([7,$document]);$insert->closeCursor();
$data=jd113Rows($db,<<<'SQL'
SELECT jt.package_code,jt.line_no,jt.sku,jt.has_items,jt.has_qty,jt.qty,
       JSON_TYPE(JSON_EXTRACT(d.payload,CONCAT('$.packages[',jt.package_no-1,'].items[',jt.line_no-1,'].qty'))) AS qty_type
FROM mysql113_json_imports AS d
JOIN JSON_TABLE(d.payload,'$.packages[*]' COLUMNS(
    package_no FOR ORDINALITY,
    package_code VARCHAR(10) PATH '$.code' ERROR ON EMPTY ERROR ON ERROR,
    has_items INT EXISTS PATH '$.items',
    NESTED PATH '$.items[*]' COLUMNS(
        line_no FOR ORDINALITY,
        sku VARCHAR(20) PATH '$.sku' NULL ON EMPTY NULL ON ERROR,
        has_qty INT EXISTS PATH '$.qty',
        qty INT PATH '$.qty' NULL ON EMPTY NULL ON ERROR
    )
)) AS jt ON TRUE
WHERE d.id=?
ORDER BY jt.package_no,jt.line_no
SQL,[7]);
$rows=[];
foreach($data as $r){
    $line=$r['line_no']===null?null:(int)$r['line_no'];$hasQty=$r['has_qty']===null?null:(int)$r['has_qty'];$qty=$r['qty']===null?null:(int)$r['qty'];
    $decision=match(true){$line===null&&(int)$r['has_items']===0=>'Нет массива items',$line===null=>'Пустой массив items',$hasQty===0=>'Нет поля qty',$r['qty_type']==='NULL'=>'Количество равно JSON null',$r['qty_type']!=='INTEGER'||$qty===null||$qty<=0=>'Ожидалось целое количество',default=>'Можно принять'};
    $rows[]=[$r['package_code'],$line,$r['sku'],(int)$r['has_items'],$hasQty,$r['qty_type'],$qty,$decision];
}
$strictErrors=[];
foreach(['missing'=>'[{}]','object'=>'[{"qty":{}}]'] as $case=>$input){
    try{jd113Rows($db,"SELECT jt.qty FROM JSON_TABLE(CAST(? AS JSON),'$[*]' COLUMNS(qty INT PATH '$.qty' ERROR ON EMPTY ERROR ON ERROR)) AS jt",[$input]);}
    catch(PDOException $error){$strictErrors[$case]=['state'=>$error->errorInfo[0]??'','code'=>(int)($error->errorInfo[1]??0)];}
}
foreach(['missing'=>'Отсутствующее qty','object'=>'Объект вместо INT'] as $case=>$label){$e=$strictErrors[$case]??null;$rows[]=['Строгий режим',null,$label,null,null,null,null,$e===null?'Неожиданно принят':'Отказ '.$e['state'].' / '.$e['code']];}
$checks=['preview_preserves_packages'=>count($data)===6&&array_column($data,'package_code')===['P1','P1','P1','P1','P2','P3'],'one_valid_position'=>$rows[0]===['P1',1,'A',1,1,'INTEGER',2,'Можно принять'],'different_invalid_states'=>$rows[1][7]==='Нет поля qty'&&$rows[2][5]==='OBJECT'&&$rows[2][6]===null&&$rows[3][5]==='NULL','empty_and_missing_arrays_visible'=>$rows[4][1]===null&&$rows[4][3]===1&&$rows[5][1]===null&&$rows[5][3]===0,'strict_missing_error'=>($strictErrors['missing']??null)===['state'=>'22035','code'=>3665],'strict_object_error'=>($strictErrors['object']??null)===['state'=>'2203F','code'=>3666]];
$details=['strict_errors'=>$strictErrors,'accepted_in_preview'=>1,'business_rows_written'=>0,'input_items_shape'=>'fixed teaching fixture, not arbitrary supplier schema'];
$db=null;
jd113Report('mysql113-json-nested-preview', ['Посылка', 'Позиция', 'SKU / проверка', 'items есть?', 'qty есть?', 'Тип qty', 'Количество', 'Решение'], $rows, $checks, $runtime, $details);
echo 'mysql113-json-nested-preview: verified result.html and result.json
';

mysql113-json-nested-preview: verified result.html and result.json

Открыть фактический HTML-результат · Скачать результат JSON

Попробуйте сами. Исправьте B на qty=1, C на qty=3, D на qty=4. Первые четыре строки P1 станут допустимыми с количествами 2,1,3,4. Пустая P2 и отсутствующий items у P3 останутся видимыми проблемами; исправление одной посылки не скрывает остальные.

Практический вопрос: Куда пропадают строки при соединении JSON_TABLE с обычной таблицей. В вопросе строки без совпадающих дочерних данных исчезали при разных соединениях. Наши сценарии явно сохраняют неизвестный артикул через LEFT JOIN и пустые либо отсутствующие вложенные позиции через NESTED PATH, не объявляя эти случаи успешным импортом. Обсуждение задаёт проблему; правила проверены по официальной документации, пример написан отдельно.

Следующий шаг: структура данных и ветки каталога

Документ удобен для дополнительных свойств и входного сообщения. Когда узлы должны ссылаться друг на друга и участвовать в самостоятельных изменениях, понадобятся таблицы и ключи. В главе об иерархиях MySQL выберем ветку категорий и путь к нужному разделу. Подготовку входа и запись нескольких связанных строк дополняют параметры запросов и транзакции PDO_MYSQL.