От автора: создавая различные проекты, достаточно часто возникает задача, сохранения некоторой информации в базу данных. Но согласитесь – это довольно обыденная операция для любого разработчика. Но что если необходимо сохранить в одну из таблиц массив или объект — конечно в явном виде, данные типы данных сохранить не возможно, но если привести их к формату JSON, сохранение реализуется очень просто, так как JSON это обычная строка.
При этом если – это строка, то использовать ее для построения различных SQLзапросов не представляется возможным, особенно если необходимо выбрать значение некоторого свойства.
Но с выходом системы управления базами данных MySQL версии 5.7, все значительно изменилось, так как разработчики добавили новый тип данных для полей создаваемых таблиц, под названием MySQL JSON. Говоря тем самым, что был добавлен функционал благодаря которому можно работать с информацией хранящейся в строке формата JSON и использовать ее для построения SQLзапросов. Поэтому в данном уроке я хотел показать, как работать с JSON данными, используя СУБД MySQL.
Для начала, хотел бы отметить, что поддержка данных формата JSON введена в систему управлениями базами данных MySQL начиная с версии 5.7, а значит для хорошего понимания данного урока и тестирования результатов работы, Вам необходимо проверить версию используемой СУБД MySQL и при необходимости выполнить обновление. Если Вы используете программное обеспечение OpenServer, в качестве инструмента для веб-разработки, то выше указанная, версия баз данных, входит в комплект версии 5.2.2 пакета OpenServer.
Теперь давайте создадим новую базу данных, под названием test, которую мы будем использовать для тестирования доступных функций. После этого откроем консоль продукта OpenServer и соединимся с сервером базы данных, используя команду:
|
1 |
mysql –u root -p |
При этом после символов -u через пробел указывается имя пользователя, а после -р пароль.
Далее, Вам предложат повторить пароль для указанного пользователя. Но так как по умолчанию пользователь root, зарегистрирован без пароля, значит просто, пропускаем данный шаг, нажимая клавишу Enter.
После этого мы увидим сообщение, что соединение с сервером MySQL успешно выполнено.
Далее, создадим таблицу articles, которую мы будем использовать для добавления данных. При этом все так же используем консоль, а значит, первым делом выберем базу данных для работы, используя команду.
|
1 |
use test; |
Затем выполним SQL запрос, для создания новой таблицы.
|
1 2 3 4 5 6 |
CREATE TABLE `articles` ( `id` mediumint(8) unsigned NOT NULL AUTO_INCREMENT, `title` varchar(255) NOT NULL, `keys` json DEFAULT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB |
Обратите внимание, на создаваемое поле ‘keys’, тип данный которого определен как json. По сути, в данном поле будет храниться обычная строка JSON данных, но используя специальные функции, Вы получите возможность использовать данное поле для формирования различных условий получения данных.
Итак, таблица создана, а значит, давайте добавим несколько записей. При этом консоль нам уже не потребуется, так как дальнейшее кодирование мы будем вести, с помощью языка PHP. Для этого выполним соединение с ранее созданной базой данных.
|
1 2 3 4 5 6 7 8 |
<?php $mysqli = new mysqli('localhost','root','','test'); if ($mysqli->connect_error) { die('Ошибка подключения (' . $mysqli->connect_errno . ') ' . $mysqli->connect_error); } |
Далее создадим массив, который после кодирования в строку JSON, сохраним в базу данных в поле ‘keys’ таблицы ‘articles’.
|
1 2 3 4 5 6 7 8 |
$array = [ 'one', 'two', 'three', 'four', 'five' ]; |
Затем непосредственно записываем информацию в базу данных.
|
1 2 3 4 5 6 |
$data = json_encode($array); $sql = "INSERT INTO `articles` (`title`,`keys`) VALUES ( 'Article two', '".$data."' )"; |
Далее, аналогичным образом запишем объект в поле ‘keys’.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 |
class Test { public $one = 1; public $two = 2; public $three = 3; public $four = 4; public $five = 5; } $obj = new Test; $data = json_encode($obj); $sql = "INSERT INTO `articles` (`title`,`keys`) VALUES ( 'Article two', '".$data."' )"; |
И последняя запись – более сложный много-мерный массив.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 |
class Test { public $one = 1; public $two = 2; public $three = 3; public $four = 4; public $five = array('value1','value2'); } $obj = new Test; $array = [ 'one', 'key'=>'value', $obj, ['six','seven','eight'], 'five' ]; $data = json_encode($array); $sql = "INSERT INTO `articles` (`title`,`keys`) VALUES ( 'Article two', '".$data."' )"; |
Конечно, сохранение информации в базу данных в поле формата JSON осуществляется после декодирования массива или объекта в строку. То есть, по сути, аналогичным образом мы можем сохранить данную строку в поле произвольного типа. Но только поле формата JSON предоставляет возможность использовать данные хранящиеся в строке JSON для формирования разнообразных условий в SQL запросах.
Теперь давайте посмотрим, как мы можем использовать данные формата JSON. Для начала осуществим выборку записей из таблицы “articles”, в которой в поле “keys” располагается простой индексный массив формата JSON в одной из ячеек которого сохранено значение “one”:
|
1 2 3 4 5 6 7 8 |
$sql = "SELECT * FROM `articles` WHERE JSON_CONTAINS(`keys`, '[\"one\"]')"; $result = $mysqli->query($sql); foreach ($result->fetch_all(MYSQLI_ASSOC) as $row) { echo $row['title'] .' | '.$row['keys'].'<br />'; } |
При этом на экране браузера мы увидим следующий результат.
Функция JSON_CONTAINS() – возвращает истину если в поле, имя которого передается в качестве первого параметра, содержится значение указанное в качестве второго аргумента. Теперь, используя вышеуказанную функцию, выберем записи, у которых в поле “keys” располагается объект формата JSON, с определенным свойством.
|
1 2 3 4 5 6 7 8 |
$sql = "SELECT * FROM `articles` WHERE JSON_CONTAINS(`keys`, '{\"one\":1}')"; $result = $mysqli->query($sql); foreach ($result->fetch_all(MYSQLI_ASSOC) as $row) { echo $row['title'] .' | '.$row['keys'].'<br />'; } |
Далее рассмотрим функцию JSON_EXTRACT(), которая возвращает конкретное значение строки формата JSON.
|
1 2 3 4 5 6 7 |
$sql = "SELECT JSON_EXTRACT((SELECT `keys` FROM `articles` WHERE id='1'),'$[0]') as `title`"; $result = $mysqli->query($sql); foreach ($result->fetch_all(MYSQLI_ASSOC) as $row) { echo $row['title']; } |
При этом функция JSON_EXTRACT(), в качестве первого аргумента принимает строку JSON, в которой будет осуществляться поиск элемента, указанного в первом параметре функции. Обратите внимание, что первый аргумент формируется с помощью вложенного запроса типа SELECT. Во втором аргументе Вы указываете в виде шаблона, какие данные необходимо выбрать из строки первого аргумента. Соответственно в шаблоне Вы можете использовать следующие элементы:
$[] – метка обозначающая индексный массив;
$[x] – представляет ячейку массива с индексом х;
$[*] – представляет произвольную ячейку массива.
$. – метка, представляющая объект.
$.* — представляет собой произвольное свойство объекта.
В следующем примере давайте получим значение под индексом 1, свойства five, которое принадлежит объекту, располагающегося в произвольной ячейке массива формата JSON.
|
1 2 3 4 5 6 7 |
$sql = "SELECT JSON_EXTRACT((SELECT `keys` FROM `articles` WHERE `id` = 4),'$.*.five[1]') as title"; $result = $mysqli->query($sql); foreach ($result->fetch_all(MYSQLI_ASSOC) as $row) { echo $row['title']; } |
Так же данные хранящиеся в строке формата JSON, Вы можете использовать для формирования SQL запросов, без использования функций.
|
1 2 3 4 5 6 7 |
$sql = "SELECT `title`,`keys`->'$.*.five' AS `key` FROM `articles`"; $result = $mysqli->query($sql); foreach ($result->fetch_all(MYSQLI_ASSOC) as $row) { echo $row['title']. ' | '. $row['key']. '<br />'; } |
Обратите внимание, что обращение к значениям строки JSON, осуществляется с помощью знака ->. То есть указываем имя поля, в котором располагается строка JSON, затем специальный оператор -> и наконец, шаблон для поиска необходимого значения.
Помимо этого, Вы можете использовать значения поля формата JSON, для формирования условия WHERE при создании SQL запроса.
|
1 2 3 4 5 6 7 |
$sql = "SELECT `title`,`keys` AS `key` FROM `articles` WHERE `keys`->'$.*.five[1]' IS NOT NULL"; $result = $mysqli->query($sql); foreach ($result->fetch_all(MYSQLI_ASSOC) as $row) { echo $row['title']. ' | '. $row['key']. '<br />'; } |
То есть по аналогии с предыдущим примером – для поиска необходимого значения JSON, используется шаблон. На этом данный урок завершен. Всего Вам доброго и удачного кодирования!!!
