预处理语句

什么是预处理语句

预处理语句（Prepared Statements）是数据库操作的一种重要技术，它将SQL语句的编译和执行分开进行。预处理语句先发送SQL模板到数据库服务器进行编译，然后再发送具体的参数值进行执行。

预处理语句的优势

1. 防止SQL注入：参数值不会被当作SQL代码执行
2. 提高性能：相同模板的语句只需编译一次，可多次执行
3. 数据类型安全：自动处理数据类型转换
4. 可读性好：SQL语句更清晰，参数明确

预处理语句的工作原理

第1步：准备SQL模板
   ↓
第2步：数据库编译SQL模板
   ↓
第3步：绑定参数值
   ↓
第4步：执行SQL语句

两种参数绑定方式

1. 位置参数（问号占位符）

<?php
// SQL模板中使用?作为占位符
$sql = "SELECT * FROM users WHERE username = ? AND age > ?";

try {
    // 准备语句
    $stmt = $pdo->prepare($sql);

    // 执行时按顺序传递参数
    $stmt->execute(['zhangsan', 18]);

    // 获取结果
    $user = $stmt->fetch();

} catch (PDOException $e) {
    echo "查询失败: " . $e->getMessage();
}
?>

2. 命名参数（冒号占位符）

<?php
// SQL模板中使用:开头的命名参数
$sql = "SELECT * FROM users
        WHERE username = :username AND age > :min_age
        ORDER BY created_at DESC
        LIMIT :limit";

try {
    // 准备语句
    $stmt = $pdo->prepare($sql);

    // 执行时使用关联数组传递参数
    $params = [
        'username' => 'zhangsan',
        'min_age' => 18,
        'limit' => 10
    ];
    $stmt->execute($params);

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

} catch (PDOException $e) {
    echo "查询失败: " . $e->getMessage();
}
?>

参数绑定方法详解

1. execute()方法绑定（常用）

<?php
// 方法一：通过execute()的参数直接传递
$sql = "INSERT INTO users (username, email, age) VALUES (?, ?, ?)";
$stmt = $pdo->prepare($sql);
$stmt->execute(['user1', 'user1@example.com', 25]);

// 方法二：命名参数使用关联数组
$sql = "INSERT INTO users (username, email, age)
        VALUES (:username, :email, :age)";
$stmt = $pdo->prepare($sql);
$stmt->execute([
    'username' => 'user2',
    'email' => 'user2@example.com',
    'age' => 30
]);
?>

2. bindValue()方法绑定

<?php
$sql = "INSERT INTO users (username, email, age) VALUES (?, ?, ?)";
$stmt = $pdo->prepare($sql);

// 逐个绑定值（1-based索引）
$stmt->bindValue(1, 'user3');
$stmt->bindValue(2, 'user3@example.com');
$stmt->bindValue(3, 28);

// 命名参数
$sql = "INSERT INTO users (username, email, age)
        VALUES (:username, :email, :age)";
$stmt = $pdo->prepare($sql);

$stmt->bindValue(':username', 'user4');
$stmt->bindValue(':email', 'user4@example.com');
$stmt->bindValue(':age', 32);

$stmt->execute();
?>

3. bindParam()方法绑定

<?php
$sql = "INSERT INTO users (username, email, age) VALUES (?, ?, ?)";
$stmt = $pdo->prepare($sql);

// bindParam绑定变量引用
$username = 'user5';
$email = 'user5@example.com';
$age = 22;

$stmt->bindParam(1, $username);
$stmt->bindParam(2, $email);
$stmt->bindParam(3, $age);

// 可以修改变量值，多次执行
$username = 'user6';
$email = 'user6@example.com';
$age = 27;
$stmt->execute();  // 插入user6

$username = 'user7';
$email = 'user7@example.com';
$age = 35;
$stmt->execute();  // 插入user7

// bindParam对命名参数
$sql = "SELECT * FROM users WHERE age > :min_age";
$stmt = $pdo->prepare($sql);

$minAge = 18;
$stmt->bindParam(':min_age', $minAge);

$minAge = 25;
$stmt->execute();  // 查询年龄>25的用户

$minAge = 30;
$stmt->execute();  // 查询年龄>30的用户
?>

数据类型处理

显式指定数据类型

<?php
$sql = "INSERT INTO products (name, price, quantity, created_at)
        VALUES (:name, :price, :quantity, :created_at)";
$stmt = $pdo->prepare($sql);

// 绑定参数并指定数据类型
$stmt->bindValue(':name', 'iPhone 15', PDO::PARAM_STR);
$stmt->bindValue(':price', 999.99, PDO::PARAM_STR);  // DECIMAL作为字符串
$stmt->bindValue(':quantity', 100, PDO::PARAM_INT);
$stmt->bindValue(':created_at', date('Y-m-d H:i:s'), PDO::PARAM_STR);

