PHP实现数据库操作全攻略 从基础连接到高级查询优化与安全防护详解

引言
PHP作为一种广泛使用的服务器端脚本语言,在Web开发中扮演着重要角色。数据库操作是PHP应用的核心功能之一,从简单的数据存储到复杂的业务逻辑处理,都离不开与数据库的交互。本文将全面介绍PHP数据库操作的各个方面,包括基础连接、CRUD操作、高级查询优化以及安全防护措施。
一、PHP数据库连接基础
1.1 数据库扩展选择
PHP支持多种数据库扩展,最常用的包括:
MySQLi
(MySQL Improved): 专为MySQL设计,支持面向对象和面向过程两种方式
PDO
(PHP Data Objects): 数据库抽象层,支持多种数据库
mysql_
*: 已废弃的旧扩展,不应在新项目中使用
1.2 使用MySQLi连接数据库
面向过程方式

面向对象方式
connect_error) {
die(“连接失败: ” . $conn->connect_error);
}
echo “连接成功”;
?>
1.3 使用PDO连接数据库
PDO提供了更灵活的数据库连接方式:
PDO::ERRMODE_EXCEPTION,
PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,
PDO::ATTR_EMULATE_PREPARES => false,
];

try {
$pdo = new PDO($dsn, $username, $password, $options);
echo “连接成功”;
} catch (\PDOException $e) {
throw new \PDOException($e->getMessage(), (int)$e->getCode());
}
?>
二、基本CRUD操作
2.1 创建数据 (INSERT)
使用MySQLi
query($sql) === TRUE) {
echo “新记录插入成功”;
} else {
echo “错误: ” . $conn->error;
}

// 获取插入的ID
$new_id = $conn->insert_id;
echo “新记录ID: ” . $new_id;
?>
使用PDO
prepare($sql);

// 绑定参数
$stmt->bindParam(‘:name’, $name);
$stmt->bindParam(‘:email’, $email);
$stmt->bindParam(‘:age’, $age);

// 执行插入
$name = “Alice”;
$email = “alice@example.com”;
$age = 25;
$stmt->execute();

// 获取插入的ID
$new_id = $pdo->lastInsertId();
echo “新记录ID: ” . $new_id;
?>
2.2 读取数据 (SELECT)
使用MySQLi
query($sql);

if ($result->num_rows > 0) {
// 输出数据
while($row = $result->fetch_assoc()) {
echo “id: ” . $row[“id”]. ” – Name: ” . $row[“name”]. ” – Email: ” . $row[“email”]. ” – Age: ” . $row[“age”]. “
“;
}
} else {
echo “0 结果”;
}
?>
使用PDO
:min_age”;
$stmt = $pdo->prepare($sql);
$stmt->execute([‘min_age’ => 20]);

$users = $stmt->fetchAll();

foreach ($users as $user) {
echo “id: ” . $user[‘id’]. ” – Name: ” . $user[‘name’]. ” – Email: ” . $user[’email’]. ” – Age: ” . $user[‘age’]. “
“;
}
?>
2.3 更新数据 (UPDATE)
使用MySQLi
query($sql) === TRUE) {
echo “记录更新成功”;
} else {
echo “错误: ” . $conn->error;
}

// 检查影响的行数
echo “受影响的行数: ” . $conn->affected_rows;
?>
使用PDO
prepare($sql);

$age = 32;
$name = “Jane Doe”;
$stmt->execute([‘age’ => $age, ‘name’ => $name]);

echo “受影响的行数: ” . $stmt->rowCount();
?>
2.4 删除数据 (DELETE)
使用MySQLi
query($sql) === TRUE) {
echo “记录删除成功”;
} else {
echo “错误: ” . $conn->error;
}

echo “受影响的行数: ” . $conn->affected_rows;
?>
使用PDO
prepare($sql);

$name = “Alice”;
$stmt->execute([‘name’ => $name]);

