以 JSON 格式在 MySQL 中存储数据

小开

我认为唯一需要考虑的两个理由是:

采用标准化方法的表现还不够好
你不能很容易地建模你特别流动/灵活/变化的数据

我在这里写了一点关于我自己的方法:

使用 NoSQL 数据存储时遇到了哪些可伸缩性问题？

(见顶部答案)

即使是 JSON 也不够快，所以我们使用了自定义文本格式的方法。

您没有使用 MongoDB 之类的东西是否有什么原因? (可能是 MySQL 是“必需的”; 只是好奇)

小开

最佳答案

CouchDB 和 MySQL 是两种截然不同的动物。JSON 是在 CouchDB 中存储内容的本地方式。在 MySQL 中，您最多只能将 JSON 数据作为文本存储在单个字段中。这将完全破坏将其存储在 RDBMS 中的目的，并将使每个数据库事务变得非常复杂。

不要。

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

小开

Json 字符在存储方面没有什么特别之处，比如

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

第一个问题是这个

{ Profile _ id: 22, 用户名: ‘ Robert’, 密码: ‘ skhgeeht893htgn34ythg9er’ }

除非您拥有自己的过程并为 mysql 开发了 jsondecode，否则更新数据库中存储的 jsondecode 并不那么简单

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

因此，如果你不能做到这一点，你将不得不首先选择 json，解码它，更改它，更新它，所以在理论上，你不妨花更多的时间构建一个合适的数据库结构！

我确实使用 json 来存储数据，但只存储元数据，不经常更新的数据，与用户无关。.例如，如果一个用户添加了一个帖子，在这个帖子中他添加了图片，将解析这些图片并创建拇指，然后使用 json 格式的拇指 URL。

小开

为了说明使用查询获取 JSON 数据有多么困难，我将与大家分享为处理这个问题而进行的查询。

它不考虑数组或其他对象，只考虑基本数据类型。您应该将4个专栏实例更改为存储 JSON 的列名，并将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'

小开

我使用 json 为一个项目记录任何东西，实际上我使用三个表！一个用于 json 中的数据，一个用于 json 结构的每个元数据的索引(每个元数据都由一个惟一的 id 编码) ，还有一个用于会话用户，仅此而已。基准测试不能量化在这个早期的代码状态，但是举例来说，我是用户视图(内部加入索引)得到一个类别(或任何东西，作为用户，...) ，它是非常缓慢的(非常非常缓慢，在 mysql 中使用视图是不好的方式)。在这个结构中，搜索模块可以做任何我想做的事情，但是，我认为 mongodb 在完整 json 数据记录的概念中会更有效率。例如，我用户视图创建类别树和面包屑，我的上帝！这么多的疑问要做！阿帕奇本身消失了！而且，事实上，对于这个小网站，我使用知道的一个 php 谁生成的树和面包屑，提取的数据是由搜索模块(谁只使用索引) ，数据表只用于更新。如果需要的话，我可以破坏所有索引，并用每个数据重新生成它，然后做相反的工作，例如，破坏所有数据(json)并只用索引表重新生成它。我的项目还很年轻，在 php 和 mysql 下运行，但是，有时候使用 node js 和 mongodb 对于这个项目来说会更有效率。

如果你认为你能做到，就用 json，因为你能做到！而且，如果是一个错误，忘记它; 尝试做出好的或坏的选择，但尝试！

低

法国人

小开

这实际上取决于您的用例。如果存储的信息在报告中完全没有价值，而且不会通过 JOIN 与其他表一起查询，那么将数据存储在一个编码为 JSON 的文本字段中可能是有意义的。

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

小开

这里有一个函数用于在列中保存/更新 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，$钥匙-要更新的 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];
}

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

小开

每个评论似乎都从错误的角度出发，通过 PHP 在关系数据库中存储 JSON 代码是很好的，事实上加载和显示这样的复杂数据会更快，但是你会有设计方面的考虑，如搜索，索引等。

最好的方法是使用混合数据，例如，如果你需要基于日期时间进行搜索，MySQL (性能调整)会比 PHP 快得多，对于像场所搜索距离这样的事情，MySQL 也应该快得多(注意搜索不是访问)。你不需要搜寻的资料，可以储存在 JSON、 BLOB 或任何你认为必要的格式。

您需要访问的数据很容易存储为 JSON，例如一个基本的个案发票系统。它们在 RDBMS 没有得到很大的好处，如果你有正确的 HTML 表单结构，它们可以通过 JSON _ coding ($_ POST [‘ entires’])存储在 jSON 中。

我很高兴你喜欢使用 MongoDB，我希望它能继续为你提供很好的服务，但是不要认为 MySQL 总是会离开你的雷达，因为你的应用程序的复杂性增加，你可能最终需要一个 RDBMS 的一些功能和特性(即使它只是为了退出归档数据或业务报告)

小开

