blocks|key|194612|text|这种方法的缺点正是您所提到的：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|194613|这使得查找内容变得非常慢，因为每次您都需要对其执行文本搜索。|194614|每列的值与整个字符串匹配。|194615|您的方法(基于JSON的数据)适用于不需要搜索的数据，只需与普通数据一起显示即可。|194616|编辑：只是为了澄清，上面的内容适用于传统的关系数据库。NoSQL在内部使用JSON，如果这是所需的行为，它可能是更好的选择。|offset|length|style|BOLD|194617|entityMap^0|0|0|0|0|0|3|0^^$0|@$1|2|3|4|5|6|7|P|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|Q|8|@]|9|@]|A|$]]|$1|D|3|E|5|6|7|R|8|@]|9|@]|A|$]]|$1|F|3|G|5|6|7|S|8|@]|9|@]|A|$]]|$1|H|3|I|5|6|7|T|8|@$J|U|K|V|L|M]]|9|@]|A|$]]|$1|N|3|-4|5|6|7|W|8|@]|9|@]|A|$]]]|O|$]]

the drawback of the approach is exactly what you mentioned :

it makes it VERY slow to find things, since each time you need to perform a text-search on it. 

value per column instead matches the whole string.

Your approach (JSON based data) is fine for data you don't need to search by, and just need to display along with your normal data.

Edit: Just to clarify, the above goes for classic relational databases. NoSQL use JSON internally, and are probably a better option if that is the desired behavior.

blocks|key|3032959|text|更新2017年6月4日|type|unstyled|depth|inlineStyleRanges|entityRanges|data|3032960|鉴于这个问题/答案已经获得了一些人气，我认为它值得更新。|3032961|当这个问题最初发布时，MySQL还不支持JSON数据类型，而PostgreSQL对它的支持还处于起步阶段。从5.7开始，MySQL+now+supports+a+JSON+data+type+(以二进制存储格式)和PostgreSQL+JSONB已经明显成熟。这两种产品都提供了可以存储任意文档的高性能JSON类型，包括对JSON对象的特定键进行索引的支持。|offset|length|3032962|但是，我仍然坚持我最初的声明，即在使用关系数据库时，您的默认首选项应该仍然是按值列。关系数据库仍然建立在这样的假设之上，即其中的数据将被相当好地规范化。与查看JSON文档中的键相比，查询规划器在查看列时具有更好的优化信息。外键可以在列之间创建(但不能在JSON文档中的键之间创建)。重要的是:如果您的大多数模式都不稳定到足以证明使用JSON的程度，那么您可能至少需要考虑一下关系数据库是否是正确的选择。|3032963|也就是说，很少有应用程序是完全面向关系或面向文档的。大多数应用程序都混合了这两种方法。以下是我个人发现JSON在关系数据库中很有用的一些示例：|3032964|3032965|存储联系人的电子邮件地址和电话号码时，将它们作为值存储在JSON数组中要比管理多个单独的表容易得多|unordered-list-item|3032966|保存任意键/值用户首选项(其中的值可以是布尔值、文本或数字，并且您不希望为不同的数据类型有单独的列)|3032967|存储没有定义模式的配置数据(如果您正在构建Zapier或IFTTT，并且需要为每个集成存储配置数据)|3032968|3032969|我相信还有其他的，但这些只是几个简单的例子。|3032970|原始答案|3032971|如果您确实希望能够不受限制地添加任意数量的字段(除了任意的文档大小限制之外)，可以考虑使用MongoDB这样的NoSQL解决方案。|3032972|对于关系数据库:每个值使用一列。将JSON+blob放在一个列中会使查询变得几乎不可能(当您实际找到一个可以工作的查询时，速度会慢得令人痛苦)。|3032973|关系数据库在索引时利用数据类型，并打算使用规范化结构来实现。|3032974|附注:这并不是说您永远不应该将JSON存储在关系数据库中。如果您正在添加真正的元数据，或者如果您的JSON描述的是不需要查询且仅用于显示的信息，那么为所有数据点创建单独的列可能过于夸张了。|3032975|entityMap|0|LINK|mutability|MUTABLE|url|https://dev.mysql.com/doc/refman/5.7/en/json.html|1|https://www.postgresql.org/docs/9.4/static/datatype-json.html^0|0|0|1U|T|0|3A|5|1|0|0|0|0|0|0|0|0|0|0|0|0|0|0^^$0|@$1|2|3|4|5|6|7|1G|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|1H|8|@]|9|@]|A|$]]|$1|D|3|E|5|6|7|1I|8|@]|9|@$F|1J|G|1K|1|1L]|$F|1M|G|1N|1|1O]]|A|$]]|$1|H|3|I|5|6|7|1P|8|@]|9|@]|A|$]]|$1|J|3|K|5|6|7|1Q|8|@]|9|@]|A|$]]|$1|L|3|-4|5|6|7|1R|8|@]|9|@]|A|$]]|$1|M|3|N|5|O|7|1S|8|@]|9|@]|A|$]]|$1|P|3|Q|5|O|7|1T|8|@]|9|@]|A|$]]|$1|R|3|S|5|O|7|1U|8|@]|9|@]|A|$]]|$1|T|3|-4|5|6|7|1V|8|@]|9|@]|A|$]]|$1|U|3|V|5|6|7|1W|8|@]|9|@]|A|$]]|$1|W|3|X|5|6|7|1X|8|@]|9|@]|A|$]]|$1|Y|3|Z|5|6|7|1Y|8|@]|9|@]|A|$]]|$1|10|3|11|5|6|7|1Z|8|@]|9|@]|A|$]]|$1|12|3|13|5|6|7|20|8|@]|9|@]|A|$]]|$1|14|3|15|5|6|7|21|8|@]|9|@]|A|$]]|$1|16|3|-4|5|6|7|22|8|@]|9|@]|A|$]]]|17|$18|$5|19|1A|1B|A|$1C|1D]]|1E|$5|19|1A|1B|A|$1C|1F]]]]

