MySQL 在数据库中存储媒体文件的最佳方式是什么？

What is the best way to store media files on a database?

提问by David Ameller

I want to store a large number of sound files in a database, but I don't know if it is a good practice. I would like to know the pros and cons of doing it in this way.

我想在数据库中存储大量声音文件，但我不知道这是否是一个好习惯。我想知道这样做的利弊。

I also thought on the possibility to have "links" to those files, but maybe this will carry more problems than solutions. Any experience in this direction will be welcome :)

我还考虑了可能有这些文件的“链接”，但也许这会带来比解决方案更多的问题。欢迎任何这方面的经验:)

Note: The database will be MySQL.

注意：数据库将是 MySQL。

回答by Mark Bessey

Every system I know of that stores large numbers of big files stores them externally to the database. You store all of the queryable data for the file (title, artist, length, etc) in the database, along with a partial path to the file. When it's time to retrieve the file, you extract the file's path, prepend some file root (or URL) to it, and return that.

我所知道的每个存储大量大文件的系统都将它们存储在数据库的外部。您将文件的所有可查询数据（标题、艺术家、长度等）以及文件的部分路径存储在数据库中。当需要检索文件时，您提取文件的路径，在其前面添加一些文件根目录（或 URL），然后返回该文件。

So, you'd have a "location" column, with a partial path in it, like "a/b/c/1000", which you then map to: "http://myserver/files/a/b/c/1000.mp3"

因此，您将有一个“位置”列，其中包含部分路径，例如“a/b/c/1000”，然后将其映射到：“ http://myserver/files/a/b/c /1000.mp3"

Make sure that you have an easy way to point the media database at a different server/directory, in case you need that for data recovery. Also, you might need a routine that re-syncs the database with the contents of the file archive.

确保您有一种简单的方法将媒体数据库指向不同的服务器/目录，以防数据恢复需要。此外，您可能需要一个例程来将数据库与文件存档的内容重新同步。

Also, if you're going to have thousands of media files, don't store them all in one giant directory - that's a performance bottleneck on some file systems. Instead,break them up into multiple balanced sub-trees.

此外，如果您要拥有数以千计的媒体文件，请不要将它们全部存储在一个巨大的目录中——这在某些文件系统上是性能瓶颈。相反，将它们分解为多个平衡的子树。

回答by DreamWerx

I think storing them in the database is ok, as long as you use a good implementation. You can read this older but good article for ideas on how to keep the larger amounts of data in the database from affecting performance.

我认为将它们存储在数据库中是可以的，只要你使用一个好的实现。您可以阅读这篇较旧但很好的文章，了解如何防止数据库中的大量数据影响性能。

http://www.dreamwerx.net/phpforum/?id=1

http://www.dreamwerx.net/phpforum/?id=1

I've had literally 100's of gigs loaded in mysql databases without any issues. The design and implementation is key, do it wrong and you'll suffer.

我已经在 mysql 数据库中加载了 100 场演出，没有任何问题。设计和实现是关键，做错了，你会受苦。

More DB Advantages (not already mentioned): - Works better in a load balanced environment - You can build in more backend storage scalability

更多 DB 优势（尚未提及）： - 在负载平衡环境中效果更好 - 您可以构建更多后端存储可扩展性

回答by Kluge

Advantages of using a database:

使用数据库的优点：

• Easy to join sound files with other data bits.
• Avoiding file i/o operations that bypass database security.
• No need for separation operations to delete sound files when database records are deleted.

• 易于将声音文件与其他数据位连接起来。
• 避免绕过数据库安全性的文件 i/o 操作。
• 删除数据库记录时无需进行分离操作来删除声音文件。

Disadvantages of using a database:

使用数据库的缺点：

• Database bloat
• Databases can be more expensive than file systems

• 数据库膨胀
• 数据库可能比文件系统更昂贵

回答by CMPalmer

I've experimented in different projects with doing it both ways and we've finally decided that it's easier to use the file system as well. After all, the file system is already optimized for storing, retrieving, and indexing files.

我在不同的项目中尝试了两种方式，我们最终决定使用文件系统也更容易。毕竟，文件系统已经针对存储、检索和索引文件进行了优化。

The one tip that I would have about that is to only store a "root relative" path to the file in the database, then have your program or your queries/stored procedures/middle-ware use an installation specific root parameter to retrieve the file.

我对此的一个提示是仅在数据库中存储文件的“根相对”路径，然后让您的程序或查询/存储过程/中间件使用特定于安装的根参数来检索文件.

For example, if you store XYZ.Wav in C:\MyProgram\Data\Sounds\X\ the full path would be

例如，如果您将 XYZ.Wav 存储在 C:\MyProgram\Data\Sounds\X\ 中，则完整路径将为

C:\MyProgram\Data\Sounds\X\XYZ.Wav

But you would store the path and or filename in the database as:

但是您可以将路径和/或文件名存储在数据库中：

X\XYZ.Wav

Elsewhere, in the database or in your program's configuration files, store a root path like SoundFilePath equal to

