pdo_mysql扩展_从MySQL扩展迁移到PDO

pdo_mysql扩展

This guide is a practical overview to help you begin migrating your existing PHP code from using the older MySQL extension to the newer PDO database abstraction extension. I won’t go into all of the features available to you with PDO, but I will share with you the basic methods you will need to know and offer a few tips so your migration will be as quick and painless as possible.

本指南是实用的概述，可帮助您开始将现有PHP代码从使用较旧MySQL扩展迁移到较新的PDO数据库抽象扩展。 我不会介绍PDO提供给您的所有功能，但是我将与您分享一些您需要了解的基本方法并提供一些技巧，以便您的迁移尽可能地快捷而轻松。

First, you should understand where PDO sits in the PHP ecosystem. There are actually three ways to interface with a MySQL database from PHP: the first is with the MySQL extension, the second is with the MySQLi extension and the third is with PDO.

首先，您应该了解PDO在PHP生态系统中的位置。 实际上，有三种方法可以与PHP中MySQL数据库进行交互：第一种方法是使用MySQL扩展，第二种方法是使用MySQLi扩展，第三种方法是使用PDO。

The MySQL extension is the oldest of the three and was the original option developers used to communicate with MySQL. The extension is now being deprecated in favor of the other two alternatives because of improvements made in newer releases of both PHP and MySQL.

MySQL扩展是这三个中最古老的扩展，并且是开发人员用来与MySQL通信的原始选项。 由于PHP和MySQL的新版本中进行了改进，因此不建议使用该扩展，而推荐使用其他两个替代方法。

MySQLi is the “improved” extension for working with MySQL databases. It takes advantage of features that are available in newer versions of the MySQL server, exposes both a function-oriented and an object-oriented interface to the developer and a does few other nifty things. If PDO isn’t right for your project, then this is the extension you should be using.

MySQLi是使用MySQL数据库的“改进”扩展。 它利用了更新版本MySQL服务器中可用的功能，向开发人员公开了面向函数和面向对象的接口，并且还做了其他一些漂亮的事情。 如果PDO不适合您的项目，则这是您应该使用的扩展。

Then most recent extension is PDO, which stands for “PHP Data Objects.” PDO offers an API that consolidates most of the functionality that was previously spread across the major database access extensions, i.e. MySQL, PostgreSQL, SQLite, MSSQL, etc. The interface exposes high-level objects for the programmer to work with database connections, queries and result sets, and low-level drivers perform communication and resource handling with the database server. A lot of discussion and work is going into PDO and it’s considered the appropriate method of working with databases in modern, professional code.

然后，最新的扩展是PDO，代表“ PHP数据对象”。 PDO提供了一个API，该API整合了以前分散在主要数据库访问扩展(即MySQL，PostgreSQL，SQLite，MSSQL等)中的大多数功能。该接口公开了高级对象，供程序员使用数据库连接，查询和结果集和低级驱动程序执行与数据库服务器的通信和资源处理。 PDO进行了大量讨论和工作，它被认为是使用现代专业代码处理数据库的适当方法。

When possible, I recommend people skip the middle man so to speak and migrate from the MySQL extension to PDO rather than to MySQLi. PDO offers a consistent API to work with a variety of databases, so you won’t have to modify your PHP code if you ever have to use another database. Also, while the MySQLi extension is currently maintained, there’s always the chance it may be deprecated in the future. Choosing to migrate to PDO now means you won’t have to migrate to it later and update your code twice. I use PDO and you should too!

如果可能的话，我建议人们跳过中间人，以便从MySQL扩展迁移到PDO而不是MySQLi。 PDO提供了一个一致的API来与各种数据库一起使用，因此，如果您不得不使用另一个数据库，则无需修改PHP代码。 另外，虽然当前维护MySQLi扩展，但总有可能在将来不推荐使用。 现在选择迁移到PDO意味着您以后不必迁移到PDO并更新两次代码。 我使用PDO，您也应该使用！

安装和配置PDO (Installing and Configuring PDO)

Once you’ve decided you want to modernize your code, you’ll need to make sure PDO is properly set up and configured. As I mentioned before, PDO consists of two parts: the extension itself which exposes the interface and a low-level database-specific driver. Drivers exist for a growing list of databases, including MySQL.

一旦决定要更新代码，就需要确保正确设置和配置了PDO。 正如我之前提到的，PDO由两部分组成：扩展本身(暴露接口)和低层特定于数据库的驱动程序。 越来越多的数据库 (包括MySQL)的驱动程序存在 。