<h2>Updated 4 June 2017</h2>

Given that this question/answer have gained some popularity, I figured it was worth an update.

When this question was originally posted, MySQL had no support for JSON data types and the support in PostgreSQL was in its infancy. Since 5.7, MySQL <a href="https://dev.mysql.com/doc/refman/5.7/en/json.html" rel="noreferrer">now supports a JSON data type</a> (in a binary storage format), and PostgreSQL <a href="https://www.postgresql.org/docs/9.4/static/datatype-json.html" rel="noreferrer">JSONB</a> has matured significantly. Both products provide performant JSON types that can store arbitrary documents, including support for indexing specific keys of the JSON object.

However, I still stand by my original statement that your default preference, when using a relational database, should still be column-per-value. Relational databases are still built on the assumption of that the data within them will be fairly well normalized. The query planner has better optimization information when looking at columns than when looking at keys in a JSON document. Foreign keys can be created between columns (but not between keys in JSON documents). Importantly: if the majority of your schema is volatile enough to justify using JSON, you might want to at least consider if a relational database is the right choice.

That said, few applications are perfectly relational or document-oriented. Most applications have some mix of both. Here are some examples where I personally have found JSON useful in a relational database:

<ul>
<li>When storing email addresses and phone numbers for a contact, where storing them as values in a JSON array is much easier to manage than multiple separate tables</li>
<li>Saving arbitrary key/value user preferences (where the value can be boolean, textual, or numeric, and you don't want to have separate columns for different data types)</li>
<li>Storing configuration data that has no defined schema (if you're building Zapier, or IFTTT and need to store configuration data for each integration)</li>
</ul>

I'm sure there are others as well, but these are just a few quick examples.

<h2>Original Answer</h2>

If you really want to be able to add as many fields as you want with no limitation (other than an arbitrary document size limit), consider a NoSQL solution such as MongoDB.

For relational databases: use one column per value. Putting a JSON blob in a column makes it virtually impossible to query (and painfully slow when you actually find a query that works).

Relational databases take advantage of data types when indexing, and are intended to be implemented with a normalized structure.

As a side note: this isn't to say you should never store JSON in a relational database. If you're adding true metadata, or if your JSON is describing information that does not need to be queried and is only used for display, it may be overkill to create a separate column for all of the data points.

blocks|key|194637|text|如果您正在尝试将非关系模型适应到关系数据库中，我认为使用MongoDB这样的NoSQL数据库会更好。没有预定义的模式可以满足您对字段数量没有限制的要求(请参阅典型的MongoDB集合示例)。查看MongoDB+documentation，了解如何查询文档，例如|type|unstyled|depth|inlineStyleRanges|entityRanges|offset|length|data|194638|db.mycollection.find(
++++{
++++++name:+'sann'
++++}
)|code-block|syntax|javascript|194639|entityMap|0|LINK|mutability|MUTABLE|url|http://en.wikipedia.org/wiki/MongoDB|1|http://docs.mongodb.org/manual/applications/read/^0|S|7|0|2X|D|1|0|0^^$0|@$1|2|3|4|5|6|7|S|8|@]|9|@$A|T|B|U|1|V]|$A|W|B|X|1|Y]]|C|$]]|$1|D|3|E|5|F|7|Z|8|@]|9|@]|C|$G|H]]|$1|I|3|-4|5|6|7|10|8|@]|9|@]|C|$]]]|J|$K|$5|L|M|N|C|$O|P]]|Q|$5|L|M|N|C|$O|R]]]]

You are trying to fit a non-relational model into a relational database, I think you would be better served using a NoSQL database such as <a href="http://en.wikipedia.org/wiki/MongoDB" rel="nofollow">MongoDB</a>. There is no predefined schema which fits in with your requirement of having no limitation to the number of fields (see the typical MongoDB collection example). Check out the MongoDB <a href="http://docs.mongodb.org/manual/applications/read/" rel="nofollow">documentation</a> to get an idea of how you'd query your documents, e.g.

<pre><code>db.mycollection.find(
 {
 name: 'sann'
 }
)
</code></pre>

blocks|key|2820452|text|基本上，您使用的第一个模型称为基于文档的存储。您应该了解一下流行的基于文档的NoSQL数据库，如MongoDB和CouchDB。基本上，在基于文档的数据库中，您将数据存储在json文件中，然后可以对这些json文件进行查询。|type|unstyled|depth|inlineStyleRanges|offset|length|style|BOLD|entityRanges|data|2820453|第二种模型是流行的关系数据库结构。|2820454|如果你想使用像MySql这样的关系数据库，那么我建议你只使用第二种模型。在第一个模型中，使用MySql和存储数据是没有意义的。|2820455|为了回答你的第二个问题，如果你使用first+model，是无法查询'foo‘这样的名字的。|2820456|entityMap^0|12|P|0|0|10|6|0|T|H|0^^$0|@$1|2|3|4|5|6|7|N|8|@$9|O|A|P|B|C]]|D|@]|E|$]]|$1|F|3|G|5|6|7|Q|8|@]|D|@]|E|$]]|$1|H|3|I|5|6|7|R|8|@$9|S|A|T|B|C]]|D|@]|E|$]]|$1|J|3|K|5|6|7|U|8|@$9|V|A|W|B|C]]|D|@]|E|$]]|$1|L|3|-4|5|6|7|X|8|@]|D|@]|E|$]]]|M|$]]

Basically, the first model you are using is called as document-based storage. You should have a look at popular NoSQL document-based database like MongoDB and CouchDB. Basically, in document based db's, you store data in json files and then you can query on these json files.

The Second model is the popular relational database structure. 

If you want to use relational database like MySql then i would suggest you to only use second model. There is no point in using MySql and storing data as in the first model.

To answer your second question, there is no way to query name like 'foo' if you use first model.

blocks|key|195097|text|看起来您主要是在犹豫是否使用关系模型。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|195098|按照目前的情况，您的示例将非常适合关系模型，但当您需要使此模型发展时，问题当然可能会出现。|195099|如果您的主实体(用户)只有一个(或几个预先确定的)属性级别，那么您仍然可以在关系数据库中使用实体属性值(EAV)模型。(这也有其优点和缺点。)|195100|如果您希望使用应用程序搜索结构化程度较低的值，那么MySQL可能不是最佳选择。|195101|如果您使用的是PostgreSQL，那么您可能会两全其美。(这实际上取决于这里的数据的实际结构...MySQL也不一定是错误的选择，而且NoSQL选项可能会引起人们的兴趣，我只是建议使用其他选项。)|195102|实际上，PostgreSQL可以在(不可变的)函数上建立索引(据我所知MySQL不能)，在最近的版本中，您可以使用use+PLV8+on+the+JSON+data+directly在感兴趣的特定JSON元素上建立索引，这将提高搜索数据时的查询速度。|offset|length|195103|编辑：|style|BOLD|195104|195105|既然我不需要在太多的列上执行搜索，那么同时使用两个模型是否明智？我需要搜索的数据是每列一个键，而其他数据是JSON+(在同一个MySQL数据库中)？|blockquote|195106|195107|195108|混合两个模型不一定是错误的(假设额外的空间可以忽略不计)，但如果您不确保两个数据集保持同步，则可能会导致问题:您的应用程序绝不能在不更新另一个的情况下更改其中一个。|195109|实现这一点的一个好方法是让触发器执行自动更新，只要进行更新或插入，就在数据库服务器中运行存储过程。据我所知，MySQL存储过程语言可能缺乏对任何类型的JSON处理的支持。同样，支持PLV8的PostgreSQL+(可能还有其他具有更灵活的存储过程语言的RDBMS+)应该更有用(使用触发器自动更新关系列与以同样的方式更新索引非常相似)。|195110|entityMap|0|LINK|mutability|MUTABLE|url|http://people.planetpostgresql.org/andrew/index.php?/archives/249-Using-PLV8-to-index-JSON.html^0|0|0|0|0|0|1L|Y|0|0|0|3|0|0|0|0|0|0|0^^$0|@$1|2|3|4|5|6|7|19|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|1A|8|@]|9|@]|A|$]]|$1|D|3|E|5|6|7|1B|8|@]|9|@]|A|$]]|$1|F|3|G|5|6|7|1C|8|@]|9|@]|A|$]]|$1|H|3|I|5|6|7|1D|8|@]|9|@]|A|$]]|$1|J|3|K|5|6|7|1E|8|@]|9|@$L|1F|M|1G|1|1H]]|A|$]]|$1|N|3|O|5|6|7|1I|8|@$L|1J|M|1K|P|Q]]|9|@]|A|$]]|$1|R|3|-4|5|6|7|1L|8|@]|9|@]|A|$]]|$1|S|3|T|5|U|7|1M|8|@]|9|@]|A|$]]|$1|V|3|-4|5|6|7|1N|8|@]|9|@]|A|$]]|$1|W|3|-4|5|6|7|1O|8|@]|9|@]|A|$]]|$1|X|3|Y|5|6|7|1P|8|@]|9|@]|A|$]]|$1|Z|3|10|5|6|7|1Q|8|@]|9|@]|A|$]]|$1|11|3|-4|5|6|7|1R|8|@]|9|@]|A|$]]]|12|$13|$5|14|15|16|A|$17|18]]]]

It seems that you're mainly hesitating whether to use a relational model or not.

As it stands, your example would fit a relational model reasonably well, but the problem may come of course when you need to make this model evolve.

If you only have one (or a few pre-determined) levels of attributes for your main entity (user), you could still use an Entity Attribute Value (EAV) model in a relational database. (This also has its pros and cons.)

If you anticipate that you'll get less structured values that you'll want to search using your application, MySQL might not be the best choice here.

If you were using PostgreSQL, you could potentially get the best of both worlds. (This really depends on the actual structure of the data here... MySQL isn't necessarily the wrong choice either, and the NoSQL options can be of interest, I'm just suggesting alternatives.)

Indeed, PostgreSQL can build index on (immutable) functions (which MySQL can't as far as I know) and in recent versions, you could <a href="http://people.planetpostgresql.org/andrew/index.php?/archives/249-Using-PLV8-to-index-JSON.html" rel="nofollow">use PLV8 on the JSON data directly</a> to build indexes on specific JSON elements of interest, which would improve the speed of your queries when searching for that data.

EDIT:

<blockquote>
 Since there won't be too many columns on which I need to perform
 search, is it wise to use both the models? Key-per-column for the data
 I need to search and JSON for others (in the same MySQL database)?
</blockquote>

Mixing the two models isn't necessarily wrong (assuming the extra space is negligible), but it may cause problems if you don't make sure the two data sets are kept in sync: your application must never change one without also updating the other.

A good way to achieve this would be to have a trigger perform the automatic update, by running a stored procedure within the database server whenever an update or insert is made. As far as I'm aware, the MySQL stored procedure language probably lack support for any sort of JSON processing. Again PostgreSQL with PLV8 support (and possibly other RDBMS with more flexible stored procedure languages) should be more useful (updating your relational column automatically using a trigger is quite similar to updating an index in the same way).

blocks|key|194669|text|简单地说，WordPress对这类东西有一个结构(至少WordPress是我观察到它的第一个地方，它可能起源于其他地方)。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|194670|它允许无限的键，并且搜索速度比使用JSON+blob快，但不如一些NoSQL解决方案快。|194671|uid+++%7C+++meta_key++++%7C+++meta_val
----------------------------------
1+++++++++name++++++++++++Frank
1+++++++++age+++++++++++++12
2+++++++++name++++++++++++Jeremiah
3+++++++++fav_food++++++++pizza
.................|code-block|syntax|javascript|194672|编辑|offset|length|style|BOLD|194673|用于存储历史记录/多个密钥|194674|uid+++%7C+meta_id++++%7C+++meta_key++++%7C+++meta_val
----------------------------------------------------
1++++++++1+++++++++++++name++++++++++++Frank
1++++++++2+++++++++++++name++++++++++++John
1++++++++3+++++++++++++age+++++++++++++12
2++++++++4+++++++++++++name++++++++++++Jeremiah
3++++++++5+++++++++++++fav_food++++++++pizza
.................|194675|并通过类似如下的方式进行查询：|194676|select+meta_val+from+`table`+where+meta_key+=+'name'+and+uid+=+1+order+by+meta_id+desc|194677|entityMap^0|0|0|0|0|2|0|0|0|0|0^^$0|@$1|2|3|4|5|6|7|Y|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|Z|8|@]|9|@]|A|$]]|$1|D|3|E|5|F|7|10|8|@]|9|@]|A|$G|H]]|$1|I|3|J|5|6|7|11|8|@$K|12|L|13|M|N]]|9|@]|A|$]]|$1|O|3|P|5|6|7|14|8|@]|9|@]|A|$]]|$1|Q|3|R|5|F|7|15|8|@]|9|@]|A|$G|H]]|$1|S|3|T|5|6|7|16|8|@]|9|@]|A|$]]|$1|U|3|V|5|F|7|17|8|@]|9|@]|A|$G|H]]|$1|W|3|-4|5|6|7|18|8|@]|9|@]|A|$]]]|X|$]]

