PHP操作PDO、预处理以及事务

àì夳堔傛蜴生んèń 2022-04-11 12:49 351阅读 0赞

PDO为PHP访问各类数据库定义了一个轻量级一致性的接口，无论什么数据库，都可以通过一致的方法执行查询和获取数据，而不用考虑不同数据库之间的差异，大大简化了数据库操作。使用PDO可以支持mysql、postgresql、oracle、mssql等多种数据库。

本文以基础讲解常用的PHP以PDO方式操作MySQL，包括常用的CURD语句执行，以及预处理语句和事务的应用。虽然很多朋友使用开发框架封装好了数据库操作层，或者使用ORM等不直接接触SQL语句，但是在一些小项目中可能会用到原生的数据库操作，所以虽然是基础但是很有用。

准备

我们准备一张mysql数据表mycomments，这是一张常见的评论表。

CREATE TABLE `mycomments` (
      `id` int(11) NOT NULL,
      `post_id` int(10) NOT NULL DEFAULT '0',
      `content` varchar(255) NOT NULL,
      `user_id` int(10) NOT NULL,
      `created_at` datetime NOT NULL
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8;

连接

首先创建PDO对象，建立与数据库服务器的连接。

$dbhost = '127.0.0.1'; //数据库服务器
    $dbport = 3306; //端口
    $dbname = 'demo'; //数据库名称
    $dbuser = 'root'; //用户名
    $dbpass = 'xxxx'; //密码
    
    // 连接
    try {
        $db = new PDO('mysql:host='.$dbhost.';port='.$dbport.';dbname='.$dbname, $dbuser, $dbpass);
        $db->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION); //设置错误模式
        $db->query('SET NAMES utf8;');
    } catch(PDOException $e) {
        echo '{"result":"failed", "msg":"连接数据库失败！"}';
        exit;
    }

`PDO::setAttribute()`用于设置属性，如上面的代码中就设置了使用异常模式处理错误。

查询

如果我们不使用预处理语句，可以直接使用`query()`和`exec()`方法执行sql语句。

//查询
    $sql = "SELECT id,content,user_id FROM `mycomments` WHERE post_id=2 ORDER BY id DESC";
    $db->query($sql);
    //更新
    $sql = "UPDATE `mycomments` SET content='second...' WHERE id=5";
    $db->exec($sql);

而实际开发中我们最常用的是预处理语句，简单的说预处理语句预先将sql命令分析一次，可以多次执行，提高了处理效率，而且能有效防止SQL注入。在执行单个查询时快于直接使用`query()`或`exec()`的方法，速度快且安全，所以强烈推荐使用预处理语句。

使用预处理语句处理时配套的方法是`prepare()`和`execute()`。

我们用预处理语句来查询符合条件的数据记录：

$sql = "SELECT id,content,user_id FROM `mycomments` WHERE post_id=? ORDER BY id DESC";
    $stmt = $db->prepare($sql);
    $post_id = 2;
    $stmt->bindParam(1, $post_id, PDO::PARAM_INT);
    $stmt->execute();
    $row = $stmt->fetch();

我们在sql语句中使用问号(?)参数作为占位符，使用`bindParam()`可以设置绑定参数值。

不过，如果有很多参数需要传递，我们最常用的是这样写：

$sql = "SELECT id,content,user_id FROM `mycomments` WHERE post_id=:post_id ORDER BY id DESC";
    $stmt = $db->prepare($sql);
    $stmt->execute([
        ':post_id' => 2
    ]);
    $row = $stmt->fetch();

在`execute()`方法中加入参数占位符数组，不使用?占位符可能更直观点。

`fetch()`返回查询结果中的一行数据，数据以数组形式返回，该方法可以带参数，其中参数默认为`PDO::FETCH_BOTH`，即返回一个索引为结果集列名和以0开始的列号的数组，而常用的参数`PDO::FETCH_ASSOC`则返回一个索引为结果集列名的数组。

`fetchAll()`可以获取结果集中的所有行，并赋给返回的二维数组。和`fetch()`一样也可以带参数。