在我看来，回答这个问题的每个人似乎都忽略了一个关键问题，除了@deceze —— 使用正确的工具做这项工作。你可以强制关系数据库存储几乎任何类型的数据，也可以强制 Mongo 处理关系型数据，但代价是什么呢？您最终会在开发和维护的各个级别引入复杂性，从模式设计到应用程序代码; 更不用说性能损失了。

在2014年，我们可以访问许多数据库服务器，这些服务器非常好地处理特定类型的数据。

Mongo (文件存储)
Redis (键值数据存储)
MySQL/Maria/PostgreSQL/Oracle/etc (关系数据)
CouchDB (JSON)

我确信我错过了其他一些，像 RabbirMQ 和 Cassandra。我的观点是，对需要存储的数据使用正确的工具。

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

小开

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

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

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

小开

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

首先，在 RDBMS 中存储 JSON 有更好的支持。您可以考虑切换到 PostgreSQL (尽管 MySQL 从5.7.7版本开始就支持 JSON)。PostgreSQL 使用与 MySQL 非常相似的 SQL 命令，只是它们支持更多的函数。他们添加的函数之一是提供 JSON 数据类型，您现在可以查询存储的 JSON。(关于这个的一些参考资料)如果你没有在你的程序中直接构建查询，例如，在 php 中使用 PDO 或者在 Laravel 使用 eloquent，你所需要做的就是在你的服务器上安装 PostgreSQL 并改变数据库连接设置。您甚至不需要更改代码。

正如其他答案所建议的那样，在大多数情况下，直接在 RDBMS 中以 JSON 形式存储数据并不是一个好主意。但也有一些例外。我能想到的一种情况是，字段的链接条目数可变。

例如，为了存储博客文章的标记，通常需要一个用于博客文章的表、一个标记表和一个匹配表。因此，当用户想要编辑一篇文章，并且需要显示与该文章相关的标记时，需要查询3个表。如果匹配的表/标记表很长，这将对性能造成很大损害。

通过将标记作为 JSON 存储在 blog post 表中，相同的操作只需要一个表搜索。然后用户就可以更快地看到博客文章进行编辑，但是如果您想报告哪篇文章链接到某个标签，或者按标签进行搜索，这将损害性能。

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

小开

对在 MySQL 中存储 JSON 的早期支持已经添加到 MySQL 5.7.7 JSON 实验室发布(Linux 二进制文件，来源) ！这个版本似乎是从一系列公开的与 JSON 相关的用户定义函数在2013年发展而来的。

这种新生的原生 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                                      |
+--------------------------------------------------+

恕我直言，上面是这个新功能的一个很好的用例; 许多 SQL 数据库已经有了一个用户表，而且，与其无休止地更改模式以适应不断发展的用户偏好集，不如在一个 JOIN之外有一个单独的 JSON 列是完美的。特别是因为它不太可能需要为单个项目进行查询。

虽然还处于早期阶段，但 MySQL 服务器团队在传达开始的博客的变化方面做得很好。

小开

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

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

小开

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

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 ()。

搜索 JSON 值的函数: JSON _ CONTAINS ()、 JSON _ CONTAINS _ PATH ()、 JSON _ EXTRACT ()、 JSON _ KEYS ()和 JSON _ SEARCH ()。

修改 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、 ORDERBY 和 GROUPBY 子句。这包括 SELECT、 UPDATE、 DELETE、 CREATETABLE 和其他 SQL 语句。左边必须是 JSON 列标识符(而不是别名)。右边是一个带引号的 JSON 路径表达式，它根据作为列值返回的 JSON 文档进行计算。

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

小开

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

在将它安装到服务器之后(只需要 root 权限而不是 super) ，您可以执行以下操作:

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

它会回来的 a 2 。您可以使用以下命令返回 JSON 中的任何内容好的方面是它支持 MySQL 5.1,5.2,5.6。而且您不需要在服务器上安装任何二进制文件。

基于旧的项目 common-schema，但它仍然工作，今天 Https://code.google.com/archive/p/common-schema/

小开

我知道这已经很晚了，但是我确实遇到过类似的情况，我使用了一种混合方法来维护 RDBMS 标准，将表规范化到一个点，然后将数据存储在 JSON 中，作为该点之后的文本值。例如，我按照 RDBMS 规范化规则将数据存储在4个表中。但是在第4个表中，为了容纳动态模式，我将数据存储为 JSON 格式。每次我想要检索数据时，我检索 JSON 数据，解析它并用 Java 显示它。到目前为止，这对我来说很有效，并且确保我仍然能够使用 ETL 以规范化的方式索引表中转换为 json 数据的字段。这样可以确保用户在处理应用程序时面临最小的延迟，并且字段被转换为 RDBMS 友好的数据分析格式等。我认为这种方法工作得很好，并且相信给定的 MYSQL (5.7 +)也允许解析 JSON，这种方法给您带来了 RDBMS 和 NOSQL 数据库的好处。