echo “受影响的行数: ” . $stmt->rowCount();
?>
三、高级查询技巧
3.1 多表连接查询
内连接 (INNER JOIN)
query($sql);

while($row = $result->fetch_assoc()) {
echo “用户: ” . $row[“name”]. ” – 订单日期: ” . $row[“order_date”]. ” – 金额: ” . $row[“amount”]. “
“;
}
?>
左连接 (LEFT JOIN)
query($sql);

while($row = $result->fetch_assoc()) {
echo “用户: ” . $row[“name”]. ” – 订单日期: ” . ($row[“order_date”] ?? ‘无’). ” – 金额: ” . ($row[“amount”] ?? ‘0’). “
“;
}
?>
3.2 子查询
(SELECT AVG(age) FROM users)”;
$result = $conn->query($sql);

while($row = $result->fetch_assoc()) {
echo “用户: ” . $row[“name”]. ” – 年龄: ” . $row[“age”]. “
“;
}
?>
3.3 聚合函数与分组
query($sql);

while($row = $result->fetch_assoc()) {
echo “年龄: ” . $row[“age”]. ” – 用户数: ” . $row[“user_count”]. “
“;
}

// 按年龄分组统计平均年龄
$sql = “SELECT age, AVG(age) as avg_age FROM users GROUP BY age”;
?>
3.4 事务处理
begin_transaction();

// 执行多个SQL操作
$sql1 = “UPDATE accounts SET balance = balance – 100 WHERE user_id = 1”;
$sql2 = “UPDATE accounts SET balance = balance + 100 WHERE user_id = 2”;

$conn->query($sql1);
$conn->query($sql2);

// 提交事务
$conn->commit();
echo “事务执行成功”;
} catch (Exception $e) {
// 回滚事务
$conn->rollback();
echo “事务执行失败: ” . $e->getMessage();
}
?>
四、查询优化策略
4.1 索引优化
— 创建索引
CREATE INDEX idx_user_email ON users(email);
CREATE INDEX idx_user_age ON users(age);
CREATE INDEX idx复合索引 ON orders(user_id, order_date);
4.2 避免SELECT *

4.3 使用LIMIT限制结果集
query($sql);
?>
4.4 预处理语句
prepare(“SELECT * FROM users WHERE age > :min_age AND status = :status”);
$stmt->execute([‘min_age’ => 20, ‘status’ => ‘active’]);
$users = $stmt->fetchAll();
?>
4.5 查询缓存
get($cache_key);

if ($cached_users === false) {
// 缓存未命中,查询数据库
$stmt = $pdo->prepare(“SELECT * FROM users WHERE age > :min_age AND status = ‘active’”);
$stmt->execute([‘min_age’ => $min_age]);
$users = $stmt->fetchAll();

// 设置缓存,有效期300秒
$memcached->set($cache_key, $users, 300);
} else {
$users = $cached_users;
}
?>
五、安全防护措施
5.1 SQL注入防护
使用预处理语句
prepare(“SELECT * FROM users WHERE id = :id”);
$stmt->execute([‘id’ => $user_input]);
$user = $stmt->fetch();
?>
MySQLi参数绑定
prepare(“SELECT * FROM users WHERE id = ?”);
$stmt->bind_param(“i”, $user_input);
$stmt->execute();
$result = $stmt->get_result();
$user = $result->fetch_assoc();

// 面向过程
$stmt = mysqli_prepare($conn, “SELECT * FROM users WHERE id = ?”);
mysqli_stmt_bind_param($stmt, “i”, $user_input);
mysqli_stmt_execute($stmt);
$result = mysqli_stmt_get_result($stmt);
$user = mysqli_fetch_assoc($result);
?>
5.2 输入验证与过滤
prepare(“SELECT * FROM users WHERE id = :id”);
$stmt->execute([‘id’ => $user_id]);
?>
5.3 数据库权限管理
— 为应用创建专用数据库用户
CREATE USER ‘webapp’@’localhost’ IDENTIFIED BY ‘strong_password’;