$stmt->execute();

// PDO参数常量
/*
PDO::PARAM_BOOL      布尔类型
PDO::PARAM_NULL      NULL值
PDO::PARAM_INT       整数
PDO::PARAM_STR       字符串
PDO::PARAM_LOB       大对象（LOB）
*/
?>

NULL值处理

<?php
// 更新用户信息，可能包含NULL值
function updateUser($pdo, $userId, $data) {
    $sql = "UPDATE users SET
            email = :email,
            phone = :phone,
            updated_at = NOW()
            WHERE id = :id";

    $stmt = $pdo->prepare($sql);

    // 绑定参数，处理NULL值
    if ($data['email'] === null) {
        $stmt->bindValue(':email', null, PDO::PARAM_NULL);
    } else {
        $stmt->bindValue(':email', $data['email'], PDO::PARAM_STR);
    }

    if ($data['phone'] === null) {
        $stmt->bindValue(':phone', null, PDO::PARAM_NULL);
    } else {
        $stmt->bindValue(':phone', $data['phone'], PDO::PARAM_STR);
    }

    $stmt->bindValue(':id', $userId, PDO::PARAM_INT);

    return $stmt->execute();
}

// 使用示例
updateUser($pdo, 1, [
    'email' => 'new@example.com',
    'phone' => null  // 将phone设为NULL
]);
?>

IN语句的特殊处理

使用问号占位符

<?php
// 查询指定ID的用户
function getUsersByIds($pdo, $ids) {
    // 创建占位符字符串
    $placeholders = str_repeat('?,', count($ids) - 1) . '?';

    $sql = "SELECT * FROM users WHERE id IN ($placeholders)";
    $stmt = $pdo->prepare($sql);
    $stmt->execute($ids);

    return $stmt->fetchAll();
}

// 使用示例
$userIds = [1, 3, 5, 7, 9];
$users = getUsersByIds($pdo, $userIds);
?>

使用命名参数

<?php
function getUsersByStatus($pdo, $statuses) {
    // 为每个状态创建命名参数
    $params = [];
    $placeholders = [];

    foreach ($statuses as $i => $status) {
        $param = ":status_$i";
        $params[$param] = $status;
        $placeholders[] = $param;
    }

    $sql = "SELECT * FROM users WHERE status IN (" .
           implode(',', $placeholders) . ")";

    $stmt = $pdo->prepare($sql);
    $stmt->execute($params);

    return $stmt->fetchAll();
}

// 使用示例
$statuses = ['active', 'pending'];
$users = getUsersByStatus($pdo, $statuses);
?>

复杂查询示例

动态条件查询

<?php
class UserQuery {
    private $pdo;
    private $conditions = [];
    private $params = [];

    public function __construct($pdo) {
        $this->pdo = $pdo;
    }

    public function whereUsername($username) {
        $this->conditions[] = "username = :username";
        $this->params[':username'] = $username;
        return $this;
    }

    public function whereAgeGt($age) {
        $this->conditions[] = "age > :min_age";
        $this->params[':min_age'] = $age;
        return $this;
    }

    public function whereStatus($status) {
        $this->conditions[] = "status = :status";
        $this->params[':status'] = $status;
        return $this;
    }

    public function orderBy($field, $direction = 'ASC') {
        $this->orderBy = "ORDER BY $field $direction";
        return $this;
    }

    public function limit($limit, $offset = 0) {
        $this->limit = "LIMIT $limit OFFSET $offset";
        return $this;
    }

    public function execute() {
        $sql = "SELECT * FROM users";

        if (!empty($this->conditions)) {
            $sql .= " WHERE " . implode(' AND ', $this->conditions);
        }

        if (isset($this->orderBy)) {
            $sql .= " " . $this->orderBy;
        }

        if (isset($this->limit)) {
            $sql .= " " . $this->limit;
        }

        $stmt = $this->pdo->prepare($sql);
        $stmt->execute($this->params);

        return $stmt->fetchAll();
    }
}

// 使用示例
$query = new UserQuery($pdo);
$users = $query->whereUsername('zhangsan')
               ->whereAgeGt(18)
               ->whereStatus('active')
               ->orderBy('created_at', 'DESC')
               ->limit(10)
               ->execute();
?>

批量操作优化

批量插入

<?php
// 方法一：使用事务批量插入
function batchInsertUsers($pdo, $users) {
    $sql = "INSERT INTO users (username, email, age, status)
            VALUES (?, ?, ?, ?)";
    $stmt = $pdo->prepare($sql);

    $pdo->beginTransaction();

    try {
        foreach ($users as $user) {
            $stmt->execute([
                $user['username'],
                $user['email'],
                $user['age'],
                $user['status']
            ]);
        }

        $pdo->commit();
        return true;

    } catch (Exception $e) {
        $pdo->rollback();
        return false;
    }
}

