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
最好的祝福
6 个解决方案
#1
9
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）或VARCHAR（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字节）。
#2
11
If you intend to store your Base64 string as is, you can use the VARCHAR data 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 VARBINARY data type and convert your Base64 string to binary. You can use the XQuery functionality (SQL Server 2005 onwards) to easily convert values to VARBINARY and 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，反之亦然。
#3
4
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 WHERE clauses 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编码数据，情况很重要。
#4
1
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的处理就更少了。
#5
1
You can either insert the Base64 string itself into a VARCHAR column, or you could convert the Base64 string back to a byte[] array and store that in a VARBINARY column.
您可以将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 VARCHAR column); If you need a byte[] array then convert it (and store it in a VARBINARY column).
确定哪个选项最合适将取决于您随后需要对数据执行的操作。如果你需要一个Base64字符串，那就保持这种方式（在VARCHAR列中）;如果需要byte []数组，则转换它（并将其存储在VARBINARY列中）。
#6
0
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）存储在DB中，因为Varbinary是性能激励，与varchar（max）相比，它占用的内存非常少，但是对于现代数据库和存储，这可能不那么令人担忧。如果你想存储byte [] go for varbinary（max）。如果你想存储base64字符串，我总是建议nvarchar（max）。但是性能和尺寸总是很昂贵。
#1
9
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）或VARCHAR（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字节）。
#2
11
If you intend to store your Base64 string as is, you can use the VARCHAR data 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 VARBINARY data type and convert your Base64 string to binary. You can use the XQuery functionality (SQL Server 2005 onwards) to easily convert values to VARBINARY and 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，反之亦然。
#3
4
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 WHERE clauses 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编码数据，情况很重要。
#4
1
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的处理就更少了。
#5
1
You can either insert the Base64 string itself into a VARCHAR column, or you could convert the Base64 string back to a byte[] array and store that in a VARBINARY column.
您可以将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 VARCHAR column); If you need a byte[] array then convert it (and store it in a VARBINARY column).
确定哪个选项最合适将取决于您随后需要对数据执行的操作。如果你需要一个Base64字符串，那就保持这种方式（在VARCHAR列中）;如果需要byte []数组，则转换它（并将其存储在VARBINARY列中）。
#6
0
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）存储在DB中，因为Varbinary是性能激励，与varchar（max）相比，它占用的内存非常少，但是对于现代数据库和存储，这可能不那么令人担忧。如果你想存储byte [] go for varbinary（max）。如果你想存储base64字符串，我总是建议nvarchar（max）。但是性能和尺寸总是很昂贵。