如何使用 PHP 在 MySQL 数据库中插入大文件？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/492549/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How can I insert large files in MySQL db using PHP?
提问by anjan
I want to upload a large file of maximum size 10MB to my MySQL database. Using .htaccessI changed PHP's own file upload limit to "10485760" = 10MB. I am able to upload files up to 10MB without any problem.
我想将最大大小为 10MB 的大文件上传到我的 MySQL 数据库。使用.htaccess我将 PHP 自己的文件上传限制更改为“10485760”= 10MB。我可以毫无问题地上传最大 10MB 的文件。
But I can not insert the file in the database if it is more that 1 MB in size.
但如果文件大小超过 1 MB，我无法将文件插入数据库。
I am using file_get_contentsto read all file data and pass it to the insert query as a string to be inserted into a LONGBLOB field.
我正在使用file_get_contents读取所有文件数据并将其作为要插入 LONGBLOB 字段的字符串传递给插入查询。
But files bigger than 1 MB are not added to the database, although I can use print_r($_FILES)to make sure that the file is uploaded correctly. Any help will be appreciated and I will need it within the next 6 hours. So, please help!
但是大于 1 MB 的文件不会添加到数据库中，尽管我可以使用它print_r($_FILES)来确保正确上传文件。任何帮助将不胜感激，我将在接下来的 6 小时内需要它。所以，请帮忙！
采纳答案by roborourke
As far as I know it's generally quicker and better practice not to store the file in the db as it will get massive very quickly and slow it down. It's best to make a way of storing the file in a directory and then just store the location of the file in the db.
据我所知，不将文件存储在数据库中通常更快更好，因为它会很快变大并减慢它的速度。最好制定一种将文件存储在目录中的方法，然后将文件的位置存储在数据库中。
We do it for images/pdfs/mpegs etc in the CMS we have at work by creating a folder for the file named from the url-safe filename and storing the folder name in the db. It's easy just to write out the url of it in the presentation layer then.
我们通过为从 url-safe 文件名命名的文件创建一个文件夹并将文件夹名称存储在 db 中，为我们正在工作的 CMS 中的图像/pdfs/mpegs 等执行此操作。只需在表示层中写出它的 url 就很容易了。
回答by Cody Caughlan
You will want to check the MySQL configuration value "max_allowed_packet", which might be set too small, preventing the INSERT (which is large itself) from happening.
您将需要检查 MySQL 配置值“max_allowed_packet”，该值可能设置得太小，以防止发生 INSERT（本身很大）。
Run the following from a mysql command prompt:
从 mysql 命令提示符运行以下命令：
mysql> show variables like 'max_allowed_packet';
Make sure its large enough. For more information on this config option see
确保它足够大。有关此配置选项的更多信息，请参阅
This also impacts mysql_escape_string() and mysql_real_escape_string() in PHP limiting the size of the string creation.
这也会影响 PHP 中的 mysql_escape_string() 和 mysql_real_escape_string() 限制字符串创建的大小。
回答by DreamWerx
The best answer is to use an implementation that is better and also works around that issue. You can read an article here. Store 10MB, 1000MB, doesn't matter. The implementation chunks/cuts the file into many smaller pieces and stores them in multiple rows.. This helps with load and fetching so memory doesn't also become an issue.
最好的答案是使用更好的实现并且也可以解决这个问题。你可以在这里阅读一篇文章。存储 10MB、1000MB，无所谓。该实现将文件分块/切割成许多更小的部分并将它们存储在多行中。这有助于加载和获取，因此内存也不会成为问题。
回答by Bill Karwin
Some PHP extensions for MySQL have issues with LONGBLOB and LONGTEXT data types. The extensions may not support blob streaming (posting the blob one segment at a time), so they have to post the entire object in one go.
MySQL 的某些 PHP 扩展在 LONGBLOB 和 LONGTEXT 数据类型方面存在问题。扩展可能不支持 blob 流（一次发布一个片段），因此它们必须一次性发布整个对象。
So if PHP's memory limit or MySQL's packet size limit restrict the size of an object you can post to the database, you may need to change some configuration on either PHP or MySQL to allow this.
因此，如果 PHP 的内存限制或 MySQL 的数据包大小限制限制了您可以发布到数据库的对象的大小，您可能需要更改 PHP 或 MySQL 上的一些配置以允许这样做。
You didn't say which PHP extension you're using (there are at least three for MySQL), and you didn't show any of the code you're using to post the blob to the database.
您没有说明您使用的是哪个 PHP 扩展（MySQL 至少有三个），也没有显示用于将 blob 发布到数据库的任何代码。
回答by Ionu? G. Stan
You could use MySQL's LOAD_FILE functionto store the file, but you still have to obey the max_allowed_packet value and the fact that the file must be on the same server as the MySQL instance.
您可以使用 MySQL 的LOAD_FILE 函数来存储文件，但您仍然必须遵守 max_allowed_packet 值以及该文件必须与 MySQL 实例位于同一服务器上的事实。
回答by Greg
You don't say what error you're getting (use mysql_error()to find out), but I suspect you may be hitting the maximum packet size.
你没有说你得到了什么错误（mysql_error()用来找出），但我怀疑你可能达到了最大数据包大小。
If this is the case, you'd need to change your MySQL configuration max_allowed_packet
如果是这种情况，您需要更改 MySQL 配置max_allowed_packet
回答by manueldahmen
You don't say what error you're getting (use mysql_error() to find out), but I suspect you may be hitting the maximum packet size.
If this is the case, you'd need to change your MySQL configuration max_allowed_packet
你没有说你得到了什么错误（使用 mysql_error() 找出），但我怀疑你可能达到了最大数据包大小。
如果是这种情况，您需要更改 MySQL 配置 max_allowed_packet
Well I have the same problem. And data cannot be entered in the mysql database chunck by chunck in a "io mode"
好吧，我有同样的问题。并且在“io模式”下chunck无法在mysql数据库chunck中输入数据
loop for :
read $data from file,
write $data to blob
end loop
close file
close blob
A solution seems to create a table with multi-part blobs like create table data_details ( id int pk auto_increment, chunck_number int not null, dataPart blob ); ???
一个解决方案似乎创建一个包含多部分 blob 的表，例如 create table data_details ( id int pk auto_increment, chunck_number int not null, dataPart blob ); ？？？
