Php 简明教程

PHP – PDO Extension

PDO 是 PHP 数据对象 (PHP Data Objects) 的缩写。PHP 可以与大多数关系型和 NOSQL 数据库进行交互。默认的 PHP 安装附带已安装和启用的特定供应商的数据库扩展。除了适用于特定类型数据库的此类数据库驱动程序（例如适用于 MySQL 的 mysqli 扩展）之外，PHP 还支持抽象层，例如 PDO 和 ODBC。

PDO is an acronym for PHP Data Objects. PHP can interact with most of the relational as well as NOSQL databases. The default PHP installation comes with vendor-specific database extensions already installed and enabled. In addition to such database drivers specific to a certain type of database, such as the mysqli extension for MySQL, PHP also supports abstraction layers such as PDO and ODBC.

PDO 扩展定义了一个轻量级的、一致的接口，用于在 PHP 中访问数据库。每个特定供应商扩展的功能不同于其他扩展。因此，如果你打算更改某个 PHP 应用程序的后端数据库，例如从 PostGreSql 更改为 MySQL，你需要对代码进行许多更改。另一方面，PDO API 除了指定要使用的数据库的新 URL 和凭据之外，不需要任何更改。

The PDO extension defines a lightweight, consistent interface for accessing databases in PHP. The functionality of each vendor-specific extension varies from the other. As a result, if you intend to change the backend database of a certain PHP application, say from PostGreSql to MySQL, you need to make a lot of changes to the code. The PDO API on the other hand doesn’t require any changes apart from specifying the URL and the credentials of the new database to be used.

你当前的 PHP 安装必须具有相应的 PDO 驱动程序才能正常工作。目前使用相应的 PDO 接口支持以下数据库 −

Your current PHP installation must have the corresponding PDO driver available to be able to work with. Currently the following databases are supported with the corresponding PDO interfaces −

Driver Name

Supported Databases

PDO_CUBRID

Cubrid

PDO_DBLIB

FreeTDS / Microsoft SQL Server / Sybase

PDO_FIREBIRD

Firebird

PDO_IBM

IBM DB2

PDO_INFORMIX

IBM Informix Dynamic Server

PDO_MYSQL

MySQL 3.x/4.x/5.x/8.x

PDO_OCI

Oracle Call Interface

PDO_ODBC

ODBC v3 (IBM DB2, unixODBC and win32 ODBC)

PDO_PGSQL

PostgreSQL

PDO_SQLITE

SQLite 3 and SQLite 2

PDO_SQLSRV

Microsoft SQL Server / SQL Azure

默认情况下，PDO_SQLITE 驱动程序在 php.ini 的设置中启用，因此如果你希望使用 PDO 与 MySQL 数据库进行交互，请确保通过删除前导分号取消注释以下行。

By default, the PDO_SQLITE driver is enabled in the settings of php.ini, so if you wish to interact with a MySQL database with PDO, make sure that the following line is uncommented by removing the leading semicolon.

extension=pdo_mysql

你可以通过调用 PDO 类中的 PDO::getAvailableDrivers() 静态函数来获取当前可用的 PDO 驱动程序的列表。

You can obtain the list of currently available PDO drivers by calling PDO::getAvailableDrivers() static function in PDO class.

PDO Connection

PDO 基本类的实例表示一个数据库连接。构造函数接受用于指定数据库源（称为 DSN）的参数，还可以接受用于指定用户名和密码（如果有的）的参数。

An instance of PDO base class represents a database connection. The constructor accepts parameters for specifying the database source (known as the DSN) and optionally for the username and password (if any).

以下代码段是与 MySQL 数据库建立连接的典型方式 −

The following snippet is a typical way of establishing connection with a MySQL database −

<?php
   $dbh = new PDO('mysql:host=localhost;dbname=test', $user, $pass);
?>

如果出现任何连接错误，将抛出 PDOException 对象。

If there is any connection error, a PDOException object will be thrown.

Example

请看以下示例：

Take a look at the following example −

<?php
   $dsn="localhost";
   $dbName="myDB";
   $username="root";
   $password="";
   try{
      $dbConn= new PDO("mysql:host=$dsn;dbname=$dbName",$username,$password);
      Echo "Successfully connected with $dbName database";
   } catch(Exception $e){
      echo "Connection failed" . $e->getMessage();
   }
?>

它将生成以下 output −

It will produce the following output −

Successfully connected with myDB database

在错误的情况下 −

In case of error −

Connection failedSQLSTATE[HY000] [1049] Unknown database 'mydb'

PDO Class Methods

PDO 类定义以下静态方法——

The PDO class defines the following static methods −

PDO::beginTransaction

在获取连接对象后，你应调用此方法来启动事务。

After obtaining the connection object, you should call this method to that initiates a transaction.

public PDO::beginTransaction(): bool

此方法关闭自动提交模式。因此，你需要调用 commit() 方法才能持久更改数据库。调用 rollBack() 将回滚对数据库的所有更改并返回连接到自动提交模式。此方法成功返回 true，失败返回 false。

This method turns off autocommit mode. Hence, you need to call commit() method to make persistent changes to the database Calling rollBack() will roll back all changes to the database and return the connection to autocommit mode.This method returns true on success or false on failure.

PDO::commit

commit() 方法提交事务。

The commit() method commits a transaction.

public PDO::commit(): bool

