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

Question

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

mysqljsondatabase

155

我认为这是新手想做的事情。所以，我从未尝试过。然后我看到FriendFeed这样做并成功地使他们的数据库扩展得更好并减少了延迟。我很好奇是否应该这样做。如果是这样，应该怎么做才算正确呢？

基本上，有什么好的方法可以学习如何将所有内容存储在MySQL中，就像在CouchDB中一样？将所有内容存储为JSON似乎会更容易和更快（不需要构建，延迟更小）。

此外，以JSON格式存储在数据库中的内容是否容易进行编辑、删除等操作？

- Oscar Godson

供参考，我认为这是FriendFeed关于在MySQL中使用JSON的讨论：http://backchannel.org/blog/friendfeed-schemaless-mysql - dimo414

13

MySQL 5.7现在支持原生JSON数据存储。 - eecue

16个回答

96

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

JSON支持

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

MySQL 5.7.8还引入了许多用于处理JSON值的函数。这些函数包括以下内容：

创建JSON值的函数：JSON_ARRAY（），JSON_MERGE（）和JSON_OBJECT（）。请参阅第12.16.2节“创建JSON值的函数”。

搜索JSON值的函数：JSON_CONTAINS（），JSON_CONTAINS_PATH（），JSON_EXTRACT（），JSON_KEYS（）和JSON_SEARCH（）。请参阅第12.16.3节“搜索JSON值的函数”。

修改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值的函数”。

提供有关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值”。还请参见二级索引和虚拟生成列。
更多信息请见：https://dev.mysql.com/doc/refman/5.7/en/json.html

- eecue

66

CouchDB和MySQL是两种非常不同的数据库。JSON是在CouchDB中存储数据的本地方式。在MySQL中，最好的方法是将JSON数据作为文本存储在单个字段中。这将完全违背将其存储在RDBMS中的目的，并且会极大地复杂化每个数据库事务。

不要这样做。

话虽如此，FriendFeed似乎在MySQL之上使用了一个极其定制的模式。这取决于您想要存储什么，几乎没有一个确定的答案可以滥用数据库系统，以使其对您有意义。鉴于该文章非常古老，他们反对Mongo和Couch的主要原因是不成熟，如果MySQL不能满足您的需求，则应重新评估这两个选项。它们现在应该已经发展了很多。

- deceze

3

是的，我正在看Mongo，而且php有一个扩展程序可以用于它，实际的DB事务语法似乎比MySQL更容易，并且与couchDB相比，总体上使用起来更容易。谢谢，我想我会选择MongoDB :) - Oscar Godson

90

在关系型数据库中存储JSON数据的确有其合理的应用场景。如果你只需要存储和检索JSON数据的不透明块，而不需要对这些数据进行查询，在某些情况下这种需求是非常常见的，那么你完全可以这样做。 - markus

10

@markus，我实际上在我的一个网站上这样做，具体来说是针对一个复杂表单中那些不会被直接用于MySQL查询的字段，但在查看表单时（无论是从表视图还是直接通过链接访问）会使用它们。这可能不是最理想的方法，但肯定可以更快地实现，并消除了需要大量表或表列的需求。 - Nick Bedford

1

如果您想为应用程序同时拥有关系型数据库和文档类型存储，则这是一个不错的方法，因此您不需要管理多个数据库。 - rjarmstrong

9

这是一个相当短视的建议，可能是由一个在 stack exchange 上花费了太多时间的人提出的？当我有一个包含100个字段的记录需要存储，并且我只需要在3或4个字段上进行搜索时，创建一个具有100个字段的表是没有意义的。你可以使用 JSON 在一个字段中存储带有整个地址簿的客户记录，并添加客户 ID、姓氏和公司作为其他用于搜索记录的字段。这是一个巨大的时间节省。 - Danial

显示剩余8条评论

27

就存储而言，JSON字符并没有什么特殊之处，例如 {,},[,],',a-z,0-9 等字符可以被当作文本进行存储。

但是遇到下面这个问题：

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

将这个存储在数据库中时，更新操作并不简单，除非您自己编写过程并为MySQL开发了jsondecode工具。

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

因此，由于你不能这样做，你必须首先选择json，解码它，更改它，然后更新它，因此理论上你可能要花更多的时间构建一个合适的数据库结构！

