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

mysql原始文件

Experienced developers eschew the original MySQL extension because of its abandoned status in PHP. Nascent web developers, however, may be completely oblivious to its dormant past and dying future.

有经验的开发人员避免使用原始MySQL扩展，因为它在PHP中已被废弃。 但是，新生的Web开发人员可能完全忽略了它过去的Hibernate状态和垂死的未来。

Introductory tutorials for connecting to MySQL from PHP are certainly abundant, as is evident from a simple “php and mysql tutorial” Google search. It’s just unfortunate that many of them teach only how to use the original MySQL extension. This has channelled new developers who would like to learn how to interact with their database to only one of three potential extensions, giving them a skill that will soon become useless in an upcoming major version of PHP.

从PHP连接到MySQL的入门教程肯定很多，从简单的“ php和mysql教程” Google搜索可以明显看出。 不幸的是，其中许多人只讲授如何使用原始MySQL扩展。 这将希望学习如何与数据库交互的新开发人员引导到三个潜在扩展中的一个，使他们掌握了一项技能，该技能很快将在即将发布的主要PHP版本中失效。

In turn, this has increased the amount of people who rely upon the extension and will, in some cases, use it as their one-and-only database access method. This means a ‘soft deprecation’ is needed to slowly phase out the use of the extension with as little impact as possible for the users who have yet switched over to a newer extension.

反过来，这增加了依赖该扩展程序的人数，并且在某些情况下，会将其用作他们的唯一数据库访问方法。 这意味着需要“软弃用”来逐步淘汰扩展程序的使用，而对已切换到较新扩展程序的用户的影响尽可能小。

It is therefore the intention of this two-part article to raise awareness among developers who still use the MySQL extension, inform them of its problems, and to help them switch over to an alternative extension. After all, who really wants to write a script knowing that it won’t work in a near-future version of PHP? In this part we’ll focus on MySQLi, and in part two we’ll look at PDO.

因此，本文分为两部分，旨在提高仍在使用MySQL扩展的开发人员的意识，告知他们该问题，并帮助他们切换到其他扩展。 毕竟，谁真的想写一个脚本，知道它不能在近期版本PHP中工作？ 在这一部分中，我们将专注于MySQLi，在第二部分中，我们将研究PDO。

MySQL扩展有什么问题？ (What’s Wrong with the MySQL Extension?)

Aside from the fact that it’s currently in the process of being deprecated, there may seem to be nothing wrong with the original MySQL extension. Dig a little deeper though into PHP history and it’s easy to see why many developers avoid using it. The extension has been maintain-only since PHP 4.3.1. This means the last time any new functionality was added to the MySQL extension was back in late 2002. That’s 10 years worth of features you’re missing out on!

除了当前不赞成使用它的事实之外，原始MySQL扩展似乎没有什么问题。 虽然深入研究了PHP历史，但很容易理解为什么许多开发人员避免使用它。 自PHP 4.3.1起，该扩展仅用于维护。 这意味着上一次向MySQL扩展中添加任何新功能是在2002年底。这是您十年来错过的功能！

If that doesn’t sound bad enough, the last version of the MySQL API to be integrated into PHP was from MySQL 4.0.0. This means the extension doesn’t handle any of the new features added to the API over the years by MySQL.

如果这还不够糟糕，那么要集成到PHP中MySQL API的最新版本是MySQL 4.0.0。 这意味着该扩展程序无法处理MySQL多年来向API添加的任何新功能。

