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([张三, zhangsantest.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;?事务处理确保多个操作要么全部成功要么全部失败。phpfunction 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分页性能好数据量大时效果明显。phpfunction 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注入事务保证数据一致性合适的获取模式提高效率。这些做到了数据库操作这块基本不会出问题。