📚 第十章：数据库操作

10.1 数据库基础与PDO简介

10.1.1 数据库连接基础

<?php
// 数据库连接基础
echo "=== 数据库连接基础 ===\n";

echo "--- PDO简介 ---\n";
echo "PDO (PHP Data Objects) 是PHP的数据库抽象层\n";
echo "支持多种数据库：MySQL、PostgreSQL、SQLite、Oracle等\n";
echo "提供统一的接口和预处理语句支持\n";

// 数据库配置
$dbConfig = [
    'mysql' => [
        'dsn' => 'mysql:host=localhost;dbname=test;charset=utf8mb4',
        'username' => 'root',
        'password' => '',
        '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 utf8mb4"
        ]
    ],
    'sqlite' => [
        'dsn' => 'sqlite:' . __DIR__ . '/test.db',
        'username' => null,
        'password' => null,
        'options' => [
            PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
            PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC
        ]
    ]
];

echo "\n--- 基础连接示例 ---\n";

// 使用SQLite进行演示（无需额外配置）
try {
    $config = $dbConfig['sqlite'];
    $pdo = new PDO(
        $config['dsn'],
        $config['username'],
        $config['password'],
        $config['options']
    );
    
    echo "✓ 数据库连接成功\n";
    echo "数据库版本: " . $pdo->getAttribute(PDO::ATTR_SERVER_VERSION) . "\n";
    echo "驱动名称: " . $pdo->getAttribute(PDO::ATTR_DRIVER_NAME) . "\n";
    
} catch (PDOException $e) {
    echo "✗ 数据库连接失败: " . $e->getMessage() . "\n";
    exit(1);
}

echo "\n--- 数据库连接管理类 ---\n";

// 数据库连接管理类
class DatabaseManager {
    private static $instances = [];
    private $pdo;
    private $config;
    
    private function __construct($config) {
        $this->config = $config;
        $this->connect();
    }
    
    public static function getInstance($name = 'default', $config = null) {
        if (!isset(self::$instances[$name])) {
            if ($config === null) {
                throw new InvalidArgumentException("首次获取实例时必须提供配置");
            }
            self::$instances[$name] = new self($config);
        }
        return self::$instances[$name];
    }
    
    private function connect() {
        try {
            $this->pdo = new PDO(
                $this->config['dsn'],
                $this->config['username'] ?? null,
                $this->config['password'] ?? null,
                $this->config['options'] ?? []
            );
        } catch (PDOException $e) {
            throw new RuntimeException("数据库连接失败: " . $e->getMessage());
        }
    }
    
    public function getPdo() {
        // 检查连接是否仍然有效
        try {
            $this->pdo->query('SELECT 1');
        } catch (PDOException $e) {
            // 重新连接
            $this->connect();
        }
        
        return $this->pdo;
    }
    
    public function query($sql, $params = []) {
        $stmt = $this->getPdo()->prepare($sql);
        $stmt->execute($params);
        return $stmt;
    }
    
    public function fetchAll($sql, $params = []) {
        return $this->query($sql, $params)->fetchAll();
    }
    
    public function fetchOne($sql, $params = []) {
        return $this->query($sql, $params)->fetch();
    }
    
    public function execute($sql, $params = []) {
        return $this->query($sql, $params)->rowCount();
    }
    
    public function lastInsertId() {
        return $this->getPdo()->lastInsertId();
    }
    
    public function beginTransaction() {
        return $this->getPdo()->beginTransaction();
    }
    
    public function commit() {
        return $this->getPdo()->commit();
    }
    
    public function rollback() {
        return $this->getPdo()->rollback();
    }
    
    public function inTransaction() {
        return $this->getPdo()->inTransaction();
    }
}

// 测试数据库管理器
$db = DatabaseManager::getInstance('main', $dbConfig['sqlite']);
echo "✓ 数据库管理器初始化成功\n";

// 创建测试表
$createTableSQL = "
CREATE TABLE IF NOT EXISTS users (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    username VARCHAR(50) UNIQUE NOT NULL,
    email VARCHAR(100) UNIQUE NOT NULL,
    password VARCHAR(255) NOT NULL,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME DEFAULT CURRENT_TIMESTAMP
)";

$db->execute($createTableSQL);
echo "✓ 用户表创建成功\n";

$createPostsTableSQL = "
CREATE TABLE IF NOT EXISTS posts (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    user_id INTEGER NOT NULL,
    title VARCHAR(200) NOT NULL,
    content TEXT,
    status VARCHAR(20) DEFAULT 'draft',
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES users(id)
)";

$db->execute($createPostsTableSQL);
echo "✓ 文章表创建成功\n";
?>

10.3 事务处理

10.3.1 事务基础

<?php
// 事务处理
echo "=== 事务处理 ===\n";

echo "--- 基础事务操作 ---\n";

// 基础事务示例
function transferMoney($db, $fromUserId, $toUserId, $amount) {
    $db->beginTransaction();
    
    try {
        // 检查发送方余额（假设用户表有balance字段）
        $sender = $db->fetchOne("SELECT id, username, balance FROM users WHERE id = ?", [$fromUserId]);
        if (!$sender) {
            throw new Exception("发送方用户不存在");
        }
        
        if ($sender['balance'] < $amount) {
            throw new Exception("余额不足");
        }
        
        // 检查接收方是否存在
        $receiver = $db->fetchOne("SELECT id, username FROM users WHERE id = ?", [$toUserId]);
        if (!$receiver) {
            throw new Exception("接收方用户不存在");
        }
        
        // 扣除发送方余额
        $result1 = $db->execute(
            "UPDATE users SET balance = balance - ? WHERE id = ?",
            [$amount, $fromUserId]
        );
        
        // 增加接收方余额
        $result2 = $db->execute(
            "UPDATE users SET balance = balance + ? WHERE id = ?",
            [$amount, $toUserId]
        );
        
        if ($result1 === 0 || $result2 === 0) {
            throw new Exception("转账操作失败");
        }
        
        // 记录转账日志
        $db->execute(
            "INSERT INTO transfer_logs (from_user_id, to_user_id, amount, created_at) VALUES (?, ?, ?, ?)",
            [$fromUserId, $toUserId, $amount, date('Y-m-d H:i:s')]
        );
        
        $db->commit();
        
        return [
            'success' => true,
            'message' => "转账成功：{$sender['username']} -> {$receiver['username']}，金额：{$amount}"
        ];
        
    } catch (Exception $e) {
        $db->rollback();
        
        return [
            'success' => false,
            'message' => "转账失败：" . $e->getMessage()
        ];
    }
}

// 为演示添加balance字段和转账日志表
try {
    $db->execute("ALTER TABLE users ADD COLUMN balance DECIMAL(10,2) DEFAULT 0.00");
    echo "✓ 为用户表添加余额字段\n";
} catch (PDOException $e) {
    echo "余额字段可能已存在\n";
}

