PHP与MySQL数据库操作入门：从连接到实战应用

数据库操作是Web开发的核心技能之一。作为一名PHP开发者，我想分享一些关于PHP与MySQL数据库交互的实用知识和最佳实践。无论你是刚开始学习还是想要提升技能，这篇文章都会对你有所帮助。

数据库连接方式

1. PDO连接（推荐）

<?php
// PDO连接示例
class DatabaseConnection {
    private $host = 'localhost';
    private $dbname = 'test_db';
    private $username = 'root';
    private $password = '';
    private $charset = 'utf8mb4';
    private $pdo;
    
    public function __construct() {
        $this->connect();
    }
    
    private function connect() {
        $dsn = "mysql:host={$this->host};dbname={$this->dbname};charset={$this->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 {$this->charset}"
        ];
        
        try {
            $this->pdo = new PDO($dsn, $this->username, $this->password, $options);
            echo "PDO数据库连接成功\n";
        } catch (PDOException $e) {
            throw new PDOException("数据库连接失败: " . $e->getMessage());
        }
    }
    
    public function getConnection() {
        return $this->pdo;
    }
    
    public function closeConnection() {
        $this->pdo = null;
    }
}

// 使用示例
try {
    $db = new DatabaseConnection();
    $pdo = $db->getConnection();
    
    // 测试连接
    $stmt = $pdo->query("SELECT VERSION() as version");
    $result = $stmt->fetch();
    echo "MySQL版本: " . $result['version'] . "\n";
    
} catch (Exception $e) {
    echo "错误: " . $e->getMessage() . "\n";
}
?>

2. MySQLi连接

<?php
// MySQLi连接示例
class MySQLiConnection {
    private $host = 'localhost';
    private $username = 'root';
    private $password = '';
    private $database = 'test_db';
    private $mysqli;
    
    public function __construct() {
        $this->connect();
    }
    
    private function connect() {
        $this->mysqli = new mysqli($this->host, $this->username, $this->password, $this->database);
        
        if ($this->mysqli->connect_error) {
            throw new Exception("MySQLi连接失败: " . $this->mysqli->connect_error);
        }
        
        // 设置字符集
        $this->mysqli->set_charset('utf8mb4');
        echo "MySQLi数据库连接成功\n";
    }
    
    public function getConnection() {
        return $this->mysqli;
    }
    
    public function closeConnection() {
        if ($this->mysqli) {
            $this->mysqli->close();
        }
    }
}

// 使用示例
try {
    $db = new MySQLiConnection();
    $mysqli = $db->getConnection();
    
    // 测试连接
    $result = $mysqli->query("SELECT VERSION() as version");
    $row = $result->fetch_assoc();
    echo "MySQL版本: " . $row['version'] . "\n";
    
    $result->free();
    $db->closeConnection();
    
} catch (Exception $e) {
    echo "错误: " . $e->getMessage() . "\n";
}
?>

3. 连接池和单例模式

<?php
// 数据库连接池
class DatabasePool {
    private static $instance = null;
    private $connections = [];
    private $config;
    private $maxConnections = 10;
    private $currentConnections = 0;
    
    private function __construct($config) {
        $this->config = $config;
    }
    
    public static function getInstance($config = null) {
        if (self::$instance === null) {
            if ($config === null) {
                throw new Exception("首次调用需要提供配置");
            }
            self::$instance = new self($config);
        }
        return self::$instance;
    }
    
    public function getConnection() {
        // 如果有空闲连接，直接返回
        if (!empty($this->connections)) {
            return array_pop($this->connections);
        }
        
        // 如果达到最大连接数，等待或抛出异常
        if ($this->currentConnections >= $this->maxConnections) {
            throw new Exception("连接池已满");
        }
        
        // 创建新连接
        return $this->createConnection();
    }
    
    public function releaseConnection($pdo) {
        if (count($this->connections) < $this->maxConnections) {
            $this->connections[] = $pdo;
        } else {
            $pdo = null;
            $this->currentConnections--;
        }
    }
    
    private function createConnection() {
        $dsn = "mysql:host={$this->config['host']};dbname={$this->config['dbname']};charset=utf8mb4";
        
        $options = [
            PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
            PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,
            PDO::ATTR_EMULATE_PREPARES => false,
        ];
        
        try {
            $pdo = new PDO($dsn, $this->config['username'], $this->config['password'], $options);
            $this->currentConnections++;
            return $pdo;
        } catch (PDOException $e) {
            throw new Exception("创建数据库连接失败: " . $e->getMessage());
        }
    }
    
    public function getStats() {
        return [
            'total_connections' => $this->currentConnections,
            'available_connections' => count($this->connections),
            'max_connections' => $this->maxConnections
        ];
    }
}

// 使用连接池
$config = [
    'host' => 'localhost',
    'dbname' => 'test_db',
    'username' => 'root',
    'password' => ''
];