Just tossing it out there, but WordPress has a structure for this kind of stuff (at least WordPress was the first place I observed it, it probably originated elsewhere).

It allows limitless keys, and is faster to search than using a JSON blob, but not as fast as some of the NoSQL solutions.

<pre><code>uid | meta_key | meta_val
----------------------------------
1 name Frank
1 age 12
2 name Jeremiah
3 fav_food pizza
.................
</code></pre>

EDIT

For storing history/multiple keys

<pre><code>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
.................
</code></pre>

and query via something like this:

<pre><code>select meta_val from `table` where meta_key = 'name' and uid = 1 order by meta_id desc
</code></pre>

blocks|key|3033077|text|表上的一些时间连接将是一种开销。比方说OLAP。如果我有两个表，一个是ORDERS表，另一个是ORDER_DETAILS表。为了获得所有的订单细节，我们必须连接两个表，当表中的行数没有增加时，这将使查询速度变慢。左/右连接比内连接速度太慢。我认为如果我们在相应的ORDERS条目中添加JSON字符串/对象，就可以避免连接。添加报告生成将更快...|type|unstyled|depth|inlineStyleRanges|entityRanges|data|3033078|entityMap^0|0^^$0|@$1|2|3|4|5|6|7|D|8|@]|9|@]|A|$]]|$1|B|3|-4|5|6|7|E|8|@]|9|@]|A|$]]]|C|$]]