PDO and the SQLite-specific driver should be part of the default PHP installation as of version 5.1.0 and the MySQL-specific driver would still need to be installed, but most Linux distributions often have different ideas how PHP should be compiled and packaged. In fact, many of the major distros now package the MySQL extension, the MySQLi extension and the MySQL PDO driver together in the same package. Chances are if you’re using MySQL on such a system then PDO is probably already set up for you.

从5.1.0版开始，PDO和特定于SQLite的驱动程序应该是默认PHP安装的一部分，并且仍然需要安装特定于MySQL的驱动程序，但是大多数Linux发行版通常对如何编译和打包PHP有不同的想法。 实际上，许多主要发行版现在都将MySQL扩展，MySQLi扩展和MySQL PDO驱动程序打包在同一包中。 如果您在这样的系统上使用MySQL，则很可能已经为您设置了PDO。

• Ubuntu builds PHP with PDO (but does not package the SQLite driver by default) and bundles together the MySQL extensions and driver. You can install the extensions and driver by running sudo apt-get install php5-mysql.

  Ubuntu使用PDO构建PHP(但默认情况下不打包SQLite驱动程序)，并将MySQL扩展和驱动程序捆绑在一起。 您可以通过运行sudo apt-get install php5-mysql来安装扩展程序和驱动程序。

• Fedora also bundles the extensions and driver together, but packages PDO as a loadable module. Running sudo yum install php-mysql installs the MySQL extensions and driver and the php-pdo package will automatically be included as a dependency.

  Fedora还将扩展和驱动程序捆绑在一起，但是将PDO打包为可加载模块。 运行sudo yum install php-mysql将安装MySQL扩展和驱动程序，并且php-pdo软件包将自动包含为依赖项。

• SUSE is the only distribution of the three that builds PHP the way the PHP Team recommends with regard to PDO; they builds PHP with PDO and include the SQLite driver. yast2 --install php5-mysql will install the MySQL extensions and driver you need.

  SUSE是这三个版本中唯一以PHP团队针对PDO推荐的方式构建PHP的发行版。 他们使用PDO构建PHP， 并包含SQLite驱动程序。 yast2 --install php5-mysql将安装所需MySQL扩展和驱动程序。

On Windows, all of the PDO drivers are included in the ext folder that was created when you installed PHP from the pre-compiled binary’s archive. You only need to update your php.ini by adding or uncommenting the line:

在Windows上，所有PDO驱动程序都包含在从预编译的二进制归档文件安装PHP时创建的ext文件夹中。 您只需要通过添加或取消注释该行来更新php.ini ：

extension=php_pdo_mysql.dll

It’s always possible to compile the drivers yourself in a worse-case scenario. Make sure the MySQL libraries and other development files are installed and accessible in your build environment since most problems that people encounter while trying to compile something results when the compiler can’t find various header files or libraries to link against.

在最坏的情况下，总是可以自己编译驱动程序。 确保MySQL库和其他开发文件已在您的构建环境中安装并且可以访问，因为当编译器找不到要链接的各种头文件或库时，人们在尝试编译某些东西时会遇到大多数问题。

基本查询 (Basic Querying)

The basic workflow for using the MySQL extension’s functions to work with a database can be thought of as a 5-step process:

使用MySQL扩展的功能与数据库一起工作的基本工作流程可以认为是一个5个步骤的过程：

1. Establish a connection to the database server and select the database you’ll be working with
   建立与数据库服务器的连接，然后选择要使用的数据库
2. Construct a query to send the server
   构造查询以发送服务器
3. Send the query
   发送查询
4. Iterate over the returned result rows
   遍历返回的结果行
5. Free the resources used by the result and possibly the database connection
   释放结果使用的资源，并可能释放数据库连接

<?php
// Step 1: Establish a connection
$db = mysql_connect("localhost", "testusr", "secretpass");
mysql_select_db("testdb", $db);

// Step 2: Construct a query
$query = "SELECT * FROM foo WHERE bar = '" . mysql_real_escape_string($zip) . "'";

// Step 3: Send the query
$result = mysql_query($query, $db);

// Step 4: Iterate over the results
while($row = myql_fetch_assoc($result)) {
    print_r($row);
}

// Step 5: Free used resources
mysql_free_result($result);
mysql_close($db);

With PDO, the same process can be followed and looks like this:

