如何使用SQL Server存储和检索图像（Server Management Studio） [英] How to Store and Retrieve Images Using SQL Server (Server Management Studio)

问题描述

尝试将文件插入SQL Server数据库时遇到困难。我会尝试尽可能地解决这个问题：

I am having difficulties when trying to insert files into a SQL Server database. I'll try to break this down as best as I can:

1. 我应该使用什么数据类型来存储图像文件（JPEG / PNG / GIF /等）？现在我的表正在使用 image 数据类型，但我很好奇，如果 varbinary 是一个更好的选择。

1. What data type should I be using to store image files (jpeg/png/gif/etc)? Right now my table is using the image data type, but I am curious if varbinary would be a better option.

如何将图像插入数据库？ Microsoft SQL Server Management Studio是否具有允许将文件插入表中的任何内置函数？如果是这样，那怎么办？另外，如何通过使用PHP表单来处理输入数据并将其放入表格中来实现这一点？

How would I go about inserting the image into the database? Does Microsoft SQL Server Management Studio have any built in functions that allow insertions of files into tables? If so, how is that done? Also, how could this be done through the use of an HTML form with PHP handling the input data and placing it into the table?

我如何获取从表中的图像并将其显示在页面上？我理解如何选择单元格的内容，但我将如何将其转换为图片。我是否必须有标题（内容类型：image / jpeg）？

How would I fetch the image from the table and display it on the page? I understand how to SELECT the cell's contents, but how would I go about translating that into a picture. Would I have to have a header(Content type: image/jpeg)?

我做任何一个都没有问题使用MySQL的这些东西，但SQL Server环境对我来说仍然是新的，我正在为我的工作开发一个项目，需要使用存储过程来获取各种数据。

I have no problem doing any of these things with MySQL, but the SQL Server environment is still new to me, and I am working on a project for my job that requires the use of stored procedures to grab various data.

赞赏任何和所有帮助。

推荐答案

图片不推荐使用type，所以不要使用它。

The image type is deprecated, so don't use that.

简短的回答是使用 FILESTREAM 类型是SQL 2008，或 varbinary（max）如果它是2005或更早。

The short answer is to use the FILESTREAM type if this is SQL 2008, or varbinary(max) if it's 2005 or earlier.

更好的答案是使用 FILESTREAM 如果这是SQL 2008则键入，否则不会将数据存储在数据库中。数据库不是为这种类型的东西构建的，并且在那里抛出BLOB会显着损害性能。 FILESTREAM 通过将实际数据存储在文件系统中来回避问题;其他一切都是不合标准的解决方案。

The better answer is to use the FILESTREAM type if this is SQL 2008, and not store images in the database at all otherwise. Databases aren't built for this type of thing and throwing BLOBs in there can hurt performance significantly. FILESTREAM sidesteps the issue by storing the actual data on the file system; everything else is a sub-standard solution.

关于如何读取和写入 varbinary 列，如果你选择采用这种非理想的方法，它将被表示为PHP中的流。这就是你从查询中得到的列值，我相信你必须把它作为参数放入。

As for how to read and write to a varbinary column if you choose to take this non-ideal approach, it's going to be represented as a stream in PHP. That's what you get out of a query as the column value, and I believe that's what you have to put in as a parameter.

这篇关于如何使用SQL Server存储和检索图像（Server Management Studio）的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！