我确实使用json存储数据，但只用于元数据，即不经常更新的数据，与用户特定内容无关...例如，如果用户添加了一篇文章，并在其中添加了图像，则我将解析图像并创建缩略图，然后使用缩略图的URL以json格式。

- RobertPitt

当我不更新JSON字符串时，将其存储在数据库中是否足够好？我只想使用LIKE对JSON数据执行普通搜索。我看到甚至WordPress也将插件元数据存储为JSON字符串在数据库中。 - shasi kanth

@shasikanth，如果你正在搜索JSON数据中的值，那么我建议你寻找更好的方法。 - Kirby

15

为了说明使用查询获取JSON数据有多么困难，我将分享我制作的处理此问题的查询。它不考虑数组或其他对象，只是基本数据类型。您应该将4个 column 实例更改为存储JSON的列名称，并将4个 myfield 实例更改为要访问的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'

- Jorjon

6

不过你不会在服务器端查询这个。这将是为了存储一个 blob 并在客户端获取它。然后，你只需要使用 JavaScript 来查询它。不过这是很久以前的事情了 :) 我现在已经转向 MongoDB 用于此类操作了 :) 不过还是要点赞这个非常流畅的查询。 - Oscar Godson

我认为问题在于这个人是否会定期访问JSON数据。例如，我将非必要的标题移入数组中，解析为JSON，然后存储。当我检索JSON（用于罕见的额外标题AJAX请求）时，我只需从MySQL中提取，将JSON读入数组并回显标题即可。对于任何更多的数据密集型操作，它可能不应该被存储为JSON。 - John

11

这是一个旧问题，但我仍然能够在谷歌搜索结果的顶部看到它，所以我猜在问题提出四年后添加一个新答案会很有意义。

首先，关系数据库管理系统中存储JSON的支持更好。您可以考虑切换到PostgreSQL（尽管MySQL自v5.7.7以来已支持JSON）。PostgreSQL使用非常类似于MySQL的SQL命令，除了他们支持更多的功能。他们添加的其中一个功能是提供JSON数据类型，您现在可以查询存储的JSON。（一些参考资料）如果您没有直接在程序中组合查询，例如在php中使用PDO或在Laravel中使用Eloquent，则只需要在服务器上安装PostgreSQL并更改数据库连接设置即可。您甚至不需要更改代码。

大多数时候，正如其他答案建议的那样，在关系数据库管理系统中直接存储数据作为JSON不是一个好主意。但也有一些例外情况。我可以想到的一个情况是具有可变数量链接条目的字段。

例如，对于存储博客文章标签，通常需要一个博客文章表、一个标签表和一个匹配表。因此，当用户要编辑帖子并且需要显示与该帖子相关的标签时，您将需要查询3个表。如果匹配表/标签表很长，这将严重影响性能。

通过将标签作为JSON存储在博客文章表中，相同的操作只需要进行单个表搜索。然后用户将能够更快地查看要编辑的博客文章，但是如果您想制作一个链接到标签的报告，或者按标签搜索，则会损害性能。

您还可以尝试去规范化数据库。通过复制数据并以两种方式存储数据，您可以获得两种方法的好处。您只需要花费更多的时间存储数据和更多的存储空间（与更多的计算能力成本相比，存储空间是便宜的）

- cytsunny

10

这取决于你的使用情况。如果你存储的信息在报告中没有任何价值，也不会通过与其他表的JOIN查询进行查询，那么将数据作为JSON编码存储在单个文本字段中可能是有意义的。

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

- Phil LaNasa

3

我的想法完全一样。如果数据从未被连接/搜索，甚至很少更新，为什么不在TEXT字段中使用JSON？一个很好的例子是食品项目表，每个食品项目都需要存储营养信息。例如：份量大小、蛋白质、碳水化合物、总脂肪、饱和脂肪等等。但不仅如此，您还需要存储值（0.2）和其测量单位（g、oz、fl oz、ml）。考虑到这些数据(根据您所做的事情而定)，不需要进行搜索，我认为用1个TEXT字段代替16个int / varchar / enum列是一个不错的选择。 - Brad Moore