使用PDO，可以遵循相同的过程，如下所示：

<?php
// Step 1: Establish a connection
$db = new PDO("mysql:host=localhost;dbname=testdb", "testusr", "secretpass");

// Step 2: Construct a query
$query = "SELECT * FROM foo WHERE bar = " . $db->quote($zip);

// Step 3: Send the query
$result = $db->query($query);

// Step 4: Iterate over the results
while($row = $result->fetch(PDO::FETCH_ASSOC)) {
    print_r($row);
}

// Step 5: Free used resources
$result->closeCursor();
$db = null;

The process of connecting to the server and selecting the database with PDO is performed using a DSN instead of the myql_connect() and mysql_select_db(). A DSN is an abbreviation for “Data Source Name” which is a string that identifies which PDO driver to use to communicate with the database and conveys any essential connection information such as the host server, connection port and database name. Depending on your database, the format of the DSN you provide may vary. The major components necessary for each driver are documented on php.net, but unfortunately they are sometimes hard to find. I recommend using the site’s search feature to look for the term “DSN” in the online documentation.

使用DSN而非myql_connect()和mysql_select_db()来执行连接到服务器并使用PDO选择数据库的过程。 DSN是“数据源名称”的缩写，它是一个字符串，用于标识要使用哪个PDO驱动程序与数据库进行通信并传达任何基本连接信息，例如主机服务器，连接端口和数据库名称。 根据您的数据库，您提供的DSN的格式可能会有所不同。 php.net上记录了每个驱动程序所需的主要组件，但是不幸的是，有时很难找到它们。 我建议使用网站的搜索功能在在线文档中查找“ DSN”一词。

Also notice that PDO::quote() both escapes and quotes a value whereas mysql_real_escape() only escapes the value. You should keep this in mind so you don’t end up with extra quotation marks in the final query and scratch your head wondering where they came from.

还要注意， PDO::quote()既转义也引用一个值，而mysql_real_escape()仅转义该值。 您应该牢记这一点，以免最终查询中没有多余的引号，并且不知所措。

While sometimes you can get away without freeing the result resource with mysql_free_result(), I recommend you don’t get into such lazy habits when using PDO. If you still have a result set from a SELECT query which hasn’t called closeCursor() yet and you try to do an INSERT or UPDATE on the table, the change may silently fail and again you’ll be left scratching your head. It’s better to make a habit out of doing things properly to avoid frustrating situations later.

虽然有时您可以不用释放mysql_free_result()的结果资源而逃脱，但我建议您在使用PDO时不要养成这种懒惰的习惯。 如果您仍然有来自尚未调用closeCursor()的SELECT查询的结果集，并且尝试在表上执行INSERT或UPDATE ，则更改可能会默默地失败，并且您将再次摸不着头脑。 最好养成正确做事的习惯，以免日后令人沮丧。

深入挖掘(略) (Digging (Slightly) Deeper)

What has been covered so far is enough to get you started in migrating your PHP app from MySQL to PDO as quickly and painlessly as possible, but there’s more to PDO than what I’ve shown you. I’d like to also highlight a couple of other features PDO you may find useful: prepared statements and error handling.

到目前为止，所介绍的内容足以使您开始快速，轻松地将PHP应用程序从MySQL迁移到PDO，但是PDO的功能远非我所介绍。 我还要强调一些您可能会发现有用的PDO的其他功能：准备好的语句和错误处理。

准备的陈述 (Prepared Statements)

A prepared statement is a representation of a query as an object. The benefit of representing a query as an object is that additional logic can be associated with it that would otherwise be difficult to manage for a flat string representation. For example, you can specify placeholders in a query which are then bound to variables in your script. This is helpful if you’re running the same query multiple times to satisfy the request but with different parameters. The query only needs to be prepared once, but can be run many times with different values as the value of the variables change.

准备好的语句是查询作为对象的表示。 将查询表示为对象的好处是，可以将附加逻辑与之关联，否则将很难为扁平字符串表示进行管理。 例如，您可以在查询中指定占位符，然后将其绑定到脚本中的变量。 如果您多次运行相同的查询以满足请求，但参数不同，这将很有帮助。 该查询只需要准备一次，但是可以随变量值的变化而使用不同的值多次运行。

<?php
// collection of user information to import into the database
$users = ...

// specify the query "template"
$query = $db->prepare("INSERT INTO users (first_name, last_name, email) VALUES (:fname, :lname, :email)");

