PHP 是否在 MySQL 中存储图像？

PHP to store images in MySQL or not?

提问by occhiso

I have built a small web application in PHP where users must first log in. Once they have logged in, I intend on showing a small thumbnail as part of their "profile".

我在 PHP 中构建了一个小型 Web 应用程序，用户必须首先登录。一旦他们登录，我打算在他们的“个人资料”中显示一个小缩略图。

I will have to ensure the image is below a particular size to conserve space, or ensure it is a particular resolution, or both, or even perhaps use something like image magick to scale it down.
Not sure what the best approach for that is yet, any ideas welcome.

我必须确保图像低于特定尺寸以节省空间，或确保它具有特定分辨率，或两者兼而有之，或者甚至可能使用 image magick 之类的东西将其缩小。
不确定最好的方法是什么，欢迎任何想法。

Also, I have been trying to work out if it is better to store the image in the userstable of MySQL as a blob, or maybe a separate imagestable with a unique id, and just store the appropriate image id in the userstable, or simply save the uploaded file on the server (via an upload page as well) and save the file as theUsersUniqueUsername.jpg. Best option?

另外，我一直在尝试弄清楚将图像users作为 blob存储在MySQL 表中是否更好，或者可能是images具有唯一 ID的单独表，然后将适当的图像 id 存储在users表中，或者只是保存在服务器上上传的文件（也通过上传页面）并将文件另存为theUsersUniqueUsername.jpg. 最佳选择？

I found a tutorial on saving images to mysql here: http://www.phpriot.com/articles/images-in-mysql

我在这里找到了一个关于将图像保存到 mysql 的教程：http: //www.phpriot.com/articles/images-in-mysql

I am only a hobby programmer, and haven't ever done anything like this before, so examples, and/or a lot of detail is greatly appreciated.

我只是一个业余程序员，以前从未做过这样的事情，因此非常感谢示例和/或很多细节。

回答by Mario

Always depends of context, but usually, I store a user image on the filesystem in a folder called /content/user/{user_id}.jpgand try to bother the database as little as possible.

始终取决于上下文，但通常，我将文件系统上的用户图像存储在名为的文件夹中，/content/user/{user_id}.jpg并尝试尽可能少地打扰数据库。

回答by Philip Morton

I would recommend storing the image as a file and then have the file URI in the database. If you store all the images in the database, you might have some problems with scaling at a later date.

我建议将图像存储为文件，然后将文件 URI 保存在数据库中。如果您将所有图像都存储在数据库中，则以后可能会遇到缩放问题。

Check out this answertoo:

也看看这个答案：

Microsoft's advice for SQL Server used to be, for speed and size, store images in the file system, with links in the database. I think they've softened their preference a bit, but I still consider it a better idea certainly for size, since it will take up no space in the database.

Microsoft 过去对 SQL Server 的建议是，为了速度和大小，将图像存储在文件系统中，并在数据库中提供链接。我认为他们已经稍微软化了他们的偏好，但我仍然认为这对于大小来说确实是一个更好的主意，因为它不会占用数据库中的空间。

回答by Joseph Earl

The overhead using BLOB is a lot less than most people would have you believe, especially if you set it up right. If you use a separate server just running the DB to store binary files then you can in fact use no file-system at all and avoid any overhead from the file-system

使用 BLOB 的开销比大多数人想象的要少得多，尤其是如果您设置正确的话。如果您使用单独的服务器来运行 DB 来存储二进制文件，那么您实际上可以根本不使用文件系统并避免来自文件系统的任何开销

That said the easiest/best way unless you have a couple of servers to yourself is storing them in the filesystem

也就是说，除非您自己有几台服务器，否则最简单/最好的方法是将它们存储在文件系统中

• Do not store the absolute URL of the file in your DB, just the unique part (and possibly a folder or two), e.g. 2009/uniqueImageName.jpgor just uniqueImageName.jpg. Then in your pages just add the host and other folders onto the front, that way you have some flexibility in moving your images - all you'll need to change is a line or two in your PHP/ASP.NET page.