— 授予最小必要权限
GRANT SELECT, INSERT, UPDATE, DELETE ON mydb.users TO ‘webapp’@’localhost’;

— 撤销不必要的权限
REVOKE DROP, ALTER ON mydb.* FROM ‘webapp’@’localhost’;
5.4 错误处理与日志记录
getMessage());

// 显示友好信息
die(“系统暂时不可用,请稍后重试”);
}
?>
5.5 敏感数据处理

六、最佳实践与性能建议
6.1 连接管理
PDO::ERRMODE_EXCEPTION,
PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,
];
$this->pdo = new PDO($dsn, ‘username’, ‘password’, $options);
}

public static function getInstance() {
if (self::$instance === null) {
self::$instance = new self();
}
return self::$instance;
}

public function getConnection() {
return $this->pdo;
}
}

// 使用
$pdo = Database::getInstance()->getConnection();
?>
6.2 批量操作
‘User1′, ’email’ => ‘user1@example.com’],
[‘name’ => ‘User2′, ’email’ => ‘user2@example.com’],
[‘name’ => ‘User3’, ‘ ’email’ => ‘user3@example.com’],
];

$sql = “INSERT INTO users (name, email) VALUES (:name, :email)”;
$stmt = $pdo->prepare($sql);

try {
$pdo->beginTransaction();
foreach ($users as $user) {
$stmt->execute($user);
}
$pdo->commit();
} catch (Exception $e) {
$pdo->rollback();
throw $e;
}
?>
6.3 使用数据库迁移
pdo = $pdo;
}

public function createUsersTable() {
$sql = ”
CREATE TABLE IF NOT EXISTS users (
id INT AUTO_INCREMENT PRIMARY KEY,
name VARCHAR(100) NOT NULL,
email VARCHAR(255) UNIQUE NOT NULL,
age INT,
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
“;
$this->pdo->exec($sql);
}

public function createOrdersTable() {
$sql = ”
CREATE TABLE IF NOT EXISTS orders (
id INT AUTO_INCREMENT PRIMARY KEY,
user_id INT NOT NULL,
amount DECIMAL(10,2) NOT NULL,
order_date DATE NOT NULL,
status ENUM(‘pending’, ‘completed’, ‘cancelled’) DEFAULT ‘pending’,
FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
INDEX idx_user_id (user_id),
INDEX idx_order_date (order_date)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
“;
$this->pdo->exec($sql);
}
}

// 使用
$migration = new Migration($pdo);
$migration->createUsersTable();
$migration->createOrdersTable();
?>
6.4 性能监控与分析
start_time = microtime(true);
}

public function logQuery($sql, $time) {
$this->queries[] = [
‘sql’ => $sql,
‘time’ => $time,
‘backtrace’ => debug_backtrace(DEBUG_BACKTRACE_IGNORE_ARGS, 5)
];
}

public function getStats() {
$total_time = microtime(true) – $this->start_time;
$total_queries = count($this->queries);

return [
‘total_time’ => $total_time,
‘total_queries’ => $total_queries,
‘average_time’ => $total_queries > 0 ? $total_time / $total_queries : 0,
‘slow_queries’ => array_filter($this->queries, function($q) {
return $q[‘time’] > 0.1; // 超过100ms的查询
})
];
}
}

// 使用PDO包装器
class MonitoredPDO extends PDO {
private $monitor;

public function __construct($dsn, $username, $password, $options = []) {
parent::__construct($dsn, $username, $password, $options);
$this->monitor = new QueryMonitor();
}

public function query($query) {
$this->monitor->start();
$start = microtime(true);
$result = parent::query($query);
$time = microtime(true) – $start;
$this->monitor->logQuery($query, $time);
return $result;
}

public function prepare($query, $options = []) {
$stmt = parent::prepare($query, $options);
return new MonitoredPDOStatement($stmt, $this->monitor, $query);
}

public function getMonitor() {
return $this->monitor;
}
}