如查询表中用户id为2的所有数据，可能会有多行结果：

$sql = "SELECT id,content FROM `mycomments` WHERE user_id=:user_id ORDER BY id DESC";
    $stmt = $db->prepare($sql);
    $stmt->execute([
        ':user_id' => 2
    ]);
    $rows = $stmt->fetchAll(PDO::FETCH_ASSOC);

打印$row结果看下，是不是一个二维数组？

插入

最常用的插入数据表的写法，如果有自增长id（一般必须有），使用`lastInsertId()`可以获取到插入成功后的id。

$sql = "INSERT INTO `mycomments` (post_id,content,user_id,created_at) VALUES (:post_id, :content, :user_id, :created_at)";
    $stmt = $db->prepare($sql);
    $stmt->execute([
        ':post_id' => 2,
        ':content' => 'Hello，啦啦啦',
        ':user_id' => 21,
        ':created_at' => date('Y-m-d H:i:s'),
    ]);
    $lastid = $db->lastInsertId(); //返回插入成功后的id

更新

使用预处理更新数据，`rowCount()`返回影响行数，大于0即表示执行成功的记录数。

$sql = "UPDATE `mycomments` SET content=:content WHERE id=:id";
    $stmt = $db->prepare($sql);
    $stmt->execute([
        ':content' => '我的天啊',
        ':id' => 6
    ]);
    echo $stmt->rowCount();//1, 影响行数

删除

对于只有一个参数需要绑定的，可以使用问号?占位符。删除后同样使用`rowCount()`返回影响行数，大于0表示执行成功。

$sql = "DELETE FROM `mycomments`  WHERE id=?";
    $stmt = $db->prepare($sql);
    $stmt->execute([6]);
    echo $stmt->rowCount();//1

事务

事务是确保数据库一致的机制，是一个或一系列的查询，作为一个单元的一组有序的数据库操作。如果组中的所有SQL语句都操作成功，则认为事务成功，事务则被提交。如果在事务的组中只有一个环节操作失败，事务也不成功，则整个事务将被回滚，该事务中所有操作都被取消。事务在开发中也经常用到，因为很多业务过程都包括多个步骤，如果任何一个步骤失败，则所有步骤都不应发生。

值得注意的是，如果要用到事务处理功能，你的MySQL应该使用InnoDB引擎或者其他支持事务的引擎，切不可以使用MyISAM引擎。

来看PDO事务处理实例：

try {
        $db->beginTransaction(); //启动事务
        $sql1 = "INSERT INTO `mycomments` VALUES (NULL,'1','wahaha','10','2018-07-25 12:12:01')";
        $sql2 = "UPDATE `mycomments` SET content='second...' WHERE sid=2";
        $sql3 = "INSERT INTO `mycomments` VALUES (NULL,'3','wahaha','30','2018-07-25 12:12:03')";
    
        $db->exec($sql1);
        $db->exec($sql2);
        $db->exec($sql3);
    
        $db->commit(); //提交事务
    } catch (Exception $e) {
        $db->rollBack(); //回滚事务
    }

上述代码中首先是启动一个事务，然后依次执行三条sql，然后提交事务。细心的同学可能会发现，在第2条sql中查询条件sid=2有误，因为我们在前面创建mycomments表的时候没有sid这个字段，所以在执行到第2条sql时就会出错，这个时候会抛出异常，使用`try{}cache(){}`语句即可捕获异常，于是就执行了回滚事务`rollBack()`，而并没有提交事务。换句话说就是上面的代码虽然第一条sql执行完了，但是最终执行不成功，数据库没有任何写入和更新。

后记

以上是笔者在使用PHP操作PDO开发时的总结，由于小项目中经常使用所以整理成文，分享给大家。

接下来我们会有文章讲解使用DB库简化PDO操作以及使用Eloquent ORM模型进行数据库操作，在大型项目中让数据库操作更轻松更规范，敬请关注。

原文链接：https://www.helloweba.net/php/573.html