Skip to main content

SQLite PHP:写入、读取和更新BLOB数据

BLOB代表二进制大对象,它是作为值存储在数据库中的二进制数据的集合。通过使用BLOB,可以将文档、图像和其他多媒体文件存储在数据库中。

为了演示,我们将创建一个名为documents的新表。

CREATE TABLE IF NOT EXISTS documents (
document_id INTEGER PRIMARY KEY,
mime_type TEXT NOT NULL,
doc BLOB
);

把水滴写进桌子里

要将BLOB数据插入表中,请使用以下步骤:

例如,SQLiteBLOB类的以下insertDoc()方法使用上述步骤将新文档插入到documents表中:

<?php

namespace App;

/**
* SQLite PHP Blob Demo
*/
class SQLiteBLOB {

/**
* PDO object
* @var \PDO
*/
private $pdo;

/**
* Initialize the object with a specified PDO object
* @param \PDO $pdo
*/
public function __construct($pdo) {
$this->pdo = $pdo;
}

/**
* Insert blob data into the documents table
* @param type $pathToFile
* @return type
*/
public function insertDoc($mimeType, $pathToFile) {
if (!file_exists($pathToFile))
throw new \Exception("File %s not found.");

$sql = "INSERT INTO documents(mime_type,doc) "
. "VALUES(:mime_type,:doc)";

// read data from the file
$fh = fopen($pathToFile, 'rb');

$stmt = $this->pdo->prepare($sql);

$stmt->bindParam(':mime_type', $mimeType);
$stmt->bindParam(':doc', $fh, \PDO::PARAM_LOB);
$stmt->execute();

fclose($fh);

return $this->pdo->lastInsertId();
}
}

下面是索引。php脚本将两个文档插入到documents表中:一个PDF文件和一张来自assets文件夹的图片。

<?php

require 'vendor/autoload.php';

use App\SQLiteConnection as SQLiteConnection;
use App\SQLiteBLOB as SQLiteBlob;

$sqlite = new SQLiteBlob((new SQLiteConnection)->connect());

// insert a PDF file into the documents table
$pathToPDFFile = 'assets/sqlite-sample database-diagram.pdf';
$pdfId = $sqlite->insertDoc('application/pdf', $pathToPDFFile);

// insert a PNG file into the documents table
$pathToPNGFile = 'assets/sqlite-tutorial-logo.png';
$pngId = $sqlite->insertDoc('image/png', $pathToPNGFile);

我们执行这个索引。php脚本文件,并使用以下SELECT语句验证插入:

SELECT id,
mime_type,
doc
FROM documents;

SQLite PHP Insert BLOB Data Example

从桌子上读水滴

为了从数据库中读取BLOB,我们将一个名为readDoc()的新方法添加到SQLiteBLOB类中,如下所示:

/**
* Read document from the documents table
* @param type $documentId
* @return type
*/
public function readDoc($documentId) {
$sql = "SELECT mime_type, doc "
. "FROM documents "
. "WHERE document_id = :document_id";

// initialize the params
$mimeType = null;
$doc = null;
//
$stmt = $this->pdo->prepare($sql);
if ($stmt->execute([":document_id" => $documentId])) {

$stmt->bindColumn(1, $mimeType);
$stmt->bindColumn(2, $doc, \PDO::PARAM_LOB);

return $stmt->fetch(\PDO::FETCH_BOUND) ?
["document_id" => $documentId,
"mime_type" => $mimeType,
"doc" => $doc] : null;
} else {
return null;
}
}

下面是一份文件。php脚本从查询字符串中获取文档id,并调用readDoc()方法来呈现文档。

<?php

require 'vendor/autoload.php';

use App\SQLiteConnection as SQLiteConnection;
use App\SQLiteBLOB as SQLiteBlob;

$pdo = (new SQLiteConnection)->connect();
$sqlite = new SQLiteBlob($pdo);

// get document id from the query string
$documentId = filter_input(INPUT_GET, 'id', FILTER_SANITIZE_NUMBER_INT);

// read documet from the database
$doc = $sqlite->readDoc($documentId);
if ($doc != null) {
header("Content-Type:" . $doc['mime_type']);
echo $doc['doc'];
} else {
echo 'Error loading document ' . $documentId;
}

例如,下面的屏幕截图显示了如何创建文档。php脚本在web浏览器中返回PDF文件:

SQLite PHP BLOB Read PDF file

要测试文档id 2,请更改查询字符串中的值,如下面的屏幕截图所示:

SQLite PHP BLOB Read PNG File

更新BLOB数据

下面的updateDoc()方法更新documents表中的BLOB数据。

   /**
* Update document
* @param type $documentId
* @param type $mimeType
* @param type $pathToFile
* @return type
* @throws \Exception
*/
public function updateDoc($documentId, $mimeType, $pathToFile) {

if (!file_exists($pathToFile))
throw new \Exception("File %s not found.");

$fh = fopen($pathToFile, 'rb');

$sql = "UPDATE documents
SET mime_type = :mime_type,
doc = :doc
WHERE document_id = :document_id";

$stmt = $this->conn->prepare($sql);

$stmt->bindParam(':mime_type', $mimeType);
$stmt->bindParam(':data', $fh, \PDO::PARAM_LOB);
$stmt->bindParam(':document_id', $documentId);

fclose($fh);

return $stmt->execute();
}

在本教程中,我们向您展示了如何使用PHP PDO在SQLite数据库中写入、读取和更新BLOB数据。