try {
    $createTransferLogsSQL = "
    CREATE TABLE IF NOT EXISTS transfer_logs (
        id INTEGER PRIMARY KEY AUTOINCREMENT,
        from_user_id INTEGER NOT NULL,
        to_user_id INTEGER NOT NULL,
        amount DECIMAL(10,2) NOT NULL,
        created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
        FOREIGN KEY (from_user_id) REFERENCES users(id),
        FOREIGN KEY (to_user_id) REFERENCES users(id)
    )";
    
    $db->execute($createTransferLogsSQL);
    echo "✓ 转账日志表创建成功\n";
} catch (PDOException $e) {
    echo "转账日志表可能已存在\n";
}

// 为用户设置初始余额
$db->execute("UPDATE users SET balance = 1000.00 WHERE balance = 0");

// 测试转账
$users = $db->fetchAll("SELECT id, username, balance FROM users LIMIT 2");
if (count($users) >= 2) {
    $result = transferMoney($db, $users[0]['id'], $users[1]['id'], 100.50);
    echo $result['message'] . "\n";
    
    // 查看转账后的余额
    $updatedUsers = $db->fetchAll(
        "SELECT username, balance FROM users WHERE id IN (?, ?)",
        [$users[0]['id'], $users[1]['id']]
    );
    
    foreach ($updatedUsers as $user) {
        echo "  {$user['username']}: {$user['balance']}\n";
    }
}

echo "\n--- 事务管理器 ---\n";

// 事务管理器类
class TransactionManager {
    private $db;
    private $transactionLevel = 0;
    private $savepoints = [];
    
    public function __construct($db) {
        $this->db = $db;
    }
    
    public function transaction(callable $callback) {
        $this->begin();
        
        try {
            $result = $callback($this->db);
            $this->commit();
            return $result;
        } catch (Exception $e) {
            $this->rollback();
            throw $e;
        }
    }
    
    public function begin() {
        if ($this->transactionLevel === 0) {
            $this->db->beginTransaction();
        } else {
            // 嵌套事务使用保存点
            $savepoint = 'sp_' . $this->transactionLevel;
            $this->db->getPdo()->exec("SAVEPOINT $savepoint");
            $this->savepoints[] = $savepoint;
        }
        
        $this->transactionLevel++;
    }
    
    public function commit() {
        if ($this->transactionLevel === 0) {
            throw new RuntimeException('没有活动的事务');
        }
        
        $this->transactionLevel--;
        
        if ($this->transactionLevel === 0) {
            $this->db->commit();
        } else {
            // 释放保存点
            $savepoint = array_pop($this->savepoints);
            $this->db->getPdo()->exec("RELEASE SAVEPOINT $savepoint");
        }
    }
    
    public function rollback() {
        if ($this->transactionLevel === 0) {
            throw new RuntimeException('没有活动的事务');
        }
        
        $this->transactionLevel--;
        
        if ($this->transactionLevel === 0) {
            $this->db->rollback();
        } else {
            // 回滚到保存点
            $savepoint = array_pop($this->savepoints);
            $this->db->getPdo()->exec("ROLLBACK TO SAVEPOINT $savepoint");
        }
    }
    
    public function getTransactionLevel() {
        return $this->transactionLevel;
    }
    
    public function inTransaction() {
        return $this->transactionLevel > 0;
    }
}

$transactionManager = new TransactionManager($db);

// 测试事务管理器
$result = $transactionManager->transaction(function($db) {
    // 创建新用户
    $db->execute(
        "INSERT INTO users (username, email, password, balance) VALUES (?, ?, ?, ?)",
        ['transaction_test', 'transaction@example.com', password_hash('123456', PASSWORD_DEFAULT), 500.00]
    );
    
    $newUserId = $db->lastInsertId();
    
    // 创建用户的第一篇文章
    $db->execute(
        "INSERT INTO posts (user_id, title, content, status) VALUES (?, ?, ?, ?)",
        [$newUserId, '事务测试文章', '这是在事务中创建的文章', 'published']
    );
    
    return $newUserId;
});

echo "✓ 事务管理器测试成功，新用户ID: $result\n";

echo "\n--- 分布式事务模拟 ---\n";

// 分布式事务协调器（简化版）
class DistributedTransactionCoordinator {
    private $participants = [];
    
    public function addParticipant($name, $db) {
        $this->participants[$name] = [
            'db' => $db,
            'prepared' => false,
            'committed' => false
        ];
    }
    
    public function executeDistributedTransaction(array $operations) {
        // Phase 1: Prepare
        echo "阶段1：准备阶段\n";
        
        foreach ($this->participants as $name => &$participant) {
            try {
                $participant['db']->beginTransaction();
                
                // 执行该参与者的操作
                if (isset($operations[$name])) {
                    $operations[$name]($participant['db']);
                }
                
                $participant['prepared'] = true;
                echo "  ✓ 参与者 $name 准备完成\n";
                
            } catch (Exception $e) {
                echo "  ✗ 参与者 $name 准备失败: " . $e->getMessage() . "\n";
                $this->rollbackAll();
                return false;
            }
        }
        
        // Phase 2: Commit
        echo "阶段2：提交阶段\n";
        
        foreach ($this->participants as $name => &$participant) {
            try {
                $participant['db']->commit();
                $participant['committed'] = true;
                echo "  ✓ 参与者 $name 提交完成\n";
                
            } catch (Exception $e) {
                echo "  ✗ 参与者 $name 提交失败: " . $e->getMessage() . "\n";
                // 在实际分布式系统中，这里需要更复杂的恢复机制
                return false;
            }
        }
        
        return true;
    }
    
    private function rollbackAll() {
        echo "回滚所有参与者\n";
        
        foreach ($this->participants as $name => &$participant) {
            if ($participant['prepared'] && !$participant['committed']) {
                try {
                    $participant['db']->rollback();
                    echo "  ✓ 参与者 $name 回滚完成\n";
                } catch (Exception $e) {
                    echo "  ✗ 参与者 $name 回滚失败: " . $e->getMessage() . "\n";
                }
            }
        }
    }
}

// 模拟分布式事务（使用同一个数据库的不同连接）
$coordinator = new DistributedTransactionCoordinator();
$coordinator->addParticipant('db1', $db);
$coordinator->addParticipant('db2', DatabaseManager::getInstance('secondary', $dbConfig['sqlite']));

$operations = [
    'db1' => function($db) {
        $db->execute(
            "INSERT INTO users (username, email, password, balance) VALUES (?, ?, ?, ?)",
            ['distributed_user1', 'dist1@example.com', password_hash('123456', PASSWORD_DEFAULT), 1000.00]
        );
    },
    'db2' => function($db) {
        $db->execute(
            "INSERT INTO users (username, email, password, balance) VALUES (?, ?, ?, ?)",
            ['distributed_user2', 'dist2@example.com', password_hash('123456', PASSWORD_DEFAULT), 1000.00]
        );
    }
];

$success = $coordinator->executeDistributedTransaction($operations);
echo $success ? "✓ 分布式事务执行成功\n" : "✗ 分布式事务执行失败\n";
?>

10.3.2 事务隔离级别

<?php
// 事务隔离级别
echo "=== 事务隔离级别 ===\n";

// 隔离级别管理器
class IsolationLevelManager {
    private $db;
    
