1. PHP与MySQL基础交互原理PHP操作MySQL数据库是Web开发中最基础也最核心的技能组合之一。作为服务端脚本语言PHP通过内置的MySQLi和PDO扩展与MySQL数据库建立连接并执行操作。这两种扩展都支持预处理语句能有效防止SQL注入攻击。MySQL作为关系型数据库管理系统采用客户端-服务器架构。PHP通过TCP/IP协议与MySQL服务器通信默认端口3306。每次操作都遵循连接-查询-获取结果-关闭连接的基本流程。重要提示虽然mysql_*函数在早期PHP版本中广泛使用但从PHP 5.5.0开始已被弃用PHP 7.0.0中完全移除。新项目应使用MySQLi或PDO。1.1 连接数据库的三种方式MySQLi面向过程方式$conn mysqli_connect(localhost, username, password, dbname); if (!$conn) { die(连接失败: . mysqli_connect_error()); }MySQLi面向对象方式$mysqli new mysqli(localhost, username, password, dbname); if ($mysqli-connect_error) { die(连接失败: . $mysqli-connect_error); }PDO方式推荐try { $pdo new PDO(mysql:hostlocalhost;dbnamedbname, username, password); $pdo-setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION); } catch(PDOException $e) { die(连接失败: . $e-getMessage()); }PDO的优势在于支持多种数据库系统统一的API接口更好的错误处理机制命名参数绑定更灵活2. 数据库增删改查完整实现2.1 创建数据Create使用MySQLi预处理语句插入数据$stmt $mysqli-prepare(INSERT INTO users (name, email, age) VALUES (?, ?, ?)); $stmt-bind_param(ssi, $name, $email, $age); $name 张三; $email zhangsanexample.com; $age 25; $stmt-execute(); echo 新记录插入成功ID: . $stmt-insert_id; $stmt-close();使用PDO插入数据$sql INSERT INTO users (name, email, age) VALUES (:name, :email, :age); $stmt $pdo-prepare($sql); $data [ name 李四, email lisiexample.com, age 30 ]; $stmt-execute($data); echo 新记录插入成功ID: . $pdo-lastInsertId();实际开发中所有用户输入都应经过验证和过滤后再插入数据库。PDO的参数绑定已经提供了基本的SQL注入防护。2.2 查询数据Read基本查询示例// MySQLi方式 $sql SELECT id, name, email FROM users WHERE age ?; $stmt $mysqli-prepare($sql); $minAge 18; $stmt-bind_param(i, $minAge); $stmt-execute(); $result $stmt-get_result(); while ($row $result-fetch_assoc()) { echo ID: {$row[id]}, 姓名: {$row[name]}, 邮箱: {$row[email]}br; } // PDO方式 $stmt $pdo-prepare(SELECT * FROM users WHERE created_at :date); $stmt-execute([date 2023-01-01]); $users $stmt-fetchAll(PDO::FETCH_ASSOC); foreach ($users as $user) { // 处理每行数据 }分页查询实现$page isset($_GET[page]) ? (int)$_GET[page] : 1; $perPage 10; $offset ($page - 1) * $perPage; $stmt $pdo-prepare(SELECT * FROM products LIMIT :offset, :perPage); $stmt-bindParam(:offset, $offset, PDO::PARAM_INT); $stmt-bindParam(:perPage, $perPage, PDO::PARAM_INT); $stmt-execute();2.3 更新数据UpdateMySQLi更新示例$stmt $mysqli-prepare(UPDATE users SET email ?, age ? WHERE id ?); $stmt-bind_param(sii, $email, $age, $id); $email new_emailexample.com; $age 28; $id 5; $stmt-execute(); echo 影响了 . $stmt-affected_rows . 行记录; $stmt-close();PDO事务处理更新try { $pdo-beginTransaction(); $stmt1 $pdo-prepare(UPDATE accounts SET balance balance - ? WHERE id ?); $stmt1-execute([100, 1]); $stmt2 $pdo-prepare(UPDATE accounts SET balance balance ? WHERE id ?); $stmt2-execute([100, 2]); $pdo-commit(); echo 转账成功; } catch (Exception $e) { $pdo-rollBack(); echo 转账失败: . $e-getMessage(); }2.4 删除数据Delete安全删除示例总应先查询再删除// 先验证记录存在 $check $pdo-prepare(SELECT id FROM orders WHERE id ? AND user_id ?); $check-execute([$orderId, $userId]); if (!$check-fetch()) { die(订单不存在或无权删除); } // 执行删除 $delete $pdo-prepare(DELETE FROM orders WHERE id ?); $delete-execute([$orderId]); if ($delete-rowCount() 0) { echo 订单删除成功; } else { echo 删除失败请重试; }3. 高级应用与性能优化3.1 预处理语句批量操作批量插入$pdo-beginTransaction(); $stmt $pdo-prepare(INSERT INTO logs (message, created_at) VALUES (?, NOW())); foreach ($messages as $msg) { $stmt-execute([$msg]); } $pdo-commit();批量更新$stmt $mysqli-prepare(UPDATE products SET price ? WHERE id ?); $stmt-bind_param(di, $price, $id); foreach ($updates as $item) { $price $item[price]; $id $item[id]; $stmt-execute(); }3.2 结果集处理技巧获取不同格式的结果// 关联数组默认 $stmt-fetch(PDO::FETCH_ASSOC); // 数字索引数组 $stmt-fetch(PDO::FETCH_NUM); // 同时使用列名和数字索引 $stmt-fetch(PDO::FETCH_BOTH); // 对象形式 $stmt-fetch(PDO::FETCH_OBJ); // 绑定到特定类 $stmt-fetchAll(PDO::FETCH_CLASS, User);3.3 数据库连接管理持久连接谨慎使用// MySQLi $mysqli new mysqli(p:localhost, user, password, db); // PDO $pdo new PDO( mysql:hostlocalhost;dbnametest, user, pass, [PDO::ATTR_PERSISTENT true] );持久连接可以减少连接建立的开销但在高并发环境下可能导致连接数过多。通常建议使用连接池或常规连接配合适当的超时设置。4. 安全防护与错误处理4.1 SQL注入防护永远不要这样做// 危险直接拼接SQL $sql SELECT * FROM users WHERE name {$_GET[name]};正确的参数化查询// MySQLi $stmt $mysqli-prepare(SELECT * FROM users WHERE name ?); $stmt-bind_param(s, $_GET[name]); // PDO $stmt $pdo-prepare(SELECT * FROM users WHERE name :name); $stmt-execute([name $_GET[name]]);4.2 错误处理最佳实践PDO错误模式设置$pdo-setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);自定义错误处理器set_exception_handler(function($e) { error_log(数据库错误: . $e-getMessage()); // 生产环境显示友好错误页面 if (ENV production) { include 500.html; exit; } // 开发环境显示详细错误 throw $e; });4.3 日志记录与监控// 记录慢查询 $start microtime(true); // 执行查询... $duration microtime(true) - $start; if ($duration 1) { // 超过1秒视为慢查询 $log sprintf( [%s] Slow query: %.3fs - %s\n, date(Y-m-d H:i:s), $duration, $sql ); file_put_contents(/var/log/mysql-slow.log, $log, FILE_APPEND); }5. 实战案例用户管理系统5.1 数据库设计CREATE TABLE users ( id int(11) NOT NULL AUTO_INCREMENT, username varchar(50) NOT NULL, password varchar(255) NOT NULL, email varchar(100) NOT NULL, created_at datetime NOT NULL DEFAULT CURRENT_TIMESTAMP, updated_at datetime DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP, PRIMARY KEY (id), UNIQUE KEY username (username), UNIQUE KEY email (email) ) ENGINEInnoDB DEFAULT CHARSETutf8mb4;5.2 用户注册实现function registerUser(PDO $pdo, array $data): int { // 验证输入 if (empty($data[username]) || empty($data[password])) { throw new InvalidArgumentException(用户名和密码不能为空); } // 检查用户名是否已存在 $stmt $pdo-prepare(SELECT id FROM users WHERE username ?); $stmt-execute([$data[username]]); if ($stmt-fetch()) { throw new RuntimeException(用户名已存在); } // 密码哈希 $hash password_hash($data[password], PASSWORD_DEFAULT); // 插入用户 $stmt $pdo-prepare( INSERT INTO users (username, password, email) VALUES (?, ?, ?) ); $stmt-execute([ $data[username], $hash, $data[email] ?? null ]); return $pdo-lastInsertId(); }5.3 用户登录验证function authenticate(PDO $pdo, string $username, string $password): ?array { $stmt $pdo-prepare( SELECT id, username, password FROM users WHERE username ? ); $stmt-execute([$username]); $user $stmt-fetch(PDO::FETCH_ASSOC); if ($user password_verify($password, $user[password])) { unset($user[password]); // 不要返回密码 return $user; } return null; }5.4 用户列表分页function getUsers(PDO $pdo, int $page 1, int $perPage 10): array { $offset ($page - 1) * $perPage; // 获取总数 $countStmt $pdo-query(SELECT COUNT(*) FROM users); $total (int)$countStmt-fetchColumn(); // 获取分页数据 $stmt $pdo-prepare( SELECT id, username, email, created_at FROM users ORDER BY id DESC LIMIT :offset, :limit ); $stmt-bindValue(:offset, $offset, PDO::PARAM_INT); $stmt-bindValue(:limit, $perPage, PDO::PARAM_INT); $stmt-execute(); return [ data $stmt-fetchAll(PDO::FETCH_ASSOC), total $total, page $page, perPage $perPage, totalPages ceil($total / $perPage) ]; }6. 常见问题与解决方案6.1 连接问题排查错误SQLSTATE[HY000] [2002] Connection refused可能原因MySQL服务未运行连接地址或端口错误防火墙阻止了连接解决方案# 检查MySQL服务状态 sudo systemctl status mysql # 测试网络连接 telnet localhost 33066.2 字符编码问题中文乱码解决方案创建连接后立即设置字符集// MySQLi $mysqli-set_charset(utf8mb4); // PDO $pdo-exec(SET NAMES utf8mb4);确保数据库、表和字段都使用utf8mb4编码ALTER DATABASE dbname CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci; ALTER TABLE tablename CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;6.3 性能优化技巧索引优化为常用查询条件添加索引ALTER TABLE users ADD INDEX idx_email (email);查询缓存MySQL 8.0已移除查询缓存应考虑应用层缓存批量操作使用事务减少磁盘I/O$pdo-beginTransaction(); // 执行多个操作... $pdo-commit();连接池考虑使用Swoole等协程框架管理数据库连接6.4 事务处理常见错误错误SQLSTATE[25006]: Read only sql transaction原因在只读事务中尝试写操作解决方案// 明确指定事务模式 $pdo-beginTransaction(); // 默认为读写 // 或 $pdo-beginTransaction(PDO::TRANSACTION_READ_WRITE);错误隐式提交某些语句会隐式提交当前事务CREATE/ALTER/DROP TABLETRUNCATEGRANT/REVOKE解决方案避免在事务中执行这些语句或在执行后重新开始事务