class MonitoredPDOStatement {
private $stmt;
private $monitor;
private $query;

public function __construct(PDOStatement $stmt, QueryMonitor $monitor, $query) {
$this->stmt = $stmt;
$this->monitor = $monitor;
$this->query = $query;
}

public function execute($params = []) {
$this->monitor->start();
$start = microtime(true);
$result = $this->stmt->execute($params);
$time = microtime(true) – $start;
$this->monitor->logQuery($this->query, $time);
return $result;
}

// 委托其他方法到原始PDOStatement
public function fetchAll() {
return $this->stmt->fetchAll();
}

public function fetch() {
return $this->stmt->fetch();
}

// … 其他方法
}

// 使用示例
$pdo = new MonitoredPDO($dsn, $username, $password, $options);
$stmt = $pdo->prepare(“SELECT * FROM users WHERE age > :age”);
$stmt->execute([‘age’ => 20]);
$users = $stmt->fetchAll();

$stats = $pdo->getMonitor()->getStats();
print_r($stats);
?>
七、总结
PHP数据库操作是一个涉及多个层面的复杂主题。通过本文的详细介绍,您应该已经掌握了:
基础连接
:使用MySQLi和PDO建立安全的数据库连接
CRUD操作
:实现完整的数据增删改查功能
高级查询
:多表连接、子查询、事务处理等高级技巧
查询优化
:索引、预处理、缓存等性能优化策略
安全防护
:SQL注入防护、输入验证、权限管理等安全措施
最佳实践
:连接管理、批量操作、迁移系统等生产环境建议
在实际项目中,建议优先使用PDO,因为它提供了更好的安全性、灵活性和跨数据库兼容性。同时,始终遵循安全最佳实践,特别是使用预处理语句来防止SQL注入攻击。
记住,良好的数据库操作习惯不仅能提高应用性能,更能保障系统安全。持续学习和实践这些技术,将帮助您构建更健壮、更高效的PHP应用。
# PHP实现数据库操作全攻略 从基础连接到高级查询优化与安全防护详解
引言
PHP作为一种广泛使用的服务器端脚本语言,在Web开发中扮演着重要角色。数据库操作是PHP应用的核心功能之一,从简单的数据存储到复杂的业务逻辑处理,都离不开与数据库的交互。本文将全面介绍PHP数据库操作的各个方面,包括基础连接、CRUD操作、高级查询优化以及安全防护措施。
一、PHP数据库连接基础
1.1 数据库扩展选择
PHP支持多种数据库扩展,最常用的包括:
MySQLi
(MySQL Improved): 专为MySQL设计,支持面向对象和面向过程两种方式
PDO
(PHP Data Objects): 数据库抽象层,支持多种数据库
mysql_
*: 已废弃的旧扩展,不应在新项目中使用
1.2 使用MySQLi连接数据库
面向过程方式

面向对象方式
connect_error) {
die(“连接失败: ” . $conn->connect_error);
}
echo “连接成功”;
?>
1.3 使用PDO连接数据库
PDO提供了更灵活的数据库连接方式:
PDO::ERRMODE_EXCEPTION,
PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,
PDO::ATTR_EMULATE_PREPARES => false,
];

try {
$pdo = new PDO($dsn, $username, $password, $options);
echo “连接成功”;
} catch (\PDOException $e) {
throw new \PDOException($e->getMessage(), (int)$e->getCode());
}
?>
二、基本CRUD操作
2.1 创建数据 (INSERT)
使用MySQLi
query($sql) === TRUE) {
echo “新记录插入成功”;
} else {
echo “错误: ” . $conn->error;
}

// 获取插入的ID
$new_id = $conn->insert_id;
echo “新记录ID: ” . $new_id;
?>
使用PDO
prepare($sql);

// 绑定参数
$stmt->bindParam(‘:name’, $name);
$stmt->bindParam(‘:email’, $email);
$stmt->bindParam(‘:age’, $age);

