三，上机实验：PHP操作MySQL数据库

'# 三，上机实验：PHP操作MySQL数据库

一、背景与问题

在Web开发中，数据持久化是核心需求。PHP作为服务器端脚本语言，其与MySQL数据库的交互是开发流程中最关键的环节之一。现代Web应用通常需要实现以下功能：

• 数据库存储结构设计
• SQL查询优化
• 事务处理
• 安全防护
• 性能调优

然而在实际开发中，开发者常遇到以下问题：

1. SQL注入漏洞导致数据泄露
2. 未正确处理连接资源造成内存泄漏
3. 事务未正确提交导致数据不一致
4. 未合理使用索引造成查询性能下降
5. 网络异常导致连接失败未处理

二、基本原理

PHP与MySQL的交互基于以下技术原理：

1. 网络通信协议

PHP通过MySQL协议与MySQL服务器进行通信，该协议基于TCP/IP协议栈。通信过程包含以下阶段：

• 建立TCP连接
• 发送查询语句
• 接收结果集
• 关闭连接

2. 数据库连接机制

PHP通过以下方式建立连接：

$conn = mysqli_connect($host, $user, $password, $dbname);

底层实现涉及：

• 套接字(Socket)通信
• 协议握手
• 链路保持机制

3. 查询执行流程

每个查询操作包含：

1. SQL解析
2. 查询缓存（MySQL 8.0已移除）
3. 查询优化器生成执行计划
4. 执行引擎处理
5. 结果集返回

三、环境准备

1. 系统要求

• PHP 7.4+（推荐8.0+）
• MySQL 5.7+（推荐8.0+）
• 开发环境：XAMPP/LAMP/WAMP

2. 安装配置

# 安装MySQL
sudo apt install mysql-server

# 创建数据库
mysql -u root -p
CREATE DATABASE blog_db;

# 创建用户
CREATE USER 'blog_user'@'localhost' IDENTIFIED BY 'secure_password';
GRANT ALL PRIVILEGES ON blog_db.* TO 'blog_user'@'localhost';
FLUSH PRIVILEGES;

3. PHP扩展

# 安装MySQLi扩展
sudo apt install php-mysql

# 安装PDO扩展
sudo apt install php-pdo php-mysql

# 启用MySQLnd扩展（PHP 7+）
sudo apt install php-mysqlnd

四、核心实现

1. 基础连接与查询

<?php
// 基础连接示例
$host = 'localhost';
$user = 'blog_user';
$password = 'secure_password';
$dbname = 'blog_db';

// 使用MySQLi连接
$conn = new mysqli($host, $user, $password, $dbname);

// 检查连接
if ($conn->connect_error) {
    die("连接失败: " . $conn->connect_error);
}

// 查询示例
$sql = "SELECT id, title FROM articles";
$result = $conn->query($sql);

if ($result->num_rows > 0) {
    while($row = $result->fetch_assoc()) {
        echo "ID: " . $row["id"]. " - Title: " . $row["title"]. "<br>";
    }
} else {
    echo "0 结果";
}

$conn->close();
?>

关键点解析：

• 使用new mysqli()创建连接对象
• 通过connect_error属性检查连接状态
• 使用query()执行SQL查询
• 通过fetch_assoc()获取结果集
• 关闭连接前确保资源释放

2. 预处理语句（防止SQL注入）

<?php
// 预处理语句示例
$stmt = $conn->prepare("INSERT INTO users (username, email) VALUES (?, ?)");
$username = 'john_doe';
$email = 'john@example.com';

$stmt->bind_param("ss", $username, $email);
$stmt->execute();
$stmt->close();

// 查询示例
$stmt = $conn->prepare("SELECT * FROM users WHERE id = ?");
$id = 1;
$stmt->bind_param("i", $id);
$stmt->execute();
$result = $stmt->get_result();
?>

关键点解析：

