php mysqli 或 PDO - 优缺点是什么？

mysqli or PDO - what are the pros and cons?

提问by Polsonby

In our place we're split between using mysqli and PDO for stuff like prepared statements and transaction support. Some projects use one, some the other. There is little realistic likelihood of us ever moving to another RDBMS.

在我们这里，我们分为使用 mysqli 和 PDO 来处理准备好的语句和事务支持等内容。有些项目使用一种，有些使用另一种。我们几乎不可能迁移到另一个 RDBMS。

I prefer PDO for the single reason that it allows named parameters for prepared statements, and as far as I am aware mysqli does not.

我更喜欢 PDO 的一个原因是它允许为准备好的语句命名参数，据我所知 mysqli 没有。

Are there any other pros and cons to choosing one over the other as a standard as we consolidate our projects to use just one approach?

在我们整合我们的项目以仅使用一种方法时，选择一种方法作为标准是否还有其他优点和缺点？

回答by e-satis

Well, you could argue with the object oriented aspect, the prepared statements, the fact that it becomes a standard, etc. But I know that most of the time, convincing somebody works better with a killer feature. So there it is:

好吧，你可以争论面向对象方面、准备好的语句、它成为标准的事实等等。但我知道大多数时候，说服某人使用杀手级功能会更好。所以它是：

A really nice thing with PDO is you can fetch the data, injecting it automatically in an object. If you don't want to use an ORM(cause it's a just a quick script) but you do like object mapping, it's REALLY cool :

PDO 一个非常好的事情是你可以获取数据，将它自动注入到一个对象中。如果您不想使用ORM（因为它只是一个快速脚本）但您确实喜欢对象映射，那么它真的很酷：

class Student {

    public $id;
    public $first_name;
    public $last_name

    public function getFullName() {
        return $this->first_name.' '.$this->last_name
    }
}

try 
{
    $dbh = new PDO("mysql:host=$hostname;dbname=school", $username, $password)

    $stmt = $dbh->query("SELECT * FROM students");

    /* MAGIC HAPPENS HERE */

    $stmt->setFetchMode(PDO::FETCH_INTO, new Student);


    foreach($stmt as $student)
    {
        echo $student->getFullName().'<br />';
    } 

    $dbh = null;
}
catch(PDOException $e)
{
    echo $e->getMessage();
}

回答by Theo

Moving an application from one database to another isn't very common, but sooner or later you may find yourself working on another project using a different RDBMS. If you're at home with PDO then there will at least be one thing less to learn at that point.

将应用程序从一个数据库移动到另一个数据库并不常见，但迟早您可能会发现自己正在使用不同的 RDBMS 处理另一个项目。如果您在家中使用 PDO，那么此时至少可以少学一件事。

Apart from that I find the PDO API a little more intuitive, and it feels more truly object oriented. mysqli feels like it is just a procedural API that has been objectified, if you know what I mean. In short, I find PDO easier to work with, but that is of course subjective.

除此之外，我发现 PDO API 更直观，感觉更真正面向对象。mysqli 感觉它只是一个被对象化的过程 API，如果你知道我的意思。简而言之，我发现 PDO 更容易使用，但这当然是主观的。

回答by Brian Warshaw

I've started using PDO because the statement support is better, in my opinion. I'm using an ActiveRecord-esque data-access layer, and it's much easier to implement dynamically generated statements. MySQLi's parameter binding must be done in a single function/method call, so if you don't know until runtime how many parameters you'd like to bind, you're forced to use call_user_func_array()(I believe that's the right function name) for selects. And forget about simple dynamic result binding.

我已经开始使用 PDO，因为在我看来，语句支持更好。我正在使用 ActiveRecord 式的数据访问层，并且实现动态生成的语句要容易得多。MySQLi 的参数绑定必须在单个函数/方法调用中完成，因此如果您直到运行时才知道要绑定多少个参数，您将被迫使用call_user_func_array()（我相信这是正确的函数名称）进行选择. 忘记简单的动态结果绑定。

Most of all, I like PDO because it's a very reasonable level of abstraction. It's easy to use it in completely abstracted systems where you don't want to write SQL, but it also makes it easy to use a more optimized, pure query type of system, or to mix-and-match the two.

最重要的是，我喜欢 PDO，因为它是一个非常合理的抽象级别。在您不想编写 SQL 的完全抽象的系统中使用它很容易，但它也可以很容易地使用更优化的、纯查询类型的系统，或者混合和匹配两者。

回答by Dave Gregory

PDO is the standard, it's what most developers will expect to use. mysqli was essentially a bespoke solution to a particular problem, but it has all the problems of the other DBMS-specific libraries. PDO is where all the hard work and clever thinking will go.

PDO 是标准，大多数开发人员都希望使用它。mysqli 本质上是针对特定问题的定制解决方案，但它具有其他特定于 DBMS 的库的所有问题。PDO 是所有辛勤工作和聪明思考的去处。

回答by Tom

Here's something else to keep in mind: For now (PHP 5.2) the PDO library is buggy. It's full of strange bugs. For example: before storing a PDOStatementin a variable, the variable should be unset()to avoid a ton of bugs. Most of these have been fixed in PHP 5.3 and they will be released in early 2009 in PHP 5.3 which will probably have many other bugs. You should focus on using PDO for PHP 6.1 if you want a stable release and using PDO for PHP 5.3 if you want to help the community.