try {
    $pool = DatabasePool::getInstance($config);
    
    // 获取连接
    $pdo1 = $pool->getConnection();
    $pdo2 = $pool->getConnection();
    
    echo "连接池状态: ";
    print_r($pool->getStats());
    
    // 释放连接
    $pool->releaseConnection($pdo1);
    $pool->releaseConnection($pdo2);
    
    echo "释放后连接池状态: ";
    print_r($pool->getStats());
    
} catch (Exception $e) {
    echo "错误: " . $e->getMessage() . "\n";
}
?>

基本CRUD操作

1. 创建表和插入数据

<?php
// 数据库操作类
class UserManager {
    private $pdo;
    
    public function __construct($pdo) {
        $this->pdo = $pdo;
        $this->createTable();
    }
    
    private function createTable() {
        $sql = "
            CREATE TABLE IF NOT EXISTS users (
                id INT AUTO_INCREMENT PRIMARY KEY,
                username VARCHAR(50) NOT NULL UNIQUE,
                email VARCHAR(100) NOT NULL UNIQUE,
                password VARCHAR(255) NOT NULL,
                age INT DEFAULT NULL,
                created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
                updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
            ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4
        ";
        
        try {
            $this->pdo->exec($sql);
            echo "用户表创建成功\n";
        } catch (PDOException $e) {
            echo "创建表失败: " . $e->getMessage() . "\n";
        }
    }
    
    // 插入单个用户
    public function createUser($username, $email, $password, $age = null) {
        $sql = "INSERT INTO users (username, email, password, age) VALUES (?, ?, ?, ?)";
        
        try {
            $stmt = $this->pdo->prepare($sql);
            $hashedPassword = password_hash($password, PASSWORD_DEFAULT);
            
            $stmt->execute([$username, $email, $hashedPassword, $age]);
            
            $userId = $this->pdo->lastInsertId();
            echo "用户创建成功，ID: $userId\n";
            
            return $userId;
        } catch (PDOException $e) {
            echo "创建用户失败: " . $e->getMessage() . "\n";
            return false;
        }
    }
    
    // 批量插入用户
    public function createMultipleUsers($users) {
        $sql = "INSERT INTO users (username, email, password, age) VALUES (?, ?, ?, ?)";
        
        try {
            $this->pdo->beginTransaction();
            $stmt = $this->pdo->prepare($sql);
            
            foreach ($users as $user) {
                $hashedPassword = password_hash($user['password'], PASSWORD_DEFAULT);
                $stmt->execute([
                    $user['username'],
                    $user['email'],
                    $hashedPassword,
                    $user['age'] ?? null
                ]);
            }
            
            $this->pdo->commit();
            echo "批量创建用户成功，共创建 " . count($users) . " 个用户\n";
            return true;
            
        } catch (PDOException $e) {
            $this->pdo->rollBack();
            echo "批量创建用户失败: " . $e->getMessage() . "\n";
            return false;
        }
    }
    
    // 查询单个用户
    public function getUserById($id) {
        $sql = "SELECT id, username, email, age, created_at, updated_at FROM users WHERE id = ?";
        
        try {
            $stmt = $this->pdo->prepare($sql);
            $stmt->execute([$id]);
            
            $user = $stmt->fetch();
            
            if ($user) {
                return $user;
            } else {
                echo "用户不存在\n";
                return null;
            }
        } catch (PDOException $e) {
            echo "查询用户失败: " . $e->getMessage() . "\n";
            return null;
        }
    }
    
    // 查询多个用户
    public function getUsers($limit = 10, $offset = 0) {
        $sql = "SELECT id, username, email, age, created_at, updated_at FROM users LIMIT ? OFFSET ?";
        
        try {
            $stmt = $this->pdo->prepare($sql);
            $stmt->bindValue(1, $limit, PDO::PARAM_INT);
            $stmt->bindValue(2, $offset, PDO::PARAM_INT);
            $stmt->execute();
            
            return $stmt->fetchAll();
        } catch (PDOException $e) {
            echo "查询用户列表失败: " . $e->getMessage() . "\n";
            return [];
        }
    }
    
    // 搜索用户
    public function searchUsers($keyword) {
        $sql = "SELECT id, username, email, age, created_at FROM users 
                WHERE username LIKE ? OR email LIKE ?";
        
        try {
            $stmt = $this->pdo->prepare($sql);
            $searchTerm = "%$keyword%";
            $stmt->execute([$searchTerm, $searchTerm]);
            
            return $stmt->fetchAll();
        } catch (PDOException $e) {
            echo "搜索用户失败: " . $e->getMessage() . "\n";
            return [];
        }
    }
    
    // 更新用户
    public function updateUser($id, $data) {
        $allowedFields = ['username', 'email', 'age'];
        $updateFields = [];
        $values = [];
        
        foreach ($data as $field => $value) {
            if (in_array($field, $allowedFields)) {
                $updateFields[] = "$field = ?";
                $values[] = $value;
            }
        }
        
        if (empty($updateFields)) {
            echo "没有有效的更新字段\n";
            return false;
        }
        
        $values[] = $id;
        $sql = "UPDATE users SET " . implode(', ', $updateFields) . " WHERE id = ?";
        
        try {
            $stmt = $this->pdo->prepare($sql);
            $stmt->execute($values);
            
            $affectedRows = $stmt->rowCount();
            echo "用户更新成功，影响行数: $affectedRows\n";
            
            return $affectedRows > 0;
        } catch (PDOException $e) {
            echo "更新用户失败: " . $e->getMessage() . "\n";
            return false;
        }
    }
    
    // 删除用户
    public function deleteUser($id) {
        $sql = "DELETE FROM users WHERE id = ?";
        
        try {
            $stmt = $this->pdo->prepare($sql);
            $stmt->execute([$id]);
            
            $affectedRows = $stmt->rowCount();
            echo "用户删除成功，影响行数: $affectedRows\n";
            
            return $affectedRows > 0;
        } catch (PDOException $e) {
            echo "删除用户失败: " . $e->getMessage() . "\n";
            return false;
        }
    }
    
    // 用户认证
    public function authenticateUser($username, $password) {
        $sql = "SELECT id, username, password FROM users WHERE username = ?";
        
        try {
            $stmt = $this->pdo->prepare($sql);
            $stmt->execute([$username]);
            
            $user = $stmt->fetch();
            
            if ($user && password_verify($password, $user['password'])) {
                echo "用户认证成功\n";
                return [
                    'id' => $user['id'],
                    'username' => $user['username']
                ];
            } else {
                echo "用户名或密码错误\n";
                return null;
            }
        } catch (PDOException $e) {
            echo "用户认证失败: " . $e->getMessage() . "\n";
            return null;
        }
    }
    
    // 获取用户统计信息
    public function getUserStats() {
        $sql = "SELECT 
                    COUNT(*) as total_users,
                    AVG(age) as avg_age,
                    MIN(age) as min_age,
                    MAX(age) as max_age,
                    COUNT(CASE WHEN age IS NOT NULL THEN 1 END) as users_with_age
                FROM users";
        
        try {
            $stmt = $this->pdo->query($sql);
            return $stmt->fetch();
        } catch (PDOException $e) {
            echo "获取统计信息失败: " . $e->getMessage() . "\n";
            return null;
        }
    }
}

// 使用示例
try {
    $db = new DatabaseConnection();
    $pdo = $db->getConnection();
    
    $userManager = new UserManager($pdo);
    
    // 创建单个用户
    $userId1 = $userManager->createUser('张三', 'zhangsan@example.com', 'password123', 25);
    $userId2 = $userManager->createUser('李四', 'lisi@example.com', 'password456', 30);
    
    // 批量创建用户
    $users = [
        ['username' => '王五', 'email' => 'wangwu@example.com', 'password' => 'pass789', 'age' => 28],
        ['username' => '赵六', 'email' => 'zhaoliu@example.com', 'password' => 'pass000', 'age' => 35],
        ['username' => '钱七', 'email' => 'qianqi@example.com', 'password' => 'pass111']
    ];
    $userManager->createMultipleUsers($users);
    
    // 查询用户
    echo "\n=== 查询用户 ===\n";
    $user = $userManager->getUserById($userId1);
    if ($user) {
        echo "用户信息: " . json_encode($user, JSON_UNESCAPED_UNICODE) . "\n";
    }
    
    // 获取用户列表
    echo "\n=== 用户列表 ===\n";
    $userList = $userManager->getUsers(3, 0);
    foreach ($userList as $user) {
        echo "ID: {$user['id']}, 用户名: {$user['username']}, 邮箱: {$user['email']}\n";
    }
    
    // 搜索用户
    echo "\n=== 搜索用户 ===\n";
    $searchResults = $userManager->searchUsers('张');
    foreach ($searchResults as $user) {
        echo "搜索结果: {$user['username']} - {$user['email']}\n";
    }
    
    // 更新用户
    echo "\n=== 更新用户 ===\n";
    $userManager->updateUser($userId1, ['age' => 26, 'email' => 'zhangsan_new@example.com']);
    
    // 用户认证
    echo "\n=== 用户认证 ===\n";
    $authResult = $userManager->authenticateUser('张三', 'password123');
    if ($authResult) {
        echo "认证成功，用户ID: {$authResult['id']}\n";
    }
    
    // 获取统计信息
    echo "\n=== 用户统计 ===\n";
    $stats = $userManager->getUserStats();
    if ($stats) {
        echo "总用户数: {$stats['total_users']}\n";
        echo "平均年龄: " . number_format($stats['avg_age'], 2) . "\n";
        echo "年龄范围: {$stats['min_age']} - {$stats['max_age']}\n";
    }
    
} catch (Exception $e) {
    echo "错误: " . $e->getMessage() . "\n";
}
?>

2. 高级查询操作

<?php
// 高级查询操作类
class AdvancedQuery {
    private $pdo;
    
