Размещение строки JSON в качестве данных поля в MySQL

У меня есть идея, в которой я превращаю многомерный/немерный массив в строку JSON, используя PHP json_encode, и сохраняю данные в своей базе данных SQL.

Например, у меня есть таблица под названием "пользователи". Таблица "Пользователи" содержит 3 поля: идентификатор, имя, данные

Используя php, я хотел бы получить данные пользователя Джона: SELECT data FROM users WHERE name='john'

Теперь значение/текст для поля "данные" будет выглядеть следующим образом: {"gender":"male","birthday":"8-Jan-1991","country":"UK","city":"London"}

Я расшифрую поле "данные" с помощью PHP json_decode, а затем я преобразую стандартный класс объект в массив с помощью одной из моих самодельных функций PHP. И тогда я смогу показывать информацию Джона везде, где захочу, вот так: $user['data']['country'].

Это избавляет меня от необходимости создавать дополнительные поля в базе данных для страны, города, дня рождения и т.д. Но безопасно ли это? Есть ли какие-либо недостатки или проблемы с использованием этого метода для хранения данных в MySQL.

Author: Paranoid, 2013-11-18
Source

4 answers

Правильное экранирование, и вы в порядке, но я должен добавить, что именно здесь XML является лучшим форматом, чем json, поскольку он также позволит вам также использовать данные внутри xml в ваших запросах

<?xml version="1.0" encoding="UTF-8" ?>
<user>
   <gender>male</gender>
   <birthday>8-Jan-1991</birthday>
   <country>UK</country>
   <city>London</city>
</user>

Выберите 

SELECT ExtractValue(data, '//gender') AS gender FROM users WHERE name='john' AND EXTRACTVALUE(data, '//country') != 'UK';

Http://dev.mysql.com/doc/refman/5.1/en/xml-functions.html#function_extractvalue

 11
Author: Eric Herlitz, 2013-11-18 07:16:42

Но безопасно ли это?

Если вы правильно экранируете ввод, используя соответствующую библиотеку для доступа к базе данных (или, по крайней мере, используете mysql_real_escape_string), то да, это безопасно. Или, по крайней мере, не более рискованно, чем хранить что-либо еще, с точки зрения взлома базы данных.

Есть ли какие-либо недостатки или проблемы с использованием этого метода для хранения данных в MySQL

Да, вот несколько:

  • Это невозможно, или, по крайней мере, гораздо сложнее запросить что-либо в столбце "данные". Скажите, что вам нужны все пользователи, которые живут в Лондоне. Вам нужно будет извлечь все столбцы "данные" во всей базе данных и выполнить поиск на PHP.

  • Также невозможно выполнить сортировку по чему-либо в столбце "данные" при запросе. Это должно быть сделано на PHP.

  • Вы должны позаботиться о том, чтобы сохраненные данные хранились в правильном формате. Вы должны сделать это в любом случае, но это действительно удаляет дополнительный уровень защиты от хранения "плохих" данных.

Похоже, что вы по сути превратили MySQL в базу данных NoSQL. Хотя мой опыт в них ограничен, они в некоторой степени способны индексировать+сортировать данные в сохраненных документах/данных JSON. Как реляционная база данных, MySQL не может: он может только сортировать + индексировать определенные столбцы. Вы получаете худшее из MySQL, сложность масштабирования, без использования каких-либо его преимуществ, а именно возможности запускать сложные запросы.

При этом, если вы уверены, что вам никогда не понадобится выполнять такие запросы, возможно, позже будет проще перейти на NoSQL, если вы будете хранить данные в формате JSON.

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

 5
Author: Michal Charemza, 2013-11-18 07:08:15

Попробуйте добавить новые столбцы. Декодирование JSON очень дорого. Но если ваше PHP-приложение не может позволить себе простои или вы по какой-то причине не можете добавить больше столбцов, вы можете сделать следующее:

  • Преобразуйте данные для ваших псевдоколонок в массив PHP и сериализуйте их в строку (см. сериализуйте) и сохраните их в MySQL CLOB.
  • То же, что и выше, но используйте пакет http://pecl.php.net/package/igbinary для сериализации и десериализации. Храните его в поле MySQL BLOB.
 3
Author: Manu M., 2016-05-09 09:03:58

На вашем месте я бы просто добавил новые столбцы для набора данных.

Использование JSON внутри поля MySQL не так уж плохо. Это избавило меня от многих огорчений. Но это приводит к значительным накладным расходам и ограничивает возможности, которые вы можете использовать в ядре базы данных. Постоянное манипулирование схемой SQL - не лучшее, что можно сделать, но и декодирование объектов JSON не является обязательным.

Если схема данных достаточно статична, как в вашем примере, где вы храните данные пользователя пол, день рождения и т. Д. Лучше всего использовать столбцы. Затем вы можете быстро и легко манипулировать данными непосредственно с помощью SQL... сортировать, фильтровать, создавать индексы для более быстрого поиска и т.д. Поскольку схема данных довольно статична, вы на самом деле ничего не получаете от JSON, за исключением, может быть, нескольких минут вашего времени на создание столбцов. В итоге вы теряете гораздо больше времени на машинные циклы в течение всего срока службы приложения.

Там, где я использую JSON в полях MySQL, схема данных очень изменчива. Как инженер-испытатель, это в значительной степени норма. Например, в одном из моих текущих проектов список целевых показателей (которые хранятся в MySQL) меняется очень регулярно, в зависимости от того, какие проблемы решаются или какие характеристики производительности корректируются. Инженеры-разработчики регулярно запрашивают новые показатели, и они, конечно, ожидают, что все это будет аккуратно отображено и изменения будут внесены быстро. Поэтому вместо того, чтобы ежедневно работать со схемой SQL, Я сохраняю статическую схему (тип теста, дату, версию продукта и т. Д.) В виде столбцов, Но постоянно изменяющиеся данные о результатах теста в виде объекта JSON. Это означает, что я все еще могу запрашивать данные с помощью операторов SQL на основе типа теста, версии, даты и т.д., Но мне никогда не придется касаться схемы таблицы при интеграции новых показателей. Для отображения фактических тестовых данных я просто повторяю результаты и декодирую объекты JSON в массивы и иду оттуда. По мере расширения этого проекта я в конечном итоге внедрю memcached в кэшируйте все.

Это также имеет побочный эффект объединения 100+ тестовых показателей в один текстовый большой двоичный объект, весь из которых я сжимаю zlib, делая его примерно на 10% от исходного размера. Это приводит к довольно значительной экономии на хранении данных, поскольку мы уже имеем 7 цифр строк.

 2
Author: nrvate, 2014-03-24 19:58:34