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

学习笔记 2018/08/02 PHP ,MYSQL

本文以基础讲解常用的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://stayed.cn/item/103

转载请注明出处。

本站部分内容来源于网络,如侵犯到您的权益,请 联系我

我的博客

人生若只如初见,何事秋风悲画扇。