欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页  >  后端开发

使用PDO操作数据库有什么好处

程序员文章站 2022-05-04 18:25:24
...

使用PDO操作数据库的好处:1、PDO真正的以底层实现的统一接口操作数据库;2、PDO支持更高级的DB特性操作;3、PDO是PHP官方的PECL库,兼容性和稳定性要高于MySQL扩展;4、PDO可以防止SQL注入,确保数据库更加安全。

使用PDO操作数据库有什么好处

PDO一是PHP数据对象(PHP Data Object)的缩写。

并不能使用PDO扩展本身执行任何数据库操作,必须使用一个database-specific PDO driver(针对特定数据库的PDO驱动)访问数据库服务器。

PDO并不提供数据库抽象,它并不会重写SQL或提供数据库本身缺失的功能,如果你需要这种功能,你需要使用一个更加成熟的抽象层。

PDO需要PHP5核心OO特性的支持,所以它无法运行于之前的PHP版本。

PDO有非常多的操作却是MySQL扩展库所不具备的:

1、PDO真正的以底层实现的统一接口数库操作接口,不管后端使用的是何种数据库,如果代码封装好了以后,应用层调用基本上差不多的,当后端数据库更换了以后,应用层代码基本不用修改.

2、PDO支持更高级的DB特性操作,如:存储过程的调度等,mysql原生库是不支持的.

3、PDO是PHP官方的PECL库,兼容性稳定性必然要高于MySQL Extension,可以直接使用 pecl upgrade pdo 命令升级.

4、PDO可以防止SQL注入,确保数据库更加安全

PDO有预处理语句,使用语句预处理将帮助你免于SQL注入攻击。

一条预处理语句是一条预编译的 SQL 语句,它可以使用多次,每次只需将数据传至服务器。其额外优势在于可以对使用占位符的数据进行安全处理,防止SQL注入攻击。

安装配置及测试

在windows下进行有关pdo测试的时候,php.ini中的extension_dir的值要填为pdo*.dll的路径,否则无法运行pdo的相关程序。

; Directory in which the loadable extensions (modules) reside.
extension_dir = "E:\www\php5\ext"

<?php
$host = 'localhost';
$user = 'root';
$password = 'develop';
$dbname = '99game';

$dbh = new PDO("mysql:host=$host;dbname=$dbname", $user, $password);

