Take you to understand MySQLi in PHP in three minutes

Starting from this article, we learn the MySQLi extension. It can be said that the MySQL (original) extension is the introductory tutor for many of us to connect to the database when we first start learning PHP. Now if you want to use procedural code to operate the database, you can only use the mysqli extension.

We have come to an end with the study of PDO. Starting from this article, we continue to learn another MySQL extension, which is the core MySQLi extension besides PDO. Arguably its ancestor, the MySQL (original) extension, was the go-to guide for connecting to databases when many of us first started learning PHP. However, as times change, the MySQL (original) extension has been completely abandoned in PHP7. Now if you want to use procedural code to operate the database, you can only use the mysqli extension. Of course, the mysqli extension also supports object-oriented writing.

What is MySQLi

MySQLi extension allows us to access the functions provided by MySQL4.1 and above versions of the database. It is specifically targeted at the MySQL database, unlike PDO which can connect to different databases through different dns.

Differences and connections with MySQL and PDO

First of all, let’s review the earliest MySQL extension.

• Only for procedures

• Does not support stored procedures, multi-statement execution, and prepared statements

• PHP7 has been deleted and is not supported at all

Then PDO

• Only supports object-oriented use

• Can connect to a variety of databases, switching databases will bring less changes, and you may not even need to modify the code

• Supports stored procedures, multi-statement execution, and prepared statements

The last is MySQLi.

• Supports both object-oriented and process-oriented writing methods

• Only supports MySQL database

• Supported Stored procedures, multi-statement execution, prepared statements

• Follow the version updates of PHP and MySQL, and can support more MySQL advanced features more quickly

Judging from the characteristics of the three of them, the MySQL (original) extension is definitely not recommended. Even for old projects, as long as it supports the PHP version of PDO or MySQLi, you should consider converting the database connection to this One of two ways. If you are still learning the use of MySQL (original) extensions in a PHP5 environment, you can put it down.

As for the choice between PDO and MySQLi, it is a matter of opinion. In fact, there is not much difference between them, but modern large-scale frameworks will basically encapsulate PDO as the default database connection. After all, its portability can facilitate these general frameworks to connect to different databases. In some small frameworks or projects, MySQLi can still be seen.

Of course, niche does not mean bad. Just like in daily development, we rarely use other databases in the PHP environment, so we can completely use a set of MySQLi to operate in our own small projects. The database is more convenient and faster. At the same time, if the old project wants to switch to the PHP7 version, if the MySQL (original) connected database was used before, the MySQL (original) code can be quickly replaced with MySQLi.

Extension installation and things to note about MySQL8

The MySQLi extension is released together with the PHP source code. We just add --with-mysqli when compiling PHP. The default database driver now uses mysqlnd, and libmysql has been basically eliminated. Therefore, there is no need to add other parameters when compiling, just compile directly.

You need to pay attention when connecting to MySQL8, because the MySQL8 server will use caching_sha2_password as password encryption by default. MySQLi in versions prior to PHP7.2.4 will use mysql_native_password to encrypt the connection password, which will result in the inability to connect to the database. You can modify the my.cnf file and set default_authentication_plugin=mysql_native_password to let MySQL8 also use mysql_native_password to encrypt user passwords.

Process-oriented

As mentioned above, MySQLi supports two writing methods, namely object-oriented and process-oriented. A simple understanding is that one way of writing is MySQL (original) extension, and the other way of writing is similar to PDO. Let’s first look at process-oriented writing.

$mysqli = mysqli_connect("localhost", "root", "", "blog_test");
$res = mysqli_query($mysqli, "SELECT * FROM zyblog_test_user");
$row = mysqli_fetch_assoc($res);
print_r($row);

Do you feel that it is really convenient to transplant the MySQL (original) extension code? Just change all the method names to mysqli_xxx.

Object-oriented

Object-oriented is a bit like PDO. We need to first obtain a connection handle class, and then operate this class.

$mysqli = new mysqli("localhost", "root", "", "blog_test");
$res = $mysqli->query("SELECT * FROM zyblog_test_user");
$row = $res->fetch_assoc();
print_r($row);

面向对象和面向过程混用

另外，这两种方式还可以混合使用，不过并不推荐。混合起来使用的话很容易让看代码的人晕头转向。所以，最好还是在一个项目中就坚持使用一种方式。

$mysqli = new mysqli("localhost", "root", "", "blog_test");
$res = mysqli_query($mysqli, "SELECT * FROM zyblog_test_user");
$row = $res->fetch_assoc();
print_r($row);

在这段代码中，我们实例化了一个 mysqli 对象，然后使用面向过程的 mysqli_query() 函数来执行语句，接着又使用面向对象的方式来获取结果集。是不是很乱？但是它是可以正常运行的。

总结

从上面的内容中可以看出，PDO 的特点是支持多种不同类型的数据库，就像 Java 中的 JDBC 一样。而 MySQLi 虽然只支持 MySQL 数据库，但它却可以同时支持面向对象和面向过程两种写法。是我们针对老项目代码进行升级优化的好帮手。同时，它还是现在入门 PHP 学习相关数据库操作的首选。在接下来的文章中，我们将默认只使用 面向对象 式的写法来继续学习 MySQLi 扩展相关的知识。

测试代码：

https://github.com/zhangyue0503/dev-blog/blob/master/php/202009/source/4.PHP中的MySQLi扩展学习（一）MySQLi介绍.php

推荐学习：php视频教程

The above is the detailed content of Take you to understand MySQLi in PHP in three minutes. For more information, please follow other related articles on the PHP Chinese website!