    const READ_UNCOMMITTED = 'READ UNCOMMITTED';
    const READ_COMMITTED = 'READ COMMITTED';
    const REPEATABLE_READ = 'REPEATABLE READ';
    const SERIALIZABLE = 'SERIALIZABLE';
    
    public function __construct($db) {
        $this->db = $db;
    }
    
    public function setIsolationLevel($level) {
        // 注意：SQLite不支持所有隔离级别，这里仅作演示
        try {
            $this->db->getPdo()->exec("PRAGMA read_uncommitted = " . ($level === self::READ_UNCOMMITTED ? '1' : '0'));
            echo "✓ 设置隔离级别为: $level\n";
        } catch (PDOException $e) {
            echo "✗ 设置隔离级别失败: " . $e->getMessage() . "\n";
        }
    }
    
    public function getCurrentIsolationLevel() {
        try {
            $result = $this->db->fetchOne("PRAGMA read_uncommitted");
            return $result ? self::READ_UNCOMMITTED : self::READ_COMMITTED;
        } catch (PDOException $e) {
            return 'UNKNOWN';
        }
    }
    
    public function demonstratePhantomRead() {
        echo "\n--- 幻读演示 ---\n";
        
        // 创建两个连接模拟并发
        $conn1 = DatabaseManager::getInstance('conn1', $dbConfig['sqlite']);
        $conn2 = DatabaseManager::getInstance('conn2', $dbConfig['sqlite']);
        
        echo "连接1开始事务并查询用户数量\n";
        $conn1->beginTransaction();
        $count1 = $conn1->fetchOne("SELECT COUNT(*) as count FROM users")['count'];
        echo "连接1查询到用户数量: $count1\n";
        
        echo "\n连接2插入新用户\n";
        $conn2->execute(
            "INSERT INTO users (username, email, password) VALUES (?, ?, ?)",
            ['phantom_user', 'phantom@example.com', password_hash('123456', PASSWORD_DEFAULT)]
        );
        echo "连接2插入用户成功\n";
        
        echo "\n连接1再次查询用户数量\n";
        $count2 = $conn1->fetchOne("SELECT COUNT(*) as count FROM users")['count'];
        echo "连接1再次查询到用户数量: $count2\n";
        
        if ($count2 > $count1) {
            echo "✓ 发生了幻读现象\n";
        } else {
            echo "✗ 未发生幻读现象\n";
        }
        
        $conn1->rollback();
    }
    
    public function demonstrateDirtyRead() {
        echo "\n--- 脏读演示 ---\n";
        
        $conn1 = DatabaseManager::getInstance('conn1', $dbConfig['sqlite']);
        $conn2 = DatabaseManager::getInstance('conn2', $dbConfig['sqlite']);
        
        // 获取一个测试用户
        $user = $conn1->fetchOne("SELECT * FROM users WHERE balance > 0 LIMIT 1");
        if (!$user) {
            echo "没有可用的测试用户\n";
            return;
        }
        
        $originalBalance = $user['balance'];
        echo "用户 {$user['username']} 原始余额: $originalBalance\n";
        
        echo "\n连接1开始事务并修改余额\n";
        $conn1->beginTransaction();
        $conn1->execute(
            "UPDATE users SET balance = balance + 500 WHERE id = ?",
            [$user['id']]
        );
        echo "连接1修改余额 +500（未提交）\n";
        
        echo "\n连接2读取余额\n";
        $modifiedUser = $conn2->fetchOne("SELECT balance FROM users WHERE id = ?", [$user['id']]);
        echo "连接2读取到余额: {$modifiedUser['balance']}\n";
        
        if ($modifiedUser['balance'] != $originalBalance) {
            echo "✓ 发生了脏读现象\n";
        } else {
            echo "✗ 未发生脏读现象\n";
        }
        
        echo "\n连接1回滚事务\n";
        $conn1->rollback();
        
        $finalUser = $conn2->fetchOne("SELECT balance FROM users WHERE id = ?", [$user['id']]);
        echo "最终余额: {$finalUser['balance']}\n";
    }
}

$isolationManager = new IsolationLevelManager($db);

echo "当前隔离级别: " . $isolationManager->getCurrentIsolationLevel() . "\n";

// 演示并发问题
$isolationManager->demonstratePhantomRead();
$isolationManager->demonstrateDirtyRead();
?>

10.4 预处理语句与安全

10.4.1 预处理语句详解

<?php
// 预处理语句与安全
echo "=== 预处理语句与安全 ===\n";

echo "--- 预处理语句基础 ---\n";

// 预处理语句管理器
class PreparedStatementManager {
    private $db;
    private $statements = [];
    
    public function __construct($db) {
        $this->db = $db;
    }
    
    public function prepare($name, $sql) {
        $this->statements[$name] = $this->db->getPdo()->prepare($sql);
        return $this->statements[$name];
    }
    
    public function execute($name, $params = []) {
        if (!isset($this->statements[$name])) {
            throw new InvalidArgumentException("预处理语句 '$name' 不存在");
        }
        
        $stmt = $this->statements[$name];
        $stmt->execute($params);
        return $stmt;
    }
    
    public function fetchAll($name, $params = []) {
        return $this->execute($name, $params)->fetchAll();
    }
    
    public function fetchOne($name, $params = []) {
        return $this->execute($name, $params)->fetch();
    }
    
    public function getStatement($name) {
        return $this->statements[$name] ?? null;
    }
    
    public function closeStatement($name) {
        if (isset($this->statements[$name])) {
            $this->statements[$name]->closeCursor();
            unset($this->statements[$name]);
        }
    }
    
    public function closeAllStatements() {
        foreach ($this->statements as $name => $stmt) {
            $stmt->closeCursor();
        }
        $this->statements = [];
    }
}

$stmtManager = new PreparedStatementManager($db);

// 准备常用的预处理语句
$stmtManager->prepare('find_user_by_username', "SELECT * FROM users WHERE username = ?");
$stmtManager->prepare('find_user_by_email', "SELECT * FROM users WHERE email = ?");
$stmtManager->prepare('insert_user', "INSERT INTO users (username, email, password) VALUES (?, ?, ?)");
$stmtManager->prepare('update_user_balance', "UPDATE users SET balance = ? WHERE id = ?");
$stmtManager->prepare('find_posts_by_user', "SELECT * FROM posts WHERE user_id = ? ORDER BY created_at DESC");

echo "✓ 预处理语句准备完成\n";

// 测试预处理语句
$user = $stmtManager->fetchOne('find_user_by_username', ['zhangsan']);
if ($user) {
    echo "找到用户: {$user['username']}\n";
    
    $posts = $stmtManager->fetchAll('find_posts_by_user', [$user['id']]);
    echo "该用户有 " . count($posts) . " 篇文章\n";
}

echo "\n--- SQL注入防护 ---\n";

// SQL注入防护演示
class SQLInjectionDemo {
    private $db;
    
    public function __construct($db) {
        $this->db = $db;
    }
    
