什么时候可以将JSON或XML数据保存在SQL表中

什么时候可以将JSON或XML数据保存在SQL表中

主要问题是

• 您将如何处理这些数据？和
• 您如何过滤/排序/合并/处理此数据？

JSON（如XML）非常适合数据交换，小型存储和通用定义的结构，但它不能参与在RDBMS中运行的典型 *** 作。在大多数情况下，将JSON数据传输到 普通表中
并在需要时重新创建JSON 会更好。

XML / JSON和 1.NF

规范化的第一条规则规定，决不要将多于一位的信息存储到一列中。您看到带有“ Mickey Mouse”之类的值的“
PersonName”列吗？您指向此并哭了： 立即更改！

XML或JSON呢？这些类型是否会破坏1.NF？好吧，是的，不是…

如果实际上只存储 一小部分信息， 则可以将一个完整的结构存储为 一小部分信息
。您会得到一个SOAP响应并想要存储它，因为您可能需要它作为以后的参考（但您 不会将这些数据用于自己的进程 ）？只需 按原样 存储它即可！

现在，想象一个 代表人 的 复杂结构（XML或JSON） （及其地址，更多详细信息…）。现在，将其
放在的一列中

PersonInCharge

。错了吗 难道这不应该存在于具有外键引用而不是XML /
JSON的经过适当设计的相关表中吗？特别是如果同一个人可能出现在许多不同的行中，那么使用XML / JSON方法肯定是错误的。

但是现在想象一下需要存储历史数据。您要在给定的时间段内 保留 该人的数据。几天后，对方告诉您新地址？没问题！如果您需要，旧地址将保存在XML /
JSON中…

结论： 如果存储数据只是为了保留它，就可以了。如果这些数据是 唯一的 部分，那没关系。
但是如果您需要定期使用 内部零件 ，或者这意味着多余的重复存储，那就不好了。

实体储存

以下内容适用于SQL Server，在其他RDBM上可能有所不同。

XML不是存储为您看到的文本，而是存储为层次结构树。查询这是惊人的好表现！不会在字符串级别解析此结构！
SQL
Server（2016+）中的JSON存在一个字符串中，必须进行解析。没有真正的本机JSON类型（例如，有本机XML类型）。这可能会在以后出现，但是现在我假设JSON在SQL
Server上的性能不如XML（请参阅 UPDATe 2 一节）。任何需要从JSON读取值的 *** 作都将需要大量隐藏的字符串方法调用…

这对您意味着什么？

您 可爱的数据库艺术家 :-D知道， _ 按原样*_ 存储 _JSON *_违反RDBM的通用原则。 他知道，

• JSON很可能会破坏1.NF
• JSON可能会随时间变化（同一列，不同内容）。
• JSON不易阅读，并且很难对其进行过滤/搜索/加入或排序。
• 这样的 *** 作会将相当多的额外负载转移到可怜的小型DB服务器上

有一些解决方法（取决于您所使用的RDBMS），但是大多数方法都无法按照您希望的方式工作…

简而言之，您的问题的答案

是

• 如果您 不想使用数据，该数据存储 在 JSON中以进行昂贵的 *** 作（过滤器/联接/排序）。
  您可以像存储任何其他 仅存在的 内容一样存储它。我们将许多图片存储为BLOB，但是我们不会尝试过滤所有带有花朵的图片…

• 如果您完全不打扰里面的内容（只需将其存储并读取为一点信息即可）

• 如果结构是可变的，这将使创建物理表变得更加困难，然后将其与JSON数据一起使用。
• 如果该结构是深层嵌套的，则物理表中的存储将产生大量开销

没有

• 如果要像使用内部数据一样使用内部表的数据（过滤器，索引，联接…）
• 如果要存储重复项（创建冗余）
• 通常：如果您遇到性能问题（可以肯定的是，在许多典型情况下您都会面对它们！）

您可以在字符串列中以JSON开头或以BLOB开头，并在需要时将其更改为物理表。我的魔幻水晶球告诉我，这可能是明天:-D

更新：有关性能的更多信息…

以下内容解决了SQL Server 2016中的JSON和XML支持

用户@
mike123指向微软官方博客上的一篇文章，该文章似乎在实验中得到了证明，与在SQL Server中 查询XML相比 ，
查询JSON的 速度快10倍 。

关于此的一些想法：

与“实验”进行一些核对：

• 在 “实验”的措施很多，但不是XML与JSON的性能 。反复对相同（不变）的字符串重复进行相同 *** 作是不现实的情况
• 一般而言 ，经过测试的示例非常 简单 ！
• 读取的值始终相同，甚至不使用。优化器将看到此…
• 关于强大的

  XQuery

  支持一言不发！在数组中找到具有给定ID的产品？JSON需要读取全部内容，并在使用之后使用过滤器

  WHERe

  ，同时

  XML

  允许内部使用

  XQuery predicate

  。不说

  FLWOR

  …
• 在“实验”的代码 作为是 我的系统上带来了：JSON似乎更快（但不是10倍）为3倍。
• 添加

  /text()

  到会将其

  XPath

  减少到小于2x。在相关文章中，用户“ Mister Magoo”已经指出了这一点，但是 点击诱饵的 标题仍然没有改变。
• 通过“实验”中提供的简单JSON，最快的纯T-SQL方法是

  SUBSTRING

  and 的组合

  CHARINDEX

  ：-D

以下代码将显示更实际的实验

• 使用一个JSON和一个以上具有多个XML的相同XML

  Product

  （JSON数组与同级节点）