// bind the placeholder names to specific script variables
$query->bindParam(":fname", $firstName);
$query->bindParam(":lname", $lastName);
$query->bindParam(":email", $email);

// assign values to the specific variables and execute the query
foreach ($users as $u) {
    $firstName = $u["first_name"];
    $lastName = $u["last_name"];
    $email = $u["email"];
    $query->execute();
}

The parameters don’t have to be quoted or escaped with prepared statements because the underlying driver automatically quotes and escapes any bound parameters for you. Because of this, many people like to use prepared statements to guard against SQL injection vulnerabilities even if the query is only executed once.

这些参数不必用预准备语句引号或转义，因为基础驱动程序会自动为您引号并转义任何绑定的参数。 因此，即使查询仅执行一次，许多人还是喜欢使用准备好的语句来防止SQL注入漏洞。

错误处理 (Error Handling)

You can specify one of three different error handling paradigms with PDO by using PDO::setAttribute() to set the error handling mode.

您可以使用PDO::setAttribute()设置错误处理模式，从而用PDO::setAttribute()指定三种不同的错误处理范例之一。

<?php
$db = new PDO($dsn, $user, $passwd);
$db->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

The modes are:

这些模式是：

• Silent Mode (PDO::ERRMODE_SILENT) – sets the internal error code but does not interrupt the script’s execution (this is the default setting)

  静默模式( PDO::ERRMODE_SILENT )–设置内部错误代码，但不中断脚本的执行(这是默认设置)

• Warning Mode (PDO::ERRMODE_WARNING) – sets the error code and triggers an E_WARNING message

  警告模式( PDO::ERRMODE_WARNING )–设置错误代码并触发E_WARNING消息

• Exception Mode (PDO::ERRMODE_EXCEPTION) – sets the error code and throws a PDOException object

  异常模式( PDO::ERRMODE_EXCEPTION )–设置错误代码并引发PDOException对象

Regardless of which mode is set, there is an internal error code which is set and you can check using the errorCode() and errorInfo() methods of the PDO and PDOStatement objects. These are analogous to mysql_errno() and mysql_error(). errorCode() returns a 5-character string as defined in the ANSI SQL-92. errorInfo() is generally more useful as it returns an array that includes an error message in addition to the 5-character code.

无论设置哪种模式，都会设置一个内部错误代码，您可以使用PDO和PDOStatement对象的errorCode()和errorInfo()方法进行检查。 这些类似于mysql_errno()和mysql_error() 。 errorCode()返回ANSI SQL-92中定义的5个字符的字符串 。 errorInfo()通常更有用，因为它返回一个数组，该数组除了包含5个字符的代码外，还包含错误消息。

<?php
$query = "SELECT * FROM foo WHERE MALFORMED QUERY";
$result = $db->query($query);
$error = $db->errorInfo();
if (!is_null($error[2])) {
    echo "Query failed! " . $error[2];
}
...

If you are using the exception mode, a description of what went wrong is available through the exception object’s getMessage() method.

如果您使用的是异常模式，则可以通过异常对象的getMessage()方法获得错误原因的描述。

<?php
$query = "SELECT * FROM foo WHERE MALFORMED QUERY";
try {
    $result = $db->query($query);
    ...
}
catch (PDOException $e) {
    echo $e->getMessage();
}

摘要 (Summary)

Now you should have the basic knowledge necessary to start migrating from the older MySQL extension to the newer PDO extension. You’ve seen that while most of the time PDO is already available, it’s easy to set up if for some reason it isn’t. You’ve also seen how easy it is to connect to a MySQL database server, issue queries and receive the results. Of course this was just a practical overview; to learn more about what PDO has to offer and how you can take advantage of it, I encourage you to read the available documentation at php.net.

现在，您应该具有开始从较旧MySQL扩展迁移到较新的PDO扩展所需的基本知识。 您已经看到，虽然在大多数情况下PDO已经可用，但是由于某种原因而没有设置很容易进行设置。 您还看到了连接到MySQL数据库服务器，发出查询和接收结果有多么容易。 当然，这只是一个实际的概述。 要了解有关PDO提供的内容以及如何利用它的更多信息，建议您阅读 php.net上的可用文档 。

Image via Dirk Ercken / Shutterstock

图片来自Dirk Ercken / Shutterstock

翻译自: https://www.sitepoint.com/migrate-from-the-mysql-extension-to-pdo/

pdo_mysql扩展