如何在MySQL中插入PDF文件并通过PHP读取，存入的BLOB无法正常调取怎么办

问题原因 #

核心问题是你把SendGrid要求的base64编码后字符串直接存入了BLOB字段：
base64编码会让文件体积比原始二进制大33%左右，phpMyAdmin等数据库管理工具显示的[BLOB - 20,0 kio]是默认的展示逻辑，仅代表字段存储内容的大小，不代表数据存储异常。

修复方案 #

1. 数据插入逻辑优化

SendGrid接口要求附件传base64编码内容，但MySQL的BLOB字段直接存原始二进制即可，无需额外编码，修改代码如下：

<?php
$filename2 = 'test.pdf';
// 先读取原始PDF二进制内容
$file_raw = file_get_contents("C:/wamp64/www/final/API_label/PDF/$filename2");
// 单独编码给SendGrid使用
$file_encoded = base64_encode($file_raw);
$email->addAttachment($file_encoded, "application/pdf", "$filename2", "attachment");

$sendgrid = new \SendGrid('SG.Ebi-CnMATfeehrmw89O5CuSNfPk');
try {
    // $response = $sendgrid->send($email);
    // print $response->statusCode() . "\n";
    // print_r($response->headers());
    // print $response->body() . "\n";
} catch (Exception $e) {
    // echo 'Caught exception: ' .  $e->getMessage() . "\n";/
}

//Insertion of the values in the database
try {
    // Connect to db
    $db = new db('mysql:dbname=jotform; host=localhost', 'root', '');
    $db->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

    // Set SQL
    $sql = 'INSERT INTO DHL (submission_id, formID, identite, email, adresse, telephone, label, commercial_invoice) 
    VALUES (:submission_id, :formID, :NOM, :EMAIL, :ADRESSE, :TELEPHONE, :file_raw, :COMMERCIAL_INVOICE)';

    // Prepare query
    $query = $db->prepare($sql);
    $query->bindParam(':file_raw', $file_raw, PDO::PARAM_LOB);

    // Execute query
    $query->execute(array(':submission_id' => $submission_id, ':formID' => $formID, ':NOM' => $NOM, ':EMAIL' => $EMAIL, ':ADRESSE' => $ADRESSE, ':TELEPHONE' => $TELEPHONE, 
    ':file_raw' => $file_raw, ':COMMERCIAL_INVOICE' => $COMMERCIAL_INVOICE));
} catch (PDOException $e) {
    echo 'Error: ' . $e->getMessage();
}
}
?>

2. 文件下载逻辑优化

如果按上述方案存储原始二进制，下载时无需解码，直接输出即可，补充Content-Length头避免下载文件损坏：

//PDO PART
include '../include/classe_PDO.php';

try {
  // Connect to db
  $db = new db('mysql:dbname=jotform; host=localhost', 'root', '');
  $db->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

  // Set SQL
  $sql = "SELECT * FROM `dhl` WHERE `submission_id` = '5094071540419221255'";

  foreach  ($db->query($sql) as $row) {
          $filedata = $row['label'];
          
          //set suitable HTTP response headers
          header('Content-Description: File Transfer'); 
          header('Content-Type: application/pdf'); 
          header('Content-Disposition: attachment; filename="label.pdf"');
          header('Content-Length: ' . strlen($filedata));
          header('Expires: 0'); 
          header('Cache-Control: must-revalidate'); 
          header('Pragma: public'); 
          echo $filedata; 
          exit;
}
} catch (PDOException $e) {
  echo 'Error: ' . $e->getMessage();
}         
?>

校验方式 #

下载导出的PDF如果可以正常打开，就说明文件存储逻辑正常。如果不想修改现有存储逻辑，保持存base64编码的内容也可以正常使用，仅会多占用部分存储空间，原有下载逻辑无需修改。

内容的提问来源于stack exchange，提问作者Eric27