以JSON格式存储MySQL数据

I thought this was a n00b thing to do. And, so, I've never done it. Then I saw that FriendFeed did this and actually made their DB scale better and decreased latency. I'm curious if I should do this. And, if so, what's the right way to do it?

我认为这是一个n00b的事情。我从来没做过。然后我看到FriendFeed做到了这一点，并且让他们的数据库变得更好，减少了延迟。我很好奇我是否应该这样做。如果是的话，正确的做法是什么?

Basically, what's a good place to learn how to store everything in MySQL as a CouchDB sort of DB? Storing everything as JSON seems like it'd be easier and quicker (not to build, less latency).

基本上，学习如何将MySQL中的所有东西存储为CouchDB之类的DB是什么好地方呢?将所有东西存储为JSON似乎更容易、更快(不是构建，更少延迟)。

Also, is it easy to edit, delete, etc., things stored as JSON on the DB?

另外，在DB上以JSON形式存储的东西是否易于编辑、删除等?

16 个解决方案

#1

CouchDB and MySQL are two very different beasts. JSON is the native way to store stuff in CouchDB. In MySQL, the best you could do is store JSON data as text in a single field. This would entirely defeat the purpose of storing it in an RDBMS and would greatly complicate every database transaction.

CouchDB和MySQL是两个完全不同的东西。JSON是在CouchDB中存储内容的本机方式。在MySQL中，最好的方法是将JSON数据作为文本存储在一个字段中。这将完全破坏在RDBMS中存储它的目的，并将极大地复杂化每个数据库事务。

Don't.

不喜欢。

Having said that, FriendFeed seemed to use an extremely custom schema on top of MySQL. It really depends on what exactly you want to store, there's hardly one definite answer on how to abuse a database system so it makes sense for you. Given that the article is very old and their main reason against Mongo and Couch was immaturity, I'd re-evaluate these two if MySQL doesn't cut it for you. They should have grown a lot by now.

话虽如此，FriendFeed似乎在MySQL之上使用了一个非常自定义的模式。这实际上取决于您想要存储什么，关于如何滥用数据库系统，几乎没有一个明确的答案，因此对您来说是有意义的。考虑到这篇文章太老了，而且他们反对Mongo和Couch的主要原因是不成熟，如果MySQL不为您提供帮助，我将重新评估这两篇文章。他们现在应该已经成长了很多。

#2

Everybody commenting seems to be coming at this from the wrong angle, it is fine to store JSON code via PHP in a relational DB and it will in fact be faster to load and display complex data like this, however you will have design considerations such as searching, indexing etc.

大家评论似乎在这个来自错误的角度,是不错的JSON通过PHP代码存储在一个关系数据库,它实际上会更快的加载和显示这样的复杂数据,然而你会设计考虑如搜索、索引等。

The best way of doing this is to use hybrid data, for example if you need to search based upon datetime MySQL (performance tuned) is going to be a lot faster than PHP and for something like searching distance of venues MySQL should also be a lot faster (notice searching not accessing). Data you do not need to search on can then be stored in JSON, BLOB or any other format you really deem necessary.

这样做的最佳方式是使用混合数据,例如如果您需要搜索基于datetime MySQL(性能调优)是一个更快比PHP和类似的搜索距离场馆MySQL也应该快很多(注意搜索不访问)。不需要搜索的数据可以存储在JSON、BLOB或其他任何您认为必要的格式中。

Data you need to access is very easily stored as JSON for example a basic per-case invoice system. They do not benefit very much at all from RDBMS, and could be stored in JSON just by json_encoding($_POST['entires']) if you have the correct HTML form structure.

您需要访问的数据很容易存储为JSON，例如一个基本的每个案例的发票系统。它们完全不能从RDBMS中获益，如果有正确的HTML表单结构，可以通过json_encoding($_POST['entires'])将它们存储在JSON中。

I am glad you are happy using MongoDB and I hope that it continues to serve you well, but don't think that MySQL is always going to be off your radar, as your app increases in complexity you may well end up needing an RDBMS for some functionality and features (even if it is just for retiring archived data or business reporting)

使用MongoDB我很高兴你是快乐的,我希望继续为你服务好,但不要认为MySQL总是从你的雷达,当你的应用程序复杂性的增加最终你可能会需要一些功能和特性的RDBMS(即使它只是为了退休归档数据或业务报告)

#3

