🐘 MySQL PHP 语法

PHP 是最常用的 Web 开发语言之一，与 MySQL 数据库的集成非常紧密。本章将介绍如何在 PHP 中连接和操作 MySQL 数据库。

📊 PHP MySQL 连接方法对比

方法	MySQL 5.7	MySQL 8.x	推荐度
MySQLi	✅ 支持	✅ 支持	⭐⭐⭐⭐
PDO	✅ 支持	✅ 支持	⭐⭐⭐⭐⭐
MySQL (已废弃)	❌ 已移除	❌ 不支持	❌ 不推荐

🔗 PDO 连接方式（推荐）

基本连接

<?php
// PDO 连接 MySQL 数据库
$host = 'localhost';
$dbname = 'test_db';
$username = 'root';
$password = 'your_password';

try {
    // MySQL 8.x 连接字符串
    $dsn = "mysql:host=$host;dbname=$dbname;charset=utf8mb4";
    $pdo = new PDO($dsn, $username, $password, [
        PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
        PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,
        PDO::ATTR_EMULATE_PREPARES => false,
    ]);
    
    echo "数据库连接成功！";
} catch (PDOException $e) {
    die("连接失败: " . $e->getMessage());
}
?>

MySQL 8.x 连接注意事项

MySQL 8.x 默认使用 caching_sha2_password 认证插件。如果遇到连接问题，可能需要：

• 升级 PHP 到 7.2.4+ 版本
• 或者修改 MySQL 用户认证方式为mysql_native_password

创建数据库和表

<?php
// 创建数据库
$sql_create_db = "CREATE DATABASE IF NOT EXISTS test_db 
                  CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci";
$pdo->exec($sql_create_db);

// 使用数据库
$pdo->exec("USE test_db");

