问答中心分类: MYSQL将JSON存储在数据库中,而不是每个键都有一个新列
0
匿名用户 提问 4天 前

我正在实现以下模型以在表中存储与用户相关的数据-我有2列-uid(主键)和meta以JSON格式存储有关用户的其他数据的列。

uid   | meta
--------------------------------------------------
 1     | {name:['foo'], 
       |  emailid:['foo@bar.com','bar@foo.com']}
--------------------------------------------------
 2     | {name:['sann'], 
       |  emailid:['sann@bar.com','sann@foo.com']}
--------------------------------------------------

这是一种比每个属性一列模型更好的方法(性能方面、设计方面)吗?在这种模型中,表将有许多列,如uid,name,emailid.
我喜欢第一个模型的地方是,您可以添加尽可能多的字段,没有限制。
另外,我想知道,现在我已经实现了第一个模型。如何对其执行查询,例如,我想获取所有名为“foo”的用户?
问题-使用-JSON或每个字段的列在数据库中存储用户相关数据(请记住字段的数量不是固定的)的更好方法是什么?此外,如果实现了第一个模型,如何如上所述查询数据库?我是否应该使用这两种模型,将查询可能搜索到的所有数据存储在单独的一行中,而将其他数据存储在JSON中(是另一行)?

使现代化
既然我不需要在太多的列上执行搜索,那么使用这两种模型明智吗?我需要搜索的数据的每列键和其他数据的JSON键(在同一个MySQL数据库中)?

10 Answers
0
Colin M 回答 4天 前

2017年6月4日更新
考虑到这个问题/答案已经得到了一些欢迎,我认为它值得更新。
最初发布这个问题时,MySQL不支持JSON数据类型,PostgreSQL中的支持还处于起步阶段。从5.7开始,MySQL现在支持JSON数据类型(二进制存储格式)和PostgreSQLJSONB公司已显著成熟。这两种产品都提供了可存储任意文档的performant JSON类型,包括对JSON对象特定键的索引支持。
然而,我仍然坚持我最初的说法,即在使用关系数据库时,您的默认首选项仍然应该是每值列。关系数据库仍然是建立在这样一个假设之上的,即其中的数据将得到很好的规范化。查询计划器在查看列时比在JSON文档中查看键时具有更好的优化信息。可以在列之间创建外键(但不能在JSON文档中的键之间创建)。重要的是:如果您的大多数模式都非常不稳定,足以证明使用JSON是正确的,那么您可能至少需要考虑关系数据库是否是正确的选择。
也就是说,很少有应用程序是完全面向关系或文档的。大多数应用程序都混合了这两者。以下是我个人发现JSON在关系数据库中有用的一些示例:

  • 存储联系人的电子邮件地址和电话号码时,将其存储为JSON数组中的值比多个单独的表更容易管理
  • 保存任意键/值用户首选项(其中值可以是布尔值、文本值或数字,并且您不希望为不同的数据类型设置单独的列)
  • 存储没有定义模式的配置数据(如果您正在构建Zapier或IFTTT,并且需要存储每个集成的配置数据)

我相信还有其他的,但这些只是几个简单的例子。
原始答案
如果您真的希望能够无限制地添加任意多的字段(任意文档大小限制除外),请考虑使用NoSQL解决方案,如MongoDB。
对于关系数据库:每个值使用一列。在列中放置JSON blob几乎不可能进行查询(当您实际找到一个有效的查询时,速度会非常慢)。
关系数据库在索引时利用数据类型,并打算使用标准化结构
附带说明:这并不是说永远不要在关系数据库中存储JSON。如果您正在添加真正的元数据,或者如果您的JSON描述的信息不需要查询和仅用于显示,为所有数据点创建单独的列可能会有点过头。

ShuklaSannidhya 回复 4天 前

既然我不需要在太多的列上执行搜索,那么使用这两种模型明智吗?我需要搜索的数据的每列键和其他数据的JSON键(在同一个MySQL数据库中)?

Colin M 回复 4天 前

@Sann对于要读取的数据,应为每个值使用一列经常查询。将某人的名字放在JSON中没有意义,因为即使您不太可能基于它进行查询,您也可能需要它非常经常在应用程序方面,这是大量浪费的解码。除非你真正地感觉您的数据更好地表示为JSON(相信我,可能不是),您不应该求助于JSON。

ted 回复 4天 前

virtually impossible to query“-今天,psql允许您搜索和索引其jsonb

Colin M 回复 4天 前

@ted真的。然而,在撰写此答案时,还没有真正可用的答案。此外,这个问题引用了MySQL,其中不存在is功能。

ted 回复 4天 前

@科林,是的,我意识到我的评论比你的帖子年轻了3年。我之所以离开它,是因为它可能会对其他人有所帮助并改变决定。至于对MySQL的引用:可能是真的,但是"For relational databases"在你的答案中=P

Shri 回复 4天 前

因此,在一个用户数据库表中,包含诸如名称、电子邮件、城市等详细信息,以及用户的一些设置(如主题、颜色等)和其他配置文件数据(如兴趣和关键字),为名称电子邮件等设置单独的列,为所有设置设置一个json列,这有意义吗?

Alex 回复 4天 前

为了进一步更新这篇文章,MySQL在5.7版中引入了JSON数据类型,它允许查询。我不知道该如何评价它的性能。

PBo 回复 4天 前

SQL Server 2016现在还允许您存储JSON行并在JSON中搜索数据文档。微软com/en-us/sql/relational-databases/json/…

0
Homan 回答 4天 前