some time joins on the table will be an overhead. lets say for OLAP. if i have two tables one is ORDERS table and other one is ORDER_DETAILS. For getting all the order details we have to join two tables this will make the query slower when no of rows in the tables increase lets say in millions or so.. left/right join is too slower than inner join.
I Think if we add JSON string/Object in the respective ORDERS entry JOIN will be avoided. add report generation will be faster...

blocks|key|193031|text|就像大多数事情一样，“视情况而定”。将数据存储在列或JSON中本身并不是对或错/好或坏。这取决于您稍后需要如何处理它。您预测的访问此数据的方式是什么？您是否需要交叉引用其他数据？|type|unstyled|depth|inlineStyleRanges|entityRanges|data|193032|其他人已经很好地回答了什么是技术上的权衡。|193033|没有多少人讨论过你的应用程序和功能随着时间的推移而发展，以及这一数据存储决策如何影响你的团队。|193034|因为使用JSON的诱惑之一是避免迁移模式，因此如果团队不守纪律，很容易将另一个键/值对插入到JSON字段中。它没有迁移，没有人记得它是用来做什么的。上面没有验证。|193035|我的团队在postgres中使用了JSON和传统的列，一开始这是自切片面包以来最好的东西。JSON很有吸引力和强大，直到有一天我们意识到灵活性是有代价的，这突然成了一个真正的痛点。有时候，这一点很快就会出现，然后就很难改变了，因为我们已经在这个设计决策的基础上构建了很多其他的东西。|193036|加班加点，添加新功能，将数据放在JSON中会导致比我们坚持使用传统列所添加的查询更复杂的查询。因此，我们开始将某些键值放回列中，以便我们可以进行连接并在值之间进行比较。馊主意。现在我们有了副本。新的开发人员会加入进来并感到困惑吗？我应该存回的值是什么？JSON+one还是column？|193037|JSON字段成了一个垃圾抽屉，里面放着一些零碎的东西。没有数据库级别的数据验证，文档之间没有一致性或完整性。这将所有的责任都推到了应用程序中，而不是从传统的列中获取硬类型和约束检查。|193038|回过头来看，JSON让我们可以非常快速地迭代，并得到一些东西。真是太棒了。然而，当我们达到一定的团队规模后，它的灵活性也允许我们用一长串技术债务来拖累自己，这就减慢了随后的功能演化进程。请谨慎使用。|193039|仔细考虑你的数据的性质。它是你的应用程序的基础。随着时间的推移，数据将如何使用。它可能会发生怎样的变化？|193040|entityMap^0|0|0|0|0|0|0|0|0|0^^$0|@$1|2|3|4|5|6|7|T|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|U|8|@]|9|@]|A|$]]|$1|D|3|E|5|6|7|V|8|@]|9|@]|A|$]]|$1|F|3|G|5|6|7|W|8|@]|9|@]|A|$]]|$1|H|3|I|5|6|7|X|8|@]|9|@]|A|$]]|$1|J|3|K|5|6|7|Y|8|@]|9|@]|A|$]]|$1|L|3|M|5|6|7|Z|8|@]|9|@]|A|$]]|$1|N|3|O|5|6|7|10|8|@]|9|@]|A|$]]|$1|P|3|Q|5|6|7|11|8|@]|9|@]|A|$]]|$1|R|3|-4|5|6|7|12|8|@]|9|@]|A|$]]]|S|$]]