// 执行插入
$name = “Alice”;
$email = “alice@example.com”;
$age = 25;
$stmt->execute();

// 获取插入的ID
$new_id = $pdo->lastInsertId();
echo “新记录ID: ” . $new_id;
?>
2.2 读取数据 (SELECT)
使用MySQLi
query($sql);

if ($result->num_rows > 0) {
// 输出数据
while($row = $result->fetch_assoc()) {
echo “id: ” . $row[“id”]. ” – Name: ” . $row[“name”]. ” – Email: ” . $row[“email”]. ” – Age: ” . $row[“age”]. “
“;
}
} else {
echo “0 结果”;
}
?>
使用PDO
:min_age”;
$stmt = $pdo->prepare($sql);
$stmt->execute([‘min_age’ => 20]);

$users = $stmt->fetchAll();

foreach ($users as $user) {
echo “id: ” . $user[‘id’]. ” – Name: ” . $user[‘name’]. ” – Email: ” . $user[’email’]. ” – Age: ” . $user[‘age’]. “
“;
}
?>
2.3 更新数据 (UPDATE)
使用MySQLi
query($sql) === TRUE) {
echo “记录更新成功”;
} else {
echo “错误: ” . $conn->error;
}

// 检查影响的行数
echo “受影响的行数: ” . $conn->affected_rows;
?>
使用PDO
prepare($sql);

$age = 32;
$name = “Jane Doe”;
$stmt->execute([‘age’ => $age, ‘name’ => $name]);

echo “受影响的行数: ” . $stmt->rowCount();
?>
2.4 删除数据 (DELETE)
使用MySQLi
query($sql) === TRUE) {
echo “记录删除成功”;
} else {
echo “错误: ” . $conn->error;
}

echo “受影响的行数: ” . $conn->affected_rows;
?>
使用PDO
prepare($sql);

$name = “Alice”;
$stmt->execute([‘name’ => $name]);

echo “受影响的行数: ” . $stmt->rowCount();
?>
三、高级查询技巧
3.1 多表连接查询
内连接 (INNER JOIN)
query($sql);

while($row = $result->fetch_assoc()) {
echo “用户: ” . $row[“name”]. ” – 订单日期: ” . $row[“order_date”]. ” – 金额: ” . $row[“amount”]. “
“;
}
?>
左连接 (LEFT JOIN)
query($sql);

while($row = $result->fetch_assoc()) {
echo “用户: ” . $row[“name”]. ” – 订单日期: ” . ($row[“order_date”] ?? ‘无’). ” – 金额: ” . ($row[“amount”] ?? ‘0’). “
“;
}
?>
3.2 子查询
(SELECT AVG(age) FROM users)”;
$result = $conn->query($sql);

while($row = $result->fetch_assoc()) {
echo “用户: ” . $row[“name”]. ” – 年龄: ” . $row[“age”]. “
“;
}
?>
3.3 聚合函数与分组
query($sql);

while($row = $result->fetch_assoc()) {
echo “年龄: ” . $row[“age”]. ” – 用户数: ” . $row[“user_count”]. “
“;
}

// 按年龄分组统计平均年龄
$sql = “SELECT age, AVG(age) as avg_age FROM users GROUP BY age”;
?>
3.4 事务处理
begin_transaction();

// 执行多个SQL操作
$sql1 = “UPDATE accounts SET balance = balance – 100 WHERE user_id = 1”;
$sql2 = “UPDATE accounts SET balance = balance + 100 WHERE user_id = 2”;

$conn->query($sql1);
$conn->query($sql2);

// 提交事务
$conn->commit();
echo “事务执行成功”;
} catch (Exception $e) {
// 回滚事务
$conn->rollback();
echo “事务执行失败: ” . $e->getMessage();
}
?>
四、查询优化策略
4.1 索引优化
— 创建索引
CREATE INDEX idx_user_email ON users(email);
CREATE INDEX idx_user_age ON users(age);
CREATE INDEX idx复合索引 ON orders(user_id, order_date);
4.2 避免SELECT *

