PHP PDO 和 MySQLi

PHP PDO and MySQLi

提问by grasshopper

I just finished an introduction course in PHP, and throughout the stackoverflow forum people have recommended that I switch to PDO, prepared statements or MYSQLi, I briefly checked the manual but most of it went over my head.

我刚刚完成了 PHP 入门课程，在整个 stackoverflow 论坛中，人们都建议我切换到 PDO、准备好的语句或 MYSQLi，我简单地查看了手册，但大部分内容都超出了我的脑海。

I've been using mysql_* functions up till now so these concepts are new to me. I think they are used to access and perform database specific actions, but I'm not sure.

到目前为止，我一直在使用 mysql_* 函数，所以这些概念对我来说是新的。我认为它们用于访问和执行特定于数据库的操作，但我不确定。

So what is the difference between PDO, prepared statements and MySQLi, are they different features that accomplishes the same task? Are they compatible in a script or is it "choose one or the other"? And lastly which offers the best performance?

那么PDO、准备好的语句和MySQLi有什么区别，它们是完成相同任务的不同功能吗？它们在脚本中兼容还是“选择其中之一”？最后哪个提供了最好的性能？

Update: Thanks for the answers, I'll be hunting for more PDO tutorials.

更新：感谢您的回答，我将寻找更多 PDO 教程。

For reference I also found the following posts useful:

作为参考，我还发现以下帖子很有用：

Which one is fast and light - mysqli or PDO

哪个又快又轻——mysqli 还是 PDO

mysqli or PDO - what are the pros and cons?

mysqli 或 PDO - 优缺点是什么？

回答by deceze

At the basic level the mysql, mysqli and PDO extensions all answer the question how do I talk to the database?They all provide functions and functionality to connect to a database and send and retrieve data from it. You can use them all at the same time establishing several connections to the database at once, but that's typically nonsense.

在基本层面上，mysql、mysqli 和 PDO 扩展都回答了我如何与数据库对话的问题？它们都提供连接到数据库并从中发送和检索数据的功能和功能。您可以同时使用它们，同时建立到数据库的多个连接，但这通常是无稽之谈。

mysql* is a very simple extension that basically allows you to connect to the database, send it SQL queries and not much else.
mysqli improves this (as the name suggests) by adding parameterized queries and a few other things into the mix.
PDO is an extension that abstracts several database drivers into one package, i.e. it allows you to use the same code to connect to MySQL, Oracle, MS SQL Server and a number of other databases without needing to use database specific extensions or rewrite your code when you switch databases (in theory at least). It also supports parameterized queries.

mysql* 是一个非常简单的扩展，它基本上允许您连接到数据库，向它发送 SQL 查询，而不是其他的。
mysqli 通过添加参数化查询和其他一些东西来改进这一点（顾名思义）。
PDO 是一种将多个数据库驱动程序抽象为一个包的扩展，即它允许您使用相同的代码连接到 MySQL、Oracle、MS SQL Server 和许多其他数据库，而无需使用特定于数据库的扩展或重写您的代码您切换数据库（至少在理论上）。它还支持参数化查询。

If you know you're going to be using MySQL exclusively, mysqli is a good choice. Especially since you can use it in a procedural way, what you're already used to from the mysql extension. If you're not familiar with OOP, that's helpful. Otherwise, PDO is a nice object oriented, flexible database connector.

如果您知道将专门使用 MySQL，那么 mysqli 是一个不错的选择。特别是因为您可以以程序方式使用它，您已经从 mysql 扩展中习惯了它。如果您不熟悉 OOP，那会很有帮助。否则，PDO 是一个很好的面向对象、灵活的数据库连接器。

---

---

* Note that the mysql extension is now deprecatedand will be removed sometime in the future. That's because it is ancient, full of bad practices and lacks some modern features. Don't use it to write new code.

* 请注意，现在不推荐使用mysql 扩展并将在将来的某个时候删除。那是因为它是古老的，充满了不好的做法并且缺乏一些现代特征。不要用它来编写新代码。

回答by Explosion Pills

PDOis the "PHP Data Object." I mostly use PDO, so I can only speak on its merits:

PDO是“PHP 数据对象”。我主要使用 PDO，所以我只能谈谈它的优点：