没错！！！当你需要存储变量和/或未知数据结构时，而且根本不打算使用 SQL 进行过滤时，这是非常有用的。数据只是按原样存储，其他人（应用程序代码）可能知道结构以及该如何处理它。 - Delmo

8

我认为考虑这种方法的唯一两个原因是：

使用规范化方法性能不够好
无法轻松地对流动/灵活/变化的数据进行建模

我在这里写了一些关于我的方法的内容：

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

（请参见顶部答案）

即使是 JSON 速度也不够快，所以我们采用了一种自定义文本格式的方法。对我们来说效果很好 / 继续很好地工作。

有没有什么原因你不使用像 MongoDB 这样的东西？（可能需要使用 MySQL；只是好奇）

- Brian

6

这里是一个保存/更新 JSON 数组键的函数，以及另一个检索 JSON 值的函数。这些函数是基于假定存储 JSON 数组的列名为 json 的情况创建的。它使用了 PDO。

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
 ));
}

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

获取值函数

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];
}

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

- Subin

1

这在冲突情况下会失败，如果你刚读取的 JSON 被另一个进程更新，然后你在当前线程中保存 JSON 覆盖它，怎么办？你可能需要像 SELECT FOR UPDATE 或者在 JSON 数据中进行版本控制等锁定机制。 - DhruvPathak

@DhruvPathak，您能否使用SELECT FOR UPDATE更新答案，以使其更好？我不知道如何使用它。 - Subin

我应该在MySQL表中将字段类型声明为JSON，还是只需将其保留为文本，并通过json_decode和json_encode存储和检索数据。 - Salem

6

我认为回答这个问题的每个人都有点忽略了一个至关重要的问题，除了@deceze—— 使用正确的工具完成工作。你可以强制关系数据库存储几乎任何类型的数据，也可以强制Mongo处理关系数据，但代价是什么？您最终会在开发和维护的所有级别引入复杂性，从模式设计到应用程序代码；更不用说性能损失了。
2014年，我们可以访问许多处理特定类型数据非常好的数据库服务器。

Mongo（文档存储）
Redis（键值数据存储）
MySQL / Maria / PostgreSQL / Oracle等（关系数据）
CouchDB（JSON）我相信我错过了一些其他的，比如RabbirMQ和Cassandra。我的观点是，使用适合需要存储的数据的正确工具。如果您的应用程序需要非常快速地存储和检索各种数据（谁不需要），请勇敢地使用多个数据源来进行应用程序。大多数流行的Web框架都支持多个数据源（Rails，Django，Grails，Cake，Zend等）。此策略将复杂性限制在应用程序的一个特定区域内，即ORM或应用程序的数据源接口。

- CheddarMonkey

2

在你看来，RabbitMQ是一个数据库服务器还是其他什么类型的服务器？我认为它是一种面向消息的中间件，具有很好的持久化功能，可以确保不会丢失任何消息，但并不适合用于保存数据。这只是我的个人看法。 - Osiriz

@Osiriz：你说得对。我可能不应该在这个讨论中包含它。 - CheddarMonkey

网页内容由stack overflow 提供, 点击上面的

可以查看英文原文，
原文链接

- Lewis Richard Phillip Cowles · Accepted Answer

评论者们似乎从错误的角度来看待这个问题，使用 PHP 将 JSON 代码存储在关系型数据库中是可以的，而且实际上加载和显示复杂数据会更快。但是您需要考虑搜索、索引等设计问题。

最好的方法是使用混合数据，例如如果您需要基于日期时间进行搜索，则经过性能调整的 MySQL 系统将比 PHP 更快，并且对于搜索场馆距离这样的内容，MySQL 也应该会更快（注意搜索而不是访问）。您不需要搜索的数据可以存储为 JSON、BLOB 或任何其他您认为必要的格式。

需要访问的数据非常容易以 JSON 格式存储，例如一个基本的按案例发票系统。它们并不会受益于 RDBMS，通过正确的 HTML 表单结构，可以仅通过 json_encoding（$ _POST ['entires']）将其存储为 JSON。

我很高兴您喜欢使用 MongoDB，并希望它可以继续为您服务，但请不要认为 MySQL 总是会被您遗忘，随着您的应用程序变得越来越复杂，您可能需要某些功能和特性的 RDBMS（即使仅用于退休归档数据或业务报告）。