4.3 使用LIMIT限制结果集
query($sql);
?>
4.4 预处理语句
prepare(“SELECT * FROM users WHERE age > :min_age AND status = :status”);
$stmt->execute([‘min_age’ => 20, ‘status’ => ‘active’]);
$users = $stmt->fetchAll();
?>
4.5 查询缓存
get($cache_key);

if ($cached_users === false) {
// 缓存未命中,查询数据库
$stmt = $pdo->prepare(“SELECT * FROM users WHERE age > :min_age AND status = ‘active’”);
$stmt->execute([‘min_age’ => $min_age]);
$users = $stmt->fetchAll();

// 设置缓存,有效期300秒
$memcached->set($cache_key, $users, 300);
} else {
$users = $cached_users;
}
?>
五、安全防护措施
5.1 SQL注入防护
使用预处理语句
prepare(“SELECT * FROM users WHERE id = :id”);
$stmt->execute([‘id’ => $user_input]);
$user = $stmt->fetch();
?>
MySQLi参数绑定
prepare(“SELECT * FROM users WHERE id = ?”);
$stmt->bind_param(“i”, $user_input);
$stmt->execute();
$result = $stmt->get_result();
$user = $result->fetch_assoc();

// 面向过程
$stmt = mysqli_prepare($conn, “SELECT * FROM users WHERE id = ?”);
mysqli_stmt_bind_param($stmt, “i”, $user_input);
mysqli_stmt_execute($stmt);
$result = mysqli_stmt_get_result($stmt);
$user = mysqli_fetch_assoc($result);
?>
5.2 输入验证与过滤
prepare(“SELECT * FROM users WHERE id = :id”);
$stmt->execute([‘id’ => $user_id]);
?>
5.3 数据库权限管理
— 为应用创建专用数据库用户
CREATE USER ‘webapp’@’localhost’ IDENTIFIED BY ‘strong_password’;

— 授予最小必要权限
GRANT SELECT, INSERT, UPDATE, DELETE ON mydb.users TO ‘webapp’@’localhost’;

— 撤销不必要的权限
REVOKE DROP, ALTER ON mydb.* FROM ‘webapp’@’localhost’;
?>
5.4 错误处理与日志记录
getMessage());

// 显示友好信息
die(“系统暂时不可用,请稍后重试”);
}
?>
5.5 敏感数据处理

六、最佳实践与性能建议
6.1 连接管理
PDO::ERRMODE_EXCEPTION,
PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,
];
$this->pdo = new PDO($dsn, ‘username’, ‘password’, $options);
}

public static function getInstance() {
if (self::$instance === null) {
self::$instance = new self();
}
return self::$instance;
}

public function getConnection() {
return $this->pdo;
}
}

// 使用
$pdo = Database::getInstance()->getConnection();
?>
6.2 批量操作
‘User1′, ’email’ => ‘user1@example.com’],
[‘name’ => ‘User2′, ’email’ => ‘user2@example.com’],
[‘name’ => ‘User3’, ‘ ’email’ => ‘user3@example.com’],
];

$sql = “INSERT INTO users (name, email) VALUES (:name, :email)”;
$stmt = $pdo->prepare($sql);

try {
$pdo->beginTransaction();
foreach ($users as $user) {
$stmt->execute($user);
}
$pdo->commit();
} catch (Exception $e) {
$pdo->rollback();
throw $e;
}
?>
6.3 使用数据库迁移
pdo = $pdo;
}