这里的其他东西要记住：现在（PHP 5.2）的PDO库是越野车。它充满了奇怪的错误。例如：在PDOStatement变量中存储 a 之前，该变量应该是unset()为了避免大量错误。其中大部分已在 PHP 5.3 中修复，它们将在 2009 年初在 PHP 5.3 中发布，这可能会有许多其他错误。如果你想要一个稳定的版本，你应该专注于使用 PDO for PHP 6.1 和使用 PDO for PHP 5.3 如果你想帮助社区。

回答by Alix Axel

Another notable (good) difference about PDO is that it's PDO::quote()method automatically adds the enclosing quotes, whereas mysqli::real_escape_string()(and similars) don't:

关于 PDO 的另一个显着（好的）区别是它的PDO::quote()方法会自动添加封闭的引号，而mysqli::real_escape_string()（和类似的）则不会：

PDO::quote() places quotes around the input string (if required) and escapes special characters within the input string, using a quoting style appropriate to the underlying driver.

PDO::quote() 在输入字符串周围放置引号（如果需要）并使用适合底层驱动程序的引用样式对输入字符串中的特殊字符进行转义。

回答by Dfranc3373

PDO will make it a lot easier to scale if your site/web app gets really being as you can daily set up Master and slave connections to distribute the load across the database, plus PHP is heading towards moving to PDO as a standard.

如果您的站点/Web 应用程序真正存在，PDO 将使扩展变得更加容易，因为您可以每天设置主连接和从连接以在整个数据库中分配负载，而且 PHP 正朝着作为标准迁移到 PDO 的方向发展。

PDO Info

PDO信息

Scaling a Web Application

扩展 Web 应用程序

回答by Dfranc3373

In sense of speed of execution MySQLi wins, but unless you have a good wrapper using MySQLi, its functions dealing with prepared statements are awful.

在执行速度方面 MySQLi 胜出，但除非你有一个很好的包装器使用 MySQLi，否则它处理准备好的语句的功能很糟糕。

There are still bugs in mine, but if anyone wants it, here it is.

我的仍然存在错误，但如果有人想要它，它就在这里。

So in short, if you are looking for a speed gain, then MySQLi; if you want ease of use, then PDO.

简而言之，如果您正在寻找速度提升，那么 MySQLi；如果您想要易用性，那么 PDO。

回答by BlaM

Personally I use PDO, but I think that is mainly a question of preference.

我个人使用 PDO，但我认为这主要是偏好问题。

PDO has some features that help agains SQL injection (prepared statements), but if you are careful with your SQL you can achieve that with mysqli, too.

PDO 有一些功能可以帮助再次 SQL 注入（准备好的语句），但是如果您对 SQL 很小心，您也可以使用 mysqli 来实现。

Moving to another database is not so much a reason to use PDO. As long as you don't use "special SQL features", you can switch from one DB to another. However as soon as you use for example "SELECT ... LIMIT 1" you can't go to MS-SQL where it is "SELECT TOP 1 ...". So this is problematic anyway.

移动到另一个数据库并不是使用 PDO 的原因。只要不使用“特殊 SQL 功能”，就可以从一个数据库切换到另一个数据库。但是，一旦您使用例如“SELECT ... LIMIT 1”，您就无法转到“SELECT TOP 1 ...”所在的 MS-SQL。所以这无论如何都是有问题的。

回答by Your Common Sense

Edited answer.

编辑的答案。

After having some experience with both these APIs, I would say that there are 2 blocking level features which renders mysqli unusable with native prepared statements.
They were already mentioned in 2 excellent (yet way underrated) answers:

在对这两个 API 有了一些经验之后，我会说有 2 个阻塞级别的特性使 mysqli 无法与本地准备好的语句一起使用。
他们已经在 2 个优秀（但被低估）的答案中提及：

1. Binding values to arbitrary number of placeholders
2. Returning data as a mere array

1. 将值绑定到任意数量的占位符
2. 将数据作为单纯的数组返回

(both also mentioned in this answer)

（在这个答案中也提到了）

For some reason mysqli failed with both.
Nowadays it got some improvement for the second one (get_result), but it works only on mysqlnd installations, means you can't rely on this function in your scripts.

由于某种原因，mysqli 两者都失败了。
现在它对第二个 ( get_result)有了一些改进，但它仅适用于 mysqlnd 安装，这意味着您不能在脚本中依赖此功能。

Yet it doesn't have bind-by-value even to this day.

然而，即使到今天，它也没有按值绑定。

So, there is only one choice: PDO

所以，只有一种选择：PDO

All the other reasons, such as

所有其他原因，例如

• named placeholders (this syntax sugar is way overrated)
• different databases support (nobody actually ever used it)
• fetch into object (just useless syntax sugar)
• speed difference (there is none)

• 命名占位符（这种语法糖被高估了）
• 不同的数据库支持（实际上没有人使用过它）
• 取入对象（只是无用的语法糖）
• 速度差异（没有）

aren't of any significant importance.

没有任何重要意义。

At the same time both these APIs lacks some real important features, like

同时，这两个 API 都缺乏一些真正重要的功能，例如

• identifier placeholder
• placeholder for the complex data types to make dynamical binding less toilsome
• shorter application code.

• 标识符占位符
• 复杂数据类型的占位符，使动态绑定不那么麻烦
• 更短的应用程序代码。

So, to cover the reallife needs, one have to create their own abstraction library, based on one of these APIs, implementing manually parsed placeholders. In this case I'd prefer mysqli, for it has lesser level of abstraction.

因此，为了满足现实生活中的需求，必须创建自己的抽象库，基于这些 API 之一，实现手动解析的占位符。在这种情况下，我更喜欢 mysqli，因为它的抽象级别较低。