在数据库中存储JSON vs.每个键都有一个新列

这种方法的缺点正如你所提到的:

它使它非常慢的东西，因为每次你需要执行文本搜索。

每个列的值匹配整个字符串。

您的方法(基于JSON的数据)适用于不需要搜索的数据，只需要与普通数据一起显示即可。

澄清一下，以上是针对经典的关系数据库。NoSQL在内部使用JSON，如果这是理想的行为，它可能是更好的选择。

2017年6月4日更新

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

当这个问题最初发布时，MySQL还不支持JSON数据类型，而PostgreSQL的支持还处于起步阶段。自5.7版本以来，MySQL 现在支持JSON数据类型(二进制存储格式)和PostgreSQL JSONB已经显著成熟。这两个产品都提供了可以存储任意文档的高性能JSON类型，包括支持索引JSON对象的特定键。

但是，我仍然坚持我最初的说法，即在使用关系数据库时，默认首选项仍然应该是每值列。关系数据库仍然建立在这样一个假设之上，即其中的数据将被很好地规范化。查询计划器在查看列时比查看JSON文档中的键时具有更好的优化信息。外键可以在列之间创建(但不能在JSON文档中的键之间创建)。重要的是:如果您的模式的大部分是不稳定的，足以证明使用JSON是合理的，那么您可能至少要考虑关系数据库是否是正确的选择。

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

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

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

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

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

原来的答案

如果您真的希望能够添加任意数量的字段，而不受任何限制(除了任意文档大小限制之外)，可以考虑使用NoSQL解决方案，例如MongoDB。

对于关系数据库:每个值使用一列。将JSON blob放在列中几乎不可能进行查询(并且当您真正找到一个有效的查询时，会非常缓慢)。

关系数据库在索引时利用数据类型，并打算用归一化结构实现。

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

如果你试图将一个非关系模型放入关系数据库中，我认为你会更好地使用NoSQL数据库，如MongoDB。没有预定义的模式可以满足您对字段数量没有限制的要求(请参阅典型的MongoDB集合示例)。查看MongoDB 文档来了解如何查询文档，例如:

db.mycollection.find(
{
name: 'sann'
}
)

基本上，您使用的第一个模型称为基于文档的存储。你应该看看流行的基于NoSQL文档的数据库，如MongoDB和CouchDB。基本上，在基于文档的db中，你将数据存储在json文件中，然后你可以对这些json文件进行查询。

第二种模型是流行的关系数据库结构。

如果你想使用像MySql这样的关系数据库，那么我建议你只使用第二种模型。在第一个模型中使用MySql和存储数据是没有意义的。

为了回答你的第二个问题，如果你使用第一个模型，就没有办法查询像'foo'这样的名称。

似乎您主要在犹豫是否要使用关系模型。

就目前的情况而言，您的示例相当适合关系模型，但是当您需要使该模型演进时，问题当然会出现。

如果您的主实体(用户)只有一个(或几个预先确定的)属性级别，您仍然可以在关系数据库中使用实体属性值(entity Attribute Value, EAV)模型。(这也有利弊。)

如果您希望使用应用程序搜索的结构化值较少，那么MySQL可能不是最佳选择。

如果你在使用PostgreSQL，你可能会两全其美。(这个真的取决于这里数据的实际结构…MySQL也不一定是错误的选择，NoSQL选项可能是有趣的，我只是建议替代方案。)

事实上，PostgreSQL可以在(不可变)函数上建立索引(据我所知，MySQL不能)，在最近的版本中，你可以直接在JSON数据上使用PLV8在特定的JSON元素上建立索引，这将提高你搜索数据时的查询速度。

编辑:

因为不会有太多的列，我需要执行 搜索，使用这两个模型明智吗?数据的每列键 我需要搜索和JSON为其他人(在同一个MySQL数据库)?< / p >

混合使用两个模型不一定是错误的(假设额外的空间可以忽略不计)，但是如果不能确保两个数据集保持同步，则可能会导致问题:应用程序必须在不更新另一个数据集的情况下更改其中一个。

实现这一点的一个好方法是让触发器执行自动更新，方法是每当进行更新或插入时在数据库服务器中运行一个存储过程。据我所知，MySQL存储过程语言可能缺乏对任何JSON处理的支持。同样，支持PLV8的PostgreSQL(可能还有其他具有更灵活的存储过程语言的RDBMS)应该更有用(使用触发器自动更新关系列与以相同方式更新索引非常相似)。

只是随便说说，但是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

某些时候表上的连接将是一个开销。比方说OLAP。如果我有两个表，一个是ORDERS表，另一个是ORDER_DETAILS。为了获得所有的订单细节，我们必须连接两个表，这将使查询变慢，当表中没有一行增加，比如数百万左右。左/右连接比内连接太慢。 我认为如果我们在各自的ORDERS条目中添加JSON字符串/对象，JOIN将被避免。添加报告生成将更快

和大多数事情一样，“视情况而定”。将数据存储在列或JSON中本身没有对错/好坏之分。这取决于你以后要用它做什么。您预计使用什么方式访问这些数据?您是否需要交叉引用其他数据?

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

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

因为使用JSON的诱惑之一是避免迁移模式，所以如果团队没有纪律，很容易在JSON字段中插入另一个键/值对。它不需要迁移，没有人记得它是干什么用的。它没有验证。

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

随着时间的推移，添加新功能，使用JSON格式的数据会导致看起来比使用传统列所添加的查询更复杂。然后我们开始把某些键值捞出来放到列中，这样我们就可以在值之间进行连接和比较。坏主意。现在我们有了复制。一个新的开发人员会感到困惑吗?我应该存回哪个值呢?JSON还是列?

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

回顾过去，JSON让我们能够快速迭代并创造出一些内容。太棒了。然而，当我们达到一定的团队规模后，它的灵活性也让我们陷入了技术债务的长绳中，从而减缓了随后的功能开发进程。请谨慎使用。

仔细思考你的数据的性质是什么。这是你的应用程序的基础。随着时间的推移，数据将如何使用。它可能会发生怎样的变化?

正如其他人指出的那样，查询将会变慢。我建议至少添加一个'_ID'列来查询。