Like most things "it depends". It's not right or wrong/good or bad in and of itself to store data in columns or JSON. It depends on what you need to do with it later. What is your predicted way of accessing this data? Will you need to cross reference other data? 

Other people have answered pretty well what the technical trade-off are.

Not many people have discussed that your app and features evolve over time and how this data storage decision impacts your team. 

Because one of the temptations of using JSON is to avoid migrating schema and so if the team is not disciplined, it's very easy to stick yet another key/value pair into a JSON field. There's no migration for it, no one remembers what it's for. There is no validation on it. 

My team used JSON along side traditional columns in postgres and at first it was the best thing since sliced bread. JSON was attractive and powerful, until one day we realized that flexibility came at a cost and it's suddenly a real pain point. Sometimes that point creeps up really quickly and then it becomes hard to change because we've built so many other things on top of this design decision.

Overtime, adding new features, having the data in JSON led to more complicated looking queries than what might have been added if we stuck to traditional columns. So then we started fishing certain key values back out into columns so that we could make joins and make comparisons between values. Bad idea. Now we had duplication. A new developer would come on board and be confused? Which is the value I should be saving back into? The JSON one or the column?

The JSON fields became junk drawers for little pieces of this and that. No data validation on the database level, no consistency or integrity between documents. That pushed all that responsibility into the app instead of getting hard type and constraint checking from traditional columns.