    public function vulnerableQuery($username) {
        // 危险：直接拼接SQL
        $sql = "SELECT * FROM users WHERE username = '$username'";
        echo "危险SQL: $sql\n";
        
        try {
            $result = $this->db->getPdo()->query($sql);
            return $result ? $result->fetchAll() : [];
        } catch (PDOException $e) {
            echo "查询失败: " . $e->getMessage() . "\n";
            return [];
        }
    }
    
    public function safeQuery($username) {
        // 安全：使用预处理语句
        $sql = "SELECT * FROM users WHERE username = ?";
        echo "安全SQL: $sql\n";
        
        try {
            $stmt = $this->db->getPdo()->prepare($sql);
            $stmt->execute([$username]);
            return $stmt->fetchAll();
        } catch (PDOException $e) {
            echo "查询失败: " . $e->getMessage() . "\n";
            return [];
        }
    }
    
    public function demonstrateInjection() {
        echo "\n=== SQL注入演示 ===\n";
        
        // 正常查询
        echo "--- 正常查询 ---\n";
        $normalInput = "zhangsan";
        echo "输入: $normalInput\n";
        
        $vulnerableResult = $this->vulnerableQuery($normalInput);
        echo "危险方法结果数量: " . count($vulnerableResult) . "\n";
        
        $safeResult = $this->safeQuery($normalInput);
        echo "安全方法结果数量: " . count($safeResult) . "\n";
        
        // 注入攻击尝试
        echo "\n--- 注入攻击尝试 ---\n";
        $maliciousInput = "zhangsan' OR '1'='1";
        echo "恶意输入: $maliciousInput\n";
        
        $vulnerableResult = $this->vulnerableQuery($maliciousInput);
        echo "危险方法结果数量: " . count($vulnerableResult) . "\n";
        
        $safeResult = $this->safeQuery($maliciousInput);
        echo "安全方法结果数量: " . count($safeResult) . "\n";
        
        if (count($vulnerableResult) > count($safeResult)) {
            echo "✗ 危险方法存在SQL注入漏洞\n";
        } else {
            echo "✓ 两种方法结果一致\n";
        }
    }
}

$injectionDemo = new SQLInjectionDemo($db);
$injectionDemo->demonstrateInjection();

echo "\n--- 输入验证与清理 ---\n";

// 输入验证器
class InputValidator {
    public static function validateEmail($email) {
        return filter_var($email, FILTER_VALIDATE_EMAIL) !== false;
    }
    
    public static function validateUsername($username) {
        // 用户名只能包含字母、数字、下划线，长度3-20
        return preg_match('/^[a-zA-Z0-9_]{3,20}$/', $username);
    }
    
    public static function sanitizeString($input) {
        // 移除HTML标签和特殊字符
        $input = strip_tags($input);
        $input = htmlspecialchars($input, ENT_QUOTES, 'UTF-8');
        return trim($input);
    }
    
    public static function validateInteger($value, $min = null, $max = null) {
        $value = filter_var($value, FILTER_VALIDATE_INT);
        if ($value === false) {
            return false;
        }
        
        if ($min !== null && $value < $min) {
            return false;
        }
        
        if ($max !== null && $value > $max) {
            return false;
        }
        
        return $value;
    }
    
    public static function validateFloat($value, $min = null, $max = null) {
        $value = filter_var($value, FILTER_VALIDATE_FLOAT);
        if ($value === false) {
            return false;
        }
        
        if ($min !== null && $value < $min) {
            return false;
        }
        
        if ($max !== null && $value > $max) {
            return false;
        }
        
        return $value;
    }
}

// 安全的用户注册函数
function safeRegisterUser($db, $username, $email, $password) {
    // 输入验证
    if (!InputValidator::validateUsername($username)) {
        throw new InvalidArgumentException('用户名格式不正确');
    }
    
    if (!InputValidator::validateEmail($email)) {
        throw new InvalidArgumentException('邮箱格式不正确');
    }
    
    if (strlen($password) < 6) {
        throw new InvalidArgumentException('密码长度至少6位');
    }
    
    // 清理输入
    $username = InputValidator::sanitizeString($username);
    $email = InputValidator::sanitizeString($email);
    
    // 检查用户名和邮箱是否已存在
    $existingUser = $db->fetchOne(
        "SELECT id FROM users WHERE username = ? OR email = ?",
        [$username, $email]
    );
    
    if ($existingUser) {
        throw new RuntimeException('用户名或邮箱已存在');
    }
    
    // 密码哈希
    $hashedPassword = password_hash($password, PASSWORD_DEFAULT);
    
    // 插入用户
    $result = $db->execute(
        "INSERT INTO users (username, email, password) VALUES (?, ?, ?)",
        [$username, $email, $hashedPassword]
    );
    
    if ($result > 0) {
        return $db->lastInsertId();
    } else {
        throw new RuntimeException('用户注册失败');
    }
}

// 测试安全注册
try {
    $newUserId = safeRegisterUser($db, 'safe_user', 'safe@example.com', 'securepassword123');
    echo "✓ 安全注册用户成功，ID: $newUserId\n";
} catch (Exception $e) {
    echo "✗ 注册失败: " . $e->getMessage() . "\n";
}

// 测试无效输入
try {
    safeRegisterUser($db, 'invalid user!', 'invalid-email', '123');
} catch (Exception $e) {
    echo "✓ 成功拦截无效输入: " . $e->getMessage() . "\n";
}
?>

10.2 基础CRUD操作

10.2.1 插入操作（Create）

<?php
// 插入操作
echo "=== 插入操作 ===\n";

echo "--- 基础插入 ---\n";

// 单条记录插入
$insertUserSQL = "INSERT INTO users (username, email, password) VALUES (?, ?, ?)";

$users = [
    ['zhangsan', 'zhangsan@example.com', password_hash('123456', PASSWORD_DEFAULT)],
    ['lisi', 'lisi@example.com', password_hash('123456', PASSWORD_DEFAULT)],
    ['wangwu', 'wangwu@example.com', password_hash('123456', PASSWORD_DEFAULT)]
];

foreach ($users as $user) {
    try {
        $result = $db->execute($insertUserSQL, $user);
        $userId = $db->lastInsertId();
        echo "✓ 用户 {$user[0]} 插入成功，ID: $userId\n";
    } catch (PDOException $e) {
        echo "✗ 用户 {$user[0]} 插入失败: " . $e->getMessage() . "\n";
    }
}

echo "\n--- 批量插入 ---\n";

// 批量插入类
class BatchInserter {
    private $db;
    private $table;
    private $columns;
    private $batchSize;
    private $buffer = [];
    
    public function __construct($db, $table, $columns, $batchSize = 100) {
        $this->db = $db;
        $this->table = $table;
        $this->columns = $columns;
        $this->batchSize = $batchSize;
    }
    
    public function add($data) {
        $this->buffer[] = $data;
        
        if (count($this->buffer) >= $this->batchSize) {
            $this->flush();
        }
    }
    