MySQL 5.7 Now supports a native JSON data type similar to MongoDB and other schemaless document data stores:

MySQL 5.7现在支持原生JSON数据类型，类似于MongoDB和其他无模式文档数据存储:

JSON support

Beginning with MySQL 5.7.8, MySQL supports a native JSON type. JSON values are not stored as strings, instead using an internal binary format that permits quick read access to document elements. JSON documents stored in JSON columns are automatically validated whenever they are inserted or updated, with an invalid document producing an error. JSON documents are normalized on creation, and can be compared using most comparison operators such as =, <, <=, >, >=, <>, !=, and <=>; for information about supported operators as well as precedence and other rules that MySQL follows when comparing JSON values, see Comparison and Ordering of JSON Values.

从MySQL 5.7.8开始，MySQL支持原生JSON类型。JSON值不是作为字符串存储的，而是使用允许快速读取文档元素的内部二进制格式。在JSON列中存储的JSON文档在插入或更新时都会自动进行验证，而无效的文档会产生错误。JSON文档在创建时是规范化的，可以使用=、<、<=、>、>=、<>、!=、<=>等大多数比较运算符进行比较;有关支持的操作符以及MySQL在比较JSON值时遵循的优先级和其他规则的信息，请参见JSON值的比较和排序。

MySQL 5.7.8 also introduces a number of functions for working with JSON values. These functions include those listed here:

MySQL 5.7.8还引入了许多处理JSON值的函数。这些职能包括下列职能:

1. Functions that create JSON values: JSON_ARRAY(), JSON_MERGE(), and JSON_OBJECT(). See Section 12.16.2, “Functions That Create JSON Values”.
2. 创建JSON值的函数:JSON_ARRAY()、JSON_MERGE()和JSON_OBJECT()。参见第12.16.2节“创建JSON值的函数”。
3. Functions that search JSON values: JSON_CONTAINS(), JSON_CONTAINS_PATH(), JSON_EXTRACT(), JSON_KEYS(), and JSON_SEARCH(). See Section 12.16.3, “Functions That Search JSON Values”.
4. 搜索JSON值的函数:JSON_CONTAINS()、JSON_CONTAINS_PATH()、JSON_EXTRACT()、JSON_KEYS()和JSON_SEARCH()。参见第12.16.3节“搜索JSON值的函数”。
5. Functions that modify JSON values: JSON_APPEND(), JSON_ARRAY_APPEND(), JSON_ARRAY_INSERT(), JSON_INSERT(), JSON_QUOTE(), JSON_REMOVE(), JSON_REPLACE(), JSON_SET(), and JSON_UNQUOTE(). See Section 12.16.4, “Functions That Modify JSON Values”.
6. 修改JSON值的函数:JSON_APPEND()、JSON_ARRAY_APPEND()、JSON_ARRAY_INSERT()、JSON_INSERT()、JSON_QUOTE()、JSON_REMOVE()、JSON_REPLACE()、JSON_SET()和JSON_UNQUOTE()。参见第12.16.4节“修改JSON值的函数”。
7. Functions that provide information about JSON values: JSON_DEPTH(), JSON_LENGTH(), JSON_TYPE(), and JSON_VALID(). See Section 12.16.5, “Functions That Return JSON Value Attributes”.
8. 提供有关JSON值的信息的函数:JSON_DEPTH()、JSON_LENGTH()、JSON_TYPE()和JSON_VALID()。参见第12.16.5节“返回JSON值属性的函数”。

In MySQL 5.7.9 and later, you can use column->path as shorthand for JSON_EXTRACT(column, path). This works as an alias for a column wherever a column identifier can occur in an SQL statement, including WHERE, ORDER BY, and GROUP BY clauses. This includes SELECT, UPDATE, DELETE, CREATE TABLE, and other SQL statements. The left hand side must be a JSON column identifier (and not an alias). The right hand side is a quoted JSON path expression which is evaluated against the JSON document returned as the column value.

在MySQL 5.7.9或更高版本中，可以使用列->路径作为JSON_EXTRACT(列、路径)的简写。当SQL语句中出现列标识符(包括WHERE、ORDER BY和GROUP BY子句)时，它就充当列的别名。这包括选择、更新、删除、创建表和其他SQL语句。左边必须是JSON列标识符(而不是别名)。右边是一个引用的JSON路径表达式，它根据作为列值返回的JSON文档进行计算。