• JSON和XML稍有变化（10000个运行数字），并已插入表中。
• 两个表都有初始调用反对表，以避免 首次调用偏见
• 读取所有10000个条目，并将检索到的值插入到另一个表中。
• 使用

  GO 10

  将遍历此块十次，以避免出现 首次通话偏见

最终结果清楚地表明，JSON比XML慢 （不是那么多，在一个非常简单的示例中约为1.5倍）。

最后的声明：

• 在过度的情况下，通过过于简化的示例，JSON可能比XML更快
• 处理JSON是 纯字符串 *** 作 ，而XML被解析和转换。在第一步中，这是相当昂贵的，但是一旦完成，它将加快一切。
• 一次性执行一次 JSON可能更好（避免了创建XML的内部层次结构表示的开销）
• 通过一个仍然非常简单但更现实的示例，XML的简单阅读速度将会更快
• 每当需要从数组中读取特定元素，过滤数组中包含给定ProductID的所有条目，或在路径中上下移动时，JSON都无法阻止。必须从字符串中完全解析出它-每次必须抓住它时…

测试代码

USE master;GO--create a clean databaseCREATE DATAbase TestJsonXml;GOUSE TestJsonXml;GO--create tablesCREATE TABLE TestTbl1(ID INT IDENTITY,SomeXml XML);CREATE TABLE TestTbl2(ID INT IDENTITY,SomeJson NVARCHAr(MAX));CREATE TABLE Target1(SomeString NVARCHAr(MAX));CREATE TABLE Target2(SomeString NVARCHAr(MAX));CREATE TABLE Times(Test VARCHAr(10),Diff INT)GO--insert 10000 XMLs into TestTbl1WITH Tally AS(SELECT TOP 10000 ROW_NUMBER() OVER(ORDER BY (SELECT NULL))*2 AS Nmbr FROM master..spt_values AS v1 CROSS APPLY master..spt_values AS v2)INSERT INTO TestTbl1(SomeXml)SELECt N'<Root>    <Products>    <ProductDescription>        <Features> <Maintenance>' + CAST(Nmbr AS NVARCHAr(10)) + ' year parts and labor extended maintenance is available</Maintenance> <Warranty>1 year parts and labor</Warranty>        </Features>        <ProductID>' + CAST(Nmbr AS NVARCHAr(10)) + '</ProductID>        <ProductName>Road Bike</ProductName>    </ProductDescription>    <ProductDescription>        <Features> <Maintenance>' + CAST(Nmbr + 1 AS NVARCHAr(10)) + ' blah</Maintenance> <Warranty>1 year parts and labor</Warranty>        </Features>        <ProductID>' + CAST(Nmbr + 1 AS NVARCHAr(10)) + '</ProductID>        <ProductName>Cross Bike</ProductName>    </ProductDescription>    </Products></Root>'FROM Tally;--insert 10000 JSONs into TestTbl2WITH Tally AS(SELECt TOP 10000 ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) AS Nmbr FROM master..spt_values AS v1 CROSS APPLY master..spt_values AS v2)INSERT INTO TestTbl2(SomeJson)SELECt N'{    "Root": {        "Products": { "ProductDescription": [     {         "Features": {  "Maintenance": "' + CAST(Nmbr AS NVARCHAr(10)) + ' year parts and labor extended maintenance is available",  "Warranty": "1 year parts and labor"         },         "ProductID": "' + CAST(Nmbr AS NVARCHAr(10)) + '",         "ProductName": "Road Bike"     },     {         "Features": {  "Maintenance": "' + CAST(Nmbr + 1 AS NVARCHAr(10)) + ' blah",  "Warranty": "1 year parts and labor"         },         "ProductID": "' + CAST(Nmbr + 1 AS NVARCHAr(10)) + '",         "ProductName": "Cross Bike"     } ]        }    }}'FROM Tally;GO--Do some initial action to avoid first-call-biasINSERT INTO Target1(SomeString)SELECt SomeXml.value('(/Root/Products/ProductDescription/Features/Maintenance/text())[1]', 'nvarchar(4000)')FROM TestTbl1;INSERT INTO Target2(SomeString)SELECt JSON_VALUE(SomeJson, N'$.Root.Products.ProductDescription[0].Features.Maintenance')FROM TestTbl2;GO--Start the testDECLARE @StartDt DATETIME2(7), @EndXml DATETIME2(7), @EndJson DATETIME2(7);--Read all ProductNames of the second product and insert them to Target1SET @StartDt = SYSDATETIME();INSERT INTO Target1(SomeString)SELECt SomeXml.value('(/Root/Products/ProductDescription/ProductName/text())[2]', 'nvarchar(4000)')FROM TestTbl1ORDER BY NEWID();--remember the time spentINSERT INTO Times(Test,Diff)SELECt 'xml',DATEDIFF(millisecond,@StartDt,SYSDATETIME());--Same with JSON into Target2SET @StartDt = SYSDATETIME();INSERT INTO Target2(SomeString)SELECT JSON_VALUE(SomeJson, N'$.Root.Products.ProductDescription[1].ProductName')FROM TestTbl2ORDER BY NEWID();--remember the time spentINSERT INTO Times(Test,Diff)SELECt 'json',DATEDIFF(millisecond,@StartDt,SYSDATETIME());GO 10 --do the block above 10 times--Show the resultSELECT Test,SUM(Diff) AS SumTime, COUNT(Diff) AS CountTimeFROM TimesGROUP BY Test;GO--clean upUSE master;GODROP DATAbase TestJsonXml;GO

结果（Acer Aspire v17 Nitro Intel i7、8GB Ram上的SQL Server 2016 Express）

Test    SumTime ------------------json    2706    xml     1604