备选方案1：MySQLi –改进了MySQL (Alternative #1: MySQLi – MySQL Improved)

There are two primary alternatives to interacting with a MySQL database. The first is the MySQLi, or MySQL Improved, extension which exposes both an object-oriented and procedural API.

与MySQL数据库交互有两种主要选择。 第一个是MySQLi或MySQL改进的扩展，它同时公开了面向对象和过程API。

The difference between the procedural API in comparison to the MySQL extension’s is minimal, but the gains are significant. With over 100 functions provided by MySQLi, it dwarfs the older extension which has a mere 48 functions. These new functions bring new features, and we’ll run through some of them right after we’ve familiarized ourselves with the basics.

与MySQL扩展相比，过程API之间的差异很小，但是收益却是可观的。 MySQLi提供了100多个功能，它使仅48个功能的旧扩展相形见war。 这些新功能带来了新功能，在熟悉基础知识之后，我们将逐一介绍其中的一些功能。

MySQLi基础 (MySQLi Basics)

First, let’s create a connection to a MySQL database and then interact with it by performing some basic queries.

首先，我们创建一个与MySQL数据库的连接，然后通过执行一些基本查询与之交互。

<?php
// Procedural API connection method #1
$db = mysqli_connect('host', 'username', 'password');
mysqli_select_db($db, 'database');

// Procedural API connection method #2
$db = mysqli_connect('host', 'username', 'password', 'database');

if (mysqli_connect_errno()) {
     die(mysqli_connect_error());
}

// Object-oriented API connection
$db = new MySQLi('host', 'username', 'password', 'database');

if ($db->connect_errno) {
    die($db->connect_error);
}

When using the procedural implementation, we are presented with two ways to connect to the database. The first is more similar to the original MySQL extension where the host connection is segregated from the database connection, requiring a little more code. The second method is more like the object-oriented interface of the MySQLi API, where the database name is passed as a parameter. We can then check if there was an error while attempting to connect to the database by questioning the return of mysqli_connect_errno(). The error message is retrieved using mysqli_connect_error().

在使用过程实现时，将为我们提供两种连接数据库的方法。 第一个与原始MySQL扩展更相似，在原始MySQL扩展中，主机连接与数据库连接是分开的，需要更多的代码。 第二种方法更像MySQLi API的面向对象的接口，其中数据库名称作为参数传递。 然后，我们可以通过询问mysqli_connect_errno()的返回值来检查尝试连接数据库时是否存在错误。 使用mysqli_connect_error()检索错误消息。

With the object-oriented implementation, we instantiate the MySQLi class and pass all of the connection details as constructor arguments. A check can then be carried out on the connect_errno property to see if there was an error, and the corresponding error message can be retrieved from the connect_error property.

通过面向对象的实现，我们实例化了MySQLi类，并将所有连接详细信息作为构造函数参数传递。 然后可以对connect_errno属性进行检查以查看是否存在错误，并且可以从connect_error属性中检索相应的错误消息。

Now let’s carry out some basic interactions against the database.

现在，让我们对数据库进行一些基本的交互。

<?php
$name = "O'Reilly";
$email = "In'valid@email.com";
$clean = array();

// Procedural API
$clean['name'] = mysqli_real_escape_string($db, $name);
$clean['email'] = mysqli_real_escape_string($db, $email);

mysqli_query($db, "INSERT INTO table_name
     VALUES (NULL, '{$clean['name']}', '{$clean['email']}')");

mysqli_query($db, "UPDATE table_name
     SET name = 'Thomas' WHERE email = '{$clean['email']}'");

$result = mysqli_query($db, "SELECT name FROM table_name
     WHERE email = '{$clean['email']}'");

if ($row = mysqli_fetch_assoc($result)) {
     echo $row['name'];
} else {
     echo 'No results found.';
}

// Object-oriented API
$clean['name'] = $db->real_escape_string($name);
$clean['email'] = $db->real_escape_string($email);

$db->query("INSERT INTO table_name
    VALUES (NULL, '{$clean['name']}', '{$clean['email']}')");

$db->query("UPDATE table_name
    SET name = 'Thomas' WHERE email = '{$clean['email']}'");

$result = $db->query("SELECT name FROM table_name
    WHERE email = '{$clean['email']}'");

if ($row = $result->fetch_assoc()) {
    echo $row['name'];
} else {
    echo 'No results found.';
}

The main difference from the original MySQL extension and MySQLi’s procedural API is the compulsory passing of the connection link as the first parameter in the mysqli_* functions (a comprehensive list of all functions and methods/properties can be found in the PHP manual). This is of course different to the MySQL extension, where it is optional to pass the connection link as the last parameter.

与原始MySQL扩展和MySQLi的过程API的主要区别在于，强制将连接链接作为mysqli_*函数中的第一个参数mysqli_* (所有功能和方法/属性的完整列表均可在PHP手册中找到 )。 这当然与MySQL扩展不同，在MySQL扩展中，可以选择将连接链接作为最后一个参数来传递。

We don’t have to pass the connection link as an argument to the object’s methods in the object-oriented approach because it’s maintained as part of the MySQLi instance’s state. Additionally, all of the methods and properties exposed by the MySQLi class also omit the mysqli_ prefix, and mysqli_stmt_ is omitted from the properties and methods of the MySQLi_stmt class.

在面向对象的方法中，我们不必将连接链接作为参数传递给对象的方法，因为它是作为MySQLi实例状态的一部分进行维护的。 此外，所有的方法和由所述公开的属性MySQLi类也省略mysqli_前缀，以及mysqli_stmt_从属性和方法省略MySQLi_stmt类。

MySQLi的新增功能？ (What’s New in MySQLi?)

Now that we’ve familiarized ourselves with the basics of the two APIs, let’s take a look at some of the new features that it introduces: prepared statements, multi-queries, and transactions.

现在，我们已经熟悉了这两个API的基础，让我们看一下它引入的一些新功能：准备好的语句，多查询和事务。

Prepared Statements

准备的陈述

Prepared statements, otherwise known as parametrized queries, are one of the main features introduced by this API. Prepared statements are considered to be safer than MySQL’s mysql_real_escape_string() function because they are not prone to human error. If MySQL’s native escaping function is applied incorrectly, then it can still leave gaping security holes in a web application.

准备好的语句(也称为参数化查询)是此API引入的主要功能之一。 预备语句被认为比MySQL的mysql_real_escape_string()函数更安全，因为它们不易发生人为错误。 如果MySQL的本机转义功能使用不当，那么它仍然会在Web应用程序中留下巨大的安全漏洞。

Parametrized queries can be applied like so:

参数化查询可以这样应用：

<?php
$name = "O'Reilly";
$email = "In'valid@email.com";

// Procedural API
$insQuery = mysqli_prepare($db, 'INSERT INTO table VALUES (NULL, ?, ?)');
mysqli_stmt_bind_param($insQuery, 'ss', $name, $email);
mysqli_stmt_execute($insQuery);

// Object-oriented API
$insQuery = $db->prepare('INSERT INTO table VALUES (NULL, ?, ?)');
$insQuery->bind_param('ss', $name, $email);
$insQuery->execute();

When creating a prepared statement, we insert question marks (not surrounded by quotes, otherwise they will be treated as string values) to identify where the values are bound in the query. These are called placeholders, and in this instance are unnamed because MySQLi does not support named placeholders (unlike PDO). Once we have prepared the query, we can then bind the parameters.

在创建准备好的语句时，我们插入问号(不要用引号引起来，否则将被视为字符串值)以标识值在查询中的绑定位置。 这些被称为占位符，在这种情况下是未命名的，因为MySQLi不支持命名的占位符(与PDO不同)。 准备好查询后，便可以绑定参数。

Focusing on the object-oriented API, the first parameter of the bind_param() method specifies the argument types we would like to cast the values to when binding them. There are four types: s (string), i (integer), d (double), and b (blob). All must be used in lower-case only, and the number of letters indicating the parameter types must match the number of values that need binding (even if all of the values are going to be cast the same).

着眼于面向对象的API， bind_param()方法的第一个参数指定了在绑定值时我们希望将值bind_param()为的参数类型。 有四种类型：s(字符串)，i(整数)，d(双精度)和b(blob)。 全部必须仅使用小写字母，并且指示参数类型的字母数必须与需要绑定的值数匹配(即使所有值都将被强制转换为相同)。

A new parameter in the bind_param() method is then passed for each value that needs binding to the prepared query. The binding values will be cast respectively to the type order in the first parameter. We can then execute our prepared statement with all of the values safely inserted into the query.

然后，为需要绑定到准备好的查询的每个值传递bind_param()方法中的新参数。 绑定值将分别转换为第一个参数中的类型顺序。 然后，我们可以将准备好的语句与所有值安全地插入查询中。

Multi-Queries

多重查询

The next major feature introduced with MySQLi is support for multiple statements. This is where we are able to stack SQL statements in one query by separating them with a semi-colon, and then execute them all at once. This is a particularly efficient feature, but can also be destructive if an out-sourced value in the query has not been properly escaped before being used.

MySQLi引入的下一个主要功能是支持多条语句。 在这里，我们可以通过用分号分隔SQL语句，然后一次执行它们，从而在一个查询中堆叠SQL语句。 这是一个特别有效的功能，但是如果在使用查询之前未正确转义查询中的外包值，也可能具有破坏性。

With multi-queries, we are unable to use parametrized queries because they are not able to prepare multiple statements in one go. The statements would have to be prepared individually, which would require more code, however the tradeoff is greater legibility and less potential for human error.

对于多查询，我们无法使用参数化查询，因为它们无法一次性准备多个语句。 这些语句将必须单独准备，这将需要更多代码，但是要权衡的原因是可读性更高，而人为错误的可能性也较小。

Transactions

交易次数

The final feature we’ll look at is support for transactions, provided you’re using the InnoDB storage engine. Transaction are often described as having ACID properties:

我们将讨论的最后一个功能是对事务的支持，前提是您使用的是InnoDB存储引擎。 事务通常被描​​述为具有ACID属性：

• Atomicity states that an action will either fully happen, or not at all.

  原子性表示动作将完全发生或根本不会发生。

• Consistency is where the new data coming into the database will conform with the current rules set in place. If the incoming data does not validate, then it will all be returned.

  一致性是进入数据库的新数据将符合当前设置的规则的地方。 如果传入的数据无效，则将全部返回。

• Isolation refers to the state of the transaction in progress, which must remain alone from other transactions until it is fully completed.

  隔离是指进行中的交易的状态，必须与其他交易保持独立，直到完全完成。

• Durability speaks in terms of a database being able to withstand loss of updated data after a failure, for example power loss. It says that once a transaction has been committed, it will remain so.

  耐久性是指数据库能够承受故障后更新数据的丢失，例如断电。 它说，一旦事务被提交，它将保持不变。

Transactions are commonly used when we have a block of SQL statements that are inter-dependent on one-another. This makes it all the more important to ensure that either they all happen successfully and are committed as such, or all fail.

当我们有彼此依赖SQL语句块时，通常使用事务。 这使得确保它们全部成功发生并被提交或全部失败更加重要。

We can test the theory of transactions by performing a repeated insertion on a table where a field has a unique key constraint set upon it:

我们可以通过在表上执行重复插入来测试事务处理理论，在该表上，字段具有唯一的键约束：

<?php
mysqli_autoCommit($db, false);

$resultA = mysqli_query($db, "INSERT INTO table_name
    VALUES (NULL, 'Tom', 'email@address.com')");
if ($resultA === false) {
    mysqli_rollback($db);
}

$resultB = mysqli_query($db, "INSERT INTO table_name
    VALUES (NULL, 'Tom', 'email@address.com')");
if ($resultB === false) {
    mysqli_rollback($db);
}

mysqli_commit($db);

When creating the transaction, we first call upon the mysqli_autoCommit() function. It’s second parameter (or first parameter if you’re using the object-oriented API), should be set false, disallowing the database to save your changes as you issue each query. This is required to prevent any changes from being saved before you’re sure that the transaction has been completely successful. Once a change has been saved, it cannot be rolled back.

创建事务时，我们首先调用mysqli_autoCommit()函数。 应将第二个参数(如果使用的是面向对象的API，则为第一个参数)设置为false，从而在发出每个查询时不允许数据库保存更改。 在确保事务已完全成功之前，需要执行此操作以防止保存任何更改。 保存更改后，便无法回滚。

We then perform our queries, checking if they failed; if any failed, then the changes would not affect the database. If, however, there are no rollbacks during the transaction, then we can successfully commit all of the changes with mysqli_commit(). In the example above, our second insert statement would have failed because of a duplicate entry being submitted, meaning that nothing at all will be inserted into the database.

然后，我们执行查询，检查它们是否失败； 如果有任何失败，则更改不会影响数据库。 但是，如果在事务期间没有回滚，那么我们可以使用mysqli_commit()成功提交所有更改。 在上面的示例中，由于提交了重复的条目，我们的第二个insert语句将失败，这意味着什么也不会插入数据库。

摘要 (Summary)

This article has demonstrated the ease of switching to the newer, and more familiar MySQLi API, as well as giving you two very good reasons why you should forgo using the MySQL extension: it is currently being deprecated (and will be officially deprecated as of PHP 5.5.0), and the extension has become extremely out-dated. I hope this makes migrating to the newer MySQLi API a little easier, so that we can finally see the back of the MySQL extension altogether.

本文展示了切换到更新的，更熟悉MySQLi API的便捷性，并为您提供了两个应该放弃使用MySQL扩展的很好的理由：目前不推荐使用(从PHP开始正式不推荐使用) 5.5.0)，并且扩展名已过时。 我希望这可以使迁移到较新MySQLi API更加容易，以便我们最终可以完全看到MySQL扩展的背面。

Don’t forget to check out the PHP manual for more information about MySQLi and its library of functions and built-in classes, and stay tuned for part two when we look at PDO.

不要忘记查看PHP手册， 以获取有关MySQLi及其功能库和内置类的更多信息 ，并在关注PDO时继续关注第二部分 。

Image via Fotolia

图片来自Fotolia

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

mysql原始文件