PHP PDO预处理语句详解

在生成网页时，许多 PHP 脚本通常都会执行除参数以外，其他部分完全相同的查询语句，针对这种重复执行一个查询，每次迭代使用不同的参数情况，PDO 提供了一种名为预处理语句（prepared statement）的机制，如下图所示。 

图：预处理语句的机制

使用预处理机制可以将整个 SQL 命令向数据库服务器发送一次，以后当参数发生变化时，数据库服务器只需对命令的结构做一次分析就够了，即编译一次，可以多次执行。预处理机制会在服务器上缓存查询的语句和执行过程，同时只在服务器和客户端之间传输有变化的值，以此来消除一些额外的开销。这不仅大大减少了需要传输的数据量，还提高了命令的处理效率。可以有效防止 SQL 注入，在执行单个查询时快于直接使用 query()、exec() 的方法，速度快而且安全，非常推荐使用。

了解 PDOStatement 对象

PDO 对预处理语句的支持需要使用 PDOStatement 类对象，但该类的对象并不是通过 new 关键字实例化出来的，而是通过执行 PDO 对象中的 prepare() 方法，在数据库服务器中准备好一个预处理的 SQL 语句后直接返回的。

与使用 query() 方法返回的 PDOStatement 类对象不同，query() 返回的是一个结果集对象，而使用 prepare() 方法返回的则是一个查询对象，能够通过这个对象定义和执行 SQL 命令。

PDOStatement 类中的全部成员方法如下表所示。

准备语句

当某个 SQL 语句需要重复执行，且每次执行仅仅是使用的参数不同时，使用预处理语句的运行效率最高。使用预处理语句，首先需要在数据库服务器中先准备好一个 SQL 语句，但并不需要马上执行它。

SQL 语句中，对于在执行时需要变化的一些值，可以使用占位符号来取代，然后将这个编辑好的 SQL 语句放到数据库服务器的缓存区等待处理，然后再去单独赋予占位符号具体的值，再通知这个准备好的预处理语句执行即可。

在 PDO 中有两种使用占位符的语法，分别是“命名参数”和“问号参数”，具体使用哪一种语法根据看个人的喜好随意选择即可。

1) 命名参数

命名参数法就是自定义一个字符串作为参数的名称，这个名称需要使用冒号（:）开始，参数的命名要有一定意义，最好和对应的字段名称相同。使用命名参数作为占位符的 INSERT 查询语句如下所示：

2) 问号参数

顾名思义就是使用问号（?）作为占位符，另外问号出现的位置一定要和字段的位置顺序对应。使用问号参数作为占位符的 INSERT 查询语句如下所示：

注意：不管是使用哪一种参数作为占位符构成的 SQL 语句，哪怕是语句中没有用到占位符，都需要使用 PDO 对象中的 prepare() 方法去准备这个将要用于迭代执行的语句，并返回 PDOStatement 类对象。

绑定参数

当 SQL 语句通过 PDO 对象中的 prepare() 方法，在数据库服务器端准备好之后，如果 SQL 语句使用了占位符，就需要在每次执行时绑定具体的参数。可以通过 PDOStatement 对象中的 bindParam() 方法，把参数变量绑定到准备好的占位符上。bindParam() 方法的语法格式如下所示：

参数说明如下：

• $parameter：参数标识符。对于使用命名占位符的预处理语句，是类似 :name 形式的参数名。对于使用问号占位符的预处理语句，应是以 1 开始索引的参数位置；
• $variable：绑定到 SQL 语句参数的 PHP 变量名，因为该参数是按引用传递的，所以只能提供变量作为参数，不能直接提供数值；
• $data_type：可选参数，表示使用 PDO::PARAM_* 常量明确地指定参数的类型。可以为以下值：
  • PDO:PARAM_BOOL：代表 boolean 数据类型；
  • PDO:PARAM_NULL：代表 SQL 中 NULL 类型；
  • PDO:PARAM_INT：代表 SQL 中 INTEGER 数据类型；
  • PDO:PARAM_STR：代表 SQL 中 CHAR、VARCHAR 和其他字符串数据类型；
  • PDO:PARAM_LOB：代表 SQL 中大对象数据类型。
• $length：可选参数，用来指定数据类型的长度；
• $driver_options：可选参数，通过该参数提供任何数据库驱动程序特定的选项。

【示例】将前面使用两种占位符语法准备的 SQL 查询，使用 bindParam() 方法分别绑定上对应的参数。

