在数据库中存储JSON与为每个键设置新列

Question

我正在实现以下模型，用于在表中存储与用户相关的数据--我有两列-uid（主键）和一列meta以JSON格式存储有关用户的其他数据。

 uid   | meta
--------------------------------------------------
 1     | {name:['foo'], 
       |  emailid:['[e-mail]','[e-mail]']}
--------------------------------------------------
 2     | {name:['sann'], 
       |  emailid:['[e-mail]','[e-mail]']}
--------------------------------------------------

这是不是一种更好的方法（从性能和设计角度），而不是每个属性一列模型，在该模型中，表将有许多列，如uid、name、emailid。

我喜欢的第一个模型是，你可以添加尽可能多的字段没有限制。

还有，我在想，现在我已经实现了第一个模型。我如何对它执行一个查询，比如，我想要获取所有具有“foo”名称的用户？

问题-使用-JSON或每字段列在数据库中存储用户相关数据（记住字段的数量不是固定的）哪种方法更好？另外，如果实现了第一个模型，那么如何进行上述数据库查询呢？我是否应该同时使用这两种模型，将查询可能搜索的所有数据存储在单独的行中，而将其他数据存储在JSON中（是不同的行）？

由于不会有太多的列需要我执行搜索，这两个模型都使用是明智的吗？对于我需要搜索的数据，使用JSON搜索其他数据（在同一个MySQL数据库中）？

Answer 1

只是把它扔在那里，但WordPress有一个这种东西的结构（至少WordPress是我第一个观察到它的地方，它可能起源于其他地方）。

它允许无限键，搜索速度比使用JSON blob快，但不如一些NoSQL解决方案快。

uid   |   meta_key    |   meta_val
----------------------------------
1         name            Frank
1         age             12
2         name            Jeremiah
3         fav_food        pizza
.................

编辑

用于存储历史记录/多个密钥

uid   | meta_id    |   meta_key    |   meta_val
----------------------------------------------------
1        1             name            Frank
1        2             name            John
1        3             age             12
2        4             name            Jeremiah
3        5             fav_food        pizza
.................

并通过以下内容进行查询：

select meta_val from `table` where meta_key = 'name' and uid = 1 order by meta_id desc

Answer 2

就像大多数事情一样“这取决于”。将数据存储在列或JSON中本身并不是对或错/好或坏。这取决于你以后需要用它做什么。您预测的访问此数据的方式是什么？是否需要交叉引用其他数据？

其他人已经很好地回答了技术上的权衡是什么。

没有多少人讨论过你的应用程序和特性会随着时间的推移而演变，以及这个数据存储决策如何影响你的团队。

因为使用JSON的一个诱惑力是避免迁移模式，所以如果团队没有纪律，就很容易将另一个键/值对添加到JSON字段中。没有人会为它迁移，也没有人记得它是为了什么。上面没有验证。

我的团队在postgres中使用JSON，同时使用传统列，一开始它是自切片面包以来最好的东西。JSON很有吸引力，功能也很强大，直到有一天我们意识到灵活性是有代价的，它突然变成了一个真正的痛点。有时候，这一点会很快地变得难以改变，因为我们已经在这个设计决策的基础上构建了很多其他东西。

超时、添加新特性、将数据放在JSON中导致了比我们坚持使用传统列可能增加的查询更复杂的查询。然后，我们开始将某些键值放回列中，这样我们就可以在值之间进行连接和比较。坏主意。现在我们有了复制。一个新的开发人员来了，会感到困惑吗？哪一个是我应该保存的值？JSON还是列？

JSON字段成了这个和那个小片段的垃圾抽屉。没有数据库级别的数据验证，文档之间没有一致性或完整性。这就把所有的责任都推到了应用程序中，而不是从传统的列中进行严格的类型和约束检查。

回顾一下，JSON允许我们非常快速地迭代并获得一些东西。真是太棒了。然而，在我们达到一定的团队规模之后，它的灵活性也允许我们用技术债务的长绳来吊死自己，这就拖慢了随后的特性进化进程。慎用。

仔细思考你的数据的本质是什么。它是你应用程序的基础。随着时间的推移，数据将如何使用。又如何可能发生变化呢？

Answer 3

鉴于这个问题/答案已经获得了一些流行，我认为它值得更新。

当这个问题最初发布时，MySQL还不支持JSON数据类型，PostgreSQL中的支持还处于起步阶段。从5.7开始，MySQL现在支持JSON数据类型（二进制存储格式），并且PostgreSQL JSONB已经显著成熟。这两个产品都提供了可以存储任意文档的Performit JSON类型，包括支持索引JSON对象的特定键。

但是，我仍然坚持我最初的声明，即当使用关系数据库时，默认首选项仍然应该是每值列。关系数据库仍然是建立在这样一个假设上的，即其中的数据将相当好地规范化。查询规划器在查看列时比查看JSON文档中的键时有更好的优化信息。可以在列之间（但不能在JSON文档中的键之间）创建外键。重要的是：如果您的模式大部分都是易变的，足以证明使用JSON是正确的，那么您可能至少需要考虑一下关系数据库是否是正确的选择。

也就是说，很少有应用程序是完全关系型的或面向文档的。大多数应用程序都有两者的混合。以下是我个人发现JSON在关系数据库中很有用的一些示例：

>

当存储联系人的电子邮件地址和电话号码时，将它们作为值存储在JSON数组中比多个单独的表更容易管理

保存任意键/值用户首选项（其中值可以是布尔值、文本值或数值值，并且您不希望为不同的数据类型拥有单独的列）

存储没有定义模式的配置数据（如果您正在构建Zapier或IFTTT并且需要为每个集成存储配置数据）

我相信还有其他的，但这些只是一些简单的例子。

如果您真的希望能够添加任意多个字段而不受任何限制（除了任意的文档大小限制），请考虑MongoDB这样的NoSQL解决方案。

对于关系数据库：每个值使用一列。将JSON blob放入列中会使查询几乎不可能（当您真正找到一个可以工作的查询时，查询速度会非常慢）。

关系数据库在索引时利用数据类型，并打算用规范化的结构来实现。

作为附带说明：这并不是说您永远不应该将JSON存储在关系数据库中。如果您要添加真正的元数据，或者如果您的JSON描述的信息不需要查询，只用于显示，那么为所有数据点创建一个单独的列可能会有些过分。