I'm writing a php application and was wondering if it's a bad idea to store complete files in the database. Files should be around 100-200kb mostly text files (txt, doc, docx and so on) or small image files. Or is it just a plain wrong idea?
我正在编写一个php应用程序，我想知道在数据库中存储完整的文件是否是个坏主意。文件应该在100-200kb左右，主要是文本文件(txt, doc, docx等)或小的图像文件。或者这只是一个完全错误的想法?
4 个解决方案
#1
2
Pro: highly portable.
正方观点:具有高度的可移植性。
Con: you can't do anything with it using SQL (indexing, searching, etc) and you'll need to add metadata in other columns (content type, filename, etc) to improve (re)usability and maintainability.
缺点:您不能使用SQL(索引、搜索等)做任何事情，您需要在其他列(内容类型、文件名等)中添加元数据，以提高(重新)可用性和可维护性。
I wouldn't do that. The disk file system is much better suited system for those tasks.
我不会这样做。磁盘文件系统更适合于这些任务。
#2
3
Advantages:
优点:
- No need to worry about write permissions on the file store.
- 无需担心文件存储上的写权限。
- No need to try and synchronise files on filestore with rows in the database, avoiding orphaned files or broken links. For instance, you can automically cascade delete files when related content is deleted.
- 无需尝试将filestore上的文件与数据库中的行同步，以避免孤立的文件或断开的链接。例如，在删除相关内容时，可以自动级联删除文件。
- In certain databases (such as Oracle and SQL Server) you can index files and search within them using SQL
- 在某些数据库(如Oracle和SQL Server)中，可以使用SQL对文件进行索引和搜索
- No need to worry about unique filenames, folders and it can make uploading simpler in some cases
- 无需担心独特的文件名、文件夹，在某些情况下，它可以使上传更简单
- Easier to protect access to files so only authorised users can see them
- 更容易保护对文件的访问，只有授权用户才能看到它们。
Disadvantages:
缺点:
- Performance of serving files often suffers compared to filestore
- 与filestore相比，服务文件的性能通常会受到影响
- Can lead to large databases. Care needs to be taken when selecting binary columns.
- 可以导致大型数据库。在选择二进制列时需要注意。
- More work to link to files and serve the contents - you need specialised handlers etc.
- 更多的工作链接到文件和服务内容-你需要专门的处理程序等。
#3
2
It really depends on the situation?
这真的取决于情况吗?
- How the files going to be distributed?
- 文件如何分发?
- Are the files used standalone or are they part of an system which might have its own authorization and authentication logic?
- 文件是单独使用的还是系统的一部分，可能有自己的授权和身份验证逻辑?
- Whats your backup strategy?
- 什么你的备份策略?
- Do you need replication?
- 你需要复制吗?
- Do you need to support a lot of I/O?
- 你需要支持很多I/O吗?
- What about caching?
- 缓存是什么呢?
Having said that, I would lean toward some kind of filesystem for documents over a database.
话虽如此，我还是倾向于使用某种文件系统来处理数据库上的文档。
#4
1
Personally, I like the idea, especially if the DB stores them compressed or you compress them manually.
就我个人而言，我喜欢这个想法，特别是如果DB存储它们压缩或手动压缩它们。
Among other things, it means you don't need to worry about unique names for files, which saves a lot of complexity.
除此之外，它意味着您不需要担心文件的唯一名称，这节省了很多复杂性。
#1
2
Pro: highly portable.
正方观点:具有高度的可移植性。
Con: you can't do anything with it using SQL (indexing, searching, etc) and you'll need to add metadata in other columns (content type, filename, etc) to improve (re)usability and maintainability.
缺点:您不能使用SQL(索引、搜索等)做任何事情，您需要在其他列(内容类型、文件名等)中添加元数据，以提高(重新)可用性和可维护性。
I wouldn't do that. The disk file system is much better suited system for those tasks.
我不会这样做。磁盘文件系统更适合于这些任务。
#2
3
Advantages:
优点:
- No need to worry about write permissions on the file store.
- 无需担心文件存储上的写权限。
- No need to try and synchronise files on filestore with rows in the database, avoiding orphaned files or broken links. For instance, you can automically cascade delete files when related content is deleted.
- 无需尝试将filestore上的文件与数据库中的行同步，以避免孤立的文件或断开的链接。例如，在删除相关内容时，可以自动级联删除文件。
- In certain databases (such as Oracle and SQL Server) you can index files and search within them using SQL
- 在某些数据库(如Oracle和SQL Server)中，可以使用SQL对文件进行索引和搜索
- No need to worry about unique filenames, folders and it can make uploading simpler in some cases
- 无需担心独特的文件名、文件夹，在某些情况下，它可以使上传更简单
- Easier to protect access to files so only authorised users can see them
- 更容易保护对文件的访问，只有授权用户才能看到它们。
Disadvantages:
缺点:
- Performance of serving files often suffers compared to filestore
- 与filestore相比，服务文件的性能通常会受到影响
- Can lead to large databases. Care needs to be taken when selecting binary columns.
- 可以导致大型数据库。在选择二进制列时需要注意。
- More work to link to files and serve the contents - you need specialised handlers etc.
- 更多的工作链接到文件和服务内容-你需要专门的处理程序等。
#3
2
It really depends on the situation?
这真的取决于情况吗?
- How the files going to be distributed?
- 文件如何分发?
- Are the files used standalone or are they part of an system which might have its own authorization and authentication logic?
- 文件是单独使用的还是系统的一部分，可能有自己的授权和身份验证逻辑?
- Whats your backup strategy?
- 什么你的备份策略?
- Do you need replication?
- 你需要复制吗?
- Do you need to support a lot of I/O?
- 你需要支持很多I/O吗?
- What about caching?
- 缓存是什么呢?
Having said that, I would lean toward some kind of filesystem for documents over a database.
话虽如此，我还是倾向于使用某种文件系统来处理数据库上的文档。
#4
1
Personally, I like the idea, especially if the DB stores them compressed or you compress them manually.
就我个人而言，我喜欢这个想法，特别是如果DB存储它们压缩或手动压缩它们。
Among other things, it means you don't need to worry about unique names for files, which saves a lot of complexity.
除此之外，它意味着您不需要担心文件的唯一名称，这节省了很多复杂性。