Looking back, JSON allowed us to iterate very quickly and get something out the door. It was great. However after we reached a certain team size it's flexibility also allowed us to hang ourselves with a long rope of technical debt which then slowed down subsequent feature evolution progress. Use with caution.

Think long and hard about what the nature of your data is. It's the foundation of your app. How will the data be used over time. And how is it likely TO CHANGE?

blocks|key|2820556|text|正如其他人所指出的那样，查询将变得更慢。我建议至少添加一个'_ID‘列来进行查询。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|2820557|entityMap^0|0^^$0|@$1|2|3|4|5|6|7|D|8|@]|9|@]|A|$]]|$1|B|3|-4|5|6|7|E|8|@]|9|@]|A|$]]]|C|$]]

As others have pointed out queries will be slower. I'd suggest to add at least an '_ID' column to query by that instead.

blocks|key|193067|text|简而言之，您必须将它们混合在一起，将json用于您不打算与它们建立关系的数据，如联系人数据、地址、产品变量|type|unstyled|depth|inlineStyleRanges|entityRanges|data|193068|entityMap^0|0^^$0|@$1|2|3|4|5|6|7|D|8|@]|9|@]|A|$]]|$1|B|3|-4|5|6|7|E|8|@]|9|@]|A|$]]]|C|$]]

