002 - Differences and choices between PDO and MySQLi

When using PHP to access the database, in addition to the database driver that comes with PHP, we generally have two better options: PDO and MySQLi . In the actual development process, to decide which one to choose, you must first have a relatively comprehensive understanding of both. This article analyzes their differences and compares them in terms of multi-database type support, stability, performance, etc.

12 different driversMySQL onlyOOPOOP proceduralEasyEasyYesNoYesYesYesNo##PerformanceStored procedures

1. Connection

##

// PDO$pdo = new PDO("mysql:host=localhost;dbname=database", 'username', 'password'); 
// mysqli, procedural way$mysqli = mysqli_connect('localhost','username','password','database'); 
// mysqli, object oriented way$mysqli = new mysqli('localhost','username','password','database');

2. API support

Both PDO and MySQLi provide API in object-oriented form, but at the same time MySQLi also provides process-oriented API. The format is easier to understand for novices. If you are familiar with the native PHP mysql driver, you will find that it is easy to use the MySQLi interface to replace the original data access. The advantage of using PDO is that PDO supports multiple databases, while MySQLi only supports MySQL. Once you master it, you can use and connect multiple databases as you like.

3. Database support

The biggest advantage of PDO over MySQLi is that PDO supports many kinds of databases, while MySQLi only supports MySQLi. To see which databases PDO supports, use the following code:

var_dump(PDO::getAvailableDrivers());

What are the benefits of supporting multiple databases? When your program wants to change from mysql to sql server or oracle in the future, the advantages of PDO will be reflected, because changing the database is transparent to the program interface, and the php code changes are very small. If you are using MySQLi, then all users Everywhere in the database has to be rewritten, so I can only make such changes.

4. Named parameters support

PDO named parameters and parameter binding:

$params = array(':username' => 'test', ':email' => $mail, ':last_login' => time() - 3600);     
$pdo->prepare('
    SELECT * FROM users
    WHERE username = :username
    AND email = :email
    AND last_login > :last_login');     
$pdo->execute($params);

And MySQLi parameter binding:

$query = $mysqli->prepare('
    SELECT * FROM users
    WHERE username = ?
    AND email = ?
    AND last_login > ?');     
$query->bind_param('sss', 'test', $mail, time() - 3600);$query->execute();

We can see from the above comparison that PDO binds values ​​through named parameters, while MySQLi’s parameter binding is Values ​​are bound using the dot point character "?" and strictly in the order of this question mark. In this way, although the code seems not as long as PDO's corresponding name, one disadvantage is that the readability and maintainability are reduced. When the number of parameters is relatively small, it is not noticeable. When the number of parameters increases to more than 10 The case of one or more is more painful. You have to assign values ​​one by one in the order of question marks. If one of them is wrong, the following ones will be wrong.

Unfortunately, MySQLi does not support named parameter binding like PDO.

5. Object Mapping

Database-based development generally reads data from the database and then uses an object to carry the data. Both PDO and MySQLi support object mapping. Suppose there is a User class that has some properties corresponding to the database.

class User {    public $id;    public $first_name;    public $last_name;     
    public function info()
    {        return '#'.$this->id.': '.$this->first_name.' '.$this->last_name;
    }
}

If there is no object mapping , we have to assign values ​​to fields one by one after reading the data, which is very cumbersome.

Please see the code of the two objects below:

##

$query = "SELECT id, first_name, last_name FROM users";     
// PDO$result = $pdo->query($query);$result->setFetchMode(PDO::FETCH_CLASS, 'User'); 
while ($user = $result->fetch()) {    echo $user->info()."\n";
}// MySQLI, procedural wayif ($result = mysqli_query($mysqli, $query)) {    while ($user = mysqli_fetch_object($result, 'User')) {        echo $user->info()."\n";
    }
}// MySQLi, object oriented wayif ($result = $mysqli->query($query)) {    while ($user = $result->fetch_object('User')) {        echo $user->info()."\n";
    }
}

6. Security

Both can prevent sql injection. Let's look at an example first.

$_GET['username'] = "'; DELETE FROM users; /*"

When the value of the username parameter entered by the user is the above value ("'; DELETE FROM users; /*"), if you do not set this value After doing any processing, the user successfully injects the delete statement, and all records in the user table will be deleted.

6.1. Manual escaping

##

// PDO, "manual" escaping$username = PDO::quote($_GET['username']); 
$pdo->query("SELECT * FROM users WHERE username = $username");         
// mysqli, "manual" escaping$username = mysqli_real_escape_string($_GET['username']); 
$mysqli->query("SELECT * FROM users WHERE username = '$username'");

The above uses the functions that come with the PDO and MySQLi APIs to escape the obtained parameter values.

6.2. Prepared statement parameter binding

The following is a more efficient and safe way to bind prepared statement parameters:

// PDO, prepared statement$pdo->prepare('SELECT * FROM users WHERE username = :username');$pdo->execute(array(':username' => $_GET['username'])); 
// mysqli, prepared statements$query = $mysqli->prepare('SELECT * FROM users WHERE username = ?');$query->bind_param('s', $_GET['username']);$query->execute();

7. Performance

Since PDO can support other non-MySQL databases, and MySQLi is specially designed for MySQL, the performance of MySQLi is slightly better than that of PDO. However, PDO and MySQLi are still not as fast as PHP's native MySQL expansion. But this kind of performance comparison actually doesn't mean much, because they are all quite fast. If your program performance requirements are not particularly demanding, all three can satisfy you. As for which one you want to choose, you have to weigh it based on your practical situation.

8. Summary

PDO supports 12 kinds of database drivers and named parameter binding is its biggest advantage. Through the above comparison, I believe you also know what you will use in your own project Which one is connected to the database?

Related recommendations:

001 - Detailed analysis of PDO usage

	PDO	##MySQLi
Database support
API
Connection
Named parameters
Object mapping
Prepared statements (client side)
Fast	Fast
Yes	Yes

The above is the detailed content of 002 - Differences and choices between PDO and MySQLi. For more information, please follow other related articles on the PHP Chinese website!