Ввод строки JSON в качестве данных поля в MySQL

Ответ 1

Правильное экранирование, и вы в порядке, но я должен добавить, что 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

Ответ 2

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

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

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

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

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

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

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

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

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

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

Ответ 3

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

• Преобразуйте данные для ваших псевдо-столбцов в массив PHP и сериализуйте их в строку (см. serialize) и сохраните ее в MySQL CLOB.
• То же, что и выше, но используйте серию http://pecl.php.net/package/igbinary для сериализации и десериализации. Сохраните его в поле MySQL BLOB.

Ответ 4

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

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

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

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

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