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

What's the best SQL datatype for storing JSON string?

存储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;
}

Which SQL datatype should we use for storing such a string containing JSON?

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

• NVARCHAR(255)?
• NVARCHAR(255)?
• TEXT?
• 文本吗?
• VARBINARY(MAX)?
• VARBINARY(MAX)?

3 个解决方案

#1

Certainly NOT:

当然不会:

• TEXT, NTEXT: those types are deprecated as of SQL Server 2005 and should not be used for new development. Use VARCHAR(MAX) or NVARCHAR(MAX) instead

  文本，NTEXT:这些类型在SQL Server 2005中已经过时，不应该用于新的开发。使用VARCHAR(MAX)或NVARCHAR(MAX)代替

• IMAGE, VARBINARY(MAX) : IMAGE is deprecated just like TEXT/NTEXT, and there's really no point in storing a text string into a binary column....

  形象,VARBINARY(MAX):图像是弃用就像文本/ NTEXT,真的是没有意义的一个文本字符串存储到一个二进制列....

So that basically leaves VARCHAR(x) or NVARCHAR(x): VARCHAR stores non-Unicode strings (1 byte per character) and NVARCHAR stores everything in a 2-byte-per-character Unicode mode. So do you need Unicode? Do you have Arabic, Hebrew, Chinese or other non-Western-European characters in your strings, potentially? Then go with NVARCHAR

这基本上就剩下VARCHAR(x)或NVARCHAR(x): VARCHAR存储非Unicode字符串(每个字符1字节)，NVARCHAR存储所有内容的方式是每个字符2字节的Unicode模式。那么您需要Unicode吗?你有阿拉伯语、希伯来语、汉语或其他非西方欧洲文字吗?然后使用NVARCHAR

The (N)VARCHAR columns come in two flavors: either you define a maximum length that results in 8000 bytes or less (VARCHAR up to 8000 characters, NVARCHAR up to 4000), or if that's not enough, use the (N)VARCHAR(MAX) versions, which store up to 2 GByte of data.

(N)VARCHAR列有两种类型:要么定义最大长度，导致8000字节以下(VARCHAR最多8000个字符，NVARCHAR最多4000个字符)，要么使用(N)VARCHAR(最大)版本，最多存储2 GByte的数据。

Update: SQL Server 2016 will have native JSON support - a new JSON datatype (which is based on nvarchar) will be introduced, as well as a FOR JSON command to convert output from a query into JSON format

更新:SQL Server 2016将具有本机JSON支持——将引入一个新的JSON数据类型(基于nvarchar)，以及一个用于JSON的命令，用于将查询输出转换为JSON格式

Update #2: in the final product, Microsoft did not include a separate JSON datatype - instead, there are a number of JSON-functions (to package up database rows into JSON, or to parse JSON into relational data) which operate on columns of type NVARCHAR(n)

更新#2:在最终的产品中，Microsoft没有包含单独的JSON数据类型—相反，有许多JSON函数(将数据库行打包为JSON，或者将JSON解析为关系数据)，它们对NVARCHAR(n)类型的列进行操作

#2

I shall go for nvarchar(max). That should fit the requirement.

我要去nvarchar(max)。这应该符合要求。

Update: With SQL Server 2016 and Azure SQL, there are a lot of additional native JSON capabilities. This might positively impact your design or approach. You may read this for more: https://docs.microsoft.com/en-us/sql/relational-databases/json/json-data-sql-server

更新:有了SQL Server 2016和Azure SQL，还有很多其他的本地JSON功能。这可能会对您的设计或方法产生积极的影响。您可以阅读以下内容:https://docs.microsoft.com/en- us/sql/relationaldatabases/json/json -data-sql-server

#3

I would recommend to use nvarchar(max) if you plan to use JSON features on SQL 2016 or Azure SQL.

如果您打算在SQL 2016或Azure SQL上使用JSON特性，我建议使用nvarchar(max)。

If you don't plan to use those features, you could use varbinary(max) combined with COMPRESS (and DECOMPRESS) functions. More information: https://blogs.msdn.microsoft.com/sqlserverstorageengine/2015/11/23/storing-json-in-sql-server/

如果不打算使用这些特性，可以使用varbinary(max)结合COMPRESS(和DECOMPRESS)函数。更多信息:https://blogs.msdn.microsoft.com/sqlserverstorageengine/2015/11/23/storing-json-in-sql-server/。

COMPRESS and DECOMPRESS functions use standard GZip compression. If your client can handle GZip compression (e.g browser that understands gzip content), you can directly return compressed content. Note that this is performance/storage trade-off. If you frequently query compressed data you mig have slower performance because text must be decompressed each time.

压缩和解压函数使用标准的GZip压缩。如果您的客户端能够处理GZip压缩(e)。g浏览器理解gzip内容)，您可以直接返回压缩内容。注意，这是性能/存储的权衡。如果经常查询压缩数据，mig的性能就会较慢，因为每次都必须对文本进行解压缩。