将Binary插入MySQL BLOB [英] Inserting Binary into MySQL BLOB

问题描述

$serv = "xxx";
$user = "xxx"; 
$pass = "xxx"; 
$db = "xxx"; 

$imgloc = "../images/bg.jpg"; 
$image = fopen($imgloc, 'rb'); 
$imageContent = fread($image, filesize($imgloc)); 

$conn = new mysqli($serv, $user, $pass, $db); 

$sql = "INSERT INTO `image`(`advert_id`,`img`) VALUES('1','" . $imageContent . "');"; 
$conn->query($sql);

我正在使用上面的代码尝试将二进制文件插入到我的MySQL数据库中，但是没有任何内容发送到该数据库. $ imageContent只是在数据库中显示为null，但是如果我回显$ imageContent，它似乎显示二进制数据.

I'm using the above code to try to insert binary into my MySQL database but nothing is being sent to the database. The $imageContent just appears in the database as null but if I echo $imageContent it seems to show binary data.

advert_id只是一个int字段，而img是一个BLOB

advert_id is just a int field and img is a BLOB

推荐答案

代码无法正常工作的原因是，您需要转义数据.

The reason why your code isn't working is because you need to escape your data.

$imageContent = fread($image, filesize($imgloc)); 
$imageContent = mysqli_real_escape_string($conn, $imageContent);

您没有看到语法错误，类似于:

You are not seeing the syntax error, similar to:

您的SQL语法有错误；检查与您的MySQL服务器版本相对应的手册以获取正确的语法，以在第1行的'8 16 @#54'附近使用...

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '8 16@#54' at line 1...

• 因为您没有检查错误.

访问 http://php.net/manual/en/mysqli.error. php 和 http://php.net/manual/zh/function.error-reporting.php ，然后在文件顶部使用以下内容:

Visit http://php.net/manual/en/mysqli.error.php and http://php.net/manual/en/function.error-reporting.php, then use the following at the top of your file:

<?php 
error_reporting(E_ALL);
ini_set('display_errors', 1);
mysqli_report(MYSQLI_REPORT_ERROR | MYSQLI_REPORT_STRICT);
// rest of your code

这将表明语法错误.

将 mysqli /strong> ，或 带有准备好的语句的PDO

Use mysqli with prepared statements, or PDO with prepared statements

另外，正如Mike Brant在评论中所说，我引用:

Plus, as Mike Brant said in comments, and I quote:

作为切向注释，我建议确保您确实有一个很好的用例，用于在MySQL中存储图像斑点.在很多情况下，与简单存储相比，这可能不是一个好主意.数据库中的文件引用."

麦克说的是实话.数据库会随着时间的推移而急剧增加，因此将文件的副本存储在文件夹中然后对其进行引用通常是一个更好的主意，但这完全取决于您.
• Mike speaks the truth. Your database will increase dramatically over time, therefore storing a copy of your files in a folder then making a reference to it, is usually a better idea, but that is entirely up to you.

阅读下面的问答集":

• php:将图像存储到Mysql blob中，好还是坏?
• 将图像存储在数据库中-是或否?
• php:Store image into Mysql blob, Good or bad?
• Storing Images in DB - Yea or Nay?

这篇关于将Binary插入MySQL BLOB的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！