PHP与MySQL交互最佳实践

PHP与MySQL交互最佳实践PHP和MySQL是经典搭配。PDO是现代PHP操作数据库的标准方式。今天说说PHP与MySQL交互的各种实践。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,PDO::MYSQL_ATTR_INIT_COMMAND SET NAMES utf8mb4 COLLATE utf8mb4_unicode_ci,];try {$pdo new PDO($dsn, $username, $password, $options);echo 连接成功\n;} catch (PDOException $e) {die(连接失败: . $e-getMessage());}?CRUD操作。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]);?事务处理。phpfunction transferMoney(PDO $pdo, int $fromId, int $toId, float $amount): void{try {$pdo-beginTransaction();$pdo-prepare(UPDATE accounts SET balance balance - ? WHERE id ?)-execute([$amount, $fromId]);$stmt $pdo-prepare(SELECT balance FROM accounts WHERE id ?);$stmt-execute([$fromId]);if ($stmt-fetchColumn() 0) throw new Exception(余额不足);$pdo-prepare(UPDATE accounts SET balance balance ? WHERE id ?)-execute([$amount, $toId]);$pdo-prepare(INSERT INTO transfer_log (from_id, to_id, amount) VALUES (?, ?, ?))-execute([$fromId, $toId, $amount]);$pdo-commit();echo 转账成功\n;} catch (Exception $e) {$pdo-rollBack();echo 转账失败: {$e-getMessage()}\n;}}?游标分页。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];}?连接管理在生产环境要注意。每次请求都创建新连接开销大。PHP-FPM模式下用持久连接要小心连接状态可能被上一个请求污染。一般来说用PDO默认配置就行不需要额外优化。PHP操作MySQL用PDO就对了。预处理防止注入事务保证一致性索引提升性能。这些做好了数据库层面基本没啥大问题。