• There is no need to store outside the document root for security - a .htaccessfile with DENY FROM ALL will work the same and provide more flexibility

• No need to 'shunt' images so much for security, just have a getImage.phppage or something, and then instead of inserting the actual URL in the srcof the image, use something like getImage.php?file=uniqueImageName.jpg. Then the getImage.phpfile can check if the user is authorised and grab the image (or not).

• Use a name which is guaranteed to be unique (preferably an integer i.e. primary key) when storing, some file-system (i.e. Windows) are case-insensitive, so JoeBloggs.jpgand joebloggs.jpgare unique for the database, but not for the file-system so one will overwrite another.

• Use a separate table for the images, and store the primary key of the image in the users table. If you ever want to add more fields or make changes in future it will be easier - it's also good practice.

• 不要将文件的绝对 URL 存储在您的数据库中，只存储唯一的部分（可能还有一两个文件夹），例如2009/uniqueImageName.jpg或只是uniqueImageName.jpg. 然后在您的页面中，只需将主机和其他文件夹添加到前面，这样您就可以灵活地移动图像 - 您需要更改的只是 PHP/ASP.NET 页面中的一两行。

• 为了安全，无需存储在文档根目录之外 -.htaccess带有 DENY FROM ALL的文件将工作相同并提供更大的灵活性

• 没有必要“分流”图片这么多的安全，只是有一个getImage.php网页或东西，然后，而不是在插入实际的URLsrc的image，使用类似getImage.php?file=uniqueImageName.jpg。然后getImage.php文件可以检查用户是否被授权并抓取图像（或没有）。

• 使用其被保证是唯一的名称（优选的整数即主键）存储时，某些文件系统（即，Windows）中是不区分大小写的，所以JoeBloggs.jpg和joebloggs.jpg是用于数据库中是唯一的，但不用于文件系统这样一个将覆盖另一个。

• 对图像使用单独的表，并将图像的主键存储在用户表中。如果您想添加更多字段或在将来进行更改会更容易 - 这也是一种很好的做法。

If you are worried about SEO and things like that, store the image's original file name in another field when you are uploading, you can then use this in your output (such as in the alttag).

如果您担心 SEO 之类的问题，请在上传时将图像的原始文件名存储在另一个字段中，然后您可以在输出中使用它（例如在alt标签中）。

回答by Ben D

Challenging the Conventional Wisdom!

挑战传统智慧！

Of course it is context dependent, but I have a very large application with thousands of images and documents stored as BLOBS in a MySQL database (average size=2MB) and the application runs fine on a server with 256MB of memory. The secret is correct database structure. Always keep two separate tables, one of which stores the basic information about the file, and the other table should just contain the blob plus a primary key for accessing it. All basic queries will be run against the details table, and the other table is only access when the file is actually needed, and it is accessed using an indexed key so performance is extremely good.

当然，它依赖于上下文，但我有一个非常大的应用程序，其中有数千个图像和文档作为 BLOBS 存储在 MySQL 数据库中（平均大小 = 2MB），并且该应用程序在具有 256MB 内存的服务器上运行良好。秘诀是正确的数据库结构。始终保留两个单独的表，其中一个存储有关文件的基本信息，另一个表应仅包含 blob 和用于访问它的主键。所有的基本查询都会在details表上运行，另外一个表只在真正需要文件的时候访问，而且是使用索引键访问的，所以性能非常好。

The advantages of storing files in the database are multiple:

将文件存储在数据库中的优点是多方面的：