//=======================================================
//例子 1. Execute a prepared statement with named placeholders
/* Execute a prepared statement by binding PHP variables */
$user_id = 1;
$email = 'caihf_73940@qq.com';
$sth = $dbh->prepare('SELECT user_id,email,token FROM 99game_user
    WHERE user_id = :user_id AND email = :email');
$sth->bindParam(':user_id', $user_id, PDO::PARAM_INT);
$sth->bindParam(':email', $email, PDO::PARAM_STR, 30);
$sth->execute();
$result = $sth->fetch(PDO::FETCH_ASSOC);
print_r($result);
print("<br />\n");

//例子 2. Execute a prepared statement with question mark placeholders
/* Execute a prepared statement by binding PHP variables */
$user_id = 2;
$email = 'caihuafeng1@gmail.com';
$sth = $dbh->prepare('SELECT user_id,email,token FROM 99game_user
    WHERE user_id = ? AND email = ?');
$sth->bindParam(1, $user_id, PDO::PARAM_INT);
$sth->bindParam(2, $email, PDO::PARAM_STR, 30);
$sth->execute();
$result = $sth->fetch(PDO::FETCH_ASSOC);
print_r($result);
print("<br />\n");

print "<hr />\n";
 //=======================================================

//=======================================================
$sth = $dbh->prepare("SELECT user_id,email,token FROM 99game_user limit 10");
$sth->execute();

/* 运用 PDOStatement::fetch 风格 */
print("PDO::FETCH_ASSOC: ");
print("Return next row as an array indexed by column name<br />\n");
$result = $sth->fetch(PDO::FETCH_ASSOC);
print_r($result);
print("<br />\n");
print("\n");

print("PDO::FETCH_BOTH: ");
print("Return next row as an array indexed by both column name and number<br />\n");
$result = $sth->fetch(PDO::FETCH_BOTH);
print_r($result);
print("<br />\n");
print("\n");

print("PDO::FETCH_LAZY: ");
print("Return next row as an anonymous object with column names as properties<br />\n");
$result = $sth->fetch(PDO::FETCH_LAZY);
print_r($result);
print("<br />\n");
print("\n");

print("PDO::FETCH_OBJ: ");
print("Return next row as an anonymous object with column names as properties<br />\n");
$result = $sth->fetch(PDO::FETCH_OBJ);
print_r($result);
print 'user_id:' . $result->user_id;
print("<br />\n");
print("\n");

print "<hr />\n";
//=======================================================

//=======================================================
function readDataForwards($dbh) {
  $sql = 'SELECT user_id,email,token FROM 99game_user limit 10';
  try {
    $stmt = $dbh->prepare($sql, array(PDO::ATTR_CURSOR, PDO::CURSOR_SCROLL));
    $stmt->execute();
    while ($row = $stmt->fetch(PDO::FETCH_NUM, PDO::FETCH_ORI_NEXT)) {
      $data = $row[0] . "\t" . $row[1] . "\t" . $row[2] . "<br />\n";
      print $data;
    }
    $stmt = null;
  }
  catch (PDOException $e) {
    print $e->getMessage();
  }
}

function readDataBackwards($dbh) {
  $sql = 'SELECT user_id,email,token FROM 99game_user limit 10';
  try {
    $stmt = $dbh->prepare($sql, array(PDO::ATTR_CURSOR => PDO::CURSOR_SCROLL));
    $stmt->execute();
    $row = $stmt->fetch(PDO::FETCH_NUM, PDO::FETCH_ORI_LAST);
    do {
      $data = $row[0] . "\t" . $row[1] . "\t" . $row[2] . "<br />\n";
      print $data;
    } while ($row = $stmt->fetch(PDO::FETCH_NUM, PDO::FETCH_ORI_PRIOR));
    $stmt = null;
  }
  catch (PDOException $e) {
    print $e->getMessage();
  }
}

print "Reading forwards:<br />\n";
readDataForwards($dbh);

print "<hr />\n";

print "Reading backwards:<br />\n";
//下面的数据没有按照想像中的倒排输出,暂时不知道什么原因,php.net官方手册中的例子也是这么写的
readDataBackwards($dbh);
//=======================================================
?>

以上测试程序输出如下:

Array
(
[user_id] => 1
[email] => caihf_73940@qq.com
[token] => 123token456_73940
)
Array
(
[user_id] => 2
[email] => caihuafeng1@gmail.com
[token] => 33fadfasdfadsf
)

PDO::FETCH_ASSOC: Return next row as an array indexed by column name
Array
(
[user_id] => 1
[email] => caihf_73940@qq.com
[token] => 123token456_73940
)

PDO::FETCH_BOTH: Return next row as an array indexed by both column name and number
Array
(
[user_id] => 2
[0] => 2
[email] => caihuafeng1@gmail.com
[1] => caihuafeng1@gmail.com
[token] => 33fadfasdfadsf
[2] => 33fadfasdfadsf
)

PDO::FETCH_LAZY: Return next row as an anonymous object with column names as properties
PDORow Object
(
[queryString] => SELECT user_id,email,token FROM 99game_user limit 10
[user_id] => 3
[email] => caihf_61039@qq.com
[token] => 123token456_61039
)

PDO::FETCH_OBJ: Return next row as an anonymous object with column names as properties
stdClass Object
(
[user_id] => 6
[email] => aa1@aa.com
[token] => cU8ady73epcmf54o7W0q1F0f8R3b2y4d
)
user_id:6

更多相关知识,请访问 PHP中文网!!

相关标签: mysql PDO php