    public function __construct($pdo) {
        $this->pdo = $pdo;
        $this->createSampleData();
    }
    
    private function createSampleData() {
        // 创建订单表
        $sql = "
            CREATE TABLE IF NOT EXISTS orders (
                id INT AUTO_INCREMENT PRIMARY KEY,
                user_id INT NOT NULL,
                product_name VARCHAR(100) NOT NULL,
                quantity INT NOT NULL,
                price DECIMAL(10,2) NOT NULL,
                status ENUM('pending', 'processing', 'shipped', 'delivered', 'cancelled') DEFAULT 'pending',
                order_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
                FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
            ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4
        ";
        
        try {
            $this->pdo->exec($sql);
            echo "订单表创建成功\n";
        } catch (PDOException $e) {
            echo "创建订单表失败: " . $e->getMessage() . "\n";
        }
    }
    
    // 联表查询
    public function getUsersWithOrders() {
        $sql = "
            SELECT 
                u.id,
                u.username,
                u.email,
                COUNT(o.id) as order_count,
                SUM(o.price * o.quantity) as total_spent
            FROM users u
            LEFT JOIN orders o ON u.id = o.user_id
            GROUP BY u.id, u.username, u.email
            ORDER BY total_spent DESC
        ";
        
        try {
            $stmt = $this->pdo->query($sql);
            return $stmt->fetchAll();
        } catch (PDOException $e) {
            echo "联表查询失败: " . $e->getMessage() . "\n";
            return [];
        }
    }
    
    // 子查询
    public function getTopSpendingUsers($limit = 5) {
        $sql = "
            SELECT 
                u.username,
                u.email,
                (SELECT SUM(o.price * o.quantity) 
                 FROM orders o 
                 WHERE o.user_id = u.id) as total_spent
            FROM users u
            WHERE (SELECT COUNT(*) FROM orders o WHERE o.user_id = u.id) > 0
            ORDER BY total_spent DESC
            LIMIT ?
        ";
        
        try {
            $stmt = $this->pdo->prepare($sql);
            $stmt->bindValue(1, $limit, PDO::PARAM_INT);
            $stmt->execute();
            
            return $stmt->fetchAll();
        } catch (PDOException $e) {
            echo "子查询失败: " . $e->getMessage() . "\n";
            return [];
        }
    }
    
    // 条件查询构建器
    public function buildQuery($conditions = [], $orderBy = null, $limit = null, $offset = null) {
        $sql = "SELECT * FROM users WHERE 1=1";
        $params = [];
        
        // 构建WHERE条件
        if (!empty($conditions)) {
            foreach ($conditions as $field => $condition) {
                if (is_array($condition)) {
                    $operator = $condition['operator'] ?? '=';
                    $value = $condition['value'];
                    
                    switch ($operator) {
                        case 'LIKE':
                            $sql .= " AND $field LIKE ?";
                            $params[] = "%$value%";
                            break;
                        case 'IN':
                            $placeholders = str_repeat('?,', count($value) - 1) . '?';
                            $sql .= " AND $field IN ($placeholders)";
                            $params = array_merge($params, $value);
                            break;
                        case 'BETWEEN':
                            $sql .= " AND $field BETWEEN ? AND ?";
                            $params[] = $value[0];
                            $params[] = $value[1];
                            break;
                        default:
                            $sql .= " AND $field $operator ?";
                            $params[] = $value;
                    }
                } else {
                    $sql .= " AND $field = ?";
                    $params[] = $condition;
                }
            }
        }
        
        // 添加ORDER BY
        if ($orderBy) {
            $sql .= " ORDER BY $orderBy";
        }
        
        // 添加LIMIT和OFFSET
        if ($limit) {
            $sql .= " LIMIT ?";
            $params[] = $limit;
            
            if ($offset) {
                $sql .= " OFFSET ?";
                $params[] = $offset;
            }
        }
        
        try {
            $stmt = $this->pdo->prepare($sql);
            
            // 绑定参数
            foreach ($params as $i => $param) {
                $type = is_int($param) ? PDO::PARAM_INT : PDO::PARAM_STR;
                $stmt->bindValue($i + 1, $param, $type);
            }
            
            $stmt->execute();
            return $stmt->fetchAll();
            
        } catch (PDOException $e) {
            echo "动态查询失败: " . $e->getMessage() . "\n";
            echo "SQL: $sql\n";
            echo "参数: " . json_encode($params) . "\n";
            return [];
        }
    }
    
    // 分页查询
    public function getPaginatedUsers($page = 1, $perPage = 10, $search = '') {
        $offset = ($page - 1) * $perPage;
        
        // 构建基础查询
        $whereClause = '';
        $params = [];
        
        if (!empty($search)) {
            $whereClause = "WHERE username LIKE ? OR email LIKE ?";
            $params = ["%$search%", "%$search%"];
        }
        
        // 获取总数
        $countSql = "SELECT COUNT(*) as total FROM users $whereClause";
        $countStmt = $this->pdo->prepare($countSql);
        $countStmt->execute($params);
        $total = $countStmt->fetch()['total'];
        
        // 获取数据
        $dataSql = "SELECT * FROM users $whereClause ORDER BY id DESC LIMIT ? OFFSET ?";
        $dataParams = array_merge($params, [$perPage, $offset]);
        
        $dataStmt = $this->pdo->prepare($dataSql);
        
        // 绑定参数
        foreach ($dataParams as $i => $param) {
            $type = is_int($param) ? PDO::PARAM_INT : PDO::PARAM_STR;
            $dataStmt->bindValue($i + 1, $param, $type);
        }
        
        $dataStmt->execute();
        $data = $dataStmt->fetchAll();
        
        return [
            'data' => $data,
            'pagination' => [
                'current_page' => $page,
                'per_page' => $perPage,
                'total' => $total,
                'total_pages' => ceil($total / $perPage),
                'has_next' => $page < ceil($total / $perPage),
                'has_prev' => $page > 1
            ]
        ];
    }
    
    // 聚合查询
    public function getAggregateData() {
        $sql = "
            SELECT 
                COUNT(*) as total_users,
                COUNT(CASE WHEN age IS NOT NULL THEN 1 END) as users_with_age,
                AVG(age) as avg_age,
                MIN(age) as min_age,
                MAX(age) as max_age,
                COUNT(CASE WHEN age BETWEEN 18 AND 25 THEN 1 END) as young_users,
                COUNT(CASE WHEN age BETWEEN 26 AND 35 THEN 1 END) as middle_users,
                COUNT(CASE WHEN age > 35 THEN 1 END) as senior_users
            FROM users
        ";
        
        try {
            $stmt = $this->pdo->query($sql);
            return $stmt->fetch();
        } catch (PDOException $e) {
            echo "聚合查询失败: " . $e->getMessage() . "\n";
            return null;
        }
    }
}

// 使用高级查询
try {
    $db = new DatabaseConnection();
    $pdo = $db->getConnection();
    
    $advancedQuery = new AdvancedQuery($pdo);
    
    // 插入一些订单数据用于测试
    $orderSql = "INSERT INTO orders (user_id, product_name, quantity, price, status) VALUES (?, ?, ?, ?, ?)";
    $orderStmt = $pdo->prepare($orderSql);
    
    $sampleOrders = [
        [1, '笔记本电脑', 1, 5999.00, 'delivered'],
        [1, '鼠标', 2, 99.00, 'delivered'],
        [2, '键盘', 1, 299.00, 'shipped'],
        [2, '显示器', 1, 1999.00, 'processing'],
    ];
    
    foreach ($sampleOrders as $order) {
        $orderStmt->execute($order);
    }
    
    echo "=== 联表查询 ===\n";
    $usersWithOrders = $advancedQuery->getUsersWithOrders();
    foreach ($usersWithOrders as $user) {
        echo "用户: {$user['username']}, 订单数: {$user['order_count']}, 总消费: {$user['total_spent']}\n";
    }
    
    echo "\n=== 条件查询 ===\n";
    $conditions = [
        'age' => ['operator' => '>', 'value' => 20],
        'username' => ['operator' => 'LIKE', 'value' => '张']
    ];
    $results = $advancedQuery->buildQuery($conditions, 'age DESC', 5);
    foreach ($results as $user) {
        echo "用户: {$user['username']}, 年龄: {$user['age']}\n";
    }
    
    echo "\n=== 分页查询 ===\n";
    $paginatedResult = $advancedQuery->getPaginatedUsers(1, 3);
    echo "分页信息: ";
    print_r($paginatedResult['pagination']);
    
    echo "用户数据:\n";
    foreach ($paginatedResult['data'] as $user) {
        echo "- {$user['username']} ({$user['email']})\n";
    }
    
    echo "\n=== 聚合数据 ===\n";
    $aggregateData = $advancedQuery->getAggregateData();
    if ($aggregateData) {
        echo "总用户数: {$aggregateData['total_users']}\n";
        echo "平均年龄: " . number_format($aggregateData['avg_age'], 2) . "\n";
        echo "年龄分布: 青年({$aggregateData['young_users']}) 中年({$aggregateData['middle_users']}) 老年({$aggregateData['senior_users']})\n";
    }
    
} catch (Exception $e) {
    echo "错误: " . $e->getMessage() . "\n";
}
?>

事务处理

1. 基本事务操作

<?php
// 事务处理示例
class TransactionManager {
    private $pdo;
    
