将数据以 JSON 格式存储在 MySQL 中

Storing Data in MySQL as JSON

提问by Oscar Godson

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?

另外，在数据库上存储为 JSON 的内容是否易于编辑、删除等？

采纳答案by deceze

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 schemaon 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 不为你剪掉它，我会重新评估这两个。他们现在应该成长了很多。

回答by Lewis Richard Phillip Cowles

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.

每个人的评论似乎都是从错误的角度来的，通过 PHP 将 JSON 代码存储在关系数据库中是可以的，实际上加载和显示这样的复杂数据会更快，但是您将有设计考虑，例如搜索、索引等

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.

这样做的最佳方法是使用混合数据，例如，如果您需要基于日期时间进行搜索，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某些功能和特性（即使只是用于停用存档数据或业务报告）

回答by eecue

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

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

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 also introduces a number of functions for working with JSON values. These functions include those listed here:

1. Functions that create JSON values: JSON_ARRAY(), JSON_MERGE(), and JSON_OBJECT(). See Section 12.16.2, “Functions That Create JSON Values”.
2. 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”.
3. 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”.
4. 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”.

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.

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 支持

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

MySQL 5.7.8 还引入了许多用于处理 JSON 值的函数。这些功能包括此处列出的功能：

1. 创建 JSON 值的函数：JSON_ARRAY()、JSON_MERGE() 和 JSON_OBJECT()。请参阅第 12.16.2 节，“创建 JSON 值的函数”。
2. 搜索 JSON 值的函数：JSON_CONTAINS()、JSON_CONTAINS_PATH()、JSON_EXTRACT()、JSON_KEYS() 和 JSON_SEARCH()。请参阅第 12.16.3 节，“搜索 JSON 值的函数”。
3. 修改 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 值的函数”。
4. 提供有关 JSON 值信息的函数：JSON_DEPTH()、JSON_LENGTH()、JSON_TYPE() 和 JSON_VALID()。请参阅第 12.16.5 节，“返回 JSON 值属性的函数”。

在 MySQL 5.7.9 及更高版本中，您可以使用 column->path 作为 JSON_EXTRACT(column, path) 的简写。在 SQL 语句（包括 WHERE、ORDER BY 和 GROUP BY 子句）中可以出现列标识符的任何位置，这都用作列的别名。这包括 SELECT、UPDATE、DELETE、CREATE TABLE 和其他 SQL 语句。左侧必须是 JSON 列标识符（而不是别名）。右侧是引用的 JSON 路径表达式，它根据作为列值返回的 JSON 文档进行评估。

有关 -> 和 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

回答by RobertPitt

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 来存储数据，但只有元数据，不经常更新的数据，与用户特定的无关。然后使用 json 格式的拇指网址。

回答by Jorjon

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 columnto the column name storing the JSON, and change the 4 instances of myfieldto the JSON field you want to access.

它不考虑数组或其他对象，只考虑基本数据类型。您应该将column的 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'

回答by Phil LaNasa

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.

这实际上取决于您的用例。如果您存储的信息在报告中绝对没有价值，并且不会通过与其他表的 JOIN 进行查询，则将数据存储在单个文本字段中（编码为 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 所提到的，不要期望能够将这些数据与其他已规范化的数据结合起来。

回答by cytsunny

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 使用与 MySQL 非常相似的 SQL 命令，只是它们支持更多的功能。他们添加的功能之一是提供 JSON 数据类型，您现在可以查询存储的 JSON。（对此有一些参考）如果您不是直接在程序中编写查询，例如，在 php 中使用 PDO 或在 Laravel 中使用 eloquent，您需要做的只是在您的服务器上安装 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 中的 JSON 并不是一个好主意。不过也有一些例外。我能想到的一种情况是具有可变数量链接条目的字段。

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.

例如，要存储博客文章的标签，通常您需要有一个博客文章表、一个标签表和一个匹配表。因此，当用户想要编辑帖子并且您需要显示与该帖子相关的标签时，您将需要查询 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 存储在博客文章表中，相同的操作只需要单表搜索。然后，用户将能够更快地看到要编辑的博客文章，但是如果您想报告链接到标签的帖子或按标签搜索，这会损害性能。

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)

您也可以尝试对数据库进行反规范化。通过以两种方式复制数据和存储数据，您可以从这两种方法中受益。您只需要多一点时间来存储数据和更多存储空间（与更多计算能力的成本相比，这是便宜的）

回答by Brian

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 是“必需的”；只是好奇）

回答by CheddarMonkey

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.

在我看来，回答这个问题的每个人都好像错过了一个关键问题，除了@deceze——为工作使用正确的工具。您可以强制关系数据库存储几乎任何类型的数据，也可以强制 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)
• CouchDB (JSON)

• Mongo（文档存储）
• Redis（键值数据存储）
• MySQL/Maria/PostgreSQL/Oracle/etc（关系数据）
• 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 或应用程序的数据源接口。

回答by Subin

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 $uidis 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 $keyis a key of JSONarray from which we need the value.

其中$key是我们需要从中获取值的JSON数组的键。