第12课: MySQL数据库操作

MySQL数据库基础

MySQL是最流行的开源关系型数据库管理系统。PHP提供了多种方式连接和操作MySQL数据库。

连接方式对比

PHP连接MySQL的三种方式：

MySQLi：MySQL改进扩展，支持面向对象和过程式
PDO：PHP数据对象，支持多种数据库
MySQL（已废弃）：旧版扩展，不推荐使用

MySQLi连接

面向对象方式

<?php
// 创建连接
$conn = new mysqli("localhost", "username", "password", "database");

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

// 设置字符集
$conn->set_charset("utf8mb4");

echo "连接成功";

// 关闭连接
$conn->close();
?>

过程式方式

<?php
// 创建连接
$conn = mysqli_connect("localhost", "username", "password", "database");

// 检查连接
if (!$conn) {
    die("连接失败: " . mysqli_connect_error());
}

// 设置字符集
mysqli_set_charset($conn, "utf8mb4");

echo "连接成功";

// 关闭连接
mysqli_close($conn);
?>

执行SQL查询

插入数据

<?php
$conn = new mysqli("localhost", "username", "password", "database");

// 插入单条记录
$sql = "INSERT INTO users (name, email, age) VALUES ('张三', 'zhang@example.com', 25)";

if ($conn->query($sql) === TRUE) {
    echo "新记录插入成功\n";
    echo "新记录ID: " . $conn->insert_id;
} else {
    echo "错误: " . $conn->error;
}

// 插入多条记录
$sql = "INSERT INTO users (name, email, age) VALUES
        ('李四', 'li@example.com', 30),
        ('王五', 'wang@example.com', 28),
        ('赵六', 'zhao@example.com', 35)";

if ($conn->query($sql) === TRUE) {
    echo "插入了 " . $conn->affected_rows . " 条记录";
}

$conn->close();
?>

查询数据

<?php
$conn = new mysqli("localhost", "username", "password", "database");

$sql = "SELECT id, name, email, age FROM users";
$result = $conn->query($sql);

if ($result->num_rows > 0) {
    // 输出每行数据
    while($row = $result->fetch_assoc()) {
        echo "ID: " . $row["id"] . "\n";
        echo "姓名: " . $row["name"] . "\n";
        echo "邮箱: " . $row["email"] . "\n";
        echo "年龄: " . $row["age"] . "\n\n";
    }
} else {
    echo "0 条结果";
}

// 释放结果集
$result->free();
$conn->close();
?>

更新数据

<?php
$conn = new mysqli("localhost", "username", "password", "database");

$sql = "UPDATE users SET email='newemail@example.com', age=26 WHERE id=1";

if ($conn->query($sql) === TRUE) {
    echo "记录更新成功\n";
    echo "影响了 " . $conn->affected_rows . " 行";
} else {
    echo "错误: " . $conn->error;
}

$conn->close();
?>

删除数据

<?php
$conn = new mysqli("localhost", "username", "password", "database");

$sql = "DELETE FROM users WHERE id=1";

if ($conn->query($sql) === TRUE) {
    echo "记录删除成功\n";
    echo "删除了 " . $conn->affected_rows . " 行";
} else {
    echo "错误: " . $conn->error;
}

$conn->close();
?>

预处理语句（防SQL注入）

使用问号占位符

<?php
$conn = new mysqli("localhost", "username", "password", "database");

// 准备语句
$stmt = $conn->prepare("INSERT INTO users (name, email, age) VALUES (?, ?, ?)");

// 绑定参数（s=string, i=integer, d=double, b=blob）
$stmt->bind_param("ssi", $name, $email, $age);

// 设置参数并执行
$name = "张三";
$email = "zhang@example.com";
$age = 25;
$stmt->execute();

$name = "李四";
$email = "li@example.com";
$age = 30;
$stmt->execute();

echo "插入了 " . $stmt->affected_rows . " 条记录";

$stmt->close();
$conn->close();
?>

查询预处理

<?php
$conn = new mysqli("localhost", "username", "password", "database");

// 准备查询语句
$stmt = $conn->prepare("SELECT id, name, email FROM users WHERE age > ?");
$stmt->bind_param("i", $age);

$age = 25;
$stmt->execute();

// 绑定结果变量
$stmt->bind_result($id, $name, $email);

// 获取结果
while ($stmt->fetch()) {
    echo "ID: $id, 姓名: $name, 邮箱: $email\n";
}

$stmt->close();
$conn->close();
?>

PDO方式

PDO连接