    public function __construct($pdo) {
        $this->pdo = $pdo;
    }
    
    // 银行转账示例
    public function transferMoney($fromUserId, $toUserId, $amount) {
        try {
            // 开始事务
            $this->pdo->beginTransaction();
            
            // 检查发送方余额
            $checkBalanceSql = "SELECT balance FROM accounts WHERE user_id = ? FOR UPDATE";
            $stmt = $this->pdo->prepare($checkBalanceSql);
            $stmt->execute([$fromUserId]);
            $fromBalance = $stmt->fetchColumn();
            
            if ($fromBalance === false) {
                throw new Exception("发送方账户不存在");
            }
            
            if ($fromBalance < $amount) {
                throw new Exception("余额不足");
            }
            
            // 扣除发送方余额
            $deductSql = "UPDATE accounts SET balance = balance - ? WHERE user_id = ?";
            $stmt = $this->pdo->prepare($deductSql);
            $stmt->execute([$amount, $fromUserId]);
            
            // 增加接收方余额
            $addSql = "UPDATE accounts SET balance = balance + ? WHERE user_id = ?";
            $stmt = $this->pdo->prepare($addSql);
            $stmt->execute([$amount, $toUserId]);
            
            // 记录转账日志
            $logSql = "INSERT INTO transfer_logs (from_user_id, to_user_id, amount, transfer_time) VALUES (?, ?, ?, NOW())";
            $stmt = $this->pdo->prepare($logSql);
            $stmt->execute([$fromUserId, $toUserId, $amount]);
            
            // 提交事务
            $this->pdo->commit();
            echo "转账成功：从用户$fromUserId 向用户$toUserId 转账 $amount 元\n";
            
            return true;
            
        } catch (Exception $e) {
            // 回滚事务
            $this->pdo->rollBack();
            echo "转账失败：" . $e->getMessage() . "\n";
            return false;
        }
    }
    