由于 BeginTransaction 禁用了自动提交模式，因此你应在事务后调用此方法。它提交事务，将数据库连接返回到自动提交模式，直到下一次调用 PDO::beginTransaction() 开始新事务。此方法成功返回 true，失败返回 false。

Since the BeginTransaction disables the autocommit mode, you should call this method after a transaction. It commits a transaction, returning the database connection to autocommit mode until the next call to PDO::beginTransaction() starts a new transaction. This method returns true on success or false on failure.

PDO::exec

exec() 方法执行 SQL 语句并返回受影响的行数

The exec() method executes an SQL statement and return the number of affected rows

public PDO::exec(string $statement): int|false

exec() 方法在单个函数调用中执行 SQL 语句，并返回该语句影响的行数。

The exec() method executes an SQL statement in a single function call, returning the number of rows affected by the statement.

请注意，它不返回 SELECT 语句的结果。如果你有一个在你的程序中只执行一次的 SELECT 语句，请考虑发出 PDO::query()。

Note that it does not return results from a SELECT statement. If you have a SELECT statement that is to be executed only once during your program, consider issuing PDO::query().

另一方面，对于你需要多次发出的语句，请准备好一个 PDOStatement 对象 (使用 PDO::prepare())，然后使用 PDOStatement::execute() 发出该语句。

On the other hand For a statement that you need to issue multiple times, prepare a PDOStatement object with PDO::prepare() and issue the statement with PDOStatement::execute().

exec() 方法需要一个字符串参数，该参数表示要准备并执行的 SQL 语句，并返回由你发出的 SQL 语句修改或删除的行数。如果没有行受到影响，则 PDO::exec() 返回 0。

The exec() method need a string parameter that represents a SQL statement to prepare and execute, and returns the number of rows that were modified or deleted by the SQL statement you issued. If no rows were affected, PDO::exec() returns 0.

PDO::query

query() 方法准备并执行没有占位符的 SQL 语句

The query() method prepares and executes an SQL statement without placeholders

public PDO::query(string $query, ?int $fetchMode = null): PDOStatement|false

此方法在单个函数调用中准备并执行 SQL 语句，并将该语句作为一个 PDOStatement 对象返回。

This method prepares and executes an SQL statement in a single function call, returning the statement as a PDOStatement object.

PDO::rollBack

rollback() 方法回滚由 PDO::beginTransaction() 启动的事务。

The rollback() method rolls back a transaction as initiated by PDO::beginTransaction().

public PDO::rollBack(): bool

如果数据库被设置为自动提交模式，则此函数在回滚事务后将恢复自动提交模式。

If the database was set to autocommit mode, this function will restore autocommit mode after it has rolled back the transaction.

请注意，某些数据库（包括 MySQL）在事务中发出 DDL 语句（例如 DROP TABLE 或 CREATE TABLE）时会自动发出隐式 COMMIT，因此它将阻止你回滚事务边界内的任何其他更改。此方法成功返回 true，失败返回 false。

Note that some databases, including MySQL, automatically issue an implicit COMMIT when a DDL statement such as DROP TABLE or CREATE TABLE is issued within a transaction, and hence it will prevent you from rolling back any other changes within the transaction boundary. This method returns true on success or false on failure.

Example

以下代码在 MySQL 服务器上的 myDB 数据库中创建一个 student 表。

The following code creates a student table in the myDB database on a MySQL server.

<?php
   $dsn="localhost";
   $dbName="myDB";
   $username="root";
   $password="";
   try{
      $conn= new PDO("mysql:host=$dsn;dbname=$dbName",$username,$password);
      Echo "Successfully connected with $dbName database";
      $qry = <<<STRING
      CREATE TABLE IF NOT EXISTS STUDENT (
         student_id INT AUTO_INCREMENT,
         name VARCHAR(255) NOT NULL,
         marks INTEGER(3),
         PRIMARY KEY (student_id)
      );
      STRING;
      echo $qry . PHP_EOL;
      $conn->exec($qry);
      $conn->commit();
      echo "Table created\n";
   }
   catch(Exception $e){
      echo "Connection failed : " . $e->getMessage();
   }
?>

Example

使用以下代码在上述示例中创建的 student 表中插入新记录——

Use the following code to insert a new record in student table created in the above example −

<?php
   $dsn="localhost";
   $dbName="myDB";
   $username="root";
   $password="";
   try {
      $conn= new PDO("mysql:host=$dsn;dbname=$dbName",$username,$password);
      echo "Successfully connected with $dbName database";

      $sql = "INSERT INTO STUDENT values(1, 'Raju', 60)";
      $conn->exec($sql);
      $conn->commit();
      echo "A record inserted\n";
   } catch(Exception $e){
      echo "Connection failed : " . $e->getMessage();
   }
?>

Example

以下 PHP 脚本获取 student 表中的所有记录——

The following PHP script fetches all the records in the student table −

<?php
   $dsn="localhost";
   $dbName="myDB";
   $username="root";
   $password="";
   try {
      $conn= new PDO("mysql:host=$dsn;dbname=$dbName",$username,$password);
      echo "Successfully connected with $dbName database";
      $sql = "SELECT * from student";
      $statement = $conn->query($sql);
      $rows = $statement->fetchAll(PDO::FETCH_ASSOC);

      foreach ($rows as $row) {
         var_dump($row);
      }
   } catch(Exception $e){
      echo "Connection failed : " . $e->getMessage();
   }
?>