首页 > 编程笔记 > PHP笔记

PHP PDO预处理语句详解

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

预处理语句的机制
图：预处理语句的机制

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

了解 PDOStatement 对象

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

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

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

方法名  描述
bindColumn() 用来匹配列名和一个指定的变量名，这样每次获取各行记录时，会自动将相应的列值赋给该变量
bindParam() 将参数绑定到相应的查询占位符上
bindValue() 将值绑定到一个对应的参数中
closeCursor() 关闭游标，使该声明再次被执行
columnCount() 在结果集中返回列的数目
errorCode() 获取错误码
errorInfo() 获取错误的信息
execute() 负责执行一个准备好的预处理查询
fetch() 返回结果集的下一行，当到达结果集末尾时返回 false
fetchAll() 获取结果集中的所有行，并赋给返回的数组
fetchColumn() 返回结果集中下一行某个列的值
fetchObject() 获取下一行记录并将其作为一个对象返回
getAttribute() 获取一个声明属性
getColumnMeta() 在结果集中返回某一列的属性信息
nextRowset() 检索下一行集（结果集）
rowCount()  返回执行 DQL 语句后查询结果的记录行数，或返回执行 DML 语句后受影响的记录行总数
setAttribute() 为一个预处理语句设置属性
setFetchMode() 设置获取结果集合的类型

准备语句

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

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

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

1) 命名参数

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

$dbh->prepare("INSERT INTO user(name, address, phone) VALUES(:name, :address, :phone)");

2) 问号参数

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

$dbh->prepare("INSERT INTO user(name, address, phone) VALUES(?, ?, ?)");

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

绑定参数

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

PDOStatement::bindParam(mixed $parameter, mixed &$variable[, int $data_type = PDO::PARAM_STR[, int $length[, mixed $driver_options]]])

参数说明如下：

【示例】将前面使用两种占位符语法准备的 SQL 查询，使用 bindParam() 方法分别绑定上对应的参数。 
<?php
    // 省略部分代码
    // 命名参数
    $query   = "INSERT INTO user (name, address, phone) VALUES (:name, :address, :phone)";
    $stmt    = $dbh -> prepare($query);
   
    $name    = '新宝库';
    $address = 'https://www.xinbaoku.com/php/';
    $phone   = '15012345678';

    $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 = 'https://www.xinbaoku.com/php/';
    $phone   = '15012345678';

    $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   = '15012345678';

    $stmt -> bindParam(':name', $name);
    $stmt -> bindParam(':address', $address);
    $stmt -> bindParam(':phone', $phone);
    $stmt -> execute();

    $name    = '李四';
    $address = '中国';
    $phone   = '15087654321';

    $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'=>'15087654321'
    ];
    $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);
    }
?>

所有教程

优秀文章