在其他地方，在数据库或程序的配置文件中，存储根路径，如 SoundFilePath 等于

C:\MyProgram\Data\Sounds\

C:\MyProgram\Data\Sounds\

Of course, where you split the root from the database path is up to you. That way if you move your program installation, you don't have to update the database.

当然，从数据库路径中拆分根目录的位置取决于您。这样，如果您移动程序安装，就不必更新数据库。

Also, if there are going to be lotsof files, find some way of hashing the paths so you don't wind up with one directory containing hundreds or thousands of files (in my little example, there are subdirectories based on the first character of the filename, but you can go deeper or use random hashes). This makes search indexers happy as well.

此外，如果将有很多文件，请找到一些散列路径的方法，这样您就不会得到一个包含数百或数千个文件的目录（在我的小例子中，有基于第一个字符的子目录文件名，但您可以更深入或使用随机哈希）。这也让搜索索引器感到高兴。

回答by MarkR

Some advantages of using blobs to store files

使用 blob 存储文件的一些优点

• Lower management overhead - use a single tool to backup / restore etc
• No possibility for database and filesystem to be out of sync
• Transactional capability (if needed)

• 降低管理开销 - 使用单一工具来备份/恢复等
• 数据库和文件系统不可能不同步
• 交易能力（如果需要）

Some disadvantages

一些缺点

• blows up your database servers' RAM with useless rubbish it could be using to store rows, indexes etc
• Makes your DB backups very large, hence less manageable
• Not as convenient as a filesystem to serve to clients (e.g. with a web server)

• 用可用于存储行、索引等的无用垃圾炸毁数据库服务器的 RAM
• 使您的数据库备份非常大，因此更难以管理
• 不像文件系统那样方便为客户端提供服务（例如使用 Web 服务器）

---

---

What about performance? Your mileage may vary. Filesystems are extremely varied, so are databases in their performance. In some cases a filesystem will win (probably with fewer larger files). In some cases a DB might be better (maybe with a very large number of smallish files).

性能呢？你的旅费可能会改变。文件系统千差万别，数据库的性能也是如此。在某些情况下，文件系统会获胜（可能会有更少的大文件）。在某些情况下，数据库可能会更好（可能有大量小文件）。

In any case, don't worry, do what seems best at the time.

无论如何，不​​要担心，做当时看起来最好的事情。

Some databases offer a built-in web server to serve blobs. At the time of writing, MySQL does not.

一些数据库提供了一个内置的 Web 服务器来为 blob 提供服务。在撰写本文时，MySQL 还没有。

回答by itsmatt

You could store them as BLOBs (or LONGBLOBs) and then retrieve the data out when you want to actually access the media files.

您可以将它们存储为 BLOB（或 LONGBLOB），然后在您想要实际访问媒体文件时检索数据。

or

或者

You could simply store the media files on a drive and store the metadata in the DB.

您可以简单地将媒体文件存储在驱动器上，并将元数据存储在数据库中。

I lean toward the latter method. I don't know how this is done overall in the world, but I suspect that many others would do the same.

我倾向于后一种方法。我不知道世界上的整体情况如何，但我怀疑许多其他人也会这样做。

You can store links (partial paths to the data) and then retrieve this info. Makes it easy to move things around on drives and still access it.

您可以存储链接（数据的部分路径），然后检索此信息。可以轻松地在驱动器上移动内容并仍然访问它。

I store off the relative path of each file in the DB along with other metadata about the files. The base path can then be changed on the fly if I need to relocate the actual data to another drive (either local or via UNC path).

我将每个文件的相对路径以及有关文件的其他元数据存储在数据库中。如果我需要将实际数据重新定位到另一个驱动器（本地或通过 UNC 路径），则可以动态更改基本路径。

That's how I do it. I'm sure others will have ideas too.

我就是这样做的。我相信其他人也会有想法。

回答by winwaed

Store them as external files. Then save the path in a varchar field. Putting large binary blobs into a relational database is generally very inefficient - they only use up space and slow things down as caches are filled are unusable. And there's nothing to be gained - the blobs themselves cannot be searched. You might want to save media meta data into the the database though.

将它们存储为外部文件。然后将路径保存在 varchar 字段中。将大型二进制 blob 放入关系数据库通常效率很低 - 它们只会占用空间，并且在缓存被填满时减慢速度是不可用的。而且没有任何收获 - 无法搜索 blob 本身。不过，您可能希望将媒体元数据保存到数据库中。

回答by Josh Kodroff

A simple solution would be to just store the relative locations of the files as strings and let the filesystem handle it. I've tried it on a project (we were storing office file attachments to a survey), and it worked fine.

一个简单的解决方案是将文件的相对位置存储为字符串并让文件系统处理它。我已经在一个项目中尝试过（我们正在将办公室文件附件存储到调查中），并且效果很好。

回答by suren

The best way to storing audio/video files, you can use any distributed storage that can be local or on cloud.

存储音频/视频文件的最佳方式，您可以使用任何本地或云上的分布式存储。

https://min.io/

https://min.io/

for cloud: AWS S3

对于云：AWS S3