SQL 将 JSON 存储在 msSQL 数据库中？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/2497678/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Storing JSON in an msSQL database?
提问by JKirchartz
I'm developing a form generator, and wondering if it would be bad mojo to store JSON in an SQL database?
我正在开发一个表单生成器，想知道将 JSON 存储在 SQL 数据库中是否会很糟糕？
I want to keep my database & tables simple, so I was going to have
我想让我的数据库和表保持简单，所以我打算
`pKey, formTitle, formJSON`
on a table, and then store
放在桌子上，然后存放
{["firstName":{"required":"true","type":"text"},"lastName":{"required":"true","type":"text"}}
in formJSON.
以 JSON 格式。
Any input is appreciated.
任何输入表示赞赏。
回答by Bryan Thrasher
I use JSON extensively in my CMS (which hosts about 110 sites) and I find the speed of access data to be very fast. I was surprised that there wasn't more speed degradation. Every object in the CMS (Page, Layout, List, Topic, etc) has an NVARCHAR(MAX) column called JSONConfiguration. My ORM tool knows to look for that column and reconstitute it as an object if needed. Or, depending on the situation, I will just pass it to the client for jQuery or Ext JS to process.
我在我的 CMS（托管大约 110 个站点）中广泛使用 JSON，我发现访问数据的速度非常快。我很惊讶没有更多的速度下降。CMS 中的每个对象（页面、布局、列表、主题等）都有一个名为 JSONConfiguration 的 NVARCHAR(MAX) 列。我的 ORM 工具知道查找该列并在需要时将其重组为对象。或者，根据情况，我只是将其传递给客户端以供 jQuery 或 Ext JS 处理。
As for readability / maintainability of my code, you might say it's improved because I now have classes that represent a lot of the JSON objects stored in the DB.
至于我的代码的可读性/可维护性，您可能会说它得到了改进，因为我现在拥有表示存储在数据库中的许多 JSON 对象的类。
I used JSON.net for all serialization / deserialization. https://www.newtonsoft.com/json
我使用 JSON.net 进行所有序列化/反序列化。https://www.newtonsoft.com/json
I also use a single query to return meta-JSON with the actual data. As in the case of Ext JS, I have queries that return both the structure of the Ext JS object as well as the data the object will need. This cuts out one post back / SQL round trip.
我还使用单个查询返回带有实际数据的元 JSON。与 Ext JS 的情况一样，我有一些查询既返回 Ext JS 对象的结构，又返回该对象需要的数据。这减少了一次回传/SQL 往返。
I was also surprised at how fast the code was to parse a list of JSON objects and map them into a DataTable object that I then handed to a GridView.
我也惊讶于代码解析 JSON 对象列表并将它们映射到 DataTable 对象的速度如此之快，然后我将其交给了 GridView。
The only downside I've seen to using JSON is indexing. If you have a property of the JSON you need to search, then you have to store it as a separate column.
我发现使用 JSON 的唯一缺点是索引。如果您有需要搜索的 JSON 属性，则必须将其存储为单独的列。
There are JSON DB's out there that might server your needs better: CouchDB, MongoDB, and Cassandra.
有一些 JSON DB 可以更好地满足您的需求：CouchDB、MongoDB 和 Cassandra。
回答by root
A brilliant way to make an object database from sql server. I do this for all config objects and everything else that doesn't need any specific querying. extending your object - easy, just create a new property in your class and init with default value. Don't need a property any more? Just delete it in the class. Easy roll out, easy upgrade. Not suitable for all objects, but if you extract any prop you need to index on - keep using it. Very modern way of using sql server.
从 sql server 创建对象数据库的绝妙方法。我为所有配置对象以及不需要任何特定查询的所有其他对象执行此操作。扩展您的对象 - 简单，只需在您的类中创建一个新属性并使用默认值进行初始化。不再需要房产？把它在课堂上删除就行了。轻松推出，轻松升级。不适合所有对象，但如果您提取任何需要索引的道具 - 继续使用它。使用 sql server 的非常现代的方式。
回答by Amy B
It will be slower than having the form defined in code, but one extra query shouldn't cause you much harm. (Just don't let 1 extra query become 10 extra queries!)
它会比在代码中定义表单慢，但是一个额外的查询应该不会对您造成太大伤害。（只是不要让 1 个额外的查询变成 10 个额外的查询！）
Edit: If you are selecting the row by formTitleinstead of pKey(I would, because then your code will be more readable), put an index on formTitle
编辑：如果您选择行formTitle而不是pKey（我会，因为那样您的代码将更具可读性），请在其上放置一个索引formTitle
回答by Charlie RC
We have used a modified version of XML for exactly the purpose you decribe for seven or eight years and it works great. Our customers' form needs are so diverse that we could never keep up with a table/column approach. We are too far down the XML road to change very easily but I think JSON would work as well and maybe evan better.
七八年来，我们一直在为您所描述的目的使用修改过的 XML 版本，并且效果很好。我们客户的表单需求是如此多样化，以至于我们永远无法跟上表/列方法。我们在 XML 的道路上走得太远了，不能很容易地改变，但我认为 JSON 也能工作，而且可能会更好。
Reporting is no problem with a couple of good parsing functions and I would defy anyone to find a significant difference in performance between our reporting/analytics and a table/column solution to this need.
报告对几个好的解析函数没有问题，我会无视任何人发现我们的报告/分析和满足此需求的表/列解决方案之间的性能显着差异。
回答by Daniel
You should be able to use SisoDb for this. http://sisodb.com
您应该能够为此使用 SisoDb。http://sisodb.com
回答by Abe Miessler
I wouldn't recommend it.
我不会推荐它。
If you ever want to do any reporting or query based on these values in the future it's going to make your life a lot harder than having a few extra tables/columns.
如果您将来想根据这些值进行任何报告或查询，那么与拥有一些额外的表/列相比，您的生活将变得更加艰难。
Why are you avoiding making new tables? I say if your application requires them go ahead and add them in... Also if someone has to go through your code/db later it's probably going to be harder for them to figure out what you had going on (depending on what kind of documentation you have).
为什么要避免创建新表？我说如果你的应用程序需要他们继续添加它们......此外，如果有人稍后必须通过你的代码/数据库，他们可能会更难弄清楚你发生了什么（取决于什么样的您拥有的文件）。
回答by LastTribunal
I think it not an optimal idea to store object data in a string in SQL. You have to do transformation outside of SQL in order to parse it. That presents a performance issue and you lose the leverage of using SQL native data parsing capability. A better way would be to store JSON as an XML datatype in SQL. This way, you kill two birds with one stone: You don't have to create shit load of tables and still get all the native querying benefits of SQL.
我认为将对象数据存储在 SQL 中的字符串中并不是一个最佳的主意。您必须在 SQL 之外进行转换才能解析它。这会带来性能问题，并且您将失去使用 SQL 本机数据解析功能的优势。更好的方法是将 JSON 作为 XML 数据类型存储在 SQL 中。通过这种方式，您可以一举两得：您不必创建大量的表，并且仍然可以获得 SQL 的所有本机查询优势。