    public function flush() {
        if (empty($this->buffer)) {
            return 0;
        }
        
        $placeholders = '(' . str_repeat('?,', count($this->columns) - 1) . '?)';
        $values = str_repeat($placeholders . ',', count($this->buffer) - 1) . $placeholders;
        
        $sql = "INSERT INTO {$this->table} (" . implode(',', $this->columns) . ") VALUES $values";
        
        $params = [];
        foreach ($this->buffer as $row) {
            $params = array_merge($params, array_values($row));
        }
        
        $result = $this->db->execute($sql, $params);
        $count = count($this->buffer);
        $this->buffer = [];
        
        return $count;
    }
    
    public function __destruct() {
        $this->flush();
    }
}

// 测试批量插入
$batchInserter = new BatchInserter($db, 'posts', ['user_id', 'title', 'content', 'status']);

$posts = [
    [1, 'PHP基础教程', 'PHP是一种流行的服务器端脚本语言', 'published'],
    [1, 'MySQL数据库', '关系型数据库管理系统', 'published'],
    [2, 'JavaScript入门', '前端编程语言', 'draft'],
    [2, 'CSS样式设计', '网页样式表语言', 'published'],
    [3, 'Python编程', '简洁优雅的编程语言', 'draft']
];

foreach ($posts as $post) {
    $batchInserter->add($post);
}

$insertedCount = $batchInserter->flush();
echo "✓ 批量插入 $insertedCount 条文章记录\n";

echo "\n--- 插入或更新 ---\n";

// 插入或更新（UPSERT）
class UpsertHelper {
    private $db;
    
    public function __construct($db) {
        $this->db = $db;
    }
    
    public function upsertUser($username, $email, $password) {
        // SQLite使用INSERT OR REPLACE
        $sql = "
            INSERT OR REPLACE INTO users (id, username, email, password, created_at, updated_at)
            VALUES (
                (SELECT id FROM users WHERE username = ? OR email = ?),
                ?, ?, ?, 
                COALESCE((SELECT created_at FROM users WHERE username = ? OR email = ?), CURRENT_TIMESTAMP),
                CURRENT_TIMESTAMP
            )
        ";
        
        $params = [$username, $email, $username, $email, $password, $username, $email];
        return $this->db->execute($sql, $params);
    }
    
    public function upsertUserSimple($username, $email, $password) {
        // 先尝试更新
        $updateSQL = "UPDATE users SET email = ?, password = ?, updated_at = CURRENT_TIMESTAMP WHERE username = ?";
        $result = $this->db->execute($updateSQL, [$email, $password, $username]);
        
        if ($result === 0) {
            // 如果没有更新任何行，则插入新记录
            $insertSQL = "INSERT INTO users (username, email, password) VALUES (?, ?, ?)";
            $this->db->execute($insertSQL, [$username, $email, $password]);
            return 'inserted';
        } else {
            return 'updated';
        }
    }
}

$upsertHelper = new UpsertHelper($db);

// 测试插入或更新
$result = $upsertHelper->upsertUserSimple('zhangsan', 'zhangsan_new@example.com', password_hash('newpassword', PASSWORD_DEFAULT));
echo "✓ 用户zhangsan操作结果: $result\n";

$result = $upsertHelper->upsertUserSimple('newuser', 'newuser@example.com', password_hash('password', PASSWORD_DEFAULT));
echo "✓ 用户newuser操作结果: $result\n";
?>

10.2.2 查询操作（Read）

<?php
// 查询操作
echo "=== 查询操作 ===\n";

echo "--- 基础查询 ---\n";

// 查询所有用户
$users = $db->fetchAll("SELECT * FROM users ORDER BY created_at");
echo "用户总数: " . count($users) . "\n";
foreach ($users as $user) {
    echo "  ID: {$user['id']}, 用户名: {$user['username']}, 邮箱: {$user['email']}\n";
}

echo "\n--- 条件查询 ---\n";

// 按条件查询
$user = $db->fetchOne("SELECT * FROM users WHERE username = ?", ['zhangsan']);
if ($user) {
    echo "找到用户: {$user['username']} ({$user['email']})\n";
} else {
    echo "用户不存在\n";
}

// 模糊查询
$users = $db->fetchAll("SELECT * FROM users WHERE email LIKE ?", ['%example.com']);
echo "\n使用example.com邮箱的用户数: " . count($users) . "\n";

echo "\n--- 查询构建器 ---\n";

// 查询构建器类
class QueryBuilder {
    private $db;
    private $table;
    private $select = ['*'];
    private $where = [];
    private $params = [];
    private $orderBy = [];
    private $groupBy = [];
    private $having = [];
    private $limit = null;
    private $offset = null;
    private $joins = [];
    
    public function __construct($db, $table) {
        $this->db = $db;
        $this->table = $table;
    }
    
    public function select($columns) {
        if (is_array($columns)) {
            $this->select = $columns;
        } else {
            $this->select = func_get_args();
        }
        return $this;
    }
    
    public function where($column, $operator, $value = null) {
        if ($value === null) {
            $value = $operator;
            $operator = '=';
        }
        
        $this->where[] = "$column $operator ?";
        $this->params[] = $value;
        return $this;
    }
    
    public function whereIn($column, $values) {
        $placeholders = str_repeat('?,', count($values) - 1) . '?';
        $this->where[] = "$column IN ($placeholders)";
        $this->params = array_merge($this->params, $values);
        return $this;
    }
    
    public function whereBetween($column, $min, $max) {
        $this->where[] = "$column BETWEEN ? AND ?";
        $this->params[] = $min;
        $this->params[] = $max;
        return $this;
    }
    
    public function whereNull($column) {
        $this->where[] = "$column IS NULL";
        return $this;
    }
    
    public function whereNotNull($column) {
        $this->where[] = "$column IS NOT NULL";
        return $this;
    }
    
    public function orderBy($column, $direction = 'ASC') {
        $this->orderBy[] = "$column $direction";
        return $this;
    }
    
    public function groupBy($column) {
        $this->groupBy[] = $column;
        return $this;
    }
    
    public function having($column, $operator, $value = null) {
        if ($value === null) {
            $value = $operator;
            $operator = '=';
        }
        
        $this->having[] = "$column $operator ?";
        $this->params[] = $value;
        return $this;
    }
    
    public function limit($limit, $offset = null) {
        $this->limit = $limit;
        if ($offset !== null) {
            $this->offset = $offset;
        }
        return $this;
    }
    
    public function offset($offset) {
        $this->offset = $offset;
        return $this;
    }
    
    public function join($table, $first, $operator, $second = null) {
        return $this->addJoin('INNER JOIN', $table, $first, $operator, $second);
    }
    
    public function leftJoin($table, $first, $operator, $second = null) {
        return $this->addJoin('LEFT JOIN', $table, $first, $operator, $second);
    }
    
    public function rightJoin($table, $first, $operator, $second = null) {
        return $this->addJoin('RIGHT JOIN', $table, $first, $operator, $second);
    }
    
    private function addJoin($type, $table, $first, $operator, $second) {
        if ($second === null) {
            $second = $operator;
            $operator = '=';
        }
        
        $this->joins[] = "$type $table ON $first $operator $second";
        return $this;
    }
    
