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?
是否有其他的优点和缺点来选择一个作为标准，因为我们整合我们的项目只使用一种方法?
13 个解决方案
#1
243
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();
}
#2
57
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更容易使用，但这当然是主观的。
#3
25
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，但是它也使得使用更优化的、纯查询类型的系统，或者将两者混在一起变得很容易。
#4
17
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是所有努力工作和聪明想法的去处。
#5
15
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 PDOStatement in 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之前，变量应该是unset()，以避免大量的错误。其中大部分已经在PHP 5.3中被修复了，它们将在2009年的PHP 5.3中发布，这可能会有很多其他的bug。如果需要稳定的版本，如果您想要帮助社区，那么您应该专注于使用PDO，如果您想要一个稳定的版本，并使用PDO作为PHP 5.3。
#6
10
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.
引用()在输入字符串(如果需要)周围引用引号，并在输入字符串中转义特殊字符，使用适合底层驱动程序的引用样式。
#7
8
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信息
扩展Web应用程序
#8
6
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.
我的还有一些bug，但如果有人想要，就在这里。
So in short, if you are looking for a speed gain, then MySQLi; if you want ease of use, then PDO.
简而言之，如果你在寻找速度增益，那么MySQLi;如果你想使用方便，那么PDO。
#9
5
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…”限制1“你不能进入MS-SQL，在那里它是“选择TOP 1…”。无论如何这都是有问题的。
#10
5
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进行了一些体验之后，我想说有两个阻塞级别的特性，这些特性使mysqli无法使用本地准备好的语句。他们已经被提到了2个优秀的(但被低估的)答案:
- Binding values to arbitrary number of placeholders
- 将值绑定到任意数量的占位符。
- Returning data as a mere array
- 将数据作为一个数组返回。
(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 real life 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，因为它的抽象级别较低。
#11
4
In my benchmark script, each method is tested 10000 times and the difference of the total time for each method is printed. You should this on your own configuration, I'm sure results will vary!
在我的基准脚本中，每个方法测试10000次，并且打印每个方法的总时间差异。您应该在您自己的配置上，我确定结果会有所不同!
These are my results:
这些是我的结果:
- "
SELECT NULL" -> PGO()faster by ~ 0.35 seconds - “选择NULL”-> PGO()速度快~ 0.35秒。
- "
SHOW TABLE STATUS" -> mysqli()faster by ~ 2.3 seconds - “显示表状态”-> mysqli()快了~ 2.3秒。
- "
SELECT * FROM users" -> mysqli()faster by ~ 33 seconds - “选择* FROM users”-> mysqli()快了~ 33秒。
Note: by using ->fetch_row() for mysqli, the column names are not added to the array, I didn't find a way to do that in PGO. But even if I use ->fetch_array() , mysqli is slightly slower but still faster than PGO (except for SELECT NULL).
注意:在mysqli中使用->fetch_row()，列名没有添加到数组中，我在PGO中没有找到方法。但是，即使我使用->fetch_array()， mysqli稍微慢一些，但仍然比PGO快(除了SELECT NULL)。
#12
3
One thing PDO has that MySQLi doesn't that I really like is PDO's ability to return a result as an object of a specified class type (e.g. $pdo->fetchObject('MyClass')). MySQLi's fetch_object() will only return an stdClass object.
PDO拥有的一件事是，MySQLi并不是我真正喜欢PDO作为指定类类型的对象返回结果的能力(例如$ PDO ->fetchObject('MyClass'))。MySQLi的fetch_object()只返回一个stdClass对象。
#13
-4
There's one thing to keep in mind.
有一件事要记住。
Mysqli does not support fetch_assoc() function which would return the columns with keys representing column names. Of course it's possible to write your own function to do that, it's not even very long, but I had really hard time writing it (for non-believers: if it seems easy to you, try it on your own some time and don't cheat :) )
Mysqli不支持fetch_assoc()函数，该函数将返回带有表示列名的键的列。当然，写自己的功能是有可能的，甚至不是很长，但我真的很难写(对于不信教的人来说:如果你觉得这很容易，试着在你自己的时间里试试，不要作弊:))
#1
243
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();
}
#2
57
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更容易使用，但这当然是主观的。
#3
25
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，但是它也使得使用更优化的、纯查询类型的系统，或者将两者混在一起变得很容易。
#4
17
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是所有努力工作和聪明想法的去处。
#5
15
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 PDOStatement in 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之前，变量应该是unset()，以避免大量的错误。其中大部分已经在PHP 5.3中被修复了，它们将在2009年的PHP 5.3中发布，这可能会有很多其他的bug。如果需要稳定的版本，如果您想要帮助社区，那么您应该专注于使用PDO，如果您想要一个稳定的版本，并使用PDO作为PHP 5.3。
#6
10
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.
引用()在输入字符串(如果需要)周围引用引号，并在输入字符串中转义特殊字符，使用适合底层驱动程序的引用样式。
#7
8
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信息
扩展Web应用程序
#8
6
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.
我的还有一些bug，但如果有人想要，就在这里。
So in short, if you are looking for a speed gain, then MySQLi; if you want ease of use, then PDO.
简而言之，如果你在寻找速度增益，那么MySQLi;如果你想使用方便，那么PDO。
#9
5
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…”限制1“你不能进入MS-SQL，在那里它是“选择TOP 1…”。无论如何这都是有问题的。
#10
5
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进行了一些体验之后，我想说有两个阻塞级别的特性，这些特性使mysqli无法使用本地准备好的语句。他们已经被提到了2个优秀的(但被低估的)答案:
- Binding values to arbitrary number of placeholders
- 将值绑定到任意数量的占位符。
- Returning data as a mere array
- 将数据作为一个数组返回。
(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 real life 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，因为它的抽象级别较低。
#11
4
In my benchmark script, each method is tested 10000 times and the difference of the total time for each method is printed. You should this on your own configuration, I'm sure results will vary!
在我的基准脚本中，每个方法测试10000次，并且打印每个方法的总时间差异。您应该在您自己的配置上，我确定结果会有所不同!
These are my results:
这些是我的结果:
- "
SELECT NULL" -> PGO()faster by ~ 0.35 seconds - “选择NULL”-> PGO()速度快~ 0.35秒。
- "
SHOW TABLE STATUS" -> mysqli()faster by ~ 2.3 seconds - “显示表状态”-> mysqli()快了~ 2.3秒。
- "
SELECT * FROM users" -> mysqli()faster by ~ 33 seconds - “选择* FROM users”-> mysqli()快了~ 33秒。
Note: by using ->fetch_row() for mysqli, the column names are not added to the array, I didn't find a way to do that in PGO. But even if I use ->fetch_array() , mysqli is slightly slower but still faster than PGO (except for SELECT NULL).
注意:在mysqli中使用->fetch_row()，列名没有添加到数组中，我在PGO中没有找到方法。但是，即使我使用->fetch_array()， mysqli稍微慢一些，但仍然比PGO快(除了SELECT NULL)。
#12
3
One thing PDO has that MySQLi doesn't that I really like is PDO's ability to return a result as an object of a specified class type (e.g. $pdo->fetchObject('MyClass')). MySQLi's fetch_object() will only return an stdClass object.
PDO拥有的一件事是，MySQLi并不是我真正喜欢PDO作为指定类类型的对象返回结果的能力(例如$ PDO ->fetchObject('MyClass'))。MySQLi的fetch_object()只返回一个stdClass对象。
#13
-4
There's one thing to keep in mind.
有一件事要记住。
Mysqli does not support fetch_assoc() function which would return the columns with keys representing column names. Of course it's possible to write your own function to do that, it's not even very long, but I had really hard time writing it (for non-believers: if it seems easy to you, try it on your own some time and don't cheat :) )
Mysqli不支持fetch_assoc()函数，该函数将返回带有表示列名的键的列。当然，写自己的功能是有可能的，甚至不是很长，但我真的很难写(对于不信教的人来说:如果你觉得这很容易，试着在你自己的时间里试试，不要作弊:))