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

Question

当使用 SQL 或 MySQL(或任何与此相关的关系数据库)时 - 我知道将数据保存在常规列中更适合索引和其他目的......

事情是加载和保存 JSON 数据有时要简单得多 - 并使开发更容易。

在数据库中保存原始 JSON 数据是否有任何“黄金法则”？

这样做绝对是一种不好的做法吗？

Answer 1

主要问题是

你打算用这些数据做什么？和

你如何过滤/排序/加入/操作这些数据？

JSON(如 XML)非常适合数据交换、小型存储和通用定义的结构，但它不能参与您在 RDBMS 中运行的典型操作。在大多数情况下，最好将 JSON 数据传输到普通表中，并在需要时重新创建 JSON。
XML/JSON 和 1.NF
规范化的第一条规则规定，永远不要将多于一位的信息存储到一列中。您看到“PersonName”列的值类似于“Mickey Mouse”吗？你指着这个哭:立即改变!
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 那样高效(请参阅更新 2 部分)。任何需要从 JSON 中读取值都需要大量隐藏的字符串方法调用......
这对你意味着什么？
您可爱的 DB 艺术家 :-D 知道，将 JSON 存储为 违反了 RDBM 的通用原则。他知道，

JSON 很可能会破坏 1.NF

JSON 可能会随时间变化(同一列，不同的内容)。

JSON 不容易阅读，并且很难过滤/搜索/加入或排序。

此类操作会将相当多的额外负载转移到可怜的小型 DB 服务器上

有一些变通方法(取决于您使用的 RDBMS)，但其中大部分都无法按照您希望的方式工作...
简短回答您的问题
是

如果您 不想使用存储在您的 JSON 中的数据 用于昂贵的操作(过滤器/连接/排序)。
您可以像任何其他仅存在的内容一样存储它。我们将许多图片存储为 BLOB，但我们不会尝试过滤所有带有花的图像...

如果你根本不关心里面的东西(只需存储它并作为一位信息读取它)

如果结构是可变的，这将使创建物理表然后处理 JSON 数据变得更加困难。

如果结构嵌套很深，物理表的存储开销太大

否

如果您想像使用关系表的数据(过滤器、索引、连接...)一样使用内部数据

如果您要存储重复项(创建冗余)

一般而言:如果您遇到性能问题(在许多典型场景中您肯定会遇到这些问题!)

您可以从字符串列中的 JSON 或 BLOB 开始，并在需要时将其更改为物理表。我的魔法 Crystal 球告诉我，这可能是明天 :-D
更新
在此处查找有关性能和磁盘空间的一些想法: https://stackoverflow.com/a/47408528/5089204
更新 2:有关性能的更多信息...
以下解决了 SQL-Server 2016 中的 JSON 和 XML 支持
用户@mike123 指向一个 article on an official microsoft blog，这似乎在实验中证明，查询 JSON 的 比在 SQL-Server 中查询 XML 快 10 倍。
对此的一些想法:
与“实验”的一些交叉检查:

“实验”测量了很多，但不是 XML 与 JSON 的性能。对相同(未更改)的字符串重复执行相同的操作是不现实的场景

测试的例子是 远到简单的一般语句 !

读取的值始终相同，甚至未使用。优化器会看到这个...

对强大的 XQuery 支持一言不发!在数组中查找具有给定 ID 的产品？ JSON 需要读取全部内容，然后使用 WHERE 使用过滤器，而 XML 将允许内部 XQuery predicate 。更不用说 FLWOR ...

我系统上的“实验”代码显示:JSON 似乎快了 3 倍(但不是 10 倍)。

将 /text() 添加到 XPath 将其减少到小于 2x 0x251819212134。在相关文章中用户“Mister Magoo”已经指出了这一点，但点击诱饵标题仍然没有改变......

使用“实验”中给出的如此简单的 JSON，最快的纯 T-SQL 方法是 SUBSTRING 和 CHARINDEX 的组合:-D 0x2919121213

下面的代码将展示一个更真实的实验

使用 JSON 和具有多个 Product(JSON 数组与同级节点)的相同 XML

JSON 和 XML 略有变化(10000 个运行数字)并插入到表中。

有一个初始调用 agaist 两个表以避免首次调用偏差

读取所有 10000 个条目并将检索到的值插入到另一个表中。

使用 GO 10 将运行此块十次以避免首次调用偏差

最终结果清楚地表明，JSON 比 XML 慢(没有那么多，在一个仍然非常简单的例子中大约是 1.5 倍)。
最后声明:

在不适当的情况下用一个过于简化的例子，JSON 可能比 XML 快

处理 JSON 是纯字符串 Action ，而 XML 是解析转换的。这在第一个 Action 中相当昂贵，但一旦完成，就会加速一切。

JSON 在一次性操作中可能更好(避免创建 XML 的内部分层表示的开销)

使用一个仍然非常简单但更现实的示例 XML 在简单读取时会更快

每当需要从数组中读取特定元素、过滤数组中包含给定 ProductID 的所有条目或在路径上上下导航时，JSON 都无法支持。它必须完全从字符串中解析出来 - 每次你必须捕获它......

测试代码

USE master;
GO
--create a clean database
CREATE DATABASE TestJsonXml;
GO
USE TestJsonXml;
GO
--create tables
CREATE 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 TestTbl1
WITH 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 TestTbl2
WITH 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-bias
INSERT 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 test
DECLARE @StartDt DATETIME2(7), @EndXml DATETIME2(7), @EndJson DATETIME2(7);

--Read all ProductNames of the second product and insert them to Target1
SET @StartDt = SYSDATETIME();
INSERT INTO Target1(SomeString)
SELECT SomeXml.value('(/Root/Products/ProductDescription/ProductName/text())[2]', 'nvarchar(4000)')
FROM TestTbl1
ORDER BY NEWID();
--remember the time spent
INSERT INTO Times(Test,Diff)
SELECT 'xml',DATEDIFF(millisecond,@StartDt,SYSDATETIME());

--Same with JSON into Target2
SET @StartDt = SYSDATETIME();
INSERT INTO Target2(SomeString)
SELECT JSON_VALUE(SomeJson, N'$.Root.Products.ProductDescription[1].ProductName')
FROM TestTbl2
ORDER BY NEWID();
--remember the time spent
INSERT INTO Times(Test,Diff)
SELECT 'json',DATEDIFF(millisecond,@StartDt,SYSDATETIME());

GO 10 --do the block above 10 times

--Show the result
SELECT Test,SUM(Diff) AS SumTime, COUNT(Diff) AS CountTime
FROM Times
GROUP BY Test;
GO
--clean up
USE master;
GO
DROP DATABASE TestJsonXml;
GO

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

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