short answer
you have to mix between them ,
use json for data that you are not going to make relations with them like contact data , address , products variabls

I am implementing the following model for storing user related data in my table - I have 2 columns - <code>uid</code> (primary key) and a <code>meta</code> column which stores other data about the user in JSON format. 

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

Is this a better way (performance-wise, design-wise) than the one-column-per-property model, where the table will have many columns like <code>uid</code>, <code>name</code>, <code>emailid</code>. 

What I like about the first model is, you can add as many fields as possible there is no limitation. 

Also, I was wondering, now that I have implemented the first model. How do I perform a query on it, like, I want to fetch all the users who have name like 'foo'?

Question - Which is the better way to store user related data (keeping in mind that number of fields is not fixed) in database using - JSON or column-per-field? Also, if the first model is implemented, how to query database as described above? Should I use both the models, by storing all the data which may be searched by a query in a separate row and the other data in JSON (is a different row)? 

<hr>

<h2>Update</h2>

Since there won't be too many columns on which I need to perform search, is it wise to use both the models? Key-per-column for the data I need to search and JSON for others (in the same MySQL database)?

Storing JSON in database vs. having a new column for each key

SQL数据库

数据存储

PostgreSQL

数据库

服务器

WordPress

MongoDB

MySQL

NoSQL

JSON

OLAP

我正在实现以下模型来在我的表中存储与用户相关的数据-我有两列- uid (主键)和一个meta列，它以JSON格式存储有关用户的其他数据。 uid   | meta-------------------------------------------------- 1     | {name:['foo'],     ...

问将JSON存储在数据库中，而每个键都有一个新列
EN

社区

活动

资源

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问将JSON存储在数据库中，而每个键都有一个新列EN

社区

活动

资源

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问将JSON存储在数据库中，而每个键都有一个新列
EN