就像大多数事情一样,“视情况而定”。将数据存储在列或JSON中本身并不是对的或错的/好的或坏的。这取决于你以后需要做什么。您预计访问此数据的方式是什么?您是否需要交叉引用其他数据?
其他人已经很好地回答了技术权衡是什么。
没有多少人讨论过您的应用程序和功能会随着时间的推移而演变,以及此数据存储决策对您的团队有何影响。
因为使用JSON的一个诱惑是避免迁移模式,所以如果团队没有遵守规则,那么很容易将另一个键/值对粘贴到JSON字段中。它没有迁移,没有人记得它的用途。没有对其进行验证。
我的团队在postgres的传统列旁边使用JSON,起初这是自切片面包以来最好的东西。JSON很有吸引力,功能强大,直到有一天我们意识到灵活性是有代价的,它突然成为了真正的痛点。有时,这一点会迅速上升,然后变得很难改变,因为我们在这个设计决策的基础上构建了很多其他东西。
加班加点,添加新功能,将数据放在JSON中,这导致了比如果我们坚持使用传统列可能会添加的查询更复杂的查询。然后我们开始将某些键值提取回列中,以便我们可以进行连接并在值之间进行比较。坏主意。现在我们有了重复。一个新的开发人员会加入进来并感到困惑吗?我应该保存回哪个值?是JSON还是列?
JSON字段变成了这些和那些小片段的垃圾抽屉。数据库级别没有数据验证,文档之间没有一致性或完整性。这将所有责任推到了应用程序中,而不是从传统的专栏中获取硬类型和约束检查。
回顾过去,JSON允许我们非常快速地迭代并获得一些东西。太棒了。然而,当我们达到一定的团队规模后,它的灵活性也让我们可以用一长串的技术债务来拖累自己,这就减缓了随后的特性演化过程。小心使用。
仔细思考数据的性质。这是你应用程序的基础。随着时间的推移,数据将如何使用。它可能会发生怎样的变化?

0
Adam 回答 4天 前

只是把它扔到外面,但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
Bruno 回复 4天 前

我很好奇,看看NoSQL解决方案在正确索引键上的性能是否真的比关系查询更好。我怀疑在这样的一个1级示例中应该或多或少是相同的。

ShuklaSannidhya 回复 4天 前

+我也注意到了!但它提供了一个巨大的表(按行)。你也可以存储多个值,例如,如果用户更改了他的/她的名称,但我也想保留旧名称,在这种情况下,我需要JSON类型的数据模型。

Bruno 回复 4天 前

@Sann,如果希望在JSON中保留旧值,还必须重命名键:可以使用EAV(本例就是这样)或JSON。这没有什么特别的不同。

Adam 回复 4天 前

它确实为您提供了一个巨大的表,但对于重复的值,您会遇到与JSON相同的问题-您不能在同一级别拥有重复的键(例如,两个“name”键)并期望可预测的行为。

ShuklaSannidhya 回复 4天 前

当然,不能有重复的键,但可以有一个与该键关联的数组。查看emailid输入我在问题中给出的示例。

Travis J 回复 4天 前

这对于单值的东西似乎很好,但复杂的结构(在我看来,这是在数据库中使用json的全部原因)在这里并没有得到很好的表示。如果有一个嵌套很深的对象,那么单纯地将索引存储为字符串会变得很难看。在这一点上,简单地存储json更有意义,因为每个键值都可能表示json结构。

0
Nick Andriopoulos 回答 4天 前

这种方法的缺点正是您提到的:
这使得查找东西的速度非常慢,因为每次都需要对其执行文本搜索。
相反,每列的值与整个字符串匹配。
您的方法(基于JSON的数据)适用于不需要搜索的数据,只需要与普通数据一起显示即可。
编辑:为了澄清这一点,上述内容适用于经典关系数据库。NoSQL在内部使用JSON,如果这是所需的行为,则可能是更好的选择。

ShuklaSannidhya 回复 4天 前

所以你的意思是,我应该两者兼用。我需要搜索的数据的每列键和其他数据的JSON键,对吗?

Nick Andriopoulos 回复 4天 前

对这样,您可以通过搜索每列数据字段获得所需的性能,并在需要时获取JSON blob以用于代码。

0
Girish 回答 4天 前

基本上,您使用的第一种模型称为基于文档的存储。你应该看看流行音乐基于NoSQL文档的数据库,如MongoDB和CouchDB. 基本上,在基于文档的db中,您将数据存储在json文件中,然后可以查询这些json文件。
第二种模型是流行的关系数据库结构。
如果您想使用像MySql这样的关系数据库,那么我建议您只使用第二种模型。在第一个模型中使用MySql和存储数据没有任何意义.
要回答你的第二个问题,如果使用第一个模型,则无法查询像“foo”这样的名称.

ShuklaSannidhya 回复 4天 前

使用这两种模型是否明智?我需要搜索的数据的每列键和其他数据的JSON键(在同一数据库中)?

Girish 回复 4天 前

@桑-哈哈。这就是数据复制。您必须确保这两段数据始终相同。即使其中一个数据在任何时候都不同,那么您的数据也不干净,可能会导致严重的问题。所以,我的答案是

ShuklaSannidhya 回复 4天 前

但是当冗余数据很小时,冗余并不昂贵,比如说,我只需要在两个字段上执行搜索,所以我为它们创建了两个新列,[可能]从JSON数据中删除它们。复制不会很昂贵,对吗?

Girish 回复 4天 前

如果您考虑性能,那么MongoDB和CouchDB提供了比MySql更快的读写操作,因为它们在关系数据库中没有提供很多在大多数用例中都不需要的功能。

markbratanov 回复 4天 前

这样做的好处难道不是存储来自API的JSON对象/回调吗?例如,您可以只查询本地数据库(mysql、lite等)中的JSON对象,而不是调用youtube的API来获取URL、thumb等?我不知道,这对我来说很有意义,尤其是当你试图缓存或让应用程序运行得更快的时候。但我不是专业人士:/