将数据存储到Blob与存储指向文件的指针之间有什么区别? [英] What is difference between storing data in a blob, vs. storing a pointer to a file?

问题描述

我对MySQL中的blob数据类型有疑问.

I have a question about the blob data type in MySQL.

我了解到该数据类型可用于存储文件.我还读到一种替代方法是将文件存储在磁盘上，并包括一个指向其在数据库中位置的指针(通过varchar列).

I read that the data type can be used to store files. I also read that an alternative is to store the file on disk and include a pointer to its location in the database (via a varchar column).

但是我有点困惑，因为我已经知道blob字段没有存储在行中，并且需要单独的查找来检索其内容.那与在文件系统上存储指向文件的指针有什么不同吗?

But I'm a little confused because I've read that blob fields are not stored in-row and require a separate look-up to retrieve its contents. So is that any different than storing a pointer to a file on the file system?

推荐答案

我了解到该数据类型可用于存储文件.

I read that the data type can be used to store files.

根据Blob上 MySQL手册页BLOB是一个二进制大对象，可以容纳可变数量的数据.

According to MySQL manual page on Blob, A BLOB is a binary large object that can hold a variable amount of data.

由于它是特定于存储二进制数据的数据类型，因此通常使用它以二进制格式存储文件，因此存储图像文件在Web应用程序中非常普遍.

Since it's a data type specific to store binary data it's common to use it to store files in binary format, being storing image files a very common use on web applications.

对于Web应用程序，这意味着您首先需要将文件转换为二进制格式然后存储它，并且每次需要检索文件时，都需要执行相反的过程将其转换回原始格式.格式.

For web applications this would mean that you would first need to convert your file into binary format and then store it, and every time you need to retrieve your file you would need to do the reverse process of converting them back to it's original format.

除此之外，在数据库 MAY 中存储大量数据会使速度变慢.特别是在并非专门用于托管数据库的系统中.

Besides that, storing large amount of data in your db MAY slow it down. Specially in systems that are not dedicated only to host a database.

我还读到一种替代方法是将文件存储在磁盘上，并包含一个指向其在数据库中位置的指针

I also read that an alternative is to store the file on disk and include a pointer to its location in the database

考虑到上述所有因素，Web应用程序的常见做法是将文件存储在MySQL以外的其他位置，然后将其路径存储在数据库中.当处理大量数据时，这种方法 MAY 可以加快数据库的速度.

Bearing in mind all above considerations a common practice for web applications is to store your files elsewhere than your MySQL and then simply store it's path on your database. This approach MAY speed up your database when dealing with large amount of data.

但是我有点困惑，因为我已经知道blob字段不是存储在行中，而是需要单独查找才能检索其内容.

But I'm a little confused because I've read that blob fields are not stored in-row and require a separate look-up to retrieve its contents.

实际上，这取决于您使用的存储引擎，因为每个引擎都会处理数据并以不同的方式存储数据.对于适用于关系数据库的InnoDB引擎，您可能需要阅读 MySQL Performance博客，介绍了如何在MySQL中存储Blob.

In fact that would depend on what storage engine you are using since every engine treats data and stores it in different ways. For the InnoDB engine, which is suited for relational database you may want to read this article from MySQL Performance blog on how the blob is stored in MySQL.

但是，抽象而言，在MySQL 5和更高版本上，blob的存储方式如下:

But in abstract, on MySQL 5 and forward the blob is stored as following:

Innodb将整个blob存储在行页面上，或者仅存储20个字节的BLOB指针，从而优先选择要存储在页面上的较小列，这是合理的，因为您可以存储更多的blob.

Innodb stores either whole blob on the row page or only 20 bytes BLOB pointer giving preference to smaller columns to be stored on the page, which is reasonable as you can store more of them.

因此，您现在可能正在考虑正确的方法是将它们存储为单独的文件，但是使用Blob存储数据有一些优点，第一个(在我看来)是备份.我管理一台小型服务器，我只需要创建另一个子例程即可将存储为路径的文件复制到另一个存储磁盘(我们买不起像样的磁带备份系统).如果我已将应用程序设计为使用Blob，那么简单的mysqldump就是备份整个数据库所需的一切.

So you are probably thinking now that the right way to go is to store them as separate file, but there are some advantages of using blob to store data, the first one (in my opinion) is the backup. I manage a small server and I had to create another subroutine only to copy my files stored as paths to another storage disk (We couldn't afford to buy a decent tape backup system). If I had designed my application to use blobs a simple mysqldump would be everything that I needed to backup my whole database.

在

The advantage of storing blobs for backups are better discussed on this post where the person who answered had a similar problem than mine.

另一个优点是安全性和易于管理的权限和访问. MySQL服务器内部的所有数据均受密码保护，您可以轻松地管理用户的权限，以了解哪些用户可以访问什么，哪些用户不可以访问.

Another advantage is security and the easiness of managing permission and access. All the data inside your MySQL server is password protected and you can easily manage permissions for your users about who access what and who doesn't.

在依赖MySQL特权系统进行身份验证和使用的应用程序中.肯定有加号，因为对于入侵者来说，从磁盘或没有访问权限的用户检索图像(或压缩文件之类的二进制文件)会更加困难.

In a application which relies on MySQL privileges system for authentication and use. It's certain a plus since it would be a little harder for let's say an invader to retrieve an image (or a binary file like a zipped one) from your disk or an user without access privileges to access it.

所以我要说

如果您要管理自己的MySQL及其中的所有数据，并且必须进行定期备份或打算更改甚至考虑将来更改操作系统，并拥有合适的硬件并针对它进行了优化，请使用BLOB .

If you gonna manage your MySQL and all the data you have in it and must do regular backups or intend to change or even consider a future change of OS, and have a decent hardware and optimized your MySQL to it, go for BLOB.

如果您不会管理MySQL(例如，在网络主机中)并且不打算更改操作系统或进行备份，请坚持使用varchar列指向您的文件.

If you will not manage your MySQL (as in a web host for example) and doesn't intend to change OS or make backups, stick with varchar columns pointing to your files.

我希望它能对您有所帮助.干杯

I hope it helped. Cheers

这篇关于将数据存储到Blob与存储指向文件的指针之间有什么区别?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！