public function createUsersTable() {
$sql = ”
CREATE TABLE IF NOT EXISTS users (
id INT AUTO_INCREMENT PRIMARY KEY,
name VARCHAR(100) NOT NULL,
email VARCHAR(255) UNIQUE NOT NULL,
age INT,
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
“;
$this->pdo->exec($sql);
}

public function createOrdersTable() {
$sql = ”
CREATE TABLE IF NOT EXISTS orders (
id INT AUTO_INCREMENT PRIMARY KEY,
user_id INT NOT NULL,
amount DECIMAL(10,2) NOT NULL,
order_date DATE NOT NULL,
status ENUM(‘pending’, ‘completed’, ‘cancelled’) DEFAULT ‘pending’,
FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
INDEX idx_user_id (user_id),
INDEX idx_order_date (order_date)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
“;
$this->pdo->exec($sql);
}
}

// 使用
$migration = new Migration($pdo);
$migration->createUsersTable();
$migration->createOrdersTable();
?>
6.4 性能监控与分析
start_time = microtime(true);
}

public function logQuery($sql, $time) {
$this->queries[] = [
‘sql’ => $sql,
‘time’ => $time,
‘backtrace’ => debug_backtrace(DEBUG_BACKTRACE_IGNORE_ARGS, 5)
];
}

public function getStats() {
$total_time = microtime(true) – $this->start_time;
$total_queries = count($this->queries);

return [
‘total_time’ => $total_time,
‘total_queries’ => $total_queries,
‘average_time’ => $total_queries > 0 ? $total_time / $total_queries : 0,
‘slow_queries’ => array_filter($this->queries, function($q) {
return $q[‘time’] > 0.1; // 超过100ms的查询
})
];
}
}

// 使用PDO包装器
class MonitoredPDO extends PDO {
private $monitor;

public function __construct($dsn, $username, $password, $options = []) {
parent::__construct($dsn, $username, $password, $options);
$this->monitor = new QueryMonitor();
}

public function query($query) {
$this->monitor->start();
$start = microtime(true);
$result = parent::query($query);
$time = microtime(true) – $start;
$this->monitor->logQuery($query, $time);
return $result;
}

public function prepare($query, $options = []) {
$stmt = parent::prepare($query, $options);
return new MonitoredPDOStatement($stmt, $this->monitor, $query);
}

public function getMonitor() {
return $this->monitor;
}
}

class MonitoredPDOStatement {
private $stmt;
private $monitor;
private $query;

public function __construct(PDOStatement $stmt, QueryMonitor $monitor, $query) {
$this->stmt = $stmt;
$this->monitor = $monitor;
$this->query = $query;
}

public function execute($params = []) {
$this->monitor->start();
$start = microtime(true);
$result = $this->stmt->execute($params);
$time = microtime(true) – $start;
$this->monitor->logQuery($this->query, $time);
return $result;
}

// 委托其他方法到原始PDOStatement
public function fetchAll() {
return $this->stmt->fetchAll();
}

public function fetch() {
return $this->stmt->fetch();
}

// … 其他方法
}

// 使用示例
$pdo = new MonitoredPDO($dsn, $username, $password, $options);
$stmt = $pdo->prepare(“SELECT * FROM users WHERE age > :age”);
$stmt->execute([‘age’ => 20]);
$users = $stmt->fetchAll();

$stats = $pdo->getMonitor()->getStats();
print_r($stats);
?>
七、总结
PHP数据库操作是一个涉及多个层面的复杂主题。通过本文的详细介绍,您应该已经掌握了:
基础连接
:使用MySQLi和PDO建立安全的数据库连接
CRUD操作
:实现完整的数据增删改查功能
高级查询
:多表连接、子查询、事务处理等高级技巧
查询优化
:索引、预处理、缓存等性能优化策略
安全防护
:SQL注入防护、输入验证、权限管理等安全措施
最佳实践
:连接管理、批量操作、迁移系统等生产环境建议
在实际项目中,建议优先使用PDO,因为它提供了更好的安全性、灵活性和跨数据库兼容性。同时,始终遵循安全最佳实践,特别是使用预处理语句来防止SQL注入攻击。
记住,良好的数据库操作习惯不仅能提高应用性能,更能保障系统安全。持续学习和实践这些技术,将帮助您构建更健壮、更高效的PHP应用。