PHP对象关系映射与PDO实战

PHP对象关系映射与PDO实战

PDO是PHP数据库操作的标准方式。它提供了一个统一的接口来操作不同类型的数据库，同时预处理语句可以防止SQL注入。今天说说PDO的高级用法和ORM的实现思路。

先看看PDO的连接配置。字符集一定要用utf8mb4，不然存emoji会报错。

```php
// PDO连接
$dsn = 'mysql:host=localhost;dbname=test;charset=utf8mb4';
$options = [
PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,
PDO::ATTR_EMULATE_PREPARES => false,
PDO::MYSQL_ATTR_INIT_COMMAND => "SET NAMES utf8mb4 COLLATE utf8mb4_unicode_ci",
];

$pdo = new PDO($dsn, 'root', '', $options);

// 获取查询结果
$stmt = $pdo->query("SELECT * FROM users LIMIT 10");
$users = $stmt->fetchAll(); // 获取所有行
$user = $stmt->fetch(); // 获取单行
$count = $stmt->fetchColumn(); // 获取单列

// 不同的获取模式
$stmt = $pdo->query("SELECT id, name FROM users");
$byId = $stmt->fetchAll(PDO::FETCH_KEY_PAIR); // 第一列做键，第二列做值
print_r($byId);

$stmt = $pdo->query("SELECT * FROM users");
$objects = $stmt->fetchAll(PDO::FETCH_OBJ); // 对象形式
foreach ($objects as $obj) {
echo "{$obj->name}\n";
}
?>
```

事务处理在涉及多个相关操作的场景中必不可少。

```php
function processOrder(PDO $pdo, int $userId, array $items): array
{
try {
$pdo->beginTransaction();

// 计算总价
$total = 0;
foreach ($items as $item) {
$total += $item['price'] * $item['quantity'];
}

// 创建订单
$stmt = $pdo->prepare(
"INSERT INTO orders (user_id, total, status, created_at) VALUES (?, ?, 'pending', NOW())"
);
$stmt->execute([$userId, $total]);
$orderId = $pdo->lastInsertId();

// 插入订单项
$stmt = $pdo->prepare(
"INSERT INTO order_items (order_id, product_id, quantity, price) VALUES (?, ?, ?, ?)"
);
foreach ($items as $item) {
$stmt->execute([$orderId, $item['product_id'], $item['quantity'], $item['price']]);
}

// 扣减库存
$stmt = $pdo->prepare(
"UPDATE products SET stock = stock - ? WHERE id = ? AND stock >= ?"
);
foreach ($items as $item) {
$stmt->execute([$item['quantity'], $item['product_id'], $item['quantity']]);
if ($stmt->rowCount() === 0) {
throw new RuntimeException("商品库存不足: {$item['product_id']}");
}
}

$pdo->commit();

return ['order_id' => $orderId, 'total' => $total, 'status' => 'pending'];

} catch (Exception $e) {
$pdo->rollBack();
throw $e;
}
}
?>
```

自定义PDO可以封装常用的查询模式，比如根据ID查询、分页查询等。

