sql blob和图像类型之间有什么区别

时间:2022-06-21 13:24:00

This question was actually posed to me this morning by one of my compadres, and it completely threw me, in so far as I didn't even realise SQL had an 'image' datatype so I've always simply gone down the route of compacting files/images etc into BLOB fields.

这个问题实际上是今天早上由我的其中一个提出给我的,它完全抛弃了我,因为我甚至没有意识到SQL有一个'图像'数据类型所以我总是简单地走下压缩路线文件/图像等进入BLOB字段。

After having a (very) quick look around msdn the most I could really find was the info that the image datatype pages works like a blob or varbinary(max) up to a [max] size of ~2GB but past this can't seem to find too much more about it.

在(非常)快速浏览msdn之后,我真正找到的最多的是图像数据类型页面的工作方式,如blob或varbinary(max),最大大小为~2GB,但过去看起来似乎不大找到更多关于它的信息。

I'm wondering if maybe the image datatype is a way of providing indexable/searchable metadata on images within SQL...?

我想知道图像数据类型是否可以在SQL中为图像提供可索引/可搜索的元数据?

Does anyone have anything to offer, by way of links or explanations regarding this?

有没有人可以通过链接或解释来提供任何东西?

Any help would be gratefully received, so many thanks in advance of any replies!

感谢任何帮助,非常感谢任何回复!

UPDATE / Possible answer

更新/可能的答案

After a little more digging I've perhaps acheived some headway with this...

经过多一点挖掘后,我或许已经取得了一些进展......

It may well be that my original thoughts on what the "image" type relates to may have been somewhat misguided, maybe due to the type being titled "image" (which I apparently found quite misleading on first look) which I thought related to an image in a mimetype kinda way, but which appears to infer more of a "disk-image" or "binary-image" sort of idea.

很可能我对“图像”类型所涉及的内容的原始想法可能有些误导,可能是因为这种类型被称为“图像”(我在第一眼看上去时显然有些误导),我认为这与有点像mimetype的图像,但它似乎推断更多的“磁盘图像”或“二进制图像”的想法。

In this way it seems that the IMAGE type was introduced to SQL before the varbinary type was allowed as a (max) field, possibly as a way of storing files in SQL in the way that is now taken for granted (by myself at least) with the use of VARBINARY(MAX)...

以这种方式,似乎在允许varbinary类型作为(max)字段之前将IMAGE类型引入SQL,可能作为一种以现在理所当然的方式在SQL中存储文件的方式(至少我自己)使用VARBINARY(MAX)......

For Reference (both reasonably old but seem to fit the bill):

供参考(两者都相当陈旧,但似乎符合条例草案):

http://channel9.msdn.com/Forums/Coffeehouse/138883-Storing-Retrieving-images-from-SQL-Server-2005

http://www.basenow.com/help/Data_types_in_Microsoft_SQL_Server.asp

If anyone can offer any constructive crit on this possible answer that would be really useful in trying to understand this...

如果有人可以对这个可能的答案提出任何建设性的批评,这对于试图理解这个问题非常有用......

Cheers All!

2 个解决方案

#1


2  

It is vendor dependent but generally:

它取决于供应商,但通常是:

A blob/image data type is a column type which stores binary data in the database separate from rest of the columns. So everytime you ask for image/blob data, database looks up the location and reads the data and send back to you.

blob / image数据类型是一种列类型,它将二进制数据与其余列分开存储在数据库中。因此,每当您要求图像/ blob数据时,数据库都会查找位置并读取数据并发送给您。

Some vendors do TEXT data type which is the same thing wit the the difference it accepts textual data so you can put full-text indexes on them.

有些供应商使用TEXT数据类型,这与它接受文本数据的区别是相同的,因此您可以在其上放置全文索引。

#2


1  

As you're referencing Microsoft SQL Server, one important thing to keep in mind when choosing between IMAGE and VARBINARY data types is that Microsoft is deprecating the IMAGE type - so the fact that you did not use it is in your case a very good thing.

当您引用Microsoft SQL Server时,在选择IMAGE和VARBINARY数据类型时要记住的一件重要事情是Microsoft正在弃用IMAGE类型 - 所以您不使用它的事实在您的情况下是一件非常好的事情。

http://msdn.microsoft.com/en-us/library/ms143729.aspx

#1


2  

It is vendor dependent but generally:

它取决于供应商,但通常是:

A blob/image data type is a column type which stores binary data in the database separate from rest of the columns. So everytime you ask for image/blob data, database looks up the location and reads the data and send back to you.

blob / image数据类型是一种列类型,它将二进制数据与其余列分开存储在数据库中。因此,每当您要求图像/ blob数据时,数据库都会查找位置并读取数据并发送给您。

Some vendors do TEXT data type which is the same thing wit the the difference it accepts textual data so you can put full-text indexes on them.

有些供应商使用TEXT数据类型,这与它接受文本数据的区别是相同的,因此您可以在其上放置全文索引。

#2


1  

As you're referencing Microsoft SQL Server, one important thing to keep in mind when choosing between IMAGE and VARBINARY data types is that Microsoft is deprecating the IMAGE type - so the fact that you did not use it is in your case a very good thing.

当您引用Microsoft SQL Server时,在选择IMAGE和VARBINARY数据类型时要记住的一件重要事情是Microsoft正在弃用IMAGE类型 - 所以您不使用它的事实在您的情况下是一件非常好的事情。

http://msdn.microsoft.com/en-us/library/ms143729.aspx