C# 将 base 64 字符串插入 SQL Server 数据库
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/1945266/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Insert base 64 string into SQL Server database
提问by Hyman Black
I get a base 64 string from a XML file which I want to insert into my SQL Server database. Which field type have the field in my database to be? varbinary(MAX)? Do I have to convert the base 64 string to another format before inserting into my database?
我从 XML 文件中获取了一个 base 64 字符串，我想将其插入到我的 SQL Server 数据库中。我的数据库中的字段是哪种字段类型？varbinary(MAX)？在插入到我的数据库之前，我是否必须将 base 64 字符串转换为另一种格式？
Best regards
此致
采纳答案by marc_s
Since it's a string and a string is a string is a string, you should be able to put it into a VARCHAR(x)or VARCHAR(MAX)field without any problems.
由于它是一个字符串并且一个字符串是一个字符串是一个字符串，因此您应该能够将它放入VARCHAR(x)orVARCHAR(MAX)字段中而不会出现任何问题。
The "sized" VARCHAR(x)has a max. length of 8000 characters, while VARCHAR(MAX)tops out at 2 GB (2**31-1 byte) of size.
“大小”VARCHAR(x)有一个最大值。长度为 8000 个字符，最大VARCHAR(MAX)为 2 GB（2**31-1 字节）。
回答by Daniel Vassallo
If you intend to store your Base64 string as is, you can use the VARCHARdata type. The Base64 encoding has been devised to use only 7-bit ASCII characters.
如果您打算按原样存储 Base64 字符串，则可以使用VARCHAR数据类型。Base64 编码已设计为仅使用 7 位 ASCII 字符。
However, if you prefer to store your data in binary format, you would need to use the VARBINARYdata type and convert your Base64 string to binary. You can use the XQuery functionality (SQL Server 2005 onwards) to easily convert values to VARBINARYand vice-versa.
但是，如果您更喜欢以二进制格式存储数据，则需要使用VARBINARY数据类型并将 Base64 字符串转换为二进制。您可以使用 XQuery 功能（SQL Server 2005 及更高版本）轻松地将值转换为值，VARBINARY反之亦然。
Convert Base64 value in a variable to VARBINARY:
将变量中的 Base64 值转换为VARBINARY：
declare @str varchar(20);
set @str = '3qAAAA==';
select cast(N'' as xml).value('xs:base64Binary(sql:variable("@str"))', 'varbinary(20)');
Convert binary value in a variable to Base64:
将变量中的二进制值转换为 Base64：
declare @bin varbinary(20);
set @bin = 0xDEA00000;
select cast(N'' as xml).value('xs:base64Binary(xs:hexBinary(sql:variable("@bin")))', 'varchar(20)');
Source (and more examples): Converting from Base64 to varbinary and vice versa.
来源（和更多示例）：从 Base64 到 varbinary 的转换，反之亦然。
回答by nitzmahone
varbinary(MAX) would be the most efficient storage medium (raw bytes are smaller than b64 encoded), but you'd have to convert from b64 to raw bytes. If you want to store the b64 as you get it, just use varchar(max). Really depends- if you're going to splat it right back into XML, it'd be less processing to leave the b64 alone.
varbinary(MAX) 将是最有效的存储介质（原始字节小于 b64 编码），但您必须从 b64 转换为原始字节。如果您想在获得 b64 时存储它，只需使用 varchar(max)。真的取决于 - 如果您打算将其直接转换回 XML，那么单独留下 b64 的处理会更少。
回答by LukeH
You can either insert the Base64 string itself into a VARCHARcolumn, oryou could convert the Base64 string back to a byte[]array and store that in a VARBINARYcolumn.
您可以插入Base64编码字符串本身成VARCHAR列，或者你可以转换的Base64串回byte[]在阵列和商店VARBINARY列。
Deciding which option is the most appropriate will depend on what you subsequently need to do with the data. If you need a Base64 string then keep it that way (in a VARCHARcolumn); If you need a byte[]array then convert it (and store it in a VARBINARYcolumn).
决定哪个选项最合适将取决于您随后需要对数据做什么。如果您需要 Base64 字符串，请保持原样（在VARCHAR列中）；如果您需要一个byte[]数组，则对其进行转换（并将其存储在VARBINARY列中）。
回答by James Holland
It's worth pointing out that if you use varchar(x)or varchar(max)with base64 strings and you use the base64 string in any WHEREclauses for lookups you should use a case sensitive collation on the column because case is significant with base64 encoded data.
值得指出的是，如果您使用varchar(x)或varchar(max)使用 base64 字符串，并且在任何子WHERE句中使用 base64 字符串进行查找，则应在列上使用区分大小写的排序规则，因为大小写对于 base64 编码数据很重要。
回答by Nagendra Hari Karthick
I would always suggest you to store image in and as varbinary(max) in DB because Varbinary is performance incentive and it occupies very less memory compared to varchar(max),but with modern databases and storage, that's probably much less of a concern. If you want to store byte[] go for varbinary(max).In case if you want to store base64 string i would always suggest nvarchar(max). However performance and size will always be expensive.
我总是建议您将图像存储在 DB 中并作为 varbinary(max) 存储，因为 Varbinary 是一种性能激励，与 varchar(max) 相比，它占用的内存要少得多，但对于现代数据库和存储，这可能不太重要。如果你想存储 byte[] 去 varbinary(max)。如果你想存储 base64 字符串，我总是建议 nvarchar(max)。然而，性能和尺寸总是很昂贵的。