    public function toSQL() {
        $sql = 'SELECT ' . implode(', ', $this->select) . ' FROM ' . $this->table;
        
        if (!empty($this->joins)) {
            $sql .= ' ' . implode(' ', $this->joins);
        }
        
        if (!empty($this->where)) {
            $sql .= ' WHERE ' . implode(' AND ', $this->where);
        }
        
        if (!empty($this->groupBy)) {
            $sql .= ' GROUP BY ' . implode(', ', $this->groupBy);
        }
        
        if (!empty($this->having)) {
            $sql .= ' HAVING ' . implode(' AND ', $this->having);
        }
        
        if (!empty($this->orderBy)) {
            $sql .= ' ORDER BY ' . implode(', ', $this->orderBy);
        }
        
        if ($this->limit !== null) {
            $sql .= ' LIMIT ' . $this->limit;
            if ($this->offset !== null) {
                $sql .= ' OFFSET ' . $this->offset;
            }
        }
        
        return $sql;
    }
    
    public function get() {
        return $this->db->fetchAll($this->toSQL(), $this->params);
    }
    
    public function first() {
        $this->limit(1);
        return $this->db->fetchOne($this->toSQL(), $this->params);
    }
    
    public function count() {
        $originalSelect = $this->select;
        $this->select = ['COUNT(*) as count'];
        $result = $this->db->fetchOne($this->toSQL(), $this->params);
        $this->select = $originalSelect;
        return $result ? (int)$result['count'] : 0;
    }
    
    public function exists() {
        return $this->count() > 0;
    }
}

// 测试查询构建器
echo "--- 查询构建器测试 ---\n";

$query = new QueryBuilder($db, 'users');

// 简单查询
$users = $query->select('id', 'username', 'email')
               ->where('username', 'LIKE', '%san%')
               ->orderBy('created_at', 'DESC')
               ->get();

echo "包含'san'的用户:\n";
foreach ($users as $user) {
    echo "  {$user['username']} - {$user['email']}\n";
}

// 复杂查询
$query = new QueryBuilder($db, 'posts');
$posts = $query->select('posts.*', 'users.username')
               ->leftJoin('users', 'posts.user_id', 'users.id')
               ->where('posts.status', 'published')
               ->orderBy('posts.created_at', 'DESC')
               ->get();

echo "\n已发布的文章:\n";
foreach ($posts as $post) {
    echo "  《{$post['title']}》 - 作者: {$post['username']}\n";
}

// 统计查询
$query = new QueryBuilder($db, 'posts');
$count = $query->where('status', 'published')->count();
echo "\n已发布文章总数: $count\n";

// 分页查询
$query = new QueryBuilder($db, 'users');
$users = $query->select('username', 'email')
               ->orderBy('created_at')
               ->limit(2, 1) // 每页2条，跳过1条
               ->get();

echo "\n分页查询结果（第2页，每页2条）:\n";
foreach ($users as $user) {
    echo "  {$user['username']} - {$user['email']}\n";
}
?>

10.2.3 更新操作（Update）

<?php
// 更新操作
echo "=== 更新操作 ===\n";

echo "--- 基础更新 ---\n";

// 单条记录更新
$updateSQL = "UPDATE users SET email = ?, updated_at = CURRENT_TIMESTAMP WHERE username = ?";
$result = $db->execute($updateSQL, ['zhangsan_updated@example.com', 'zhangsan']);
echo "更新用户zhangsan的邮箱，影响行数: $result\n";

// 批量更新
$updateSQL = "UPDATE posts SET status = ? WHERE user_id = ?";
$result = $db->execute($updateSQL, ['published', 2]);
echo "更新用户ID为2的所有文章状态，影响行数: $result\n";

echo "\n--- 条件更新 ---\n";

// 更新构建器类
class UpdateBuilder {
    private $db;
    private $table;
    private $set = [];
    private $where = [];
    private $params = [];
    
    public function __construct($db, $table) {
        $this->db = $db;
        $this->table = $table;
    }
    
    public function set($column, $value) {
        $this->set[] = "$column = ?";
        $this->params[] = $value;
        return $this;
    }
    
    public function increment($column, $amount = 1) {
        $this->set[] = "$column = $column + ?";
        $this->params[] = $amount;
        return $this;
    }
    
    public function decrement($column, $amount = 1) {
        $this->set[] = "$column = $column - ?";
        $this->params[] = $amount;
        return $this;
    }
    
    public function where($column, $operator, $value = null) {
        if ($value === null) {
            $value = $operator;
            $operator = '=';
        }
        
        $this->where[] = "$column $operator ?";
        $this->params[] = $value;
        return $this;
    }
    
    public function whereIn($column, $values) {
        $placeholders = str_repeat('?,', count($values) - 1) . '?';
        $this->where[] = "$column IN ($placeholders)";
        $this->params = array_merge($this->params, $values);
        return $this;
    }
    
    public function toSQL() {
        if (empty($this->set)) {
            throw new InvalidArgumentException('没有设置要更新的字段');
        }
        
        $sql = 'UPDATE ' . $this->table . ' SET ' . implode(', ', $this->set);
        
        if (!empty($this->where)) {
            $sql .= ' WHERE ' . implode(' AND ', $this->where);
        }
        
        return $sql;
    }
    
    public function execute() {
        return $this->db->execute($this->toSQL(), $this->params);
    }
}

// 测试更新构建器
$updateBuilder = new UpdateBuilder($db, 'users');
$result = $updateBuilder->set('email', 'lisi_new@example.com')
                        ->set('updated_at', date('Y-m-d H:i:s'))
                        ->where('username', 'lisi')
                        ->execute();

echo "使用更新构建器更新用户lisi，影响行数: $result\n";

echo "\n--- 批量更新不同值 ---\n";

// 批量更新不同值的辅助类
class BatchUpdater {
    private $db;
    private $table;
    private $keyColumn;
    
    public function __construct($db, $table, $keyColumn = 'id') {
        $this->db = $db;
        $this->table = $table;
        $this->keyColumn = $keyColumn;
    }
    
    public function updateMultiple($updates) {
        if (empty($updates)) {
            return 0;
        }
        
        $this->db->beginTransaction();
        
        try {
            $totalAffected = 0;
            
            foreach ($updates as $keyValue => $data) {
                $setParts = [];
                $params = [];
                
                foreach ($data as $column => $value) {
                    $setParts[] = "$column = ?";
                    $params[] = $value;
                }
                
                $params[] = $keyValue;
                
                $sql = "UPDATE {$this->table} SET " . implode(', ', $setParts) . 
                       " WHERE {$this->keyColumn} = ?";
                
                $affected = $this->db->execute($sql, $params);
                $totalAffected += $affected;
            }
            
            $this->db->commit();
            return $totalAffected;
            
        } catch (Exception $e) {
            $this->db->rollback();
            throw $e;
        }
    }
    
