mysql原始文件_避免原始MySQL扩展，第2部分

mysql原始文件

In part one of this series we looked at what was wrong with the original MySQL API and how we can migrate to the newer, feature-rich MySQLi API. In this second part, we’ll be exploring the PDO extension to uncover some of the features it has to offer.

在本系列的第一部分中，我们研究了原始MySQL API的问题以及如何迁移到功能丰富的新MySQLi API。 在第二部分中，我们将探索PDO扩展，以发现其必须提供的某些功能。

选择2：PDO – PHP数据对象 (Alternative #2: PDO – PHP Data Objects)

The PDO extension supports twelve drivers, enabling it to connect to a variety of relational databases without the developer having to learn a number of different APIs. It does this by abstracting the database interaction calls behind a common interface, enabling the developer to utilize a consistent interface for different databases. This of course gives it a major advantage over both the MySQL and MySQLi extensions who are limited to only one database.

PDO扩展支持十二种驱动程序，从而使其能够连接到各种关系数据库，而开发人员无需学习许多不同的API。 它通过在公共接口后面抽象数据库交互调用来实现此目的，从而使开发人员能够为不同的数据库使用一致的接口。 当然，与仅限于一个数据库MySQL和MySQLi扩展相比，它具有很大的优势。

PDO基础 (PDO Basics)

Connecting to a database through PDO is a quick and easy process where the required details are passed as arguments to PDO’s constructor. We then access the object’s public methods and properties to send queries and retrieve data.

通过PDO连接到数据库是一个快速简便的过程，其中所需的详细信息作为参数传递给PDO的构造函数。 然后，我们访问对象的公共方法和属性以发送查询和检索数据。

<?php
$dsn = 'mysql:host=localhost;dbname=database_name';
$username = 'user';
$password = 'password';

$db = new PDO($dsn, $username, $password);

$numRows = $db->exec("INSERT INTO table VALUES ('val1', 'val2', 'val3')");

$result = $db->query('SELECT col1, col2, col3 FROM table', PDO::FETCH_ASSOC);
foreach ($result as $row) {
    echo "{$row['col1']} - {$row['col2']} - {$row['col3']}n";
}

$result->closeCursor();

unset($db);

A connection to the database is made first, where the DSN, username, and password are passed as arguments to the PDO class. A DSN is a connection string that will vary from database to database but usually contains the name of the database driver, host, database name, and sometimes the port number.

首先建立到数据库的连接，其中将DSN，用户名和密码作为参数传递给PDO类。 DSN是一个连接字符串，它随数据库的不同而不同，但通常包含数据库驱动程序的名称，主机，数据库名称，有时还包括端口号。

Two basic queries are then performed in the example, the first using the exec() method and the second using the query() method. The important difference between these two methods is the successful return value; exec() should be used for non-selection statements (INSERT, UPDATE, or DELETE) and returns the number of affected rows, and query() should be used for selection statements and returns a result set object upon success. They both return false if there’s a failure.

然后，在该示例中执行两个基本查询，第一个使用exec()方法，第二个使用query()方法。 这两种方法之间的重要区别是成功的返回值。 exec()应该用于非选择语句(INSERT，UPDATE或DELETE)并返回受影响的行数，而query()应该用于选择语句并在成功时返回结果集对象。 如果失败，它们都将返回false。

We can manipulate the manner in which the data is returned by passing a second argument to query(). The default is PDO::FETCH_BOTH which returns duplicated data in one array for each row; one will be associative data where the column name is the key, and column value as the value (PDO::FETCH_ASSOC), and another will be an integer indexed array (PDO::FETCH_NUM). Because this is not usually needed, it is suggested you specify an appropriate fetching mode to save resources.

我们可以通过将第二个参数传递给query()来操纵返回数据的方式。 默认值为PDO::FETCH_BOTH ，它在每一行的一个数组中返回重复的数据。 一个将是关联数据，其中列名是键，列值是值( PDO::FETCH_ASSOC )，另一个将是整数索引数组( PDO::FETCH_NUM )。 由于通常不需要这样做，因此建议您指定一种适当的提取模式以节省资源。

We then invoke the closeCursor() method to clean up the used resources stored inside of the $result object once they aren’t needed any more. Moreover, we unset our PDO object to free up any resources used when we know the script will not need to interact with the database any more.

然后，一旦不再需要在$result对象中存储的已使用资源，我们将调用closeCursor()方法来清理它们。 此外，当我们知道脚本不再需要与数据库进行交互时，我们可以取消设置PDO对象以释放所使用的任何资源。

PDO的功能 (Features of PDO)

Like with the MySQLi API, a number of features have been introduced with the PDO extension that developers can take advantage of. Now that we’ve covered the absolute basics of using PDO, we can move on to looking at some of the features, including prepared statements, transactions, changing PDO’s default behaviour, and the ramifications of abstraction.

与MySQLi API一样，PDO扩展引入了许多功能，开发人员可以利用这些功能。 既然我们已经介绍了使用PDO的绝对基础，我们就可以继续研究一些功能，包括准备好的语句，事务，更改PDO的默认行为以及抽象的后果。

Prepared Statements

准备的陈述

Like with MySQLi, PDO also supports prepared statements, whereby we bind parameters to our query to prevent injection attacks against the database. Prepared statements also enable client and server-side caching, which speeds up execution time when the same prepared query needs different values bound to it and executed. PDO does however have a couple advantages over MySQLi’s parametrized queries that are worth looking into.

与MySQLi一样，PDO也支持准备好的语句，从而将参数绑定到查询中，以防止对数据库的注入攻击。 预准备语句还启用客户端和服务器端缓存，当同一个预准备查询需要绑定并执行不同的值时，可以加快执行时间。 但是，与值得研究MySQLi参数化查询相比，PDO确实具有几个优势。

The first advantage is that PDO supports the usage of named parameters, giving us the ability to identify placeholders inside our queries by giving them meaningful names. This helps us keep track of parameters needing to be bound to a query when there are a large number involved, as opposed to having a sequence of unnamed (or positional) placeholders using question marks, which are dependent upon the order of binding.

第一个优点是PDO支持使用命名参数，从而使我们能够通过赋予查询有意义的名称来识别查询中的占位符。 这有助于我们跟踪涉及大量参数时需要绑定到查询的参数，而不是使用一系列依赖于绑定顺序的使用问号的未命名(或位置)占位符。

The second advantage is that we have the added flexibility of binding values to our prepared queries by using the bindValue() method. This enables us to bypass the limitation of only being able to bind variables and then being evaluated upon invoking the execute() method, as with MySQLi’s bind_param() method.

第二个优点是，通过使用bindValue()方法，我们将绑定值绑定到准备好的查询上具有更大的灵活性。 与MySQLi的bind_param()方法一样，这使我们能够绕开仅能够绑定变量然后在调用execute()方法时进行评估的限制。

Let’s take a closer look at how we can explicitly bind both variables and values to our prepared queries through an example.

让我们仔细看一下如何通过示例将变量和值明确绑定到准备好的查询中。

<?php
$insQuery = $db->prepare('INSERT INTO table VALUES (:col1, :col2, :col3)');
$insQuery->bindParam('col1', $val1, PDO::PARAM_INT);
$insQuery->bindParam('col2', $val2, PDO::PARAM_STR);
$insQuery->bindParam('col3', $val3, PDO::PARAM_INT);
$insQuery->execute();

$selQuery = $db->prepare('SELECT col2, col3 FROM table WHERE col1 LIKE :val');
$selQuery->bindValue('val', "%{$val}%", PDO::PARAM_STR);
$result = $selQuery->execute();

while ($row = $result->fetch(PDO::FETCH_ASSOC)) {
    echo "{$row['col2']} - {$row['col3']}n";
}

Named placeholders begin with a colon, and then use the same naming conventions as variables in PHP. When binding our parameters to the prepared query, we must provide the bindParam() and bindValue() methods with at least two arguments, along with an optional third argument.

命名占位符以冒号开头，然后使用与PHP中的变量相同的命名约定。 将我们的参数绑定到准备好的查询时，我们必须为bindParam()和bindValue()方法提供至少两个参数，以及一个可选的第三个参数。

The first argument is the name of the placeholder (which is case-sensitive), the second argument is the variable or value we want to bind to the query, and the optional third argument is the type to bind the variable/value as (the default is PDO::PARAM_STR, however I always specify the type for clarity). The bindParam() method also enables us to specify an optional fourth and fifth parameter, the data type length and any additional driver options respectively.

第一个参数是占位符的名称(区分大小写)，第二个参数是我们要绑定到查询的变量或值，第三个可选参数是将变量/值绑定为(默认是PDO::PARAM_STR ，但是为了清楚起见，我总是指定类型)。 bindParam()方法还使我们能够分别指定一个可选的第四和第五个参数，数据类型长度和任何其他驱动程序选项。

The bindParam() and bindValue() methods are orthogonal, and so either or both can be used when binding values to a query. This is not the case with named and unnamed placeholders however, where only one or the other may be used on a single prepared query.

bindParam()和bindValue()方法是正交的，因此在将值绑定到查询时可以使用其中一个或两个。 但是，对于命名和未命名的占位符，情况并非如此，在单个准备好的查询中，只能使用一个或另一个。

Implicit binding is where we forgo using the bindParam() and bindValue() methods, and just pass the parameters to bind in an array format to the execute() method. If named placeholders are being used, then an associative array will need to be passed to execute(); if positional placeholders are used, then an indexed array can be passed to execute().

隐式绑定是我们放弃使用bindParam()和bindValue()方法的地方，只是将参数以数组格式传递给execute()方法。 如果使用命名占位符，则需要将一个关联数组传递给execute() ； 如果使用位置占位符，则可以将索引数组传递给execute() 。

Here’s how we can use unnamed placeholders in the short-hand format of parametrized queries:

这是我们如何以参数化查询的简写形式使用未命名的占位符：

<?php
$updQuery = $db->prepare('UPDATE table SET col1 = ?, col2 = ? WHERE col3 = ?');
$updQuery->execute(array('val1', 'val2', 'val3'));

if ($updQuery->rowCount() !== 0) {
    echo 'Success';
}

We begin by preparing our query and putting the unnamed placeholders in position, and then invoke the execute() method with an array containing the values to be bound to our prepared query. This array being passed can contain either (or both) variables and strings. The downside to this short-hand method is that we aren’t able to specify the type of parameters being bound to our prepared query. Next we question if any rows were updated by using the return value from the rowCount() method, which will contain the number of rows affected from the previous operation. Provided the number of rows does not equal zero, then we consider it a success.

我们首先准备查询，然后将未命名的占位符放在适当的位置，然后使用包含要绑定到准备好的查询的值的数组调用execute()方法。 传递的数组可以包含(或同时包含)变量和字符串。 这种简便方法的缺点是我们无法指定绑定到准备好的查询的参数类型。 接下来，我们质疑是否通过使用rowCount()方法的返回值更新了任何行，该返回值将包含受上一操作影响的行数。 如果行数不为零，则认为成功。

The last type of binding we’ll look at with prepared queries is not for security purposes, but instead for data fetching. This is where we bind column names to variables using the bindColumn() method. The bindColumn() and bindParam()/bindValue() methods can all be used upon one prepared query, giving us flexibility in fetching data through assigning results directly to variables all while being immune to injection attacks.

我们将使用准备好的查询来查看的最后一种绑定类型不是出于安全目的，而是为了数据获取。 这是我们使用bindColumn()方法将列名绑定到变量的地方。 bindColumn()和bindParam() / bindValue()方法都可以在一个准备好的查询中使用，这使我们可以灵活地通过直接将结果分配给变量来获取数据，同时不受注入攻击。

<?php
$preQuery = $db->prepare('SELECT col2, col3 FROM table WHERE col1 = :val');
$preQuery->bindParam('val', $value, PDO::PARAM_STR);
$preQuery->execute();

$preQuery->bindColumn('col2', $OUTcol2);
$preQuery->bindColumn('col3', $OUTcol3);

while ($result = $preQuery->fetch(PDO::FETCH_BOUND)) {
    echo "{$OUTcol2} - {$OUTcol3}n";
}

We first prepare and bind a value to our query. This is then executed, and we invoke the bindColumn() method; the first parameter is the column name (which can be specified numerically), and the second parameter is the variable to bind the column’s value to. For this, I have used my own naming convention to help distinguish the variables I create (with known, safe values), from those containing values from the database. This helps me to know which ones may contain tainted data, and so will need to be escaped upon output to prevent XSS attacks.

我们首先准备一个值并将其绑定到我们的查询。 然后执行此操作，并调用bindColumn()方法； 第一个参数是列名(可以用数字指定)，第二个参数是将列值绑定到的变量。 为此，我使用了自己的命名约定来帮助区分我创建的变量(具有已知的安全值)和包含数据库值的变量。 这可以帮助我知道哪些数据可能包含污染数据，因此需要在输出时进行转义以防止XSS攻击。

We then loop through the fetched data row-by-row, where the method of fetching (PDO::FETCH_BOUND) is assigning the values of the columns in our result set to the variables they were bound to above ($OUTcol2 and $OUTcol3). While the value of $result remains true (there are rows to loop through still), then the loop will continue to execute.

然后，我们逐行遍历获取的数据，其中的获取方法( PDO::FETCH_BOUND )将结果集中的列的值分配给它们上面绑定的变量( $OUTcol2和$OUTcol3 ) 。 虽然$result的值保持为true(仍然有行要循环通过)，然后循环将继续执行。

The MySQLi API also provides the same functionality (with a similar syntax to the above) using the bind_result() method.

MySQLi API还使用bind_result()方法提供了相同的功能(语法与上述类似bind_result() 。

Transactions

交易次数

PDO also supports transactions, however they’re created in a slightly different fashion to those in the MySQLi API. As stated in the previous article, transactions work upon ACID properties and are used to maintain data integrity (ACID) across multiple tables inside a relational database. If one of the statement’s fails during the execution, we are able to roll back all of the statements effects before the changes are permanently committed (ACID); this is due to the isolated nature of each transaction (ACID) before being considered successful. This is particularly important when statements are reliant upon one-another’s success, and so they must either all succeed or all fail (ACID).

PDO还支持事务，但是创建它们的方式与MySQLi API中的方式略有不同。 如上一篇文章所述，事务处理基于ACID属性，并用于维护关系数据库内多个表之间的数据完整性(A C ID)。 如果在执行过程中其中一条语句失败，则我们可以回滚所有语句的影响，然后永久提交更改(ACI D )； 这是由于每笔交易(AC I D)都具有隔离性，因此才被视为成功。 当语句依赖于另一个的成功时，这尤其重要，因此语句必须全部成功或全部失败( A CID)。

We can once again see transactions in form by performing a repeated insertion upon a table with a unique constraint key set upon one of the columns:

我们可以通过对表执行重复插入，并在其中一个列上设置唯一约束键，从而再次看到事务的形式：

<?php
$db->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

try
{
    $db->beginTransaction();

    $db->exec("INSERT INTO table VALUES (NULL, 'col1', 'col2')");
    $db->exec("INSERT INTO table VALUES (NULL, 'col1', 'col2')");

    $db->commit();
}
catch(PDOException $e) {
    $db->rollBack();
}

We must first set the error-handling behavior to throw exceptions when they’re caused. This is because PDO’s default error-handling settings do not trigger exceptions, which would mean that our catch block would never execute.

我们必须首先将错误处理行为设置为引发异常。 这是因为PDO的默认错误处理设置不会触发异常，这意味着我们的catch块将永远不会执行。

To start a transaction, we invoke the beginTransaction() method, and then attempt to execute two destructive queries (where the data inside our database will be permanently modified using INSERT/UPDATE/DELETE statements). We then call upon the commit() method to attempt to commit the transaction and then to return the query processing back to auto-committing. The above would of course violate the data integrity rules set upon the table, causing the catch block to execute, and the transaction to be rolled back. This means no new data would be inserted into the database.

要开始事务，我们调用beginTransaction()方法，然后尝试执行两个破坏性查询(其中，数据库内部的数据将使用INSERT / UPDATE / DELETE语句进行永久性修改)。 然后，我们调用commit()方法尝试提交事务，然后将查询处理返回到自动提交。 上面的内容当然违反了在表上设置的数据完整性规则，导致catch块执行，并且事务回滚。 这意味着不会将新数据插入数据库。

Manipulating Default Behaviour

操纵默认行为

Changing PDO’s default behavior can be done through the constructor method during class instantiation, or upon the pre-existing object created after class instantiation. By using PDO’s constructor method, we can change any number of settings by passing them in an array format as the fourth (optional) parameter. When wanting to manipulate the behavior of a pre-existing object, we can use the setAttribute() method.

更改PDO的默认行为可以在类实例化期间通过构造函数方法完成，也可以在类实例化之后创建的预先存在的对象上完成。 通过使用PDO的构造方法，我们可以通过以数组格式作为第四(可选)参数传递设置来更改任何数量的设置。 当想要操纵一个预先存在的对象的行为时，我们可以使用setAttribute()方法。

<?php
$dsn = 'mysql:host=localhost;dbname=database_name';
$username = 'user';
$password = 'password';
$options = array(PDO::ATTR_PERSISTENT => TRUE,
                 PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC);
$pdo = new PDO($dsn, $username, $password, $options);

The above will enable persistent connections and change the default fetching mode from FETCH_BOTH to FETCH_ASSOC. The connection persistence setting is however among a minority of settings that must be changed before object creation in order to take affect. The default fetching behavior on the other hand is changeable upon a pre-existing object, enabling its settings to be changed at any time during the execution of a script.

上面的代码将启用持久连接，并将默认的获取模式从FETCH_BOTH更改为FETCH_ASSOC 。 但是，连接持久性设置只是少数几个必须创建的对象才能生效的设置。 另一方面，默认的获取行为可以在预先存在的对象上更改，从而可以在脚本执行期间随时更改其设置。

Changeable settings upon an object are made with the setAttribute() method, where the object’s default behavior is updated for the rest of its usage. We are also able to temporarily change some settings during method calls for some operations; one such example is with the prepare() method, which enables us to specify additional driver options when preparing a query (as an optional second argument). These together provide us with the extra flexibility in freely adjusting the behavior of our PDO object throughout the script.

使用setAttribute()方法对对象进行可更改的设置，在此方法中，对象的默认行为将在其余使用时进行更新。 我们还可以在进行某些操作的方法调用期间临时更改某些设置。 一个这样的示例就是prepare()方法，它使我们能够在准备查询时指定其他驱动程序选项(作为可选的第二个参数)。 这些共同为我们提供了在整个脚本中自由调整PDO对象行为的额外灵活性。

Changing the error handling settings is a common occurrence for wanting our PDO object to react to errors during script execution. This can be done either through the constructor or setAttribute() method, and comes in three modes: SILENT (default), WARNING, and EXCEPTION. While we are always able to view error information using the errorCode() and errorInfo() methods, the error reporting settings enable us to choose if errors encountered are either: completely silenced (PDO::ERRMODE_SILENT), raised if it’s a warning (PDO::ERRMODE_WARNING), or always thrown (PDO::ERRMODE_EXCEPTION).

希望我们的PDO对象在脚本执行过程中对错误做出React，经常会更改错误处理设置。 可以通过构造函数或setAttribute()方法完成此操作，并提供三种模式： SILENT (默认)， WARNING和EXCEPTION 。 尽管我们始终能够使用errorCode()和errorInfo()方法查看错误信息，但错误报告设置使我们可以选择是否遇到以下错误：完全静音( PDO::ERRMODE_SILENT )，如果警告则提示( PDO::ERRMODE_WARNING )，或始终抛出( PDO::ERRMODE_EXCEPTION )。

抽象的后果 (Ramifications of Abstraction)

There are however a few downsides to the abstraction layer that PDO provides. One of these is compatibility issues between each of the databases, where PDO must attempt to only use features that are available to all databases. One example of this can be seen with the PDO_MYSQL driver, where the default setting for queries is set to unbuffered due to it not being supported by other drivers. This means that for optimal usage of this abstraction layer, individual settings may need to be changed when switching from database to database.

但是，PDO提供的抽象层存在一些缺点。 其中之一是每个数据库之间的兼容性问题，其中PDO必须尝试仅使用所有数据库都可用的功能。 PDO_MYSQL驱动程序就是一个例子，由于其他驱动程序不支持将查询的默认设置设置为无缓冲。 这意味着为了最佳利用此抽象层，从数据库切换到数据库时，可能需要更改各个设置。

We must also be careful about what methods we choose to implement when using PDO. While much of the functionality PDO offers through its methods has been normalized to work on all of the supported databases, there are still methods that aren’t fully ported to all databases because they simply aren’t supported. One of these is the quote() method, which does not work for the PDO_ODBC driver. Generic methods should always be used in order to achieve portability.

我们还必须注意使用PDO时选择采用哪种方法。 虽然PDO通过其方法提供的许多功能已被规范化以在所有受支持的数据库上工作，但是仍然存在一些方法并未完全移植到所有数据库中，因为它们根本不受支持。 其中之一是quote()方法，该方法不适用于PDO_ODBC驱动程序。 为了实现可移植性，应始终使用通用方法。

A further caveat to watch out for it writing incompatible SQL code. This is because PDO, although it has the ability to work with multiple databases, is not a query abstraction layer. One classic example of writing database-dependent SQL code is when using the backtick character, whereby it may be supported by your MySQL database, but not other databases that PDO can interact with. Other databases will have their own definitions for escaping invalid table and column names, such as PostgreSQL and Oracle which use double quotes, or Microsoft SQL Server which uses square brackets.

还要特别注意编写不兼容SQL代码。 这是因为PDO尽管具有处理多个数据库的能力，但它不是查询抽象层。 编写数据库相关SQL代码的一个经典示例是使用反引号字符，该字符可能由您MySQL数据库支持，但不受PDO可以与之交互的其他数据库支持。 其他数据库将使用自己的定义来转义无效的表和列名称，例如使用双引号的PostgreSQL和Oracle，或使用方括号的Microsoft SQL Server。

结束语 (Closing Comments)

In this article, we covered the basics of PDO and manipulating its default behaviour, along with exploring prepared statements for sanitizing input (with explicit and implicit binding), while demonstrating another use for them. We also looked at the creation of transactions and their respective behavior (described through ACID properties). There is however still much additional functionality to PDO that we didn’t have the chance to discuss in this article, so be sure to head over to the PHP.net manual for more information about PDO.

在本文中，我们介绍了PDO的基础知识以及如何处理其默认行为，并探讨了准备好的语句来清理输入(具有显式和隐式绑定)，同时演示了PDO的另一种用法。 我们还研究了事务的创建及其各自的行为(通过ACID属性进行描述)。 但是，PDO还有许多其他功能，我们没有机会在本文中进行讨论，因此请务必访问PHP.net手册以获取有关PDO的更多信息 。

That concludes our two part series of why we should avoid the MySQL API, and how we can avoid it by introducing two alternatives. I hope we can now better rid ourselves of the original MySQL API in favor of either MySQLi, or the more prominent PDO extension!

总结了我们的两部分系列，为什么我们应该避免使用MySQL API，以及如何通过引入两种选择来避免使用MySQL API。 我希望我们现在可以更好地摆脱原始MySQL API，转而使用MySQLi或更突出的PDO扩展！

Image via Fotolia

图片来自Fotolia

翻译自: https://www.sitepoint.com/avoid-the-original-mysql-extension-2/

mysql原始文件