使用PHP将Blob插入MySQL的更好方法 [英] Better way to insert blob into MySQL with PHP
问题描述
我正在将文件插入数据库的系统上工作. 我有两种方法可以将blob插入数据库,因此我很好奇哪种方法更好.
I am working on a system where I insert files into the database. There are two ways how I am able to insert blob into DB, so I'm curious which one is better.
首先是获取内容,然后在插入时将内容的参数绑定为字符串:
The first is to get the content and then bind parameter of content as a string when inserting:
$fp = fopen($tmpName, 'r');
$content = fread($fp, filesize($tmpName));
$content = addslashes($content);
fclose($fp);
...
$prep_stmt = "INSERT INTO dokumenty (name, size, type, content, autor, poznamka) VALUES (?, ?, ?, ?, ?, ?)";
$stmt = $mysqli->prepare($prep_stmt);
$stmt->bind_param('sissis',$fileName,$fileSize,$fileType,$content,$user_id,$poznamka );
另一种方法是像这样使用send_long_data:
The other way is to use send_long_data like this:
$content = NULL;
...
$prep_stmt = "INSERT INTO dokumenty (name, size, type, content, autor, poznamka) VALUES (?, ?, ?, ?, ?, ?)";
$stmt = $mysqli->prepare($prep_stmt);
$stmt->bind_param('sisbis',$fileName,$fileSize,$fileType,$content,$user_id,$poznamka );
$stmt->send_long_data(3, file_get_contents($tmpName));
我的问题是:哪种方法都可以同时使用?
My question is: which way is better to use as both works?
推荐答案
使用send_long_data方法可解决与特定MySQL服务器设置中的max_allowed_packet值相比文件太大的情况.但是,您的两个代码有效的事实表明您在测试中没有达到此限制. send_long_data方法已被设计为在循环中被多次调用,其中部分数据的块"太大而无法在一次通过中发送.此外,我不赞成您编写第二个代码的方式,因为您无法在文件读取期间处理错误.我将建议第三种编码方式:
Using the send_long_data method will allow you to solve the case where a file is too big compared to the max_allowed_packet value in your particular MySQL server setup. However the fact your two codes works demonstrate that you did not reach this limit in your tests. The send_long_data method has been designed to be called multiple times in a loop with partial "chunks" of the data too big to be send in one pass. Furthermore I do not favor the way you wrote the second code because you are not able to handle error during file read. I will suggest a third way to code this :
define ('YOUR_MYSQL_MAX_ALLOWED_PACKET', 8192);
$fp = fopen($tmpName, "r");
// TODO check here if fopen succeed
$prep_stmt = "INSERT INTO dokumenty (name, size, type, content, autor, poznamka) VALUES (?, ?, ?, ?, ?, ?)";
$stmt = $mysqli->prepare($prep_stmt);
while (!feof($fp)) {
$stmt->send_long_data(3, fread($fp, YOUR_MYSQL_MAX_ALLOWED_PACKET));
}
fclose($fp);
必须将YOUR_MYSQL_MAX_ALLOWED_PACKET常数值调整为特定于MySQL的设置.
Where the YOUR_MYSQL_MAX_ALLOWED_PACKET constant value must be adjusted to your MySQL particular setup.
这篇关于使用PHP将Blob插入MySQL的更好方法的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!