    public function updateBatch($data, $columns) {
        if (empty($data) || empty($columns)) {
            return 0;
        }
        
        // 构建CASE WHEN语句
        $cases = [];
        $keyValues = [];
        $allParams = [];
        
        foreach ($columns as $column) {
            $whenClauses = [];
            foreach ($data as $row) {
                $whenClauses[] = "WHEN ? THEN ?";
                $allParams[] = $row[$this->keyColumn];
                $allParams[] = $row[$column];
            }
            $cases[] = "$column = CASE {$this->keyColumn} " . implode(' ', $whenClauses) . " ELSE $column END";
        }
        
        foreach ($data as $row) {
            $keyValues[] = $row[$this->keyColumn];
        }
        
        $keyPlaceholders = str_repeat('?,', count($keyValues) - 1) . '?';
        $allParams = array_merge($allParams, $keyValues);
        
        $sql = "UPDATE {$this->table} SET " . implode(', ', $cases) . 
               " WHERE {$this->keyColumn} IN ($keyPlaceholders)";
        
        return $this->db->execute($sql, $allParams);
    }
}

// 测试批量更新
$batchUpdater = new BatchUpdater($db, 'posts', 'id');

// 方法1：逐条更新
$updates = [
    1 => ['title' => 'PHP高级教程', 'status' => 'published'],
    2 => ['title' => 'MySQL优化指南', 'status' => 'published']
];

$affected = $batchUpdater->updateMultiple($updates);
echo "批量更新（逐条），影响行数: $affected\n";

// 方法2：CASE WHEN批量更新
$batchData = [
    ['id' => 3, 'title' => 'JavaScript高级编程', 'status' => 'published'],
    ['id' => 4, 'title' => 'CSS3动画设计', 'status' => 'published']
];

$affected = $batchUpdater->updateBatch($batchData, ['title', 'status']);
echo "批量更新（CASE WHEN），影响行数: $affected\n";

echo "\n--- 乐观锁更新 ---\n";

// 乐观锁更新示例
class OptimisticLockUpdater {
    private $db;
    
    public function __construct($db) {
        $this->db = $db;
    }
    
    public function updateWithVersion($table, $id, $data, $currentVersion) {
        // 添加版本号到更新数据
        $data['version'] = $currentVersion + 1;
        $data['updated_at'] = date('Y-m-d H:i:s');
        
        $setParts = [];
        $params = [];
        
        foreach ($data as $column => $value) {
            $setParts[] = "$column = ?";
            $params[] = $value;
        }
        
        $params[] = $id;
        $params[] = $currentVersion;
        
        $sql = "UPDATE $table SET " . implode(', ', $setParts) . 
               " WHERE id = ? AND version = ?";
        
        $affected = $this->db->execute($sql, $params);
        
        if ($affected === 0) {
            throw new RuntimeException('记录已被其他用户修改，请刷新后重试');
        }
        
        return $affected;
    }
}

// 为用户表添加版本字段（仅演示，实际应用中应该在建表时添加）
try {
    $db->execute("ALTER TABLE users ADD COLUMN version INTEGER DEFAULT 1");
    echo "✓ 为用户表添加版本字段\n";
} catch (PDOException $e) {
    // 字段可能已存在
    echo "版本字段可能已存在\n";
}

$optimisticUpdater = new OptimisticLockUpdater($db);

// 模拟乐观锁更新
$user = $db->fetchOne("SELECT * FROM users WHERE username = ?", ['zhangsan']);
if ($user) {
    try {
        $affected = $optimisticUpdater->updateWithVersion(
            'users',
            $user['id'],
            ['email' => 'zhangsan_optimistic@example.com'],
            $user['version'] ?? 1
        );
        echo "✓ 乐观锁更新成功，影响行数: $affected\n";
    } catch (RuntimeException $e) {
        echo "✗ 乐观锁更新失败: " . $e->getMessage() . "\n";
    }
}
?>

10.2.4 删除操作（Delete）

<?php
// 删除操作
echo "=== 删除操作 ===\n";

echo "--- 基础删除 ---\n";

// 先插入一些测试数据
$testUsers = [
    ['testuser1', 'test1@example.com', password_hash('123456', PASSWORD_DEFAULT)],
    ['testuser2', 'test2@example.com', password_hash('123456', PASSWORD_DEFAULT)],
    ['testuser3', 'test3@example.com', password_hash('123456', PASSWORD_DEFAULT)]
];

foreach ($testUsers as $user) {
    $db->execute("INSERT INTO users (username, email, password) VALUES (?, ?, ?)", $user);
}
echo "✓ 插入测试用户数据\n";

// 单条删除
$deleteSQL = "DELETE FROM users WHERE username = ?";
$result = $db->execute($deleteSQL, ['testuser1']);
echo "删除用户testuser1，影响行数: $result\n";

// 条件删除
$deleteSQL = "DELETE FROM users WHERE username LIKE ?";
$result = $db->execute($deleteSQL, ['testuser%']);
echo "删除所有testuser开头的用户，影响行数: $result\n";

echo "\n--- 删除构建器 ---\n";

// 删除构建器类
class DeleteBuilder {
    private $db;
    private $table;
    private $where = [];
    private $params = [];
    
    public function __construct($db, $table) {
        $this->db = $db;
        $this->table = $table;
    }
    
    public function where($column, $operator, $value = null) {
        if ($value === null) {
            $value = $operator;
            $operator = '=';
        }
        
        $this->where[] = "$column $operator ?";
        $this->params[] = $value;
        return $this;
    }
    
    public function whereIn($column, $values) {
        $placeholders = str_repeat('?,', count($values) - 1) . '?';
        $this->where[] = "$column IN ($placeholders)";
        $this->params = array_merge($this->params, $values);
        return $this;
    }
    
    public function whereBetween($column, $min, $max) {
        $this->where[] = "$column BETWEEN ? AND ?";
        $this->params[] = $min;
        $this->params[] = $max;
        return $this;
    }
    
    public function toSQL() {
        $sql = 'DELETE FROM ' . $this->table;
        
        if (!empty($this->where)) {
            $sql .= ' WHERE ' . implode(' AND ', $this->where);
        }
        
        return $sql;
    }
    
    public function execute() {
        return $this->db->execute($this->toSQL(), $this->params);
    }
}

// 插入更多测试数据
$morePosts = [
    [1, '待删除文章1', '内容1', 'draft'],
    [1, '待删除文章2', '内容2', 'draft'],
    [2, '待删除文章3', '内容3', 'draft']
];

foreach ($morePosts as $post) {
    $db->execute("INSERT INTO posts (user_id, title, content, status) VALUES (?, ?, ?, ?)", $post);
}

// 使用删除构建器
$deleteBuilder = new DeleteBuilder($db, 'posts');
$result = $deleteBuilder->where('status', 'draft')
                        ->where('user_id', 1)
                        ->execute();

echo "删除用户1的草稿文章，影响行数: $result\n";

echo "\n--- 软删除 ---\n";

// 软删除实现
class SoftDeleteManager {
    private $db;
    private $deletedAtColumn;
    
    public function __construct($db, $deletedAtColumn = 'deleted_at') {
        $this->db = $db;
        $this->deletedAtColumn = $deletedAtColumn;
    }
    
