什么是用于存储JSON字符串的最佳SQL数据类型? [英] What's best SQL datatype for storing JSON string?

问题描述

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

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

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

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

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

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

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

推荐答案

肯定不:

• TEXT, NTEXT:从SQL Server 2005开始，这些类型已弃用，并且不应用于新开发.使用VARCHAR(MAX)或NVARCHAR(MAX)代替

• 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

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

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....

因此基本上离开了VARCHAR(x)或NVARCHAR(x):VARCHAR存储非Unicode字符串(每个字符1个字节)，而NVARCHAR以2个字符/字符的Unicode模式存储所有内容.那么，您需要Unicode吗?您的字符串中是否可能包含阿拉伯文，希伯来文，中文或其他非西欧字符?然后使用NVARCHAR

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

(N)VARCHAR列有两种形式:您定义的最大长度导致8000个字节或更少(VARCHAR最多8000个字符，NVARCHAR最多4000个字符)，或者如果不够用，请使用(N)VARCHAR(MAX)版本，最多可存储2 GB数据.

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.

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

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

更新#2:，在最终产品中，Microsoft没有包括单独的JSON数据类型-而是有许多JSON函数(用于将数据库行打包为JSON，或者将JSON解析为关系数据)，该操作在NVARCHAR(n)

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)

这篇关于什么是用于存储JSON字符串的最佳SQL数据类型?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！