PHP PDO
PDO class reference: connections, methods, constants, error handling.
Key PointsPDO is a uniform database abstraction — one API, any driver.
Connecting
PHP
<?php
$dsns = [
"mysql" => "mysql:host=127.0.0.1;dbname=demo;charset=utf8mb4",
"pgsql" => "pgsql:host=127.0.0.1;dbname=demo",
"sqlite" => "sqlite:/var/data/demo.db",
];
foreach ($dsns as $driver => $dsn) {
echo $driver . " => " . strtok($dsn, ":") . "\n";
}
echo "可用驱动: " . implode(",", PDO::getAvailableDrivers()), "\n";Output
mysql => mysql
pgsql => pgsql
sqlite => sqlite
可用驱动: mysql,sqliteAttribute constants
| Constant | Effect |
|---|---|
| ATTR_ERRMODE | 错误模式（建议 ERRMODE_EXCEPTION） |
| ATTR_DEFAULT_FETCH_MODE | 默认取数模式 |
| ATTR_EMULATE_PREPARES | 是否模拟预处理（建议 false） |
| ATTR_STRINGIFY_FETCHES | 是否把数字转成字符串 |
| ATTR_PERSISTENT | 持久连接 |
| ATTR_TIMEOUT | 连接超时秒数 |
| ATTR_CASE | 列名大小写处理 |
Core methods
| Method | Purpose |
|---|---|
| prepare | 预编译语句 |
| execute | 执行（传参或空数组） |
| query | 执行无参 SQL |
| exec | 执行并返回影响行数（无结果集） |
| fetch | 取一行 |
| fetchAll | 取全部 |
| fetchColumn | 取一个值 |
| rowCount | 影响行数 |
| lastInsertId | 最后自增 ID |
| beginTransaction | 开始事务 |
| commit | 提交 |
| rollBack | 回滚 |
| quote | 转义字符串（少用，优先预处理） |
Transactions
PHP
<?php
$pdo = new PDO($dsn, $user, $pass, [PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION]);
$pdo->beginTransaction();
try {
$pdo->exec("UPDATE accounts SET balance = balance - 100 WHERE id = 1");
$pdo->exec("UPDATE accounts SET balance = balance + 100 WHERE id = 2");
$pdo->commit();
echo "转账成功\n";
} catch (Throwable $e) {
if ($pdo->inTransaction()) {
$pdo->rollBack();
}
echo "已回滚: " . $e->getMessage() . "\n";
}Output
转账成功Check
inTransaction() before rollBack(). If the exception came after commit, rolling back throws "no active transaction".Error information
PHP
<?php
$pdo = new PDO($dsn, $user, $pass, [PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION]);
try {
$pdo->exec("INSERT INTO users (name, email) VALUES ('A', 'a@e.com')");
} catch (PDOException $e) {
echo "SQLSTATE: " . $e->getCode() . "\n";
echo "驱动码: " . ($e->errorInfo[1] ?? "-") . "\n";
echo "驱动消息: " . ($e->errorInfo[2] ?? "-") . "\n";
}Output
SQLSTATE: 23000
驱动码: 1062
驱动消息: Duplicate entry 'a@e.com' for key 'uk_email'| SQLSTATE | Meaning |
|---|---|
| 23000 | 完整性约束冲突（唯一键、外键、非空） |
| 42S02 | 表不存在 |
| 42S22 | 列不存在 |
| 42000 | 语法错误或权限不足 |
| HY093 | 参数数量与占位符不符 |
| 08006 | 连接失败 |
Emulated prepares
PHP
<?php
$pdo = new PDO($dsn, $user, $pass);
$pdo->setAttribute(PDO::ATTR_EMULATE_PREPARES, false);
echo $pdo->getAttribute(PDO::ATTR_EMULATE_PREPARES) ? "模拟" : "真实服务端预处理", "\n";Output
真实服务端预处理In emulation mode PDO builds the SQL client-side —
LIMIT ? works but as a string. In native mode it errors. The divergence explains many odd bugs.连接
PHP
<?php
$dsns = [
"mysql" => "mysql:host=127.0.0.1;dbname=demo;charset=utf8mb4",
"pgsql" => "pgsql:host=127.0.0.1;dbname=demo",
"sqlite" => "sqlite:/var/data/demo.db",
];
foreach ($dsns as $driver => $dsn) {
echo $driver . " => " . strtok($dsn, ":") . "\n";
}
echo "可用驱动: " . implode(",", PDO::getAvailableDrivers()), "\n";Output
mysql => mysql
pgsql => pgsql
sqlite => sqlite
可用驱动: mysql,sqlite属性常量
| 常量 | 作用 |
|---|---|
| ATTR_ERRMODE | 错误模式（建议 ERRMODE_EXCEPTION） |
| ATTR_DEFAULT_FETCH_MODE | 默认取数模式 |
| ATTR_EMULATE_PREPARES | 是否模拟预处理（建议 false） |
| ATTR_STRINGIFY_FETCHES | 是否把数字转成字符串 |
| ATTR_PERSISTENT | 持久连接 |
| ATTR_TIMEOUT | 连接超时秒数 |
| ATTR_CASE | 列名大小写处理 |
核心方法
| 方法 | 作用 |
|---|---|
| prepare | 预编译语句 |
| execute | 执行（传参或空数组） |
| query | 执行无参 SQL |
| exec | 执行并返回影响行数（无结果集） |
| fetch | 取一行 |
| fetchAll | 取全部 |
| fetchColumn | 取一个值 |
| rowCount | 影响行数 |
| lastInsertId | 最后自增 ID |
| beginTransaction | 开始事务 |
| commit | 提交 |
| rollBack | 回滚 |
| quote | 转义字符串（少用，优先预处理） |
事务
PHP
<?php
$pdo = new PDO($dsn, $user, $pass, [PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION]);
$pdo->beginTransaction();
try {
$pdo->exec("UPDATE accounts SET balance = balance - 100 WHERE id = 1");
$pdo->exec("UPDATE accounts SET balance = balance + 100 WHERE id = 2");
$pdo->commit();
echo "转账成功\n";
} catch (Throwable $e) {
if ($pdo->inTransaction()) {
$pdo->rollBack();
}
echo "已回滚: " . $e->getMessage() . "\n";
}Output
转账成功catch 里要先 inTransaction() 再 rollBack()。如果异常发生在 commit 之后，再回滚会抛「no active transaction」。错误信息
PHP
<?php
$pdo = new PDO($dsn, $user, $pass, [PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION]);
try {
$pdo->exec("INSERT INTO users (name, email) VALUES ('A', 'a@e.com')");
} catch (PDOException $e) {
echo "SQLSTATE: " . $e->getCode() . "\n";
echo "驱动码: " . ($e->errorInfo[1] ?? "-") . "\n";
echo "驱动消息: " . ($e->errorInfo[2] ?? "-") . "\n";
}Output
SQLSTATE: 23000
驱动码: 1062
驱动消息: Duplicate entry 'a@e.com' for key 'uk_email'| SQLSTATE | 含义 |
|---|---|
| 23000 | 完整性约束冲突（唯一键、外键、非空） |
| 42S02 | 表不存在 |
| 42S22 | 列不存在 |
| 42000 | 语法错误或权限不足 |
| HY093 | 参数数量与占位符不符 |
| 08006 | 连接失败 |
模拟预处理
PHP
<?php
$pdo = new PDO($dsn, $user, $pass);
$pdo->setAttribute(PDO::ATTR_EMULATE_PREPARES, false);
echo $pdo->getAttribute(PDO::ATTR_EMULATE_PREPARES) ? "模拟" : "真实服务端预处理", "\n";Output
真实服务端预处理模拟模式下 PDO 在客户端拼好 SQL 再发，**
LIMIT ? 能工作但类型全变字符串**；真实预处理下 LIMIT ? 直接报错。两者行为差异是很多诡异 bug 的来源。