<?php
    // 省略部分代码
    // 命名参数
    $query   = "INSERT INTO user (name, address, phone) VALUES (:name, :address, :phone)";
    $stmt    = $dbh -> prepare($query);
   
    $name    = '城东书院';
    $address = 'http://www.cdsy.xyz/computer/programme/PHP/';
    $phone   = '13711111111';
    $stmt -> bindParam(':name', $name);
    $stmt -> bindParam(':address', $address);
    $stmt -> bindParam(':phone', $phone);
?>
<?php
    // 省略部分代码
    // 问号参数
    $query   = "INSERT INTO user (name, address, phone) VALUES (?, ?, ?)";
    $stmt    = $dbh -> prepare($query);
   
    $name    = '城东书院';
    $address = 'http://www.cdsy.xyz/computer/programme/PHP/';
    $phone   = '13711111111';
    $stmt -> bindParam('1', $name, PDO::PARAM_STR);
    $stmt -> bindParam('2', $address, PDO::PARAM_STR);
    $stmt -> bindParam('3', $phone, PDO::PARAM_STR, 11);
?>

执行准备好的查询

当准备好查询并绑定了相应的参数后，就可以通过调用 PDOStatement 类对象中的 execute() 方法，反复执行在数据库缓存区准备好的语句了。

【示例】向 user 表中，使用预处理方式连续执行同一个 INSERT 语句，通过改变不同的参数添加两条记录。如下所示：

<?php
    try{
        $dbh = new PDO('mysql:dbname=testdb;host=localhost', 'root', 'root');
    }catch(PDOException $e){
        echo '数据库连接失败：'.$e->getMessage();
        exit();
    }
    $query   = "INSERT INTO user (name, address, phone) VALUES (:name, :address, :phone)";
    $stmt    = $dbh -> prepare($query);
   
    $name    = '张三';
    $address = '中国';
    $phone   = '13622222222';
    $stmt -> bindParam(':name', $name);
    $stmt -> bindParam(':address', $address);
    $stmt -> bindParam(':phone', $phone);
    $stmt -> execute();
    $name    = '李四';
    $address = '中国';
    $phone   = '13622222222';
    $stmt -> bindParam(':name', $name);
    $stmt -> bindParam(':address', $address);
    $stmt -> bindParam(':phone', $phone);
    $stmt -> execute();
?>

另外，execute() 方法还可以接收一个数组作为参数，该参数是由 SQL 语句中的命名参数占位符组成的数组，这是第二种为预处理语句中参数赋值的方式。使用这种方式可以省去对 $stmt->bindParam() 的调用。示例代码如下所示：

<?php
    try{
        $dbh = new PDO('mysql:dbname=testdb;host=localhost', 'root', 'root');
    }catch(PDOException $e){
        echo '数据库连接失败：'.$e->getMessage();
        exit();
    }
    $query   = "INSERT INTO user (name, address, phone) VALUES (:name, :address, :phone)";
    $stmt    = $dbh -> prepare($query);
    $stmt -> execute(array(':name'=>'张三', ':address'=>'中国', ':phone'=>'15012345678'));
    $arr = [
        ':name'=>'李四',
        ':address'=>'中国',
        ':phone'=>'13533333333'
    ];
    $stmt -> execute($arr);
?>

获取数据

PDO 的获取查询结果的方法我们前面已经详细的介绍过了，这里就不再重复介绍了。详细信息大家可以翻阅《使用PDO获取查询结果》一节。

不管是使用 PDO 对象中的 query() 方法，还是使用 prepare() 和 execute() 等方法结合的预处理语句，执行 SELECT 查询语句都会得到相同的结果集对象 PDOStatement。都需要通过 PDOStatement 类对象中的方法将数据遍历出来。

【示例】下面就分别使用 fetch() 和 fetchAll() 两种方式来获取结果集中的数据

<?php
    try{
        $dbh = new PDO('mysql:dbname=testdb;host=localhost', 'root', 'root');
    }catch(PDOException $e){
        echo '数据库连接失败：'.$e->getMessage();
        exit();
    }
    $query = "SELECT name,address,phone FROM user";
    $stmt = $dbh -> prepare($query);
    $stmt -> execute();
    while(list($name, $address, $phone) = $stmt -> fetch(PDO::FETCH_NUM)){
        echo $name.$address.$phone.'<br>';
    }
?>
<?php
    try{
        $dbh = new PDO('mysql:dbname=testdb;host=localhost', 'root', 'root');
    }catch(PDOException $e){
        echo '数据库连接失败：'.$e->getMessage();
        exit();
    }
    $query = "SELECT name,address,phone FROM user";
    $stmt = $dbh -> prepare($query);
    $stmt -> execute();
    $data = $stmt -> fetchAll(PDO::FETCH_ASSOC);
    echo '<pre>';
    foreach ($data as $key => $value) {
        var_dump($value);
    }
?>