将JSON内容存储为字符串的数据类型应该是正确的?

时间:2022-12-08 11:42:43

I plan to use JSON.NET to serialize and deserialize objects to and with the json string store it in the database.

我计划使用JSON.NET对对象进行序列化和反序列化,并将json字符串存储在数据库中。

What datatype of the SQL Server 2008 should be the best to store it?

什么数据类型的SQL Server 2008应该最好存储它?

  • nvarchar(max)?
  • 为nvarchar(最大)?
  • varchar(max)?
  • VARCHAR(最大值)?

3 个解决方案

#1


3  

Will your json be multilingual? If yes - go with nvarchar. If not - go with varchar, it uses less storage space and is more efficient in terms of performance.

你的json会多语言吗?如果是 - 请使用nvarchar。如果不是 - 使用varchar,它会占用更少的存储空间,并且在性能方面更有效。

#2


2  

You should probably use nvarchar(max).

您应该使用nvarchar(max)。

http://www.ietf.org/rfc/rfc4627.txt

http://www.ietf.org/rfc/rfc4627.txt

The preferred encoding for JSON is UTF-8, but UTF-16 and UTF-32 are also possible, so you may be getting data which would include wide characters.

JSON的首选编码是UTF-8,但也可以使用UTF-16和UTF-32,因此您可能会获得包含宽字符的数据。

On the other hand, you could also use varbinary(max), store the actual UTF-8 stream and save some space and processing since the UTF-8 stream wouldn't need to be converted to an actual string in .NET (i.e. no UTF-8 to Unicode internal .NET string).

另一方面,您也可以使用varbinary(max),存储实际的UTF-8流并节省一些空间和处理,因为UTF-8流不需要转换为.NET中的实际字符串(即没有UTF-8到Unicode内部.NET字符串)。

I would not use varchar(max) because if you do have wide characters coming in then they could be misinterpreted by any queries which are not converting the UTF-8 which happens to be stored in varchar properly (assuming 8-bit varchar because you can also store multi-byte in varchar depending upon your database settings), and you wouldn't have to worry about converting the UTF-8 to the particular code page for your varchar. Again, nvarchar is Unicode so you wouldn't have to worry about any of that mess.

我不会使用varchar(max),因为如果你确实有广泛的字符,那么它们可能会被任何未转换UTF-8的查询错误解释,而UTF-8恰好存储在varchar中(假设8位varchar,因为你可以还可以在varchar中存储多字节,具体取决于您的数据库设置),您不必担心将UTF-8转换为varchar的特定代码页。同样,nvarchar是Unicode,因此您不必担心任何混乱。

Are you going to do any operations on the JSON in SQL Server or just storage?

您是要对SQL Server中的JSON还是仅存储进行任何操作?

#3


0  

varchar by 10 times. Faster and better.

varchar 10倍。更快更好。

#1


3  

Will your json be multilingual? If yes - go with nvarchar. If not - go with varchar, it uses less storage space and is more efficient in terms of performance.

你的json会多语言吗?如果是 - 请使用nvarchar。如果不是 - 使用varchar,它会占用更少的存储空间,并且在性能方面更有效。

#2


2  

You should probably use nvarchar(max).

您应该使用nvarchar(max)。

http://www.ietf.org/rfc/rfc4627.txt

http://www.ietf.org/rfc/rfc4627.txt

The preferred encoding for JSON is UTF-8, but UTF-16 and UTF-32 are also possible, so you may be getting data which would include wide characters.

JSON的首选编码是UTF-8,但也可以使用UTF-16和UTF-32,因此您可能会获得包含宽字符的数据。

On the other hand, you could also use varbinary(max), store the actual UTF-8 stream and save some space and processing since the UTF-8 stream wouldn't need to be converted to an actual string in .NET (i.e. no UTF-8 to Unicode internal .NET string).

另一方面,您也可以使用varbinary(max),存储实际的UTF-8流并节省一些空间和处理,因为UTF-8流不需要转换为.NET中的实际字符串(即没有UTF-8到Unicode内部.NET字符串)。

I would not use varchar(max) because if you do have wide characters coming in then they could be misinterpreted by any queries which are not converting the UTF-8 which happens to be stored in varchar properly (assuming 8-bit varchar because you can also store multi-byte in varchar depending upon your database settings), and you wouldn't have to worry about converting the UTF-8 to the particular code page for your varchar. Again, nvarchar is Unicode so you wouldn't have to worry about any of that mess.

我不会使用varchar(max),因为如果你确实有广泛的字符,那么它们可能会被任何未转换UTF-8的查询错误解释,而UTF-8恰好存储在varchar中(假设8位varchar,因为你可以还可以在varchar中存储多字节,具体取决于您的数据库设置),您不必担心将UTF-8转换为varchar的特定代码页。同样,nvarchar是Unicode,因此您不必担心任何混乱。

Are you going to do any operations on the JSON in SQL Server or just storage?

您是要对SQL Server中的JSON还是仅存储进行任何操作?

#3


0  

varchar by 10 times. Faster and better.

varchar 10倍。更快更好。