    // 批量操作事务
    public function batchUpdateUsers($updates) {
        try {
            $this->pdo->beginTransaction();
            
            $sql = "UPDATE users SET username = ?, email = ? WHERE id = ?";
            $stmt = $this->pdo->prepare($sql);
            
            $successCount = 0;
            foreach ($updates as $update) {
                $stmt->execute([
                    $update['username'],
                    $update['email'],
                    $update['id']
                ]);
                
                if ($stmt->rowCount() > 0) {
                    $successCount++;
                }
            }
            
            if ($successCount === count($updates)) {
                $this->pdo->commit();
                echo "批量更新成功，共更新 $successCount 条记录\n";
                return true;
            } else {
                throw new Exception("部分更新失败");
            }
            
        } catch (Exception $e) {
            $this->pdo->rollBack();
            echo "批量更新失败：" . $e->getMessage() . "\n";
            return false;
        }
    }
    
    // 嵌套事务（保存点）
    public function nestedTransaction() {
        try {
            $this->pdo->beginTransaction();
            
            // 第一个操作
            $this->pdo->exec("INSERT INTO users (username, email, password) VALUES ('test1', 'test1@example.com', 'pass')");
            echo "插入用户1成功\n";
            
            // 创建保存点
            $this->pdo->exec("SAVEPOINT sp1");
            
            try {
                // 第二个操作（可能失败）
                $this->pdo->exec("INSERT INTO users (username, email, password) VALUES ('test2', 'invalid-email', 'pass')");
                echo "插入用户2成功\n";
                
            } catch (Exception $e) {
                // 回滚到保存点
                $this->pdo->exec("ROLLBACK TO SAVEPOINT sp1");
                echo "插入用户2失败，回滚到保存点\n";
            }
            
            // 第三个操作
            $this->pdo->exec("INSERT INTO users (username, email, password) VALUES ('test3', 'test3@example.com', 'pass')");
            echo "插入用户3成功\n";
            
            // 提交事务
            $this->pdo->commit();
            echo "嵌套事务完成\n";
            
        } catch (Exception $e) {
            $this->pdo->rollBack();
            echo "嵌套事务失败：" . $e->getMessage() . "\n";
        }
    }
}
?>

数据库安全

1. SQL注入防护

<?php
// SQL注入防护示例
class SecurityManager {
    private $pdo;
    