• 使用prepare()创建预处理语句
• 通过bind_param()绑定参数
• 使用参数类型标识符（"s"表示字符串，"i"表示整数）
• 通过get_result()获取结果集

3. 事务处理

<?php
// 事务处理示例
$conn->begin_transaction();

try {
    $conn->query("START TRANSACTION");
    
    // 插入用户
    $stmt = $conn->prepare("INSERT INTO users (username, email) VALUES (?, ?)");
    $stmt->bind_param("ss", $username, $email);
    $stmt->execute();
    
    // 更新文章
    $stmt = $conn->prepare("UPDATE articles SET status = 'published' WHERE id = ?");
    $stmt->bind_param("i", $article_id);
    $stmt->execute();
    
    $conn->commit();
} catch (Exception $e) {
    $conn->rollback();
    echo "事务回滚: " . $e->getMessage();
}
?>

关键点解析：

• 使用begin_transaction()开启事务
• 通过START TRANSACTION显式控制
• 使用commit()提交事务
• 使用rollback()回滚事务
• 异常处理确保事务完整性

五、完整案例：博客系统

1. 数据库设计

CREATE DATABASE blog_db;

USE blog_db;

CREATE TABLE users (
    id INT AUTO_INCREMENT PRIMARY KEY,
    username VARCHAR(50) NOT NULL UNIQUE,
    email VARCHAR(100) NOT NULL UNIQUE,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE articles (
    id INT AUTO_INCREMENT PRIMARY KEY,
    title VARCHAR(255) NOT NULL,
    content TEXT,
    author_id INT,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (author_id) REFERENCES users(id)
);

2. PHP实现（关键部分）

<?php
// 用户注册
function registerUser($username, $email, $password) {
    $conn = new mysqli('localhost', 'blog_user', 'secure_password', 'blog_db');
    
    if ($conn->connect_error) {
        throw new Exception("数据库连接失败");
    }
    
    $stmt = $conn->prepare("INSERT INTO users (username, email, password) VALUES (?, ?, ?)");
    $stmt->bind_param("sss", $username, $email, $password);
    
    if (!$stmt->execute()) {
        throw new Exception("注册失败: " . $stmt->error);
    }
    
    $stmt->close();
    $conn->close();
}

// 文章发布
function publishArticle($title, $content, $author_id) {
    $conn = new mysqli('localhost', 'blog_user', 'secure_password', 'blog_db');
    
    if ($conn->connect_error) {
        throw new Exception("数据库连接失败");
    }
    
    $stmt = $conn->prepare("INSERT INTO articles (title, content, author_id) VALUES (?, ?, ?)");
    $stmt->bind_param("ssi", $title, $content, $author_id);
    
    if (!$stmt->execute()) {
        throw new Exception("发布失败: " . $stmt->error);
    }
    
    $stmt->close();
    $conn->close();
}
?>

3. 安全措施

• 密码存储：使用password_hash()和password_verify()函数
• SQL注入防护：使用预处理语句
• XSS防护：对用户输入进行过滤
• 防止CSRF：使用token验证机制

六、源码解析

1. MySQLi扩展源码结构

PHP的MySQLi扩展是C语言实现的，核心源码位于ext/mysqli/目录。关键文件包括：

• mysqli.c：主入口文件
• mysqli_stmt.c：预处理语句实现
• mysqli_result.c：结果集处理
• mysqli_connect.c：连接管理

2. 查询执行流程

// 简化版查询执行流程
PHP_METHOD(mysqli, query) {
    zval *query;
    char *sql;
    size_t sql_len;
    mysqli_connect_data *conn;

    if (zend_parse_parameters(ZEND_NUM_ARGS, "s", &sql, &sql_len) == FAILURE) {
        return;
    }

    conn = mysqli_get_connect_data(Z_OBJ_P(obj));
    if (!conn->socket) {
        php_error_docref(NULL, E_WARNING, "没有有效的数据库连接");
        return;
    }

    // 发送查询到MySQL服务器
    if (mysql_real_query(conn->socket, sql, sql_len) != 0) {
        // 处理错误
    }
}

七、进阶使用

1. 性能优化技巧

1. 索引优化：

   CREATE INDEX idx_author ON articles(author_id);

2. 查询缓存（MySQL 8.0已移除）：

   $stmt = $conn->prepare("SELECT * FROM users WHERE id = ?");
   $stmt->bind_param("i", $id);

3. 批量操作：

   $stmt = $conn->prepare("INSERT INTO logs (message) VALUES (?)");
   for ($i=0; $i<100; $i++) {
       $stmt->bind_param("s", "log message $i");
       $stmt->execute();
   }

2. 高级功能

• 事务隔离级别：

  SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;

• 锁机制：

  START TRANSACTION;
  SELECT * FROM articles WHERE id = 1 FOR UPDATE;

• 查询分析：

  EXPLAIN SELECT * FROM articles WHERE author_id = 1;

八、性能与工程实践

1. 性能优化策略

2. 异常处理机制

try {
    $conn->begin_transaction();
    // 执行业务逻辑
    $conn->commit();
} catch (Exception $e) {
    $conn->rollback();
    error_log("事务失败: " . $e->getMessage());
    throw $e;
}

3. 安全防护措施

1. 输入验证：

   if (!filter_var($email, FILTER_VALIDATE_EMAIL)) {
       throw new Exception("无效的电子邮件地址");
   }

2. 参数过滤：

   $safe_title = htmlspecialchars($title, ENT_QUOTES, 'UTF-8');

3. 会话管理：

   session_start();
   if (!isset($_SESSION['user_id'])) {
       header("Location: login.php");
       exit;
   }

九、常见问题与踩坑

1. 常见错误及解决办法

2. 常见陷阱

• 连接泄漏：未及时关闭连接
• 资源未释放：未调用free_result()方法
• 事务未提交：忘记调用commit()方法
• 索引未使用：未为常用查询字段添加索引
• 密码明文存储：未使用哈希算法存储密码

十、最佳实践

1. 推荐方案

1. 使用PDO：提供统一的数据库抽象层
2. 使用预处理语句：防止SQL注入
3. 使用事务处理：确保数据一致性
4. 使用连接池：提高高并发性能
5. 使用索引优化：提高查询效率
6. 使用日志记录：便于排查问题

2. 推荐代码结构

/blog
│
├── config
│   └── db.php          # 数据库配置
│
├── models
│   ├── User.php       # 用户模型
│   └── Article.php    # 文章模型
│
├── controllers
│   ├── UserController.php
│   └── ArticleController.php
│
├── views
│   ├── user
│   └── article
│
└── index.php          # 入口文件

3. 推荐开发流程

1. 设计数据库结构
2. 编写配置文件
3. 实现数据访问层
4. 编写业务逻辑层
5. 开发前端界面
6. 进行单元测试
7. 做性能优化
8. 部署上线

十一、总结

PHP操作MySQL数据库是Web开发中的核心技能，需要深入理解其工作原理和实现机制。通过本文的详细讲解，我们掌握了：

1. PHP与MySQL的通信原理
2. 多种连接方式的实现方法
3. 预处理语句的使用技巧
4. 事务处理的完整流程
5. 性能优化的多种策略
6. 安全防护的常见方法
7. 实际开发中容易遇到的陷阱
8. 推荐的最佳实践方案

在实际开发中，应根据具体需求选择合适的方案。对于高并发场景推荐使用PDO和连接池；对于安全敏感的系统必须使用预处理语句；对于复杂查询需要合理使用索引。同时要避免常见错误，如连接泄漏、事务未提交等。

通过规范的开发流程和良好的代码结构，可以确保数据库操作的稳定性、安全性和可维护性，为构建高质量的Web应用打下坚实基础。