Skip to main content
 首页 » 编程设计

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

2024年04月30日11zhoujg

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

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

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

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

请您参考如下方法:

主要问题是

  • 你打算用这些数据做什么?和
  • 你如何过滤/排序/加入/操作这些数据?

  • 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 方法是 SUBSTRINGCHARINDEX 的组合:-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