// 方法二：使用批量INSERT语法
function batchInsertUsersOptimized($pdo, $users) {
    $values = [];
    $params = [];

    foreach ($users as $i => $user) {
        $values[] = "(?, ?, ?, ?)";
        array_push($params,
            $user['username'],
            $user['email'],
            $user['age'],
            $user['status']
        );
    }

    $sql = "INSERT INTO users (username, email, age, status)
            VALUES " . implode(',', $values);

    $stmt = $pdo->prepare($sql);
    return $stmt->execute($params);
}

// 使用示例
$newUsers = [
    ['username' => 'user1', 'email' => 'user1@test.com', 'age' => 25, 'status' => 'active'],
    ['username' => 'user2', 'email' => 'user2@test.com', 'age' => 30, 'status' => 'active'],
    ['username' => 'user3', 'email' => 'user3@test.com', 'age' => 28, 'status' => 'pending']
];

batchInsertUsers($pdo, $newUsers);
?>

调试预处理语句

获取调试信息

<?php
$sql = "SELECT * FROM users WHERE username = :username AND age > :min_age";
$stmt = $pdo->prepare($sql);

// 获取原始SQL
echo "SQL模板: " . $stmt->queryString . "\n";

// 执行查询
$stmt->execute(['username' => 'test', 'min_age' => 18]);

// 获取行数
echo "影响行数: " . $stmt->rowCount() . "\n";

// 获取列数
echo "列数: " . $stmt->columnCount() . "\n";

// 模拟SQL输出（用于调试）
function debugQuery($sql, $params) {
    foreach ($params as $key => $value) {
        if (is_string($value)) {
            $value = "'" . addslashes($value) . "'";
        }
        $sql = str_replace($key, $value, $sql);
    }
    return $sql;
}

echo "执行的SQL: " . debugQuery($sql, ['username' => 'test', 'min_age' => 18]);
?>

安全最佳实践

1. 始终使用预处理语句

<?php
// ❌ 错误：直接拼接SQL（不安全）
function unsafeSearch($pdo, $keyword) {
    $sql = "SELECT * FROM products WHERE name LIKE '%$keyword%'";
    return $pdo->query($sql)->fetchAll();
}

// ✅ 正确：使用预处理语句（安全）
function safeSearch($pdo, $keyword) {
    $sql = "SELECT * FROM products WHERE name LIKE :keyword";
    $stmt = $pdo->prepare($sql);
    $stmt->execute(['keyword' => "%$keyword%"]);
    return $stmt->fetchAll();
}
?>

2. 验证输入数据

<?php
function insertUser($pdo, $userData) {
    // 验证必填字段
    $required = ['username', 'email', 'age'];
    foreach ($required as $field) {
        if (!isset($userData[$field]) || empty($userData[$field])) {
            throw new Exception("字段 $field 是必填的");
        }
    }

    // 验证数据类型
    if (!filter_var($userData['email'], FILTER_VALIDATE_EMAIL)) {
        throw new Exception("邮箱格式不正确");
    }

    if (!is_int($userData['age']) || $userData['age'] < 0) {
        throw new Exception("年龄必须是正整数");
    }

    $sql = "INSERT INTO users (username, email, age)
            VALUES (:username, :email, :age)";
    $stmt = $pdo->prepare($sql);

    return $stmt->execute([
        'username' => $userData['username'],
        'email' => $userData['email'],
        'age' => $userData['age']
    ]);
}
?>

性能优化建议

1. 重用预处理语句

<?php
// 预编译一次，多次执行
function updateUserStatus($pdo, $userIds, $newStatus) {
    $sql = "UPDATE users SET status = :status WHERE id = :id";
    $stmt = $pdo->prepare($sql);

    $stmt->bindValue(':status', $newStatus, PDO::PARAM_STR);

    foreach ($userIds as $userId) {
        $stmt->bindValue(':id', $userId, PDO::PARAM_INT);
        $stmt->execute();
    }

    return $stmt->rowCount();
}
?>

2. 使用合适的数据类型

<?php
// ✅ 使用正确的数据类型
$stmt->bindValue(':count', $count, PDO::PARAM_INT);
$stmt->bindValue(':price', $price, PDO::PARAM_STR);  // DECIMAL
$stmt->bindValue(':active', $active, PDO::PARAM_BOOL);

// ❌ 避免类型转换错误
$stmt->bindValue(':count', (string)$count);  // 不推荐
?>

通过本节的学习，你应该掌握了预处理语句的各种用法和安全编程技巧。下一节我们将学习事务处理，进一步掌握数据库的高级操作。