pdo-mysql

pdo-mysql

PHP连接数据库推荐使用PDO，PDO扩展为PHP访问数据库定义了一个轻量级接口。我们可以通过实现PDO接口的每个数据库驱动来访问数据库服务。

访问mysql数据库服务，我们使用PDO_MYSQL驱动

1.PDO实现CRUD

在192.168.1.13:3306的mysql实例上创建数据库roach，创建roach用户并授权，在roach库中创建表t_user，sql如下

CREATE TABLE `t_user` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT COMMENT '用户id',
  `user_name` varchar(32) CHARACTER SET utf8 DEFAULT 'NULL' COMMENT '登录名',
  `true_name` varchar(32) CHARACTER SET utf8 DEFAULT '' COMMENT '真实姓名',
  `password` char(32) CHARACTER SET utf8 DEFAULT '' COMMENT '密码',
  `is_on` tinyint(3) unsigned DEFAULT '0' COMMENT '是否启用(0禁用1启用)',
  `last_login_ip` bigint(20) unsigned DEFAULT '0' COMMENT '上次登录ip',
  `add_time` int(10) unsigned DEFAULT '0' COMMENT '添加时间',
  `update_time` int(10) unsigned DEFAULT '0' COMMENT '修改时间',
  `version` int(10) unsigned DEFAULT '0' COMMENT '乐观锁版本',
  PRIMARY KEY (`id`),
  UNIQUE KEY `user_name` (`user_name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 ROW_FORMAT=COMPACT COMMENT='用户表';

使用PDO操作t_user表

<?php
//\PDO构造函数有四个参数，第一个参数是dsn，第二个参数是用户名，第三个参数是密码，第四个参数是连接选项
//mysql数据库服务的dsn示例如下
$pdo = new \PDO('mysql:host=192.168.1.13;port=3306;dbname=roach;charset=utf8', 'roach', 'roach', [\PDO::ATTR_ERRMODE => \PDO::ERRMODE_EXCEPTION]);

//1.insert
/**
 * @var \PDOStatement $stmt
 */
$stmt = $pdo->query("INSERT INTO `t_user`(`user_name`,`true_name`,`add_time`) VALUES('".uniqid('u')."','".uniqid('t')."',".time().")");

$userId = $pdo->lastInsertId();
//输出受影响行数和lastInsertId
echo 'insert受影响行数:'.$stmt->rowCount().';插入的用户id:'.$userId.PHP_EOL;

//2.update
$stmt = $pdo->query('UPDATE `t_user` SET `user_name`=\'pdo-mysql\' WHERE id='.$userId);
echo 'update受影响行数:'.$stmt->rowCount().PHP_EOL;

//3.select
$stmt = $pdo->query('SELECT * FROM `t_user` WHERE `id`='.$userId);
echo 'select查询结果:'.json_encode($stmt->fetchAll(\PDO::FETCH_ASSOC), JSON_UNESCAPED_UNICODE).PHP_EOL;

//4.delete
$stmt = $pdo->query('DELETE FROM `t_user` WHERE id='.$userId);
echo 'delete受影响行数:'.$stmt->rowCount().PHP_EOL;

以上例程输出

insert受影响行数:1;插入的用户id:1
update受影响行数:1
select查询结果:[{"id":"1","user_name":"pdo-mysql","true_name":"t5f2d341bc4b43","password":"","is_on":"0","last_login_ip":"add_time":"0000-00-00 00:00:00","update_time":"0","version":"0"}]
delete受影响行数:1

2.防sql注入

以上例程我们可以看到，并未做sql注入安全防范处理，使用PDO操作mysql数据库一般使用参数绑定来防止sql注入，参数绑定有以下两种方式

2.1 ?占位符绑定

1例程中的CRUD代码通过?参数绑定修改后的代码

<?php
//\PDO构造函数有四个参数，第一个参数是dsn，第二个参数是用户名，第三个参数是密码，第四个参数是连接选项
//mysql数据库服务的dsn示例如下
$pdo = new \PDO('mysql:host=192.168.1.13;port=3306;dbname=roach;charset=utf8', 'roach', 'roach', [\PDO::ATTR_ERRMODE => \PDO::ERRMODE_EXCEPTION]);

//1.insert
/**
 * @var \PDOStatement $stmt
 */
//$stmt = $pdo->query("INSERT INTO `t_user`(`user_name`,`true_name`,`add_time`) VALUES('".uniqid('u')."','".uniqid('t')."',".time().")");

$stmt = $pdo->prepare("INSERT INTO `t_user`(`user_name`,`true_name`,`add_time`) VALUES(?, ?, ?)");

$stmt->bindValue(1, uniqid('u'), \PDO::PARAM_STR);
$stmt->bindValue(2, uniqid('t'), \PDO::PARAM_STR);
$stmt->bindValue(3, time(), \PDO::PARAM_INT);
$stmt->execute();

//以上四行可以简写成
//$stmt->execute([uniqid('u'), uniqid('t'), time()]);

$userId = $pdo->lastInsertId();
//输出受影响行数和lastInsertId
echo 'insert受影响行数:'.$stmt->rowCount().';插入的用户id:'.$userId.PHP_EOL;

//2.update
//$stmt = $pdo->query('UPDATE `t_user` SET `user_name`=\'pdo-mysql\' WHERE id='.$userId);
$stmt = $pdo->prepare('UPDATE `t_user` SET `user_name`=? WHERE id=?');

$stmt->bindValue(1, 'pdo-msyql');
$stmt->bindValue(2, $userId, \PDO::PARAM_INT);
$stmt->execute();

//以上三句可以简写成
//$stmt->execute(['pdo-mysql', $userId]);

echo 'update受影响行数:'.$stmt->rowCount().PHP_EOL;

//3.select
//$stmt = $pdo->query('SELECT * FROM `t_user` WHERE `id`='.$userId);
$stmt = $pdo->prepare('SELECT * FROM `t_user` WHERE `id`=?');

$stmt->bindValue(1, $userId, \PDO::PARAM_INT);
$stmt->execute();

//以上两句可以简写成
//$stmt->execute([$userId]);

echo 'select查询结果:'.json_encode($stmt->fetchAll(\PDO::FETCH_ASSOC), JSON_UNESCAPED_UNICODE).PHP_EOL;

//4.delete
//$stmt = $pdo->query('DELETE FROM `t_user` WHERE id='.$userId);

$stmt = $pdo->prepare('DELETE FROM `t_user` WHERE id=?');

$stmt->bindValue(1, $userId, \PDO::PARAM_INT);
$stmt->execute();

//以上两句可以简写成
//$stmt->execute([$userId]);

echo 'delete受影响行数:'.$stmt->rowCount().PHP_EOL;

例程的执行结果是一致的

2.2 自定义占位符绑定

参数绑定也可以自定义占位符，如一下例程

<?php
//\PDO构造函数有四个参数，第一个参数是dsn，第二个参数是用户名，第三个参数是密码，第四个参数是连接选项
//mysql数据库服务的dsn示例如下
$pdo = new \PDO('mysql:host=192.168.1.13;port=3306;dbname=roach;charset=utf8', 'roach', 'roach', [\PDO::ATTR_ERRMODE => \PDO::ERRMODE_EXCEPTION]);
                                                                                               
//1.insert
/**
 * @var \PDOStatement $stmt
 */
//$stmt = $pdo->query("INSERT INTO `t_user`(`user_name`,`true_name`,`add_time`) VALUES('".uniqid('u')."','".uniqid('t')."',".time().")");
$stmt = $pdo->prepare("INSERT INTO `t_user`(`user_name`,`true_name`,`add_time`) VALUES(:user_name, :true_name, :add_time)");

$stmt->bindValue(':user_name', uniqid('u'), \PDO::PARAM_STR);
$stmt->bindValue(':true_name', uniqid('t'), \PDO::PARAM_STR);
$stmt->bindValue(':add_time', time(), \PDO::PARAM_INT);
$stmt->execute();

$userId = $pdo->lastInsertId();
//输出受影响行数和lastInsertId
echo 'insert受影响行数:'.$stmt->rowCount().';插入的用户id:'.$userId.PHP_EOL;

//2.update
//$stmt = $pdo->query('UPDATE `t_user` SET `user_name`=\'pdo-mysql\' WHERE id='.$userId);
$stmt = $pdo->prepare('UPDATE `t_user` SET `user_name`=:user_name WHERE id=:id');

$stmt->bindValue(':user_name', 'pdo-msyql');
$stmt->bindValue(':id', $userId, \PDO::PARAM_INT);
$stmt->execute();

echo 'update受影响行数:'.$stmt->rowCount().PHP_EOL;

//3.select
//$stmt = $pdo->query('SELECT * FROM `t_user` WHERE `id`='.$userId);
$stmt = $pdo->prepare('SELECT * FROM `t_user` WHERE `id`=:id');

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

echo 'select查询结果:'.json_encode($stmt->fetchAll(\PDO::FETCH_ASSOC), JSON_UNESCAPED_UNICODE).PHP_EOL;

//4.delete
//$stmt = $pdo->query('DELETE FROM `t_user` WHERE id='.$userId);
$stmt = $pdo->prepare('DELETE FROM `t_user` WHERE id=:id');

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

echo 'delete受影响行数:'.$stmt->rowCount().PHP_EOL;

例程执行结果也是一致的

3.事务

以下例程演示用户id只能为奇数，否则回滚事务的示例

<?php
//\PDO构造函数有四个参数，第一个参数是dsn，第二个参数是用户名，第三个参数是密码，第四个参数是连接选项
//mysql数据库服务的dsn示例如下
$pdo = new \PDO('mysql:host=192.168.1.13;port=3306;dbname=roach;charset=utf8', 'roach', 'roach');

//开启事务
$pdo->beginTransaction();

try {
    $stmt = $pdo->prepare("INSERT INTO `t_user`(`user_name`,`true_name`,`add_time`) VALUES(:user_name, :true_name, :add_time)");
    $stmt->bindValue(':user_name', uniqid('u'), \PDO::PARAM_STR);
    $stmt->bindValue(':true_name', uniqid('t'), \PDO::PARAM_STR);
    $stmt->bindValue(':add_time', time(), \PDO::PARAM_INT);
    $stmt->execute();
    $userId = $pdo->lastInsertId();
    //仅用于演示
    if($userId % 2 === 0) {
        throw new \Exception('用户id不能为偶数');
    }

    //update
    $stmt = $pdo->prepare('UPDATE `t_user` SET `user_name`=:user_name WHERE id=:id');
    $stmt->bindValue(':user_name', 'pdo-msyql'.uniqid());
    $stmt->bindValue(':id', $userId, \PDO::PARAM_INT);
    $stmt->execute();

    //select
    $stmt = $pdo->prepare('SELECT * FROM `t_user` WHERE `id`=:id');
    $stmt->bindValue(':id', $userId, \PDO::PARAM_INT);
    $stmt->execute();

    //提交事务
    $pdo->commit();

    echo '刚刚插入并修改的记录为:'.json_encode($stmt->fetchAll(\PDO::FETCH_ASSOC), JSON_UNESCAPED_UNICODE).PHP_EOL;
}catch (\Exception $exception) {
    $pdo->rollBack();
    exit($exception->getMessage());
}

• 注意：此处的try...catch是必须的，因为\PDO::ATTR_ERRMODE设置为\PDO::ERRMODE_EXCEPTION，当出现异常时会被catch捕捉到，并回滚事务。

学习更多内容: https://404.360tryst.com

我的视频课程: https://edu.csdn.net/course/detail/9933