    public function __construct($pdo) {
        $this->pdo = $pdo;
    }
    
    // 错误的做法（容易SQL注入）
    public function unsafeLogin($username, $password) {
        // 危险！不要这样做
        $sql = "SELECT * FROM users WHERE username = '$username' AND password = '$password'";
        
        echo "危险的SQL: $sql\n";
        echo "如果用户名输入: admin' OR '1'='1' -- \n";
        echo "SQL将变成: SELECT * FROM users WHERE username = 'admin' OR '1'='1' -- ' AND password = '...'\n";
        echo "这将绕过密码验证！\n";
        
        // 不执行这个危险的查询
        return false;
    }
    
    // 正确的做法（使用预处理语句）
    public function safeLogin($username, $password) {
        $sql = "SELECT id, username, password FROM users WHERE username = ?";
        
        try {
            $stmt = $this->pdo->prepare($sql);
            $stmt->execute([$username]);
            
            $user = $stmt->fetch();
            
            if ($user && password_verify($password, $user['password'])) {
                echo "安全登录成功\n";
                return $user;
            } else {
                echo "用户名或密码错误\n";
                return false;
            }
        } catch (PDOException $e) {
            echo "登录查询失败: " . $e->getMessage() . "\n";
            return false;
        }
    }
    
    // 输入验证和清理
    public function validateAndSanitizeInput($data) {
        $cleaned = [];
        
        // 用户名验证
        if (isset($data['username'])) {
            $username = trim($data['username']);
            if (preg_match('/^[a-zA-Z0-9_]{3,20}$/', $username)) {
                $cleaned['username'] = $username;
            } else {
                throw new InvalidArgumentException("用户名格式不正确");
            }
        }
        
        // 邮箱验证
        if (isset($data['email'])) {
            $email = filter_var(trim($data['email']), FILTER_VALIDATE_EMAIL);
            if ($email === false) {
                throw new InvalidArgumentException("邮箱格式不正确");
            }
            $cleaned['email'] = $email;
        }
        
        // 年龄验证
        if (isset($data['age'])) {
            $age = filter_var($data['age'], FILTER_VALIDATE_INT, [
                'options' => [
                    'min_range' => 1,
                    'max_range' => 150
                ]
            ]);
            if ($age === false) {
                throw new InvalidArgumentException("年龄必须是1-150之间的整数");
            }
            $cleaned['age'] = $age;
        }
        
        return $cleaned;
    }
    