// 创建用户表
$sql_create_table = "
CREATE TABLE IF NOT EXISTS users (
    id INT AUTO_INCREMENT PRIMARY KEY,
    username VARCHAR(50) NOT NULL UNIQUE,
    email VARCHAR(100) NOT NULL,
    password VARCHAR(255) NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci";

$pdo->exec($sql_create_table);
echo "表创建成功！";
?>

插入数据（预处理语句）

<?php
// 插入单条数据
function insertUser($pdo, $username, $email, $password) {
    $sql = "INSERT INTO users (username, email, password) VALUES (?, ?, ?)";
    $stmt = $pdo->prepare($sql);
    
    // 密码加密
    $hashed_password = password_hash($password, PASSWORD_DEFAULT);
    
    return $stmt->execute([$username, $email, $hashed_password]);
}

// 使用示例
try {
    insertUser($pdo, 'john_doe', '[email protected]', 'secure123');
    echo "用户插入成功！";
} catch (PDOException $e) {
    echo "插入失败: " . $e->getMessage();
}

// 批量插入
function insertMultipleUsers($pdo, $users) {
    $sql = "INSERT INTO users (username, email, password) VALUES (?, ?, ?)";
    $stmt = $pdo->prepare($sql);
    
    $pdo->beginTransaction();
    try {
        foreach ($users as $user) {
            $hashed_password = password_hash($user['password'], PASSWORD_DEFAULT);
            $stmt->execute([$user['username'], $user['email'], $hashed_password]);
        }
        $pdo->commit();
        return true;
    } catch (Exception $e) {
        $pdo->rollback();
        throw $e;
    }
}
?>

查询数据

<?php
// 查询所有用户
function getAllUsers($pdo) {
    $sql = "SELECT id, username, email, created_at FROM users ORDER BY created_at DESC";
    $stmt = $pdo->query($sql);
    return $stmt->fetchAll();
}

// 根据ID查询用户
function getUserById($pdo, $id) {
    $sql = "SELECT id, username, email, created_at FROM users WHERE id = ?";
    $stmt = $pdo->prepare($sql);
    $stmt->execute([$id]);
    return $stmt->fetch();
}

// 分页查询
function getUsersPaginated($pdo, $page = 1, $limit = 10) {
    $offset = ($page - 1) * $limit;
    $sql = "SELECT id, username, email, created_at FROM users 
            ORDER BY created_at DESC LIMIT ? OFFSET ?";
    $stmt = $pdo->prepare($sql);
    $stmt->execute([$limit, $offset]);
    return $stmt->fetchAll();
}

// 搜索用户
function searchUsers($pdo, $keyword) {
    $sql = "SELECT id, username, email, created_at FROM users 
            WHERE username LIKE ? OR email LIKE ?";
    $stmt = $pdo->prepare($sql);
    $searchTerm = "%$keyword%";
    $stmt->execute([$searchTerm, $searchTerm]);
    return $stmt->fetchAll();
}

// 使用示例
$users = getAllUsers($pdo);
foreach ($users as $user) {
    echo "用户: " . $user['username'] . " - " . $user['email'] . "\n";
}
?>

更新和删除数据

<?php
// 更新用户信息
function updateUser($pdo, $id, $username, $email) {
    $sql = "UPDATE users SET username = ?, email = ? WHERE id = ?";
    $stmt = $pdo->prepare($sql);
    return $stmt->execute([$username, $email, $id]);
}

// 更新密码
function updatePassword($pdo, $id, $new_password) {
    $sql = "UPDATE users SET password = ? WHERE id = ?";
    $stmt = $pdo->prepare($sql);
    $hashed_password = password_hash($new_password, PASSWORD_DEFAULT);
    return $stmt->execute([$hashed_password, $id]);
}

// 删除用户
function deleteUser($pdo, $id) {
    $sql = "DELETE FROM users WHERE id = ?";
    $stmt = $pdo->prepare($sql);
    return $stmt->execute([$id]);
}

// 软删除（推荐）
function softDeleteUser($pdo, $id) {
    $sql = "UPDATE users SET deleted_at = CURRENT_TIMESTAMP WHERE id = ?";
    $stmt = $pdo->prepare($sql);
    return $stmt->execute([$id]);
}
?>

🔧 MySQLi 连接方式

面向对象方式

<?php
// MySQLi 面向对象连接
$host = 'localhost';
$username = 'root';
$password = 'your_password';
$database = 'test_db';

$mysqli = new mysqli($host, $username, $password, $database);

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

// 设置字符集（重要！）
$mysqli->set_charset("utf8mb4");

echo "MySQLi 连接成功！";

// 查询示例
$sql = "SELECT id, username, email FROM users";
$result = $mysqli->query($sql);

if ($result->num_rows > 0) {
    while($row = $result->fetch_assoc()) {
        echo "ID: " . $row["id"]. " - 用户名: " . $row["username"]. " - 邮箱: " . $row["email"]. "\n";
    }
} else {
    echo "0 结果";
}

$mysqli->close();
?>

预处理语句（MySQLi）

<?php
// MySQLi 预处理语句
function insertUserMySQLi($mysqli, $username, $email, $password) {
    $sql = "INSERT INTO users (username, email, password) VALUES (?, ?, ?)";
    $stmt = $mysqli->prepare($sql);
    
    if ($stmt) {
        $hashed_password = password_hash($password, PASSWORD_DEFAULT);
        $stmt->bind_param("sss", $username, $email, $hashed_password);
        
        if ($stmt->execute()) {
            echo "新记录插入成功";
            return $mysqli->insert_id; // 返回插入的ID
        } else {
            echo "Error: " . $stmt->error;
        }
        $stmt->close();
    }
}

// 查询预处理语句
function getUserByIdMySQLi($mysqli, $id) {
    $sql = "SELECT id, username, email, created_at FROM users WHERE id = ?";
    $stmt = $mysqli->prepare($sql);
    $stmt->bind_param("i", $id);
    $stmt->execute();
    
    $result = $stmt->get_result();
    $user = $result->fetch_assoc();
    $stmt->close();
    
    return $user;
}
?>

🔒 安全最佳实践

1. 防止 SQL 注入

<?php
// ❌ 错误示例 - 容易受到 SQL 注入攻击
$username = $_POST['username'];
$sql = "SELECT * FROM users WHERE username = '$username'";

// ✅ 正确示例 - 使用预处理语句
$username = $_POST['username'];
$sql = "SELECT * FROM users WHERE username = ?";
$stmt = $pdo->prepare($sql);
$stmt->execute([$username]);
?>

2. 密码安全处理

<?php
// 密码加密
function hashPassword($password) {
    return password_hash($password, PASSWORD_DEFAULT);
}

// 密码验证
function verifyPassword($password, $hash) {
    return password_verify($password, $hash);
}

// 登录验证示例
function authenticateUser($pdo, $username, $password) {
    $sql = "SELECT id, username, password FROM users WHERE username = ?";
    $stmt = $pdo->prepare($sql);
    $stmt->execute([$username]);
    $user = $stmt->fetch();
    
    if ($user && password_verify($password, $user['password'])) {
        return $user;
    }
    return false;
}
?>

3. 错误处理

<?php
// 生产环境错误处理
try {
    $pdo = new PDO($dsn, $username, $password, $options);
} catch (PDOException $e) {
    // 记录错误日志，不要直接显示给用户
    error_log("数据库连接失败: " . $e->getMessage());
    die("系统暂时不可用，请稍后再试");
}

// 开发环境可以显示详细错误
if (ENVIRONMENT === 'development') {
    $pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
}
?>

🚀 MySQL 8.x 特性在 PHP 中的应用

JSON 数据处理

<?php
// 创建包含 JSON 字段的表
$sql = "CREATE TABLE IF NOT EXISTS user_profiles (
    id INT AUTO_INCREMENT PRIMARY KEY,
    user_id INT,
    profile JSON,
    settings JSON
)";
$pdo->exec($sql);