<?php
try {
    // 创建PDO连接
    $dsn = "mysql:host=localhost;dbname=database;charset=utf8mb4";
    $pdo = new PDO($dsn, "username", "password");

    // 设置错误模式为异常
    $pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

    // 设置默认获取模式为关联数组
    $pdo->setAttribute(PDO::ATTR_DEFAULT_FETCH_MODE, PDO::FETCH_ASSOC);

    echo "连接成功";
} catch(PDOException $e) {
    die("连接失败: " . $e->getMessage());
}
?>

PDO执行查询

<?php
try {
    $pdo = new PDO("mysql:host=localhost;dbname=database", "user", "pass");
    $pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

    // 插入数据
    $sql = "INSERT INTO users (name, email) VALUES ('张三', 'zhang@example.com')";
    $pdo->exec($sql);
    echo "新记录ID: " . $pdo->lastInsertId();

    // 查询数据
    $sql = "SELECT * FROM users";
    $stmt = $pdo->query($sql);

    while ($row = $stmt->fetch()) {
        echo $row["name"] . "\n";
    }

    // 获取所有结果
    $users = $stmt->fetchAll();
    print_r($users);

} catch(PDOException $e) {
    echo "错误: " . $e->getMessage();
}
?>

PDO预处理语句

<?php
try {
    $pdo = new PDO("mysql:host=localhost;dbname=database", "user", "pass");
    $pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

    // 使用命名占位符
    $stmt = $pdo->prepare("INSERT INTO users (name, email, age) VALUES (:name, :email, :age)");
    $stmt->execute([
        ':name' => '张三',
        ':email' => 'zhang@example.com',
        ':age' => 25
    ]);

    // 使用问号占位符
    $stmt = $pdo->prepare("SELECT * FROM users WHERE age > ?");
    $stmt->execute([25]);
    $users = $stmt->fetchAll();

    // 绑定参数
    $stmt = $pdo->prepare("UPDATE users SET email = :email WHERE id = :id");
    $stmt->bindParam(':email', $email);
    $stmt->bindParam(':id', $id, PDO::PARAM_INT);

    $email = "new@example.com";
    $id = 1;
    $stmt->execute();

} catch(PDOException $e) {
    echo "错误: " . $e->getMessage();
}
?>

事务处理

<?php
// MySQLi事务
$conn = new mysqli("localhost", "username", "password", "database");

try {
    // 开始事务
    $conn->begin_transaction();

    $conn->query("INSERT INTO users (name) VALUES ('张三')");
    $conn->query("UPDATE accounts SET balance = balance - 100 WHERE user_id = 1");
    $conn->query("UPDATE accounts SET balance = balance + 100 WHERE user_id = 2");

    // 提交事务
    $conn->commit();
    echo "事务成功";
} catch (Exception $e) {
    // 回滚事务
    $conn->rollback();
    echo "事务失败: " . $e->getMessage();
}

$conn->close();

// PDO事务
try {
    $pdo = new PDO("mysql:host=localhost;dbname=database", "user", "pass");
    $pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

    $pdo->beginTransaction();

    $pdo->exec("INSERT INTO users (name) VALUES ('李四')");
    $pdo->exec("UPDATE accounts SET balance = balance - 100 WHERE user_id = 1");
    $pdo->exec("UPDATE accounts SET balance = balance + 100 WHERE user_id = 2");

    $pdo->commit();
    echo "事务成功";
} catch (Exception $e) {
    $pdo->rollBack();
    echo "事务失败: " . $e->getMessage();
}
?>

数据库操作封装

<?php
class Database {
    private $conn;

    public function __construct($host, $user, $pass, $db) {
        $this->conn = new mysqli($host, $user, $pass, $db);

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

        $this->conn->set_charset("utf8mb4");
    }

    // 查询单条记录
    public function fetchOne($sql, $params = []) {
        $stmt = $this->conn->prepare($sql);

        if (!empty($params)) {
            $types = str_repeat('s', count($params));
            $stmt->bind_param($types, ...$params);
        }

        $stmt->execute();
        $result = $stmt->get_result();
        return $result->fetch_assoc();
    }

    // 查询多条记录
    public function fetchAll($sql, $params = []) {
        $stmt = $this->conn->prepare($sql);

        if (!empty($params)) {
            $types = str_repeat('s', count($params));
            $stmt->bind_param($types, ...$params);
        }

        $stmt->execute();
        $result = $stmt->get_result();
        return $result->fetch_all(MYSQLI_ASSOC);
    }

    // 执行插入/更新/删除
    public function execute($sql, $params = []) {
        $stmt = $this->conn->prepare($sql);

        if (!empty($params)) {
            $types = str_repeat('s', count($params));
            $stmt->bind_param($types, ...$params);
        }

        return $stmt->execute();
    }

    // 获取最后插入的ID
    public function lastInsertId() {
        return $this->conn->insert_id;
    }

    // 关闭连接
    public function close() {
        $this->conn->close();
    }
}