1. Much easier backup systems are required, as you do not need to back up the file system
2. Controlling file security is much easier as you can validate before releasing the binary (yes, you can store the file in a non-public directory and have a script read and regurgitate the file, but performance will not be noticeably faster.
3. (Similar to #1) It cleanly separates "user content" and "system content", making migrations and cloning easier.
4. Easier to manage files, track/store version changes, etc, as you need fewer script modifications to add version controls in.

1. 需要更简单的备份系统，因为您不需要备份文件系统
2. 控制文件安全性要容易得多，因为您可以在发布二进制文件之前进行验证（是的，您可以将文件存储在非公共目录中，并让脚本读取并重新生成文件，但性能不会明显加快。
3. （类似于#1）它干净地分离了“用户内容”和“系统内容”，使迁移和克隆更容易。
4. 更容易管理文件、跟踪/存储版本更改等，因为您需要更少的脚本修改来添加版本控制。

If performance is a big issue and security and backups aren't (or if you have a good fs backup system) then you can store it the the FS, but even then I often store files (in the case of images) in the DB and building a caching script that writes the image to a cache folder after the first time it's used (yes, this uses more HD space, but that is almost never a limiting factor).

如果性能是一个大问题而安全性和备份不是（或者如果你有一个好的 fs 备份系统），那么你可以将它存储在 FS，但即便如此，我也经常将文件（在图像的情况下）存储在数据库中并构建一个缓存脚本，在第一次使用后将图像写入缓存文件夹（是的，这会使用更多的高清空间，但这几乎从来不是限制因素）。

Anyway, obviously FS works well in many instances, but I personally find DB management much easier and more flexible, and if written well the performance penalties are extremely small.

无论如何，显然 FS 在许多情况下都运行良好，但我个人发现数据库管理更容易和更灵活，如果写得好，性能损失非常小。

回答by Neil Aitken

We created a shop that stored images in the DB. It worked great during development but once we tested it on the production servers the page load time was far too high, and it added unneccessary load to the DB servers.

我们创建了一个商店，将图像存储在数据库中。它在开发过程中运行良好，但是一旦我们在生产服务器上对其进行测试，页面加载时间就太长了，并且给数据库服务器增加了不必要的负载。

While it seems attractive to store binary files in the DB, fetching and manipulating them adds extra complexity that can be avoided by just keeping files on the file system and storing paths / metadata in the DB.

虽然将二进制文件存储在 DB 中似乎很有吸引力，但获取和操作它们会增加额外的复杂性，只需将文件保存在文件系统上并将路径/元数据存储在 DB 中即可避免。

This is one of those eternal debates, with excellent arguments on both sides, but for my money I would keep images away from the DB.

这是永恒的争论之一，双方都有很好的论据，但为了我的钱，我会将图像远离数据库。

回答by AntonioCS

I recently saw this tip's list: http://www.ajaxline.com/32-tips-to-speed-up-your-mysql-queries

我最近看到了这个提示的列表：http: //www.ajaxline.com/32-tips-to-speed-up-your-mysql-queries

Tip 17: For your web application, images and other binary assets should normally be stored as files. That is, store only a reference to the file rather than the file itself in the database.

提示 17：对于您的 Web 应用程序，图像和其他二进制资产通常应存储为文件。也就是说，在数据库中只存储对文件的引用而不是文件本身。

So just save the file path to the image :)

所以只需将文件路径保存到图像:)

回答by Dustin

I have implemented both solutions (file system and database-persisted images) in previous projects. In my opinion, you should store images in your database. Here's why:

我在以前的项目中实现了这两种解决方案（文件系统和数据库持久化图像）。在我看来，您应该将图像存储在数据库中。原因如下：

1. File system storage is more complicated when your app servers are clustered. You have to have shared storage. Even if your current environment is not clustered, this makes it more difficult to scale up when you need to.
2. You should be using a CDN for your static content anyways, and set your app up as the origin. This means that your app will only be hit once for a given image, then it will be cached on the CDN. CloudFront is dirt cheap and simple to set up...there's no reason not to use it. Save your bandwidth for your dynamic content.
3. It's much quicker (and thus cheaper) to develop database persisted images
4. You get referential integrity with database persisted images. If you're storing images on the file system, you will inevitably have orphan files with no matching database records, or you'll have database records with broken file links. This WILL happen...it's just a matter of time. You'll have to write something to clean these up.

