PHP 数据库操作
本章将介绍使用 PDO 进行数据库操作。
PDO 基础
连接数据库
<?php
try {
$pdo = new PDO(
'mysql:host=localhost;dbname=test;charset=utf8mb4',
'username',
'password',
[
PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,
PDO::ATTR_EMULATE_PREPARES => false,
]
);
echo "连接成功";
} catch (PDOException $e) {
die("连接失败：" . $e->getMessage());
}
?>
关闭连接
<?php
$pdo = null; // 关闭连接
?>
查询操作
基本查询
<?php
// 执行查询
$stmt = $pdo->query("SELECT * FROM users");
// 获取所有结果
$users = $stmt->fetchAll();
// 逐行获取
while ($row = $stmt->fetch()) {
echo $row['name'];
}
// 获取单行
$user = $pdo->query("SELECT * FROM users WHERE id = 1")->fetch();
// 获取单个值
$count = $pdo->query("SELECT COUNT(*) FROM users")->fetchColumn();
?>
预处理语句
<?php
// 位置参数
$stmt = $pdo->prepare("SELECT * FROM users WHERE age > ? AND status = ?");
$stmt->execute([18, 'active']);
$users = $stmt->fetchAll();
// 命名参数
$stmt = $pdo->prepare("SELECT * FROM users WHERE email = :email");
$stmt->execute(['email' => '[email protected]']);
$user = $stmt->fetch();
// 绑定参数
$stmt = $pdo->prepare("SELECT * FROM users WHERE id = :id");
$stmt->bindParam(':id', $id, PDO::PARAM_INT);
$id = 1;
$stmt->execute();
// 绑定值
$stmt = $pdo->prepare("SELECT * FROM users WHERE name = :name");
$stmt->bindValue(':name', '张三', PDO::PARAM_STR);
$stmt->execute();
?>
获取结果
<?php
$stmt = $pdo->prepare("SELECT * FROM users");
$stmt->execute();
// 关联数组（默认）
$users = $stmt->fetchAll(PDO::FETCH_ASSOC);
// 索引数组
$users = $stmt->fetchAll(PDO::FETCH_NUM);
// 对象
$users = $stmt->fetchAll(PDO::FETCH_OBJ);
// 类实例
$users = $stmt->fetchAll(PDO::FETCH_CLASS, User::class);
// 单列
$names = $stmt->fetchAll(PDO::FETCH_COLUMN, 0);
// 键值对
$users = $stmt->fetchAll(PDO::FETCH_KEY_PAIR); // [id => name]
// 分组
$users = $stmt->fetchAll(PDO::FETCH_GROUP); // [group_id => [users...]]
?>
插入操作
基本插入
<?php
// 单条插入
$sql = "INSERT INTO users (name, email, age) VALUES (?, ?, ?)";
$stmt = $pdo->prepare($sql);
$stmt->execute(['张三', '[email protected]', 25]);
// 获取插入 ID
$id = $pdo->lastInsertId();
echo "插入成功，ID：$id";
?>
批量插入
<?php
$users = [
['李四', '[email protected]', 28],
['王五', '[email protected]', 30],
['赵六', '[email protected]', 22]
];
$sql = "INSERT INTO users (name, email, age) VALUES (?, ?, ?)";
$stmt = $pdo->prepare($sql);
foreach ($users as $user) {
$stmt->execute($user);
}
// 使用事务提高性能
$pdo->beginTransaction();
foreach ($users as $user) {
$stmt->execute($user);
}
$pdo->commit();
?>
更新操作
<?php
// 基本更新
$sql = "UPDATE users SET name = ?, age = ? WHERE id = ?";
$stmt = $pdo->prepare($sql);
$stmt->execute(['张三三', 26, 1]);
// 获取影响行数
$count = $stmt->rowCount();
echo "更新了 $count 行";
// 条件更新
$sql = "UPDATE users SET status = :status WHERE age >= :age";
$stmt = $pdo->prepare($sql);
$stmt->execute(['status' => 'adult', 'age' => 18]);
?>
删除操作
<?php
// 基本删除
$sql = "DELETE FROM users WHERE id = ?";
$stmt = $pdo->prepare($sql);
$stmt->execute([1]);
// 获取影响行数
$count = $stmt->rowCount();
// 条件删除
$sql = "DELETE FROM users WHERE status = :status";
$stmt = $pdo->prepare($sql);
$stmt->execute(['status' => 'inactive']);
?>
事务
<?php
try {
$pdo->beginTransaction();
// 执行多个操作
$pdo->prepare("UPDATE accounts SET balance = balance - ? WHERE id = ?")
->execute([100, 1]);
$pdo->prepare("UPDATE accounts SET balance = balance + ? WHERE id = ?")
->execute([100, 2]);
$pdo->prepare("INSERT INTO transactions (from_id, to_id, amount) VALUES (?, ?, ?)")
->execute([1, 2, 100]);
$pdo->commit();
echo "事务成功";
} catch (Exception $e) {
$pdo->rollBack();
echo "事务失败：" . $e->getMessage();
}
?>
封装数据库操作
<?php
class Database {
private static $instance = null;
private $pdo;
private function __construct() {
$this->pdo = new PDO(
'mysql:host=localhost;dbname=test;charset=utf8mb4',
'username',
'password',
[
PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,
]
);
}
public static function getInstance() {
if (self::$instance === null) {
self::$instance = new self();
}
return self::$instance;
}
public function select($sql, $params = []) {
$stmt = $this->pdo->prepare($sql);
$stmt->execute($params);
return $stmt->fetchAll();
}
public function selectOne($sql, $params = []) {
$stmt = $this->pdo->prepare($sql);
$stmt->execute($params);
return $stmt->fetch();
}
public function insert($table, $data) {
$fields = implode(', ', array_keys($data));
$placeholders = implode(', ', array_fill(0, count($data), '?'));
$sql = "INSERT INTO $table ($fields) VALUES ($placeholders)";
$stmt = $this->pdo->prepare($sql);
$stmt->execute(array_values($data));
return $this->pdo->lastInsertId();
}
public function update($table, $data, $where, $whereParams = []) {
$set = implode(' = ?, ', array_keys($data)) . ' = ?';
$sql = "UPDATE $table SET $set WHERE $where";
$stmt = $this->pdo->prepare($sql);
$stmt->execute(array_merge(array_values($data), $whereParams));
return $stmt->rowCount();
}
public function delete($table, $where, $params = []) {
$sql = "DELETE FROM $table WHERE $where";
$stmt = $this->pdo->prepare($sql);
$stmt->execute($params);
return $stmt->rowCount();
}
public function beginTransaction() {
return $this->pdo->beginTransaction();
}
public function commit() {
return $this->pdo->commit();
}
public function rollBack() {
return $this->pdo->rollBack();
}
}
// 使用
$db = Database::getInstance();
// 查询
$users = $db->select("SELECT * FROM users WHERE age > ?", [18]);
// 插入
$id = $db->insert('users', [
'name' => '张三',
'email' => '[email protected]',
'age' => 25
]);
// 更新
$db->update('users', ['age' => 26], 'id = ?', [1]);
// 删除
$db->delete('users', 'id = ?', [1]);
?>
小结
本章我们学习了：
- PDO 连接数据库
- 查询操作：query、prepare、execute
- 插入、更新、删除操作
- 事务处理
- 封装数据库操作类
下一章我们将学习安全实践。