See Section 12.16.3, “Functions That Search JSON Values”, for more information about -> and JSON_EXTRACT(). For information about JSON path support in MySQL 5.7, see Searching and Modifying JSON Values. See also Secondary Indexes and Virtual Generated Columns.

有关->和JSON_EXTRACT()的更多信息，请参阅第12.16.3节“搜索JSON值的函数”。有关MySQL 5.7中的JSON路径支持的信息，请参见搜索和修改JSON值。还请参见辅助索引和虚拟生成的列。

More info:

更多信息:

https://dev.mysql.com/doc/refman/5.7/en/json.html

https://dev.mysql.com/doc/refman/5.7/en/json.html

#4

json characters are nothing special when it comes down to storage, chars such as

json字符在存储过程中并没有什么特别之处，比如。

{,},[,],',a-z,0-9.... are really nothing special and can be stored as text.

},{[,],“a - z,0 - 9 ....真的没什么特别的，可以作为文本存储。

the first problem your going to have is this

第一个问题是

{ profile_id: 22, username: 'Robert', password: 'skhgeeht893htgn34ythg9er' }

{profile_id: 22，用户名:'Robert'，密码:'skhgeeht893htgn34ythg9er'}

that stored in a database is not that simple to update unless you had your own proceedure and developed a jsondecode for mysql

存储在数据库中的数据并不容易更新，除非您有自己的过程并为mysql开发了jsondecode

UPDATE users SET JSON(user_data,'username') = 'New User';

So as you cant do that you would Have to first SELECT the json, Decode it, change it, update it, so in theory you might as well spend more time constructing a suitable database structure!

因此，如果您不能做到这一点，那么您必须首先选择json、解码、修改它、更新它，因此在理论上，您最好花更多时间构建一个合适的数据库结构!

I do use json to store data but only Meta Data, data that dont get updated often, not related to the user specific.. example if a user adds a post, and in that post he adds images ill parse the images and create thumbs and then use the thumb urls in a json format.

我使用json存储数据，但只存储元数据，不经常更新的数据，与用户无关。例如，如果用户添加了一个post，并且在该post中添加了图像，我会解析这些图像并创建拇指，然后使用json格式的拇指url。

#5

To illustrate how difficult it is to get JSON data using a query, I will share the query I made to handle this.

为了说明使用查询获取JSON数据有多困难，我将共享我为处理它而进行的查询。

It doesn't take into account arrays or other objects, just basic datatypes. You should change the 4 instances of column to the column name storing the JSON, and change the 4 instances of myfield to the JSON field you want to access.

它不考虑数组或其他对象，只考虑基本的数据类型。您应该将列的4个实例更改为存储JSON的列名，并将myfield的4个实例更改为要访问的JSON字段。

SELECT
    SUBSTRING(
        REPLACE(REPLACE(REPLACE(column, '{', ''), '}', ','), '"', ''),
        LOCATE(
            CONCAT('myfield', ':'),
            REPLACE(REPLACE(REPLACE(column, '{', ''), '}', ','), '"', '')
        ) + CHAR_LENGTH(CONCAT('myfield', ':')),
        LOCATE(
            ',',
            SUBSTRING(
                REPLACE(REPLACE(REPLACE(column, '{', ''), '}', ','), '"', ''),
                LOCATE(
                    CONCAT('myfield', ':'),
                    REPLACE(REPLACE(REPLACE(column, '{', ''), '}', ','), '"', '')
                ) + CHAR_LENGTH(CONCAT('myfield', ':'))
            )
        ) - 1
    )
    AS myfield
FROM mytable WHERE id = '3435'

#6

It really depends on your use case. If you are storing information that has absolutely no value in reporting, and won't be queried via JOINs with other tables, it may make sense for you to store your data in a single text field, encoded as JSON.

这真的取决于你的用例。如果您正在存储的信息在报告中没有任何价值，并且不会通过与其他表的连接进行查询，那么您可以将数据存储在一个编码为JSON的文本字段中。

This could greatly simplify your data model. However, as mentioned by RobertPitt, don't expect to be able to combine this data with other data that has been normalized.

这可以极大地简化数据模型。但是，正如RobertPitt所提到的，不要期望能够将这些数据与其他已规范化的数据结合起来。

#7

I would say the only two reasons to consider this are:

我想说，考虑这一点的唯一两个理由是:

• performance just isn't good enough with a normalised approach
• 用一种规范化的方法，表现还不够好
• you cannot readily model your particularly fluid/flexible/changing data
• 你不能很容易地为你特别流畅/灵活/变化的数据建模

I wrote a bit about my own approach here:

我在这里写了一些我自己的方法:

What scalability problems have you encountered using a NoSQL data store?

您在使用NoSQL数据存储时遇到过哪些可伸缩性问题?

(see the top answer)

(见上面的回答)

Even JSON wasn't quite fast enough so we used a custom-text-format approach. Worked / continues to work well for us.

甚至JSON也不够快，所以我们采用了自定义文本格式的方法。为我们工作得很好。

Is there a reason you're not using something like MongoDB? (could be MySQL is "required"; just curious)

你不使用MongoDB有什么原因吗?(可能是“必需”MySQL;只是好奇)

#8

This is an old question, but I am still able to see this at the top of the search result of Google, so I guess it would be meaningful to add a new answer 4 years after the question is asked.

这是一个老问题，但是我仍然可以在谷歌搜索结果的顶部看到这个，所以我想在问题被提出4年后添加一个新的答案是有意义的。

First of all, there is better support in storing JSON in RDBMS. You may consider switching to PostgreSQL (although MySQL has supported JSON since v5.7.7). PostgreSQL uses very similar SQL commands as MySQL except they support more functions. One of the functions they added is that they provide JSON data type and you are now able to query the JSON stored. (Some reference on this) If you are not making up the query directly in your program, for example, using PDO in php or eloquent in Laravel, all you need to do is just to install PostgreSQL on your server and change database connection settings. You don't even need to change your code.

首先，在RDBMS中存储JSON有更好的支持。您可以考虑切换到PostgreSQL(尽管MySQL从v5.7.7开始就支持JSON)。PostgreSQL使用非常类似的SQL命令作为MySQL，但它们支持更多的函数。他们添加的功能之一是提供JSON数据类型，现在您可以查询存储的JSON。如果您没有在程序中直接创建查询(例如，在php中使用PDO或在Laravel中使用雄辩)，那么只需在服务器上安装PostgreSQL并更改数据库连接设置。你甚至不需要改变你的代码。

Most of the time, as the other answers suggested, storing data as JSON directly in RDBMS is not a good idea. There are some exception though. One situation I can think of is a field with variable number of linked entry.

大多数时候，正如其他答案所建议的那样，将数据直接存储在RDBMS中不是一个好主意。不过也有例外。我能想到的一种情况是有多个链接条目的字段。

For example, for storing tag of a blog post, normally you will need to have a table for blog post, a table of tag and a matching table. So, when the user wants to edit a post and you need to display which tag is related to that post, you will need to query 3 tables. This will damage the performance a lot if your matching table / tag table is long.

例如，要存储blog post的标记，通常需要一个用于blog post的表、一个标记表和一个匹配的表。所以，当用户想要编辑一个帖子，你需要显示哪个标签与那个帖子相关时，你需要查询3个表。如果匹配的表/标记表很长，这将严重损害性能。

By storing the tags as JSON in the blog post table, the same action only requires a single table search. The user will then be able to see the blog post to be edit quicker, but this will damage the performance if you want to make a report on what post is linked to a tag, or maybe search by tag.

通过将标签作为JSON存储在blog post表中，相同的操作只需要一个表搜索。用户将能够看到博客文章的编辑速度更快，但是如果你想要做一个关于什么post链接到标签，或者是标签搜索的报告，这将会破坏性能。

You may also try to de-normalize the database. By duplicating the data and storing the data in both ways, you can receive benefit of both method. You will just need a little bit more time to store your data and more storage space (which is cheap comparing to the cost of more computing power)

您还可以尝试去规范化数据库。通过复制数据并以两种方式存储数据，您可以从这两种方法中获益。您只需要多一点的时间来存储数据和更多的存储空间(与计算能力的成本相比，这是很便宜的)

#9

Here is a function that would save/update keys of a JSON array in a column and another function that retrieves JSON values. This functions are created assuming that the column name of storing the JSON array is json. It is using PDO.

这里有一个函数将在列中保存/更新JSON数组的键，还有一个函数将检索JSON值。如果存储JSON数组的列名是JSON，则创建此函数。它是使用PDO。

Save/Update Function

function save($uid, $key, $val){
 global $dbh; // The PDO object
 $sql = $dbh->prepare("SELECT `json` FROM users WHERE `id`=?");
 $sql->execute(array($uid));
 $data      = $sql->fetch();
 $arr       = json_decode($data['json'],true);
 $arr[$key] = $val; // Update the value
 $sql=$dbh->prepare("UPDATE `users` SET `json`=? WHERE `id`=?");
 $sql->execute(array(
   json_encode($arr), 
   $uid
 ));
}

where $uid is the user's id, $key - the JSON key to update and it's value is mentioned as $val.

其中$uid是用户的id， $key—要更新的JSON键和它的值被称为$val。

Get Value Function

function get($uid, $key){
 global $dbh;
 $sql = $dbh->prepare("SELECT `json` FROM `users` WHERE `id`=?");
 $sql->execute(array($uid));
 $data = $sql->fetch();
 $arr  = json_decode($data['json'], true);
 return $arr[$key];
}

where $key is a key of JSON array from which we need the value.

$key是一个JSON数组的键，我们需要它的值。

#10

It seems to me that everyone answering this question is kind-of missing the one critical issue, except @deceze -- use the right tool for the job. You can force a relational database to store almost any type of data and you can force Mongo to handle relational data, but at what cost? You end up introducing complexity at all levels of development and maintenance, from schema design to application code; not to mention the performance hit.

在我看来，每个回答这个问题的人都有一种缺失的感觉——除了@欺诈者之外，他们都错过了一个关键的问题——使用正确的工具来完成这项工作。您可以强制关系数据库存储几乎任何类型的数据，并强制Mongo处理关系数据，但代价是什么呢?您最终会在开发和维护的所有级别引入复杂性，从模式设计到应用程序代码;更不用说它的表演了。

In 2014 we have access to many database servers that handle specific types of data exceptionally well.

2014年，我们访问了许多数据库服务器，它们处理特定类型的数据非常出色。

• Mongo (document storage)
• 蒙戈(文档存储)
• Redis (key-value data storage)
• 复述(键值数据存储)
• MySQL/Maria/PostgreSQL/Oracle/etc (relational data)
• MySQL /玛丽亚/ PostgreSQL / Oracle / etc(关系数据)
• CouchDB (JSON)
• CouchDB(JSON)

I'm sure I missed some others, like RabbirMQ and Cassandra. My point is, use the right tool for the data you need to store.

我肯定我漏掉了一些其他的，比如RabbirMQ和Cassandra。我的观点是，对需要存储的数据使用正确的工具。

If your application requires storage and retrieval of a variety of data really, really fast, (and who doesn't) don't shy away from using multiple data sources for an application. Most popular web frameworks provide support for multiple data sources (Rails, Django, Grails, Cake, Zend, etc). This strategy limits the complexity to one specific area of the application, the ORM or the application's data source interface.

如果您的应用程序需要非常、非常快地存储和检索各种数据，(并且谁不需要)，请不要回避使用多个数据源来应用程序。大多数流行的web框架都支持多个数据源(Rails、Django、Grails、Cake、Zend等)。这种策略将复杂性限制在应用程序的一个特定领域，即ORM或应用程序的数据源接口。

#11

Early support for storing JSON in MySQL has been added to the MySQL 5.7.7 JSON labs release (linux binaries, source)! The release seems to have grown from a series of JSON-related user-defined functions made public back in 2013.

在MySQL中存储JSON的早期支持已经被添加到MySQL 5.7.7 JSON实验室版本中(linux二进制文件，源代码)!这个版本似乎是从2013年由一系列与json相关的用户定义的功能所衍生出来的。

This nascent native JSON support seems to be heading in a very positive direction, including JSON validation on INSERT, an optimized binary storage format including a lookup table in the preamble that allows the JSN_EXTRACT function to perform binary lookups rather than parsing on every access. There is also a whole raft of new functions for handling and querying specific JSON datatypes:

这种新生的原生JSON支持似乎正朝着非常积极的方向发展，包括对INSERT的JSON验证，这是一种优化的二进制存储格式，包括序言中的查找表，允许JSN_EXTRACT函数执行二进制查找，而不是对每个访问进行解析。还有大量用于处理和查询特定JSON数据类型的新函数:

CREATE TABLE users (id INT, preferences JSON);

INSERT INTO users VALUES (1, JSN_OBJECT('showSideBar', true, 'fontSize', 12));

SELECT JSN_EXTRACT(preferences, '$.showSideBar') from users;

+--------------------------------------------------+
| id   | JSN_EXTRACT(preferences, '$.showSideBar') |
+--------------------------------------------------+
| 1    | true                                      |
+--------------------------------------------------+

IMHO, the above is a great use case for this new functionality; many SQL databases already have a user table and, rather than making endless schema changes to accommodate an evolving set of user preferences, having a single JSON column a single JOIN away is perfect. Especially as it's unlikely that it would ever need to be queried for individual items.

IMHO，上面是这个新功能的一个很好的用例;许多SQL数据库已经有了一个用户表，与其为了满足不断变化的用户偏好而进行无休止的模式更改，不如让一个单独的JSON列连接起来就很完美了。特别是它不太可能需要查询单个项目。

While it's still early days, the MySQL server team are doing a great job of communicating the changes on the blog.

虽然MySQL服务器团队还处于起步阶段，但他们在博客上进行了很好的交流。

#12

JSON is a valid datatype in PostgreSQL database as well. However, MySQL database has not officially supported JSON yet. But it's baking: http://mysqlserverteam.com/json-labs-release-native-json-data-type-and-binary-format/

JSON也是PostgreSQL数据库中的有效数据类型。然而，MySQL数据库还没有正式支持JSON。但这是烘焙:http://mysqlserverteam.com/json-labs-release-native-json-data-type-and-binary-format/

I also agree that there are many valid cases that some data is better be serialized to a string in a database. The primary reason might be when it's not regularly queried, and when it's own schema might change - you don't want to change the database schema corresponding to that. The second reason is when the serialized string is directly from external sources, you may not want to parse all of them and feed in the database at any cost until you use any. So I'll be waiting for the new MySQL release to support JSON since it'll be easier for switching between different database then.

我也同意有许多有效的情况，即某些数据最好被序列化为数据库中的字符串。最主要的原因可能是当它没有被定期查询时，以及当它自己的模式可能改变时——您不希望改变相应的数据库模式。第二个原因是，当序列化的字符串直接来自外部源时，在使用任何数据源之前，您可能不希望对它们进行解析并在数据库中以任何代价提供提要。因此，我将等待新的MySQL版本来支持JSON，因为在不同的数据库之间切换会比较容易。

#13

I use json to record anything for a project, I use three tables in fact ! one for the data in json, one for the index of each metadata of the json structure (each meta is encoded by an unique id), and one for the session user, that's all. The benchmark cannot be quantified at this early state of code, but for exemple I was user views (inner join with index) to get a category (or anything, as user, ...), and it was very slow (very very slow, used view in mysql is not the good way). The search module, in this structure, can do anything I want, but, I think mongodb will be more efficient in this concept of full json data record. For my exemple, I user views to create tree of category, and breadcrumb, my god ! so many query to do ! apache itself gone ! and, in fact, for this little website, I use know a php who generate tree and breadcrumb, the extraction of the datas is done by the search module (who use only index), the data table is used only for update. If I want, I can destroy the all indexes, and regenerate it with each data, and do the reverse work to, like, destroy all the data (json) and regenerate it only with the index table. My project is young, running under php and mysql, but, sometime I thing using node js and mongodb will be more efficient for this project.

我使用json记录项目的任何内容，实际上我使用了三个表!一个用于json中的数据，一个用于json结构的每个元数据的索引(每个元数据都用唯一的id编码)，一个用于会话用户，仅此而已。在这种早期的代码状态下，无法对基准进行量化，但是例如，我是user views(内部连接到index)来获得一个类别(或任何东西，作为user，…)，而且它非常慢(非常慢，在mysql中使用的view不是好方法)。在这个结构中，搜索模块可以做任何我想做的事情，但是，我认为mongodb在这个完整的json数据记录的概念中会更高效。以我为例，我用户视图创建类别树，而面包屑，我的上帝!这么多的查询要做!apache本身不见了!实际上，对于这个小网站，我使用一个php生成树和面包屑，数据的提取由搜索模块(只使用索引)完成，数据表只用于更新。如果我想，我可以销毁所有的索引，并使用每个数据重新生成它，然后做相反的工作，比如，销毁所有数据(json)，只使用索引表重新生成它。我的项目很年轻，在php和mysql下运行，但是有时使用node js和mongodb会对这个项目更有效。

Use json if you think you can do, just for do it, because you can ! and, forget it if it was a mistake; try by make good or bad choice, but try !

如果你认为你能做到，就使用json，因为你能做到!如果这是一个错误，请忘记它;试着做出正确或错误的选择，但要尝试!

Low

低

a french user

法国的用户

#14

I believe that storing JSON in a mysql database does in fact defeat the purpose of using RDBMS as it is intended to be used. I would not use it in any data that would be manipulated at some point or reported on, since it not only adds complexity but also could easily impact performance depending on how it is used.

我认为，将JSON存储在mysql数据库中实际上违背了使用RDBMS的目的。我不会在任何数据中使用它，因为它不仅增加了复杂性，而且很容易影响性能，这取决于它的使用方式。

However, I was curious if anyone else thought of a possible reason to actually do this. I was thinking to make an exception for logging purposes. In my case, I want to log requests that have a variable amount of parameters and errors. In this situation, I want to use tables for the type of requests, and the requests themselves with a JSON string of different values that were obtained.

然而，我很好奇，是否有人想到了一个可能的理由去做这件事。我想为日志记录的目的做一个例外。在我的示例中，我希望记录具有可变数量参数和错误的请求。在这种情况下，我希望使用表来表示请求的类型，并使用获得的不同值的JSON字符串来表示请求本身。

In the above situation, the requests are logged and never manipulated or indexed within the JSON string field. HOWEVER, in a more complex environment, I would probably try to use something that has more of an intention for this type of data and store it with that system. As others have said, it really depends on what you are trying to accomplish, but following standards always helps longevity and reliability!

在上面的情况中，请求被记录，并且从未在JSON字符串字段中被操作或索引。然而，在更复杂的环境中，我可能会尝试使用对这种类型的数据更有意图的东西，并将其存储到该系统中。正如其他人所说，这真的取决于你想要完成什么，但是遵循标准总是有助于长寿和可靠性!

#15

You can use this gist: https://gist.github.com/AminaG/33d90cb99c26298c48f670b8ffac39c3

您可以使用这个要点:https://gist.github.com/AminaG/33d90cb99c26298c48f670b8ffac39c3

After installing it to the server (just need root privilege not super), you can do something like this:

在将它安装到服务器之后(只需要root权限而不是super)，您可以这样做:

select extract_json_value('{"a":["a","2"]}','(/a)')

选择extract_json_value({”“:(“a”,“2”]}”,“(/)”)

It will return a 2 .You can return anything inside JSON by using this The good part is that it is support MySQL 5.1,5.2,5.6. And you do not need to install any binary on the server.

它会返回一个2，你可以用这个返回JSON里面的任何东西，好的地方是它支持MySQL 5.1 5.2 5.6。并且不需要在服务器上安装任何二进制文件。

Based on old project common-schema, but it is still working today https://code.google.com/archive/p/common-schema/

基于旧的项目通用模式，但是它仍然可以工作到今天:https://code.google.com/archive/p/commonschema/

#16

I know this is really late but I did have a similar situation where I used a hybrid approach of maintaining RDBMS standards of normalizing tables upto a point and then storing data in JSON as text value beyond that point. So for example I store data in 4 tables following RDBMS rules of normalization. However in the 4th table to accomodate dynamic schema I store data in JSON format. Every time I want to retrieve data I retrieve the JSON data, parse it and display it in Java. This has worked for me so far and to ensure that I am still able to index the fields I transform to json data in the table to a normalized manner using an ETL. This ensures that while the user is working on the application he faces minimal lag and the fields are transformed to a RDBMS friendly format for data analysis etc. I see this approach working well and believe that given MYSQL (5.7+) also allows parsing of JSON this approach gives you the benefits of both RDBMS and NOSQL databases.

我知道这已经很晚了，但是我也有类似的情况，我使用了一种混合的方法来维护RDBMS标准，使其标准化，然后将数据以JSON的形式存储到超过那个点的文本值中。例如，我将数据存储在4个表中，遵循RDBMS规范化规则。然而，在第4个表中，我以JSON格式存储数据。每当我想检索数据时，我都会检索JSON数据，解析它，并在Java中显示它。到目前为止，这对我来说是可行的，并确保我仍然能够使用ETL将表中转换为json数据的字段索引为规范化的方式。这将确保在用户在应用程序他面临最少的滞后和字段转换为RDBMS友好格式数据分析等等。我看到这种方法工作得很好,相信给MYSQL(5.7 +)这种方法还允许解析JSON给你RDBMS和NOSQL数据库的好处。