// 插入 JSON 数据
function insertUserProfile($pdo, $user_id, $profile, $settings) {
    $sql = "INSERT INTO user_profiles (user_id, profile, settings) VALUES (?, ?, ?)";
    $stmt = $pdo->prepare($sql);
    return $stmt->execute([
        $user_id, 
        json_encode($profile), 
        json_encode($settings)
    ]);
}

// 查询 JSON 数据
function getUserProfile($pdo, $user_id) {
    $sql = "SELECT profile->>'$.name' as name, 
                   profile->>'$.age' as age,
                   settings->>'$.theme' as theme
            FROM user_profiles WHERE user_id = ?";
    $stmt = $pdo->prepare($sql);
    $stmt->execute([$user_id]);
    return $stmt->fetch();
}

// 使用示例
$profile = ['name' => '张三', 'age' => 25, 'city' => '北京'];
$settings = ['theme' => 'dark', 'language' => 'zh-CN'];
insertUserProfile($pdo, 1, $profile, $settings);
?>

窗口函数应用

<?php
// MySQL 8.x 窗口函数示例
function getUserRanking($pdo) {
    $sql = "SELECT username, 
                   score,
                   ROW_NUMBER() OVER (ORDER BY score DESC) as ranking,
                   RANK() OVER (ORDER BY score DESC) as rank_with_ties
            FROM user_scores 
            ORDER BY score DESC";
    $stmt = $pdo->query($sql);
    return $stmt->fetchAll();
}

// 分组排名
function getDepartmentRanking($pdo) {
    $sql = "SELECT department, 
                   username, 
                   salary,
                   ROW_NUMBER() OVER (PARTITION BY department ORDER BY salary DESC) as dept_rank
            FROM employees 
            ORDER BY department, salary DESC";
    $stmt = $pdo->query($sql);
    return $stmt->fetchAll();
}
?>

📋 完整示例：用户管理系统

<?php
class UserManager {
    private $pdo;
    
    public function __construct($pdo) {
        $this->pdo = $pdo;
    }
    
    // 创建用户
    public function createUser($username, $email, $password) {
        $sql = "INSERT INTO users (username, email, password) VALUES (?, ?, ?)";
        $stmt = $this->pdo->prepare($sql);
        $hashed_password = password_hash($password, PASSWORD_DEFAULT);
        return $stmt->execute([$username, $email, $hashed_password]);
    }
    
    // 获取用户列表
    public function getUsers($page = 1, $limit = 10) {
        $offset = ($page - 1) * $limit;
        $sql = "SELECT id, username, email, created_at FROM users 
                ORDER BY created_at DESC LIMIT ? OFFSET ?";
        $stmt = $this->pdo->prepare($sql);
        $stmt->execute([$limit, $offset]);
        return $stmt->fetchAll();
    }
    
    // 用户认证
    public function authenticate($username, $password) {
        $sql = "SELECT id, username, password FROM users WHERE username = ?";
        $stmt = $this->pdo->prepare($sql);
        $stmt->execute([$username]);
        $user = $stmt->fetch();
        
        if ($user && password_verify($password, $user['password'])) {
            unset($user['password']); // 移除密码字段
            return $user;
        }
        return false;
    }
}

// 使用示例
$userManager = new UserManager($pdo);
$userManager->createUser('admin', '[email protected]', 'admin123');
$users = $userManager->getUsers();
?>