存储JSON字符串最佳的SQL数据类型是什么？

Question

存储JSON字符串的最佳SQL数据类型是什么？

static List<ProductModel> CreateProductList()
{
    string json = @"[
        {
            ProductId: 1, 
            ProductCode: 'A', 
            Product: 'A'
        },
        {
            ProductId: 2, 
            ProductCode: 'B', 
            Product: 'B'
        }
    ]";

    IList<JToken> tokenList = JToken.Parse(json).ToList();
    List<ProductModel> productList = new List<ProductModel>();

    foreach (JToken token in tokenList)
    {
        productList.Add(JsonConvert.DeserializeObject<ProductModel>(token.ToString()));
    }

    return productList;
}

存储包含JSON的字符串应该使用哪种SQL数据类型？

• NVARCHAR(255)？
• TEXT？
• VARBINARY(MAX)？

- DatPT

---

只是一些随意的噪音（注释，而不是数据）：您可能还想将其压缩。在这种情况下，您需要一些二进制内容。另一方面，为什么不为数据设计适当的表格呢？ - The Nail

@The Nail：有时将某些内容存储为JSON格式（或者称之为“文档”）是符合需求的。比如工作流引擎或文档管理等需要使用该方法。我正在一个项目中实现此功能，实际上是从关系型数据库转换到文档形式以实现CQRS命令端。如果使用诸如ServiceStack或JSON.Net的序列化器，速度非常快。 - swannee

Answer 1

绝对不要使用以下类型：

• TEXT和NTEXT：这些类型自SQL Server 2005起被弃用，并且不应在新开发中使用。请改用VARCHAR(MAX)或NVARCHAR(MAX)

• IMAGE和VARBINARY(MAX)：IMAGE与TEXT/NTEXT一样已被弃用，将文本字符串存储到二进制列中没有任何意义...

那么基本上只剩下VARCHAR(x)或NVARCHAR(x)了：VARCHAR存储非Unicode字符串（每个字符1字节），而NVARCHAR则以每个字符2字节的Unicode模式存储所有内容。所以您需要Unicode码吗？您的字符串中是否可能有阿拉伯语、希伯来语、汉语或其他非西欧语言的字符？如果是，请使用NVARCHAR

(N)VARCHAR列有两种类型：您可以定义最大长度，结果为8000字节或更少（VARCHAR最多8000个字符，NVARCHAR最多4000个字符），或者如果这不够用，使用(N)VARCHAR(MAX)版本，可以存储高达2GB的数据。

更新：SQL Server 2016将具有原生JSON支持-引入了一个新的JSON数据类型（基于nvarchar），以及一个FOR JSON命令，可将查询输出转换为JSON格式。

更新#2：在最终产品中，Microsoft没有包括单独的JSON数据类型-相反，有许多JSON函数（将数据库行打包成JSON，或将JSON解析为关系型数据），这些函数对NVARCHAR(n)类型的列进行操作。

- marc_s

---

作为 SQL Server 2016 的本地 JSON 支持，NVARCHAR 应该是首选的选择。请参考 http://blogs.msdn.com/b/jocapc/archive/2015/05/16/json-support-in-sql-server-2016.aspx。 - Loudenvier

@marc_s 你的 "update" 语句正确吗？我找不到任何官方的 JSON 数据类型...？ - Nix

@Nix：我认为最终，SQL Server支持在NVARCHAR(n)数据类型上操作的JSON函数。 - marc_s

你可能需要更新你的回答，不要声明有一个Json数据类型。 - Nix

varbinary(max) 可以在使用压缩时使用。 - Marat Gallyamov

正如Marat所提到的，我使用VARBINARY（max）和压缩将存储占用减少了80％。虽然压缩/解压需要更多时间，但在这种情况下，我的存储问题超过了性能。如果您有大型对象，则值得研究一下。 - faddison

Answer 2

我将选择使用 nvarchar(max)。这应该可以满足需求。 更新： 在SQL Server 2016和Azure SQL中，有许多额外的本地JSON功能。这可能会对您的设计或方法产生积极的影响。您可以阅读此文了解更多信息：https://learn.microsoft.com/en-us/sql/relational-databases/json/json-data-sql-server。

- Kangkan

---

你真的需要每个字符占用2字节的Unicode存储吗？根据你的数据情况，这可能会浪费两倍的存储空间...(但是如果你确实需要Unicode，那么这是唯一可行的方式，我同意！) - marc_s

nvarchar是因为数据未定义。如果我们认为系统不需要Unicode，我们可以避免使用nvarchar，而改用varchar(max)。 - Kangkan

此外，使用 nvarchar 可避免使用 varchar 时可能出现的排序问题，但在查询性能方面会比 varchar 慢。更多信息请参阅DBA Stack Exchange 的此问题。 - Scotty.NET

这个问题为什么会有这么多赞？它说要使用哪种数据类型，没问题...但它甚至没有尝试解释为什么那是正确的选择。 - stakx - no longer contributing

你可以始终使用varchar并转义任何Unicode字符。如果您的文本中只偶尔包含Unicode字符，则这是一个不错的方法，因为它比使用nvarchar节省空间。 - chrisb

Answer 3

推荐使用NVARCHAR数据类型。
请参考https://learn.microsoft.com/zh-cn/sql/relational-databases/json/json-data-sql-server?view=sql-server-ver15#store-and-index-json-data-in-sql-server][1]

- Prabhazeal

Answer 4

如果您计划在 SQL 2016 或 Azure SQL 上使用 JSON 功能，我建议使用 nvarchar(max)。如果您不打算使用这些功能，可以结合 COMPRESS（和 DECOMPRESS）函数使用 varbinary(max)。更多信息请参见：https://blogs.msdn.microsoft.com/sqlserverstorageengine/2015/11/23/storing-json-in-sql-server/

COMPRESS 和 DECOMPRESS 函数使用标准的 GZip 压缩。如果您的客户端可以处理 GZip 压缩（例如理解 gzip 内容的浏览器），则可以直接返回压缩内容。请注意，这是性能/存储权衡。如果您经常查询压缩数据，则可能会导致性能较慢，因为每次都必须解压文本。

- Marat Gallyamov

---

SQL 2016 中的 JSON 功能有哪些？ - Kiquenet