PHP数据库核心技术PDO详解

PHP数据库核心技术PDO详解

PDO是PHP数据库操作的标准方式。它提供了一个统一的接口来操作不同类型的数据库，预处理语句天然防止SQL注入。今天说说PDO的各种用法和技巧。

连接数据库是第一步。推荐设置错误模式为异常，获取模式为关联数组。

```php
$host = 'localhost';
$dbname = 'test';
$username = 'root';
$password = '';
$charset = 'utf8mb4';

$dsn = "mysql:host=$host;dbname=$dbname;charset=$charset";

$options = [
PDO::ATTR_ERRMODE => 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 "连接成功\n";
} catch (PDOException $e) {
die("连接失败: " . $e->getMessage());
}
?>
```

增删改查是数据库基本操作。预处理语句用参数绑定，不能拼接SQL。

```php
$stmt = $pdo->prepare("INSERT INTO users (name, email, age) VALUES (?, ?, ?)");
$stmt->execute(['张三', 'zhangsan@test.com', 28]);
$userId = $pdo->lastInsertId();

$stmt = $pdo->prepare("SELECT * FROM users WHERE id = ?");
$stmt->execute([1]);
$user = $stmt->fetch();

$stmt = $pdo->prepare("SELECT * FROM users WHERE status = ?");
$stmt->execute(['active']);
$users = $stmt->fetchAll();

$stmt = $pdo->prepare("UPDATE users SET name = ? WHERE id = ?");
$stmt->execute(['李四', 1]);

$stmt = $pdo->prepare("DELETE FROM users WHERE id = ?");
$stmt->execute([1]);

echo "用户数: " . count($users) . "\n";
?>
```

事务处理确保多个操作要么全部成功，要么全部失败。

```php
function transfer(PDO $pdo, int $fromId, int $toId, float $amount): void
{
try {
$pdo->beginTransaction();

$stmt = $pdo->prepare("UPDATE accounts SET balance = balance - ? WHERE id = ?");
$stmt->execute([$amount, $fromId]);

$stmt = $pdo->prepare("SELECT balance FROM accounts WHERE id = ?");
$stmt->execute([$fromId]);
$balance = $stmt->fetchColumn();
if ($balance < 0) {
throw new Exception("余额不足");
}

$stmt = $pdo->prepare("UPDATE accounts SET balance = balance + ? WHERE id = ?");
$stmt->execute([$amount, $toId]);

$pdo->commit();
echo "转账成功\n";
} catch (Exception $e) {
$pdo->rollBack();
echo "转账失败: " . $e->getMessage() . "\n";
}
}
?>
```

游标分页比OFFSET分页性能好，数据量大时效果明显。

```php
function paginate(PDO $pdo, ?int $cursor, int $perPage = 20): array
{
if ($cursor === null) {
$stmt = $pdo->prepare("SELECT * FROM articles ORDER BY id DESC LIMIT ?");
$stmt->execute([$perPage]);
} else {
$stmt = $pdo->prepare("SELECT * FROM articles WHERE id < ? ORDER BY id DESC LIMIT ?");
$stmt->execute([$cursor, $perPage]);
}

$items = $stmt->fetchAll();
$nextCursor = !empty($items) ? end($items)['id'] : null;

return ['items' => $items, 'next_cursor' => $nextCursor];
}
?>
```

PDO还有一些容易被忽略的功能。rowCount获取影响行数，quote做安全转义，errorInfo获取错误详情。PDO::lastInsertId获取自增ID。这些功能在实际项目中很常用。

使用PDO的基本规则：预处理加参数绑定防止SQL注入，事务保证数据一致性，合适的获取模式提高效率。这些做到了，数据库操作这块基本不会出问题。