    // 权限检查
    public function checkPermission($userId, $action, $resource = null) {
        $sql = "SELECT p.permission_name 
                FROM user_permissions up 
                JOIN permissions p ON up.permission_id = p.id 
                WHERE up.user_id = ?";
        
        try {
            $stmt = $this->pdo->prepare($sql);
            $stmt->execute([$userId]);
            
            $permissions = $stmt->fetchAll(PDO::FETCH_COLUMN);
            
            // 检查是否有所需权限
            $requiredPermission = $resource ? "$action:$resource" : $action;
            
            if (in_array($requiredPermission, $permissions) || in_array('admin', $permissions)) {
                return true;
            }
            
            echo "权限不足：需要 $requiredPermission 权限\n";
            return false;
            
        } catch (PDOException $e) {
            echo "权限检查失败: " . $e->getMessage() . "\n";
            return false;
        }
    }
    
    // 数据加密存储
    public function encryptSensitiveData($data, $key) {
        $cipher = "AES-256-CBC";
        $ivlen = openssl_cipher_iv_length($cipher);
        $iv = openssl_random_pseudo_bytes($ivlen);
        
        $encrypted = openssl_encrypt($data, $cipher, $key, 0, $iv);
        
        // 将IV和加密数据一起存储
        return base64_encode($iv . $encrypted);
    }
    
    public function decryptSensitiveData($encryptedData, $key) {
        $cipher = "AES-256-CBC";
        $ivlen = openssl_cipher_iv_length($cipher);
        
        $data = base64_decode($encryptedData);
        $iv = substr($data, 0, $ivlen);
        $encrypted = substr($data, $ivlen);
        
        return openssl_decrypt($encrypted, $cipher, $key, 0, $iv);
    }
}

// 使用安全管理器
try {
    $db = new DatabaseConnection();
    $pdo = $db->getConnection();
    
    $security = new SecurityManager($pdo);
    
    // 演示SQL注入防护
    echo "=== SQL注入防护演示 ===\n";
    $security->unsafeLogin("admin' OR '1'='1' --", "anything");
    
    echo "\n=== 安全登录 ===\n";
    $security->safeLogin("张三", "password123");
    
    // 输入验证
    echo "\n=== 输入验证 ===\n";
    try {
        $validData = $security->validateAndSanitizeInput([
            'username' => 'test_user',
            'email' => 'test@example.com',
            'age' => 25
        ]);
        echo "验证通过: " . json_encode($validData) . "\n";
    } catch (Exception $e) {
        echo "验证失败: " . $e->getMessage() . "\n";
    }
    
    // 数据加密
    echo "\n=== 数据加密 ===\n";
    $secretKey = "my-secret-key-32-characters-long";
    $sensitiveData = "这是敏感信息";
    
    $encrypted = $security->encryptSensitiveData($sensitiveData, $secretKey);
    echo "加密后: $encrypted\n";
    
    $decrypted = $security->decryptSensitiveData($encrypted, $secretKey);
    echo "解密后: $decrypted\n";
    
} catch (Exception $e) {
    echo "错误: " . $e->getMessage() . "\n";
}
?>

性能优化

1. 查询优化技巧

<?php
// 数据库性能优化
class PerformanceOptimizer {
    private $pdo;
    
    public function __construct($pdo) {
        $this->pdo = $pdo;
    }
    
    // 索引优化建议
    public function analyzeQueryPerformance($sql, $params = []) {
        try {
            // 使用EXPLAIN分析查询
            $explainSql = "EXPLAIN " . $sql;
            $stmt = $this->pdo->prepare($explainSql);
            $stmt->execute($params);
            
            $results = $stmt->fetchAll();
            
            echo "查询分析结果:\n";
            foreach ($results as $row) {
                echo "表: {$row['table']}, 类型: {$row['type']}, 可能的键: {$row['possible_keys']}, 使用的键: {$row['key']}, 扫描行数: {$row['rows']}\n";
                
                // 性能建议
                if ($row['type'] === 'ALL') {
                    echo "⚠️ 警告: 全表扫描，建议添加索引\n";
                }
                
                if ($row['rows'] > 1000) {
                    echo "⚠️ 警告: 扫描行数过多，考虑优化查询条件\n";
                }
            }
            
        } catch (PDOException $e) {
            echo "查询分析失败: " . $e->getMessage() . "\n";
        }
    }
    