1. 当您的应用服务器集群时，文件系统存储会更加复杂。你必须有共享存储。即使您当前的环境没有集群，这也会让您在需要时更难扩展。
2. 无论如何，您应该为静态内容使用 CDN，并将您的应用设置为源。这意味着对于给定的图像，您的应用程序只会被点击一次，然后它将被缓存在 CDN 上。CloudFront 非常便宜且易于设置……没有理由不使用它。为动态内容节省带宽。
3. 开发数据库持久化图像要快得多（因此也更便宜）
4. 您可以获得数据库持久图像的参照完整性。如果您将图像存储在文件系统上，您将不可避免地拥有没有匹配数据库记录的孤立文件，或者您将拥有带有损坏文件链接的数据库记录。这会发生……这只是时间问题。你必须写一些东西来清理这些。

Anyways, my two cents.

无论如何，我的两分钱。

回答by Sam Wilson

1. What's the blob datatype for anyway, if not for storing files?
2. If your application involves authorisation prior to accessing the files, the changes are that you're a) storing the files outside of DOCUMENT_ROOT (so they can't be accessed directly; and possibly b) sending the entire contents of the files through the application (of course, maybe you're doing some sort of temporarilly-move-to-hashed-but-publicly-accessible-filename thing). So the memory overhead is there anyway, and you might as well be retrieving the data from the database.
3. If you must store files in a filesystem, do as Andreas suggested above, and name them using something you already know (i.e. the primary key of the relevant table).

1. 如果不是用于存储文件，那么 blob 数据类型是什么？
2. 如果您的应用程序在访问文件之前涉及授权，则更改是 a) 将文件存储在 DOCUMENT_ROOT 之外（因此无法直接访问它们；并且可能 b）通过应用程序发送文件的全部内容（当然，也许您正在做某种临时移动到散列但可公开访问的文件名事情）。因此无论如何内存开销都存在，您不妨从数据库中检索数据。
3. 如果您必须将文件存储在文件系统中，请按照上面 Andreas 的建议进行操作，并使用您已经知道的名称（即相关表的主键）命名它们。

回答by Jens Jansson

I think that most database engines are so advanced already that storing BLOB's of data does not produce any disadvantages (bloated db etc). One advantage is that you don't have any broken links when the image is in the database already. That being said, I have myself always done so that I store the file on disk and give the URI to the database. It depends on the usage. It may be easier to handle img-in-db if the page is very dynamic and changes often - no namespace -problems. I have to say that it ends down to what you prefer.

我认为大多数数据库引擎已经如此先进，以至于存储 BLOB 的数据不会产生任何缺点（膨胀的 db 等）。一个优点是当图像已经在数据库中时，您没有任何断开的链接。话虽如此，我总是这样做，以便将文件存储在磁盘上并将 URI 提供给数据库。这取决于使用情况。如果页面非常动态且经常更改 - 没有命名空间 - 问题，则处理 img-in-db 可能更容易。我不得不说它最终取决于你喜欢什么。

回答by Alec Smart

I would suggest you do not store the image in your db. Instead since every user will be having a unique id associated with his/her profile in the db, use that id to store the image physically on the server.

我建议您不要将图像存储在您的数据库中。相反，由于每个用户都将在数据库中拥有与他/她的个人资料相关联的唯一 ID，因此请使用该 ID 将图像物理存储在服务器上。

e.g. if a user has id 23, you can store an image in www.yourname.com/users/profile_images/23.jpg. Then to display, you can check if the image exists, and display it accordingly else display your generic icon.

例如，如果用户的 id 为 23，您可以在 www.yourname.com/users/profile_images/23.jpg 中存储图像。然后要显示，您可以检查图像是否存在，并相应地显示它，否则显示您的通用图标。