PHP与MySQL数据库实战指南

PHP与MySQL数据库实战指南PHP和MySQL是经典搭档。从最早的mysql扩展到mysqli再到PDOPHP连接MySQL的方式一直在进化。今天说说在实际项目中怎么用好这对组合。首先说连接方式。PDO是目前的首选它支持多种数据库使用预处理语句天然防止SQL注入。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操作。增删改查各有对应的SQL语句。php// 插入数据$stmt $pdo-prepare(INSERT INTO users (name, email, age, status, created_at)VALUES (:name, :email, :age, :status, NOW()));$stmt-execute([:name 张三,:email zhangsanexample.com,:age 28,:status active,]);$userId $pdo-lastInsertId();echo 插入成功ID: $userId\n;// 批量插入$users [[李四, lisitest.com, 25],[王五, wangwutest.com, 30],[赵六, zhaoliutest.com, 22],];$stmt $pdo-prepare(INSERT INTO users (name, email, age, status, created_at)VALUES (?, ?, ?, active, NOW()));foreach ($users as $user) {$stmt-execute($user);}echo 批量插入完成\n;?查询数据是最常用的操作。PDO提供了多种获取模式。php// 查询所有$stmt $pdo-query(SELECT * FROM users WHERE status active);$users $stmt-fetchAll();echo 活跃用户数: . count($users) . \n;foreach ($users as $user) {echo {$user[name]} ({$user[email]})\n;}// 查询单条$stmt $pdo-prepare(SELECT * FROM users WHERE id ?);$stmt-execute([1]);$user $stmt-fetch();if ($user) {echo 用户: {$user[name]}, 年龄: {$user[age]}\n;} else {echo 用户不存在\n;}// 查询单列$stmt $pdo-query(SELECT name FROM users);$names $stmt-fetchAll(PDO::FETCH_COLUMN);print_r($names);// 查询单个值$stmt $pdo-query(SELECT COUNT(*) FROM users);$count $stmt-fetchColumn();echo 用户总数: $count\n;// 分组统计$stmt $pdo-query(SELECT status, COUNT(*) as countFROM usersGROUP BY status);$stats $stmt-fetchAll();print_r($stats);?更新和删除操作需要谨慎一定要带上WHERE条件。php// 更新$stmt $pdo-prepare(UPDATE users SET age :age, updated_at NOW() WHERE id :id);$stmt-execute([:age 29, :id 1]);echo 更新了 {$stmt-rowCount()} 行\n;// 删除$stmt $pdo-prepare(DELETE FROM users WHERE id ?);$stmt-execute([1]);echo 删除了 {$stmt-rowCount()} 行\n;?事务处理保证多个操作要么全部成功要么全部失败。php// 转账示例function transferMoney(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]);// 记录日志$stmt $pdo-prepare(INSERT INTO transfer_log (from_id, to_id, amount, created_at)VALUES (?, ?, ?, NOW()));$stmt-execute([$fromId, $toId, $amount]);$pdo-commit();echo 转账成功\n;} catch (Exception $e) {$pdo-rollBack();echo 转账失败: . $e-getMessage() . \n;}}$pdo-beginTransaction();// 各种操作...$pdo-commit();?分页查询在列表页中很常用。传统的OFFSET分页在数据量大时性能差可以用游标分页替代。php// 传统分页OFFSET越大越慢function paginateOffset(PDO $pdo, int $page, int $perPage 20): array{$offset ($page - 1) * $perPage;$stmt $pdo-prepare(SELECT * FROM articlesORDER BY created_at DESCLIMIT ? OFFSET ?);$stmt-execute([$perPage, $offset]);$items $stmt-fetchAll();$count $pdo-query(SELECT COUNT(*) FROM articles)-fetchColumn();return [items $items,total $count,page $page,per_page $perPage,total_pages ceil($count / $perPage),];}// 游标分页性能稳定function paginateCursor(PDO $pdo, ?int $cursor, int $perPage 20): array{if ($cursor null) {$stmt $pdo-prepare(SELECT * FROM articlesORDER BY id DESCLIMIT ?);$stmt-execute([$perPage]);} else {$stmt $pdo-prepare(SELECT * FROM articlesWHERE id ?ORDER BY id DESCLIMIT ?);$stmt-execute([$cursor, $perPage]);}$items $stmt-fetchAll();$nextCursor !empty($items) ? end($items)[id] : null;return [items $items,next_cursor $nextCursor,has_more count($items) $perPage,];}// 使用游标分页$result paginateCursor($pdo, null, 20);echo 获取了 . count($result[items]) . 篇文章\n;if ($result[has_more]) {echo 下一页游标: {$result[next_cursor]}\n;}?索引优化是数据库性能的关键。没有索引的查询会全表扫描数据量大时很慢。php// 创建索引$pdo-exec(ALTER TABLE users ADD INDEX idx_email (email));$pdo-exec(ALTER TABLE users ADD INDEX idx_status_created (status, created_at));$pdo-exec(ALTER TABLE articles ADD FULLTEXT idx_content (title, content));// 使用索引的查询$stmt $pdo-prepare(SELECT * FROM users WHERE email ?);$stmt-execute([testexample.com]);// 这个查询会使用idx_email索引// 全文搜索$stmt $pdo-prepare(SELECT * FROM articlesWHERE MATCH(title, content) AGAINST(? IN BOOLEAN MODE));$stmt-execute([PHP MySQL]);$articles $stmt-fetchAll();?连接管理在生产环境要注意。每次请求都创建新连接开销大可以考虑长连接或连接池。PHP-FPM模式下用持久连接要小心因为连接状态可能被上一个请求污染。总的来说PHP操作MySQL用PDO就对了。预处理防止注入事务保证一致性索引提升性能。这些做好了数据库层面基本没啥大问题。