```php
class Db
{
private PDO $pdo;

public function __construct(PDO $pdo)
{
$this->pdo = $pdo;
}

public function find(string $table, int $id): ?array
{
$stmt = $this->pdo->prepare("SELECT * FROM {$table} WHERE id = ?");
$stmt->execute([$id]);
$result = $stmt->fetch();
return $result ?: null;
}

public function findAll(string $table, array $conditions = []): array
{
$sql = "SELECT * FROM {$table}";
$params = [];

if (!empty($conditions)) {
$wheres = [];
foreach ($conditions as $column => $value) {
$wheres[] = "{$column} = ?";
$params[] = $value;
}
$sql .= " WHERE " . implode(' AND ', $wheres);
}

$sql .= " ORDER BY id DESC";

$stmt = $this->pdo->prepare($sql);
$stmt->execute($params);
return $stmt->fetchAll();
}

public function insert(string $table, array $data): int
{
$columns = implode(', ', array_keys($data));
$placeholders = implode(', ', array_fill(0, count($data), '?'));

$stmt = $this->pdo->prepare(
"INSERT INTO {$table} ({$columns}) VALUES ({$placeholders})"
);
$stmt->execute(array_values($data));

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

public function update(string $table, int $id, array $data): int
{
$sets = [];
$params = [];
foreach ($data as $column => $value) {
$sets[] = "{$column} = ?";
$params[] = $value;
}
$params[] = $id;

$stmt = $this->pdo->prepare(
"UPDATE {$table} SET " . implode(', ', $sets) . " WHERE id = ?"
);
$stmt->execute($params);

return $stmt->rowCount();
}

public function delete(string $table, int $id): int
{
$stmt = $this->pdo->prepare("DELETE FROM {$table} WHERE id = ?");
$stmt->execute([$id]);
return $stmt->rowCount();
}

public function paginate(string $table, int $page = 1, int $perPage = 20, array $conditions = []): array
{
$offset = ($page - 1) * $perPage;
$where = '';
$params = [];

if (!empty($conditions)) {
$wheres = [];
foreach ($conditions as $column => $value) {
$wheres[] = "{$column} = ?";
$params[] = $value;
}
$where = " WHERE " . implode(' AND ', $wheres);
}

$countStmt = $this->pdo->prepare("SELECT COUNT(*) FROM {$table}{$where}");
$countStmt->execute($params);
$total = (int)$countStmt->fetchColumn();

$dataStmt = $this->pdo->prepare(
"SELECT * FROM {$table}{$where} ORDER BY id DESC LIMIT ? OFFSET ?"
);
$dataStmt->execute(array_merge($params, [$perPage, $offset]));
$items = $dataStmt->fetchAll();

return [
'items' => $items,
'total' => $total,
'page' => $page,
'per_page' => $perPage,
'total_pages' => ceil($total / $perPage),
'has_more' => $page * $perPage < $total,
];
}
}

$db = new Db($pdo);

// 使用
$user = $db->find('users', 1);
$users = $db->findAll('users', ['status' => 'active']);
$newId = $db->insert('users', ['name' => '张三', 'email' => 'test@test.com']);
$db->update('users', $newId, ['name' => '李四']);
$result = $db->paginate('users', 1, 20);
echo "总记录: {$result['total']}, 当前页: {$result['page']}\n";
?>
```

基于PDO可以构建一个简单的ORM。

```php
abstract class Model
{
protected static string $table = '';
protected static string $primaryKey = 'id';
protected array $attributes = [];
protected array $original = [];
protected array $dirty = [];

public function __construct(array $attributes = [])
{
$this->fill($attributes);
}

public function fill(array $attributes): void
{
foreach ($attributes as $key => $value) {
$this->attributes[$key] = $value;
}
$this->original = $this->attributes;
}

public function __get(string $name): mixed
{
return $this->attributes[$name] ?? null;
}

public function __set(string $name, mixed $value): void
{
$this->attributes[$name] = $value;
$this->dirty[$name] = true;
}

public function save(): bool
{
if (isset($this->attributes[static::$primaryKey])) {
return $this->update();
}
return $this->insert();
}

protected function insert(): bool
{
$db = static::getDb();
$id = $db->insert(static::$table, $this->attributes);
if ($id) {
$this->attributes[static::$primaryKey] = $id;
$this->original = $this->attributes;
$this->dirty = [];
return true;
}
return false;
}

protected function update(): bool
{
if (empty($this->dirty)) return true;

$data = [];
foreach ($this->dirty as $field => $_) {
$data[$field] = $this->attributes[$field];
}

$db = static::getDb();
$result = $db->update(
static::$table,
$this->attributes[static::$primaryKey],
$data
);

if ($result !== false) {
$this->original = $this->attributes;
$this->dirty = [];
return true;
}
return false;
}

// 为了演示，直接用静态方法
private static ?PDO $_db = null;
protected static function getDb(): Db
{
if (self::$_db === null) {
self::$_db = new Db(new PDO('mysql:host=localhost;dbname=test', 'root', ''));
}
return self::$_db;
}
}

class User extends Model
{
protected static string $table = 'users';
}

$user = new User(['name' => '张三', 'email' => 'test@test.com']);
$user->save();
echo "用户ID: {$user->id}\n";

$user->name = '李四';
$user->save();
echo "更新后: {$user->name}\n";
?>
```

PDO还有几个容易被忽略的功能。比如PDO::lastInsertId获取自增ID，rowCount获取影响行数，quote做安全转义（但不如预处理安全）。掌握了这些，PHP操作数据库就很顺手了。