PHP PDO 数据库操作

PDO（PHP Data Objects）是 PHP 推荐的数据库抽象层，支持多种数据库，代码更简洁安全。

为什么用 PDO？

特性MySQLiPDO
支持数据库仅 MySQL12+ 种数据库
预处理语句支持支持
命名参数不支持支持
异常处理手动自动（可配置）
切换数据库需重写只改 DSN

连接数据库

PHP 实例
$dsn  = 'mysql:host=localhost;dbname=myapp;charset=utf8mb4';
$user = 'root';
$pass = 'your_password';


try {
    $pdo = new PDO($dsn, $user, $pass, [
        PDO::ATTR_ERRMODE            => PDO::ERRMODE_EXCEPTION, // 异常模式
        PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,       // 默认关联数组
        PDO::ATTR_EMULATE_PREPARES   => false,                  // 使用真实预处理
    ]);
} catch (PDOException $e) {
    die("连接失败：" . $e->getMessage());
}
?>
``


增删改查


插入


`php
<?php
// 问号占位符
$stmt = $pdo->prepare("INSERT INTO users (username, email) VALUES (?, ?)");
$stmt->execute(["张三", "zhangsan@example.com"]);
echo "新 ID：" . $pdo->lastInsertId();


// 命名占位符（更清晰）
$stmt = $pdo->prepare("INSERT INTO users (username, email) VALUES (:username, :email)");
$stmt->execute([
    ':username' => '李四',
    ':email'    => 'lisi@example.com',
]);
?>
`


查询


`php
<?php
// 查询多行
$stmt = $pdo->query("SELECT * FROM users");
$users = $stmt->fetchAll(); // 返回所有行
foreach ($users as $user) {
    echo $user['username'] . "\n";
}


// 带参数查询
$stmt = $pdo->prepare("SELECT * FROM users WHERE id = :id");
$stmt->execute([':id' => 1]);
$user = $stmt->fetch(); // 返回单行
echo $user['username'];


// 直接绑定到变量
$stmt = $pdo->prepare("SELECT * FROM users WHERE status = ?");
$stmt->execute(['active']);
while ($row = $stmt->fetch()) {
    echo $row['username'] . "\n";
}
?>
`


更新与删除


`php
<?php
// 更新
$stmt = $pdo->prepare("UPDATE users SET email = :email WHERE id = :id");
$stmt->execute([':email' => 'new@example.com', ':id' => 1]);
echo "影响行数：" . $stmt->rowCount();


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


事务


`php
<?php
try {
    $pdo->beginTransaction();


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


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


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


封装数据库类


实际项目中通常封装一个 DB 类：


`php
<?php
class DB {
    private static ?PDO $instance = null;


public static function getInstance(): PDO {
        if (!self::$instance) {
            self::$instance = new PDO(
                'mysql:host=localhost;dbname=myapp;charset=utf8mb4',
                'root', 'password',
                [
                    PDO::ATTR_ERRMODE            => PDO::ERRMODE_EXCEPTION,
                    PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,
                ]
            );
        }
        return self::$instance;
    }


public static function query(string $sql, array $params = []): array {
        $stmt = self::getInstance()->prepare($sql);
        $stmt->execute($params);
        return $stmt->fetchAll();
    }


public static function execute(string $sql, array $params = []): int {
        $stmt = self::getInstance()->prepare($sql);
        $stmt->execute($params);
        return $stmt->rowCount();
    }
}


// 使用
$users = DB::query("SELECT * FROM users WHERE status = ?", ['active']);
DB::execute("UPDATE users SET last_login = NOW() WHERE id = ?", [1]);
?>
`


不同数据库的 DSN


``php
<?php
// MySQL
$dsn = 'mysql:host=localhost;dbname=myapp;charset=utf8mb4';


// PostgreSQL
$dsn = 'pgsql:host=localhost;dbname=myapp';


// SQLite（无需用户名密码）
$pdo = new PDO('sqlite:/path/to/database.db');


// SQL Server
$dsn = 'sqlsrv:Server=localhost;Database=myapp';