    public function softDelete($table, $id) {
        $sql = "UPDATE $table SET {$this->deletedAtColumn} = CURRENT_TIMESTAMP WHERE id = ? AND {$this->deletedAtColumn} IS NULL";
        return $this->db->execute($sql, [$id]);
    }
    
    public function restore($table, $id) {
        $sql = "UPDATE $table SET {$this->deletedAtColumn} = NULL WHERE id = ?";
        return $this->db->execute($sql, [$id]);
    }
    
    public function forceDelete($table, $id) {
        $sql = "DELETE FROM $table WHERE id = ?";
        return $this->db->execute($sql, [$id]);
    }
    
    public function findWithTrashed($table, $id) {
        $sql = "SELECT * FROM $table WHERE id = ?";
        return $this->db->fetchOne($sql, [$id]);
    }
    
    public function findOnlyTrashed($table, $id) {
        $sql = "SELECT * FROM $table WHERE id = ? AND {$this->deletedAtColumn} IS NOT NULL";
        return $this->db->fetchOne($sql, [$id]);
    }
    
    public function getAllActive($table) {
        $sql = "SELECT * FROM $table WHERE {$this->deletedAtColumn} IS NULL";
        return $this->db->fetchAll($sql);
    }
    
    public function getAllTrashed($table) {
        $sql = "SELECT * FROM $table WHERE {$this->deletedAtColumn} IS NOT NULL";
        return $this->db->fetchAll($sql);
    }
}

// 为表添加软删除字段
try {
    $db->execute("ALTER TABLE posts ADD COLUMN deleted_at DATETIME NULL");
    echo "✓ 为文章表添加软删除字段\n";
} catch (PDOException $e) {
    echo "软删除字段可能已存在\n";
}

$softDeleteManager = new SoftDeleteManager($db);

// 测试软删除
$posts = $db->fetchAll("SELECT id, title FROM posts WHERE deleted_at IS NULL LIMIT 2");
if (!empty($posts)) {
    $postId = $posts[0]['id'];
    $postTitle = $posts[0]['title'];
    
    // 软删除
    $result = $softDeleteManager->softDelete('posts', $postId);
    echo "软删除文章《{$postTitle}》，影响行数: $result\n";
    
    // 查看软删除的记录
    $trashedPost = $softDeleteManager->findOnlyTrashed('posts', $postId);
    if ($trashedPost) {
        echo "✓ 找到软删除的文章: {$trashedPost['title']}\n";
    }
    
    // 恢复软删除
    $result = $softDeleteManager->restore('posts', $postId);
    echo "恢复文章《{$postTitle}》，影响行数: $result\n";
}

echo "\n--- 级联删除 ---\n";

// 级联删除管理器
class CascadeDeleteManager {
    private $db;
    private $relationships = [];
    
    public function __construct($db) {
        $this->db = $db;
    }
    
    public function addRelationship($parentTable, $childTable, $foreignKey) {
        if (!isset($this->relationships[$parentTable])) {
            $this->relationships[$parentTable] = [];
        }
        $this->relationships[$parentTable][] = [
            'table' => $childTable,
            'foreign_key' => $foreignKey
        ];
    }
    
    public function cascadeDelete($table, $id) {
        $this->db->beginTransaction();
        
        try {
            // 递归删除相关记录
            $this->deleteRecursive($table, $id);
            
            $this->db->commit();
            return true;
            
        } catch (Exception $e) {
            $this->db->rollback();
            throw $e;
        }
    }
    
    private function deleteRecursive($table, $id) {
        // 先删除子记录
        if (isset($this->relationships[$table])) {
            foreach ($this->relationships[$table] as $relationship) {
                $childTable = $relationship['table'];
                $foreignKey = $relationship['foreign_key'];
                
                // 查找所有子记录
                $children = $this->db->fetchAll(
                    "SELECT id FROM $childTable WHERE $foreignKey = ?",
                    [$id]
                );
                
                // 递归删除每个子记录
                foreach ($children as $child) {
                    $this->deleteRecursive($childTable, $child['id']);
                }
            }
        }
        
        // 最后删除当前记录
        $this->db->execute("DELETE FROM $table WHERE id = ?", [$id]);
    }
}

// 设置级联关系
$cascadeManager = new CascadeDeleteManager($db);
$cascadeManager->addRelationship('users', 'posts', 'user_id');

// 测试级联删除（注意：这会删除用户及其所有文章）
$testUser = $db->fetchOne("SELECT id, username FROM users WHERE username LIKE 'newuser%' LIMIT 1");
if ($testUser) {
    echo "准备级联删除用户: {$testUser['username']}\n";
    
    // 查看该用户的文章数
    $postCount = $db->fetchOne("SELECT COUNT(*) as count FROM posts WHERE user_id = ?", [$testUser['id']]);
    echo "该用户有 {$postCount['count']} 篇文章\n";
    
    try {
        $cascadeManager->cascadeDelete('users', $testUser['id']);
        echo "✓ 级联删除成功\n";
    } catch (Exception $e) {
        echo "✗ 级联删除失败: " . $e->getMessage() . "\n";
    }
}

echo "\n--- 批量删除 ---\n";

// 批量删除类
class BatchDeleter {
    private $db;
    private $batchSize;
    
    public function __construct($db, $batchSize = 1000) {
        $this->db = $db;
        $this->batchSize = $batchSize;
    }
    
    public function deleteByIds($table, $ids) {
        if (empty($ids)) {
            return 0;
        }
        
        $totalDeleted = 0;
        $batches = array_chunk($ids, $this->batchSize);
        
        foreach ($batches as $batch) {
            $placeholders = str_repeat('?,', count($batch) - 1) . '?';
            $sql = "DELETE FROM $table WHERE id IN ($placeholders)";
            $deleted = $this->db->execute($sql, $batch);
            $totalDeleted += $deleted;
        }
        
        return $totalDeleted;
    }
    
    public function deleteByCondition($table, $condition, $params = []) {
        // 分批删除以避免锁定太多行
        $totalDeleted = 0;
        
        do {
            $sql = "DELETE FROM $table WHERE $condition LIMIT {$this->batchSize}";
            $deleted = $this->db->execute($sql, $params);
            $totalDeleted += $deleted;
        } while ($deleted > 0);
        
        return $totalDeleted;
    }
}

$batchDeleter = new BatchDeleter($db, 100);

// 创建一些测试数据
for ($i = 1; $i <= 5; $i++) {
    $db->execute(
        "INSERT INTO posts (user_id, title, content, status) VALUES (?, ?, ?, ?)",
        [1, "批量删除测试文章$i", "内容$i", 'draft']
    );
}

// 获取要删除的ID
$posts = $db->fetchAll("SELECT id FROM posts WHERE title LIKE '批量删除测试文章%'");
$ids = array_column($posts, 'id');

if (!empty($ids)) {
    $deleted = $batchDeleter->deleteByIds('posts', $ids);
    echo "批量删除测试文章，删除数量: $deleted\n";
}

// 按条件批量删除
$deleted = $batchDeleter->deleteByCondition('posts', 'status = ?', ['draft']);
echo "按条件批量删除草稿文章，删除数量: $deleted\n";
?>