    // 批量插入优化
    public function optimizedBatchInsert($tableName, $data, $batchSize = 1000) {
        if (empty($data)) {
            return false;
        }
        
        $fields = array_keys($data[0]);
        $placeholders = '(' . str_repeat('?,', count($fields) - 1) . '?)';
        
        try {
            $this->pdo->beginTransaction();
            
            $totalInserted = 0;
            $batches = array_chunk($data, $batchSize);
            
            foreach ($batches as $batch) {
                $values = str_repeat($placeholders . ',', count($batch) - 1) . $placeholders;
                $sql = "INSERT INTO $tableName (" . implode(',', $fields) . ") VALUES $values";
                
                $stmt = $this->pdo->prepare($sql);
                
                $params = [];
                foreach ($batch as $row) {
                    foreach ($fields as $field) {
                        $params[] = $row[$field];
                    }
                }
                
                $stmt->execute($params);
                $totalInserted += $stmt->rowCount();
            }
            
            $this->pdo->commit();
            echo "批量插入完成，共插入 $totalInserted 条记录\n";
            
            return $totalInserted;
            
        } catch (PDOException $e) {
            $this->pdo->rollBack();
            echo "批量插入失败: " . $e->getMessage() . "\n";
            return false;
        }
    }
    
    // 连接池监控
    public function monitorConnections() {
        try {
            $sql = "SHOW STATUS LIKE 'Threads_%'";
            $stmt = $this->pdo->query($sql);
            $results = $stmt->fetchAll();
            
            echo "MySQL连接状态:\n";
            foreach ($results as $row) {
                echo "{$row['Variable_name']}: {$row['Value']}\n";
            }
            
            // 检查慢查询
            $slowQuerySql = "SHOW STATUS LIKE 'Slow_queries'";
            $stmt = $this->pdo->query($slowQuerySql);
            $slowQueries = $stmt->fetch();
            
            echo "慢查询数量: {$slowQueries['Value']}\n";
            
            if ($slowQueries['Value'] > 0) {
                echo "⚠️ 建议检查慢查询日志\n";
            }
            
        } catch (PDOException $e) {
            echo "连接监控失败: " . $e->getMessage() . "\n";
        }
    }
    
    // 缓存查询结果
    private $cache = [];
    
    public function cachedQuery($sql, $params = [], $ttl = 300) {
        $cacheKey = md5($sql . serialize($params));
        
        // 检查缓存
        if (isset($this->cache[$cacheKey])) {
            $cached = $this->cache[$cacheKey];
            if (time() - $cached['timestamp'] < $ttl) {
                echo "从缓存返回结果\n";
                return $cached['data'];
            }
        }
        
        // 执行查询
        try {
            $stmt = $this->pdo->prepare($sql);
            $stmt->execute($params);
            $result = $stmt->fetchAll();
            
            // 存储到缓存
            $this->cache[$cacheKey] = [
                'data' => $result,
                'timestamp' => time()
            ];
            
            echo "查询结果已缓存\n";
            return $result;
            
        } catch (PDOException $e) {
            echo "缓存查询失败: " . $e->getMessage() . "\n";
            return false;
        }
    }
    
    // 清理缓存
    public function clearCache() {
        $this->cache = [];
        echo "缓存已清理\n";
    }
}

// 使用性能优化器
try {
    $db = new DatabaseConnection();
    $pdo = $db->getConnection();
    
    $optimizer = new PerformanceOptimizer($pdo);
    
    // 分析查询性能
    echo "=== 查询性能分析 ===\n";
    $optimizer->analyzeQueryPerformance("SELECT * FROM users WHERE age > ?", [20]);
    
    // 监控连接状态
    echo "\n=== 连接监控 ===\n";
    $optimizer->monitorConnections();
    
    // 缓存查询测试
    echo "\n=== 缓存查询测试 ===\n";
    $result1 = $optimizer->cachedQuery("SELECT COUNT(*) as count FROM users");
    $result2 = $optimizer->cachedQuery("SELECT COUNT(*) as count FROM users"); // 这次从缓存返回
    
} catch (Exception $e) {
    echo "错误: " . $e->getMessage() . "\n";
}
?>

总结

PHP与MySQL数据库操作的关键要点：

1. 连接管理：

   • 优先使用PDO，支持多种数据库
   • 合理配置连接选项和错误处理
   • 实现连接池提高性能

2. CRUD操作：

   • 使用预处理语句防止SQL注入
   • 合理设计数据表结构
   • 实现完整的增删改查功能

3. 高级查询：

   • 掌握联表查询和子查询
   • 实现动态查询构建器
   • 合理使用分页和聚合函数

4. 事务处理：

   • 理解ACID特性
   • 正确使用事务保证数据一致性
   • 合理处理事务异常

5. 安全防护：

   • 防止SQL注入攻击
   • 实现输入验证和权限控制
   • 加密存储敏感数据

6. 性能优化：

   • 分析和优化查询性能
   • 使用批量操作提高效率
   • 实现查询结果缓存

7. 最佳实践：

   • 使用面向对象的数据库操作类
   • 实现错误处理和日志记录
   • 遵循数据库设计规范

掌握这些知识点，你就能够：

• 安全地操作数据库，防止各种攻击
• 高效地处理数据，优化应用性能
• 可靠地管理事务，保证数据完整性
• 灵活地构建查询，满足复杂需求

记住，数据库操作不仅仅是技术问题，更关系到应用的安全性、性能和可维护性。在实际开发中，要根据具体需求选择合适的方案，并始终考虑安全性和性能。

希望这篇文章能帮助你更好地掌握PHP与MySQL数据库操作技巧！