// 使用示例
$db = new Database("localhost", "user", "pass", "mydb");

// 查询单条
$user = $db->fetchOne("SELECT * FROM users WHERE id = ?", [1]);

// 查询多条
$users = $db->fetchAll("SELECT * FROM users WHERE age > ?", [25]);

// 插入数据
$db->execute("INSERT INTO users (name, email) VALUES (?, ?)", ["张三", "zhang@example.com"]);
$userId = $db->lastInsertId();

// 更新数据
$db->execute("UPDATE users SET email = ? WHERE id = ?", ["new@example.com", 1]);

// 删除数据
$db->execute("DELETE FROM users WHERE id = ?", [1]);

$db->close();
?>

常用查询示例

分页查询

<?php
$page = $_GET['page'] ?? 1;
$perPage = 10;
$offset = ($page - 1) * $perPage;

// 获取总记录数
$totalSql = "SELECT COUNT(*) as total FROM users";
$totalResult = $conn->query($totalSql);
$total = $totalResult->fetch_assoc()['total'];
$totalPages = ceil($total / $perPage);

// 分页查询
$sql = "SELECT * FROM users LIMIT $perPage OFFSET $offset";
$result = $conn->query($sql);

while ($row = $result->fetch_assoc()) {
    echo $row['name'] . "\n";
}

echo "第 $page 页，共 $totalPages 页";
?>

搜索和过滤

<?php
$keyword = $_GET['keyword'] ?? '';
$minAge = $_GET['min_age'] ?? 0;
$maxAge = $_GET['max_age'] ?? 100;

$stmt = $conn->prepare("
    SELECT * FROM users
    WHERE (name LIKE ? OR email LIKE ?)
    AND age BETWEEN ? AND ?
    ORDER BY created_at DESC
");

$searchTerm = "%$keyword%";
$stmt->bind_param("ssii", $searchTerm, $searchTerm, $minAge, $maxAge);
$stmt->execute();
$result = $stmt->get_result();

while ($row = $result->fetch_assoc()) {
    print_r($row);
}
?>

联表查询

<?php
$sql = "
    SELECT
        u.id, u.name, u.email,
        p.title, p.content, p.created_at
    FROM users u
    LEFT JOIN posts p ON u.id = p.user_id
    WHERE u.id = ?
    ORDER BY p.created_at DESC
";

$stmt = $conn->prepare($sql);
$stmt->bind_param("i", $userId);
$userId = 1;
$stmt->execute();
$result = $stmt->get_result();

while ($row = $result->fetch_assoc()) {
    echo "用户: " . $row['name'] . "\n";
    echo "文章: " . $row['title'] . "\n";
}
?>

错误处理

<?php
// MySQLi错误处理
mysqli_report(MYSQLI_REPORT_ERROR | MYSQLI_REPORT_STRICT);

try {
    $conn = new mysqli("localhost", "user", "pass", "db");
    $conn->set_charset("utf8mb4");

    $stmt = $conn->prepare("INSERT INTO users (name, email) VALUES (?, ?)");
    $stmt->bind_param("ss", $name, $email);

    $name = "张三";
    $email = "zhang@example.com";
    $stmt->execute();

} catch (mysqli_sql_exception $e) {
    error_log("数据库错误: " . $e->getMessage());
    echo "操作失败，请稍后重试";
}

// PDO错误处理
try {
    $pdo = new PDO("mysql:host=localhost;dbname=db", "user", "pass");
    $pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

    $stmt = $pdo->prepare("INSERT INTO users (name, email) VALUES (?, ?)");
    $stmt->execute(["李四", "li@example.com"]);

} catch (PDOException $e) {
    error_log("数据库错误: " . $e->getMessage());
    echo "操作失败，请稍后重试";
}
?>

安全建议：

始终使用预处理语句防止SQL注入
不要在SQL中直接拼接用户输入
使用最小权限原则配置数据库用户
敏感信息加密存储（如密码使用password_hash）
定期备份数据库
在生产环境关闭错误显示，使用日志记录

MySQLi vs PDO：

MySQLi：仅支持MySQL，性能略好，支持异步查询
PDO：支持多种数据库，更好的可移植性，统一的API
建议：新项目推荐使用PDO，已有项目可继续使用MySQLi

实践练习

数据库连接：创建数据库连接类，支持单例模式
CRUD操作：实现用户的增删改查功能
预处理语句：使用预处理语句实现安全的登录验证
分页功能：实现带搜索的分页列表
事务处理：实现转账功能，使用事务保证数据一致性
联表查询：查询用户及其发布的所有文章
数据导出：将查询结果导出为CSV文件
ORM封装：封装简单的ORM类，实现模型操作

← 上一课下一课 →