SQLite与PHP语言集成及自定义函数聚合
SQLite 与 PHP 语言集成基础
SQLite 简介
SQLite 是一款轻型的嵌入式数据库，它的设计目标是嵌入式，占用资源非常低，在嵌入式设备中，可能只需要几百K的内存就够了。SQLite 直接把数据库文件存储在本地，不需要像 MySQL 那样开启一个独立的服务进程。这使得它在很多场景下，比如移动应用、桌面应用以及一些对性能要求不是特别高但又需要持久化存储的小型项目中，非常受欢迎。
PHP 与 SQLite 的连接
在 PHP 中使用 SQLite 非常方便，从 PHP 5.3 版本开始，就已经内置了 SQLite3 扩展。要连接到一个 SQLite 数据库文件，首先需要实例化 SQLite3 类。假设我们有一个名为 example.db 的数据库文件，连接代码如下：
<?php
try {
$db = new SQLite3('example.db');
echo "成功连接到 SQLite 数据库";
} catch (Exception $e) {
echo "连接失败: ". $e->getMessage();
}
?>
在上述代码中，new SQLite3('example.db') 尝试打开名为 example.db 的数据库文件。如果该文件不存在，SQLite 会自动创建一个新的数据库文件。try - catch 块用于捕获可能出现的异常，比如文件权限问题导致无法打开数据库等。
执行 SQL 语句
连接到数据库后，就可以执行各种 SQL 语句了。对于非查询类的 SQL 语句，比如 CREATE TABLE、INSERT、UPDATE、DELETE 等，可以使用 exec() 方法。以下是创建一个简单的 users 表的示例：
<?php
try {
$db = new SQLite3('example.db');
$sql = "CREATE TABLE IF NOT EXISTS users (
id INTEGER PRIMARY KEY AUTOINCREMENT,
name TEXT NOT NULL,
email TEXT UNIQUE
)";
$db->exec($sql);
echo "表创建成功";
} catch (Exception $e) {
echo "操作失败: ". $e->getMessage();
}
?>
CREATE TABLE IF NOT EXISTS users 语句确保只有在 users 表不存在时才会创建。表中有三个列，id 是自增长的主键，name 是不能为空的文本类型，email 是唯一的文本类型。
对于查询类的 SQL 语句，比如 SELECT，可以使用 query() 方法或 querySingle() 方法。query() 方法返回一个 SQLite3Stmt 对象，通过这个对象可以获取查询结果。以下是查询所有用户的示例：
<?php
try {
$db = new SQLite3('example.db');
$sql = "SELECT * FROM users";
$result = $db->query($sql);
while ($row = $result->fetchArray(SQLITE3_ASSOC)) {
echo "ID: ". $row['id']. ", 姓名: ". $row['name']. ", 邮箱: ". $row['email']. "<br>";
}
} catch (Exception $e) {
echo "查询失败: ". $e->getMessage();
}
?>
在上述代码中，fetchArray(SQLITE3_ASSOC) 以关联数组的形式获取每一行数据，方便通过列名来访问数据。
querySingle() 方法则用于获取单个值的查询，比如查询表中的记录数：
<?php
try {
$db = new SQLite3('example.db');
$sql = "SELECT COUNT(*) FROM users";
$count = $db->querySingle($sql);
echo "用户数量: ". $count;
} catch (Exception $e) {
echo "查询失败: ". $e->getMessage();
}
?>
数据插入与事务处理
插入数据
向 SQLite 数据库中插入数据是常见操作。可以使用 INSERT INTO 语句结合 exec() 方法来实现。有两种常见的插入方式，一种是指定列名插入，另一种是按照表结构顺序插入。
指定列名插入的示例如下：
<?php
try {
$db = new SQLite3('example.db');
$name = '张三';
$email = 'zhangsan@example.com';
$sql = "INSERT INTO users (name, email) VALUES ('$name', '$email')";
$db->exec($sql);
echo "数据插入成功";
} catch (Exception $e) {
echo "插入失败: ". $e->getMessage();
}
?>
按照表结构顺序插入（假设表结构为 (id, name, email)，id 自增长）：
<?php
try {
$db = new SQLite3('example.db');
$name = '李四';
$email = 'lisi@example.com';
$sql = "INSERT INTO users VALUES (NULL, '$name', '$email')";
$db->exec($sql);
echo "数据插入成功";
} catch (Exception $e) {
echo "插入失败: ". $e->getMessage();
}
?>
不过，上述直接将变量拼接到 SQL 语句中的方式存在 SQL 注入风险。更好的方式是使用预处理语句。
预处理语句插入数据
预处理语句可以有效防止 SQL 注入攻击，同时也提高了执行效率。在 PHP 的 SQLite3 扩展中，可以使用 prepare() 方法来创建预处理语句。以下是使用预处理语句插入数据的示例：
<?php
try {
$db = new SQLite3('example.db');
$name = '王五';
$email = 'wangwu@example.com';
$stmt = $db->prepare("INSERT INTO users (name, email) VALUES (:name, :email)");
$stmt->bindParam(':name', $name, SQLITE3_TEXT);
$stmt->bindParam(':email', $email, SQLITE3_TEXT);
$stmt->execute();
echo "数据插入成功";
} catch (Exception $e) {
echo "插入失败: ". $e->getMessage();
}
?>
在上述代码中，prepare() 方法创建了一个预处理语句模板，bindParam() 方法将变量绑定到模板中的占位符。这样，SQLite3 扩展会自动处理变量的类型和转义，从而避免 SQL 注入风险。
事务处理
在数据库操作中，事务是一组操作的集合，这些操作要么全部成功执行，要么全部失败回滚。SQLite 支持事务处理，在 PHP 中可以通过 beginTransaction()、commit() 和 rollBack() 方法来实现。
假设我们要向 users 表插入多条记录，并且希望这些插入操作在一个事务中进行，示例代码如下：
<?php
try {
$db = new SQLite3('example.db');
$db->beginTransaction();
$names = ['赵六', '孙七'];
$emails = ['zhaoliu@example.com','sunqi@example.com'];
for ($i = 0; $i < count($names); $i++) {
$stmt = $db->prepare("INSERT INTO users (name, email) VALUES (:name, :email)");
$stmt->bindParam(':name', $names[$i], SQLITE3_TEXT);
$stmt->bindParam(':email', $emails[$i], SQLITE3_TEXT);
$stmt->execute();
}
$db->commit();
echo "事务提交成功";
} catch (Exception $e) {
$db->rollBack();
echo "事务回滚: ". $e->getMessage();
}
?>
在上述代码中，beginTransaction() 开启一个事务，然后通过循环插入多条记录。如果在插入过程中没有发生异常，commit() 方法会提交事务，使所有插入操作生效；如果发生异常，rollBack() 方法会回滚事务，撤销所有已经执行的插入操作。
SQLite 与 PHP 的高级应用
自定义函数
SQLite 允许用户定义自己的函数，这在一些特定场景下非常有用。在 PHP 中，可以通过 createFunction() 方法来创建 SQLite 自定义函数。
例如，我们要创建一个自定义函数 my_concat，用于将两个字符串连接起来，代码如下：
<?php
try {
$db = new SQLite3('example.db');
function my_concat($str1, $str2) {
return $str1. $str2;
}
$db->createFunction('my_concat', 'my_concat');
$sql = "SELECT my_concat('Hello, ', 'World!') AS result";
$result = $db->querySingle($sql);
echo "结果: ". $result;
} catch (Exception $e) {
echo "操作失败: ". $e->getMessage();
}
?>
在上述代码中，首先定义了 PHP 函数 my_concat，然后使用 createFunction() 方法将其注册为 SQLite 的自定义函数。这样就可以在 SQL 语句中像使用内置函数一样使用 my_concat 函数了。
自定义聚合函数
除了普通的自定义函数，SQLite 还支持自定义聚合函数。自定义聚合函数用于对一组值进行计算，返回一个单一的结果，比如 SUM、AVG 等内置聚合函数。
下面我们创建一个自定义聚合函数 my_avg，用于计算一组数字的平均值。首先，需要定义一个 PHP 类来实现聚合函数的逻辑：
class MyAvg {
private $sum = 0;
private $count = 0;
public function step($value) {
if (!is_null($value)) {
$this->sum += $value;
$this->count++;
}
}
public function finalize() {
return $this->count > 0? $this->sum / $this->count : null;
}
}
然后，在 PHP 代码中注册这个自定义聚合函数：
<?php
try {
$db = new SQLite3('example.db');
$db->createAggregate('my_avg', 'MyAvg');
$sql = "CREATE TABLE numbers (num REAL)";
$db->exec($sql);
$values = [1.5, 2.5, 3.5];
foreach ($values as $value) {
$stmt = $db->prepare("INSERT INTO numbers (num) VALUES (:num)");
$stmt->bindParam(':num', $value, SQLITE3_REAL);
$stmt->execute();
}
$sql = "SELECT my_avg(num) AS avg_value FROM numbers";
$result = $db->querySingle($sql);
echo "平均值: ". $result;
} catch (Exception $e) {
echo "操作失败: ". $e->getMessage();
}
?>
在上述代码中，MyAvg 类包含 step 方法和 finalize 方法。step 方法在处理每一个值时被调用，用于累积计算所需的数据，这里是累加值和计数。finalize 方法在所有值处理完毕后被调用，返回最终的聚合结果，即平均值。通过 createAggregate() 方法将 MyAvg 类注册为 SQLite 的自定义聚合函数 my_avg，然后在 SQL 语句中使用它来计算 numbers 表中 num 列的平均值。
处理二进制数据
SQLite 支持存储二进制数据，在 PHP 中，可以使用 BLOB 数据类型来处理。例如，我们要将一个图片文件存储到数据库中。首先，读取图片文件的二进制内容：
<?php
try {
$db = new SQLite3('example.db');
$imagePath = 'test.jpg';
$imageData = file_get_contents($imagePath);
$stmt = $db->prepare("CREATE TABLE IF NOT EXISTS images (
id INTEGER PRIMARY KEY AUTOINCREMENT,
image_data BLOB
)");
$stmt->execute();
$stmt = $db->prepare("INSERT INTO images (image_data) VALUES (:image_data)");
$stmt->bindParam(':image_data', $imageData, SQLITE3_BLOB);
$stmt->execute();
echo "图片数据插入成功";
} catch (Exception $e) {
echo "操作失败: ". $e->getMessage();
}
?>
在上述代码中，file_get_contents($imagePath) 读取图片文件的二进制内容，然后通过 bindParam(':image_data', $imageData, SQLITE3_BLOB) 将二进制数据绑定到预处理语句中，插入到 images 表的 image_data 列，该列的数据类型为 BLOB。
要从数据库中读取并显示图片，可以使用以下代码：
<?php
try {
$db = new SQLite3('example.db');
$sql = "SELECT image_data FROM images WHERE id = 1";
$result = $db->querySingle($sql, true);
if ($result) {
header('Content-Type: image/jpeg');
echo $result['image_data'];
} else {
echo "未找到图片数据";
}
} catch (Exception $e) {
echo "操作失败: ". $e->getMessage();
}
?>
上述代码首先查询出指定 id 的图片二进制数据，然后通过 header('Content-Type: image/jpeg') 设置响应头为图片类型，最后输出二进制数据，从而在浏览器中显示图片。
性能优化
- 使用索引：在 SQLite 中，索引可以显著提高查询性能。例如，如果经常根据
users表的email列进行查询，可以为email列创建索引：
<?php
try {
$db = new SQLite3('example.db');
$sql = "CREATE INDEX idx_email ON users (email)";
$db->exec($sql);
echo "索引创建成功";
} catch (Exception $e) {
echo "操作失败: ". $e->getMessage();
}
?>
- 批量操作：尽量避免单个数据的多次插入、更新等操作，而是采用批量操作的方式。例如，在插入多条记录时，可以使用一条
INSERT INTO...VALUES (...), (...), (...);这样的语句，而不是多次执行单个INSERT语句。 - 缓存查询结果：对于不经常变化的数据查询结果，可以在 PHP 端进行缓存。例如，可以使用
apcu扩展来缓存查询结果，减少对数据库的查询次数：
<?php
try {
$db = new SQLite3('example.db');
$cacheKey = 'users_list';
if (!apcu_exists($cacheKey)) {
$sql = "SELECT * FROM users";
$result = $db->query($sql);
$users = [];
while ($row = $result->fetchArray(SQLITE3_ASSOC)) {
$users[] = $row;
}
apcu_store($cacheKey, $users);
} else {
$users = apcu_fetch($cacheKey);
}
// 处理用户数据
foreach ($users as $user) {
echo "ID: ". $user['id']. ", 姓名: ". $user['name']. ", 邮箱: ". $user['email']. "<br>";
}
} catch (Exception $e) {
echo "操作失败: ". $e->getMessage();
}
?>
- 优化数据库文件：定期对 SQLite 数据库文件进行
VACUUM操作，可以回收未使用的空间，优化数据库性能：
<?php
try {
$db = new SQLite3('example.db');
$db->exec('VACUUM');
echo "数据库优化成功";
} catch (Exception $e) {
echo "操作失败: ". $e->getMessage();
}
?>
通过以上各种方式，可以在 SQLite 与 PHP 集成的应用中，提高系统的性能和稳定性，满足不同场景下的业务需求。无论是小型的个人项目，还是大型的企业级应用中的部分数据存储需求，合理使用 SQLite 与 PHP 的集成技术，都能带来高效且便捷的解决方案。同时，不断深入理解和优化这些技术，有助于开发者打造更加优秀的应用程序。