• Works for many more databases than just MySQL (may not matter to you)
• Compiled C, so it's faster (supposedly)
• Prepared statements (others have these, though)
• SO seems to like it, so you can probably get a lot of help here at least
• Various fetch/error handling modes you can set and change on the fly

• 适用于比 MySQL 更多的数据库（可能对你来说无关紧要）
• 编译 C，所以它更快（据说）
• 准备好的语句（不过其他人有这些）
• SO 似乎喜欢它，所以你至少可以在这里得到很多帮助
• 您可以即时设置和更改的各种获取/错误处理模式

You ask

你问

So what is the difference between PDO, prepared statements and MySQLi ...

那么 PDO、准备好的语句和 MySQLi 之间有什么区别...

PDOand MySQLiare DB wrappers. "Prepared statements" is a different concept altogether. You can prepare a query that can be executed multiple times, and properly parameterized statements are SQL-Injection safe (though maybe not proof). The latter reason is most of the reason why you should be using PDO (or MySQLi), but prepared statements also bring a level of clarity to the queries.

PDO并且MySQLi是 DB 包装器。“准备好的报表”是一个完全不同的概念。您可以准备一个可以多次执行的查询，并且正确参数化的语句是 SQL 注入安全的（尽管可能不是证明）。后一个原因是您应该使用 PDO（或 MySQLi）的大部分原因，但准备好的语句也为查询带来了一定程度的清晰度。

/* mysql_* version */
mysql_connect("host");
$query = "SELECT column FROM db1.t1 WHERE id = ";
foreach ($_GET['id'] as $id) {
   $id = mysql_real_escape_string($id);
   $result = mysql_query($query . "'$id'";
   while ($row = mysql_fetch_assoc($result)) {
      echo "$row[column]\n";
   }
}
//NOTE: it would probably be better to store the resource returned by
//mysql_connect and use that consistently (in query/escape)

/* PDO version */
$pdo = new PDO('mysql:host=HOST', 'user', 'pass');
$query = $pdo->prepare("SELECT column FROM db1.t1 WHERE id = ?";
foreach ($_GET['id'] as $id) {
   $query->execute($id);
   echo $query->fetch(PDO::FETCH_COLUMN);
}
//Notice that you skip the escape step.

You can do essentially the same with MySQLi, but I prefer PDO's syntax. It may be faster too, but I could be making that up. There's also the PEAR MDB2that rarely gets spoken of, and I'm sure many more. Since PDOis built in, I would go with it.

基本上可以用 做同样的事情MySQLi，但我更喜欢PDO的语法。它也可能更快，但我可以弥补。还有MDB2一个很少被提及的 PEAR ，我敢肯定还有更多。既然PDO是内置的，我会选择它。

回答by SDC

If you're used to the mysql_xxxfunctions, then I would starting by moving across to the MySQLiextension instead.

如果您已经习惯了这些mysql_xxx功能，那么我会先转向MySQLi扩展程序。

You could use PDO instead if you wish, but this would only really be worth it in the first instance if you need to start supporting multiple databases. For your purposes, I'd suggest switching to MySQLi, as it'll be easier for you, and you won't be getting the benefits of PDO right away anyway.

如果您愿意，您可以改用 PDO，但如果您需要开始支持多个数据库，这只会在第一个实例中真正值得。出于您的目的，我建议切换到 MySQLi，因为这对您来说会更容易，而且您不会立即获得 PDO 的好处。

The functions available with MySQLi are pretty much analogous to the mysql_xxfunctions you're used to; it's generally possible to take existing code, do a direct swap between them, and the code should continue working just fine.

MySQLi 提供的功能与mysql_xx您习惯的功能非常相似；通常可以采用现有代码，在它们之间进行直接交换，并且代码应该可以继续正常工作。

So that's a good place to start -- get your code using mysqli_xxxinstead of mysql_xxx`.

所以这是一个很好的开始——让你的代码使用mysqli_xxx而不是 mysql_xxx`。

If possible, I'd recommend using the object oriented syntax rather than the procedural syntax. MySQLi supports both, and the procedural syntax will be closer to what you're used to, but the OO syntax is more flexible in the long run, and really isn't that much different once you're used to it.

如果可能，我建议使用面向对象的语法而不是过程语法。MySQLi 两者都支持，并且过程语法将更接近您所习惯的，但是从长远来看，OO 语法更加灵活，并且一旦您习惯了它，实际上并没有太大的不同。

Once you've got your code converted to using the MySQLi library, and you're comfortable with the basics, you're ready to start using the more advanced features like prepared statements. But get yourself comfortable with the basics first.

一旦您将代码转换为使用 MySQLi 库，并且您对基础知识感到满意，您就可以开始使用更高级的功能，如准备好的语句。但首先让自己熟悉基础知识。

回答by PhilDin

One big advantage of PDO is platform independence. This means that you can migrate to a different DBMS at some point without having to recode all of your function calls. This is how things are typically done in Java (via JDBC), .Net (ADO) and most other environments. The advantage is not just that you can switch DBMS per se, it's also that you have only one API to learn.

PDO 的一大优势是平台独立性。这意味着您可以在某个时候迁移到不同的 DBMS，而无需重新编码所有函数调用。这是在 Java（通过 JDBC）、.Net (ADO) 和大多数其他环境中通常完成的事情。优点不仅在于您可以切换 DBMS 本身，而且您只需学习一个 API。

As regards your question, the PDO layer provides the facility to do prepared statements. The idea behind prepared statements is that you create placeholders for the parts of your SQL statement that will not be known until run time. Many learners start off by creating SQL as a string which gets executed by calling mysqli::query($someQuery). This is problematic for many reasons, most prominent of which is the vulnerability to SQL injection (see stackoverflow.com/questions/5315351 for a similar question and answer). With PDO, you can avoid SQL injection and all of the problems of handling characters such as quotes, backslashes etc. The end result is that your code is more secure, readable and predictable.

至于你的问题，PDO 层提供了做准备好的语句的工具。准备好的语句背后的想法是为 SQL 语句中直到运行时才知道的部分创建占位符。许多学习者首先将 SQL 创建为字符串，该字符串通过调用 mysqli::query($someQuery) 来执行。这是有问题的，原因有很多，其中最突出的是 SQL 注入漏洞（有关类似的问答，请参阅 stackoverflow.com/questions/5315351）。使用 PDO，您可以避免 SQL 注入以及处理引号、反斜杠等字符的所有问题。最终结果是您的代码更加安全、可读和可预测。

If you've already figured out how to use mysqli then using PDO is not much different. The linked question and answer above shows an example of a query being submitted using PDO prepared statements which should act as a useful guide.

如果您已经弄清楚如何使用 mysqli，那么使用 PDO 并没有太大的不同。上面链接的问题和答案显示了使用 PDO 准备好的语句提交的查询示例，该示例应该作为有用的指南。

回答by cDecker32

Coming from the same point of view as you. From my perspective I don't think the difference is truly noticeable (depending on what you're using it for). It looks like PDO is simply a database api that merges ALL of the other database api's into one. So if you needed to connect to a MS Sql server and MySQL server, you could simply call on the PDO api and specify the driver for the specific db. My guess is also that any future features and abilities in MySQL will be only available in PDO. So basically just use PDO to ensure that you have access to all the latest features.

来自和你一样的观点。从我的角度来看，我认为差异并不明显（取决于您使用它的目的）。看起来 PDO 只是一个数据库 api，它将所有其他数据库 api 合并为一个。因此，如果您需要连接到 MS Sql 服务器和 MySQL 服务器，您只需调用 PDO api 并为特定数据库指定驱动程序。我的猜测也是 MySQL 中的任何未来特性和能力将仅在 PDO 中可用。所以基本上只使用 PDO 来确保您可以访问所有最新功能。

回答by Your Common Sense

So what is the difference between PDO, prepared statements and MySQLi, are they different features that accomplishes the same task?

那么PDO、准备好的语句和MySQLi有什么区别，它们是完成相同任务的不同功能吗？

The difference is fairly simple.
PDO is usablewith prepared statementsand mysqli is not.

区别相当简单。
PDO是使用与准备好的语句和mysqli的是不是。

Just run some usual queries with both API using native prepared statements, and you will clearly see the difference.

只需使用本机准备好的语句对这两个 API 运行一些常见的查询，您就会清楚地看到差异。