在SQL Server中将base64字符串转换为varbinary(max)

Question

我在我的表中以二进制形式存储了PDF文档，存储PDF字节的列是varbinary(max)类型。 我想通过SQL Studio更新记录中的文档，我尝试的方法如下：

UPDATE table
SET file_bytes=CONVERT(varbinary(max),'JVBERi0xLjYNCiW2JqDQo8PC9UeX...0YNCg==') --this is a base64 string
WHERE id='73c75254-ad86-466e-a881-969e2c6e7a04';

查询运行正常，但当我尝试通过网站下载文档时，会出现错误消息，显示为未找到PDF文件头签名。

这种转换是否可能？

- esausilva

---

网站应该添加正确的文件/ MIME 类型头，不是吗？ - Aaron Bertrand

是的，我通过Web服务上传的文档可以正常下载。这就是网站所使用的代码：Response.ContentType = "application/pdf" Response.AppendHeader("Content-Disposition", "attachment; filename=" & packetName.Replace(".", "_")) Response.BinaryWrite(mergedPdfBytes) Response.End() - esausilva

我认为您需要将该代码放入问题中（而不是注释），并描述WebService与数据库更新的不同之处。 另外，您是如何想出那个“JVBER ...”字符串的？ 而且它应该是 N'JVBER...' 吗？此外，您确定要将PDF文件存储在VARBINARY（MAX）列中吗？ 大多数人都可以更好地使用文件系统或使用Filestream或FileTable... - Aaron Bertrand

添加了 N 前缀，但仍然是同样的事情。 - esausilva

好的，抱歉，这已经超出了我的专业知识范围。（我在十年前就学会了不要将文件塞入SQL Server中。你也可能会从中吸取教训。） - Aaron Bertrand

Answer 1

使用此方法描述的方法是可能的：https://blogs.msdn.microsoft.com/sqltips/2008/06/30/converting-from-base64-to-varbinary-and-vice-versa/

这是一个两步过程，首先您声明一个变量：

declare @str varchar(max) = '/9j/4AAQSkZJRgABAQEAAAAAAAD/==';

然后你可以按照以下方式在SQL语句中使用该变量：

INSERT INTO Documents (Name, Body, MIMEType)
VALUES('12446_photo.jpg', cast(N'' as xml).value('xs:base64Binary(sql:variable("@str"))', 'varbinary(max)'), 'image/jpeg');

- Marc-André Gosset

---

你不需要这个变量。 cast('/9j/4AAQSkZJRgABAQEAAAAAAAD/==' as xml).value('xs:base64Binary(.)', 'varbinary(max)') 由于在XML中，base64中的每个字符都被视为文本，因此整个字符串成为一个单独的文本节点。 - Markus Jarderot

Answer 2

Convert a base64 to varbinary

SELECT
   Id,
   AttachmentBase64, --the base64 value we want converted to varbinary
   CAST(AttachmentBase64 AS xml).value('xs:base64Binary(.)', 'varbinary(max)') AS AttachmentBinary
FROM Attachments

将 varbinary 列转换为 base64

SELECT
   Id,
   AttachmentBinary, --the varbinary value we want converted to base64
   CAST('' AS XML).value('xs:base64Binary(sql:column("AttachmentBinary"))', 'varchar(max)') AS AttachmentBase64
FROM Attachments

- Ian Boyd