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
捕捉到,并回滚事务。