Some knowledge analysis on the difference between mysqli and mysql in php
- 2020-05-12 06:17:07
- OfStack
1:
PHP-MySQL is the original Extension for PHP to operate MySQL database. i of PHP-MySQLi stands for Improvement, which improves the relatively advanced functions. In the case of Extension, it also increases the security. While PDO (PHP Data Object) provides an Abstraction Layer to operate the database, it doesn't really make any difference in terms of what you're talking about, so just look at the program...
First, let's look at a piece of code written in PHP-MySQL. Examples of this type are commonly used around the world:
At first glance it doesn't seem like a problem, but there's some science behind it...
This approach cannot be Bind Column. For example, in the case of SQL, $location is easily SQL Injection. mysql_escape_string() (note: not used after 5.3.0) and mysql_real_escape_string() were developed to solve this problem, but this way, the whole narrative would become ugly and confusing, and you can imagine what would happen if there were too many fields...
In addition to Bind Column to solve the above problems, Transaction, Multi Query are also provided with Object oriented style (the PHP-MySQLi example below) and Procedural style (the PHP-MySQL example above). And so on.
This is a bit redundant, but it doesn't really matter, because the biggest problem is that it's not an abstract (Abstraction) method, so when the back end changes the database, it's the beginning of the pain...
This is where PDO comes in (note: for Ubuntu and Debian, PDO does not have a direct kit to install, but must be installed via PECL).
Once installed through PECL, the database can be operated in the following ways:
At first glance, PDO's code doesn't seem to be any shorter, so what are the benefits?
1. When PDO connects to a database, use Connection String to determine which database to connect to.
2. PDO can determine the setting of the connection via PDO::setAttribute, such as Persistent Connection, return the wrong way (Exception, E_WARNING, NULL). Even the case of the return field name... And so on.
2. PDO supports Bind Column. In addition to the basic Prepare, Execute, Bind can also have a single column and specify the column type.
4. PDO is Abstraction Layer, so even if you change the storage medium, it takes the least effort.
Unfortunately, these things have been around for a long time, but they are still not popular enough. I think maybe it's because people are used to reading books, but those books tend to introduce the simplest and most traditional ways. As a result, many people still use MySQL to connect directly to the database.
However, for now, my personal favorite way to connect to databases is through DBI, such as ActiveRecord and Propel ORM(Object-Relational Mapping).
For example, take ActiveRecord as an example. If such an SQL statement is to be realized...
INSERT INTO `users` (id, name, gender, location) VALUES(1, 'roga', 'male', 'tpe')
Written in PDO:
But in the case of ActiveRecord, it is:
The latter is a lot simpler grammatically and significantly less dependent on the SQL language! (for questions on SQL implementation from different databases, please refer to Comparison of different SQL implementations)
The above is a simple introduction, if there are errors and omissions are welcome to add.
mysql is a non-persistent join function and mysqli is an eternal join function. That is to say,
While mysql opens 1 connected process per link, mysqli runs mysqli multiple times using the same 1 connected process, reducing server overhead
Some friends use new mysqli('localhost', 'usenamer', 'password', 'databasename') when programming. Always quote
Wrong, Fatal error: Class 'mysqli' not found in d:\...
Isn't mysqli a class that comes with php?
win should be changed to php.ini, and php_mysqli.dll should be removed from php_mysqli.dll. I'm going to compile mysqli under linux.
1:Mysqli.dll is a database that allows you to manipulate databases as objects or procedures, and it is easy to use. Here is a comparison of several common operations with mysql.dll.
1:mysql.dll:
mysqli also has a procedural approach, but it starts with the mysqli prefix, and the rest is pretty much the same. If mysqli operates in a procedural manner, some functions must specify the resource, such as mysqli_query(resource id,SQL statement), and the resource id parameter is placed first, while mysql_query(SQL statement,' optional ') is placed later and can be unspecified. It defaults to the last open connection or resource.
2mysqli.dll(object mode) :
2: mysql_fetch_row (), mysql_fetch_array ()
Both of these functions return an array. The difference is that the array returned by the first function only contains values, so we can only get $row[0].
$row[1], which reads the data as an array index, and mysql_fetch_array() returns an array that contains both the first and the key values
We can read the data like this (if the database field is username,passwd) :
$row [' username], $row [' passwd]
Also, if you use ($row as $kay = > $value) to get the database field name directly.
More importantly, mysqli is a new library of functions provided by php5. (i) represents an improvement, which can be performed faster.
PHP-MySQL is the original Extension for PHP to operate MySQL database. i of PHP-MySQLi stands for Improvement, which improves the relatively advanced functions. In the case of Extension, it also increases the security. While PDO (PHP Data Object) provides an Abstraction Layer to operate the database, it doesn't really make any difference in terms of what you're talking about, so just look at the program...
First, let's look at a piece of code written in PHP-MySQL. Examples of this type are commonly used around the world:
<?php
mysql_connect($db_host, $db_user, $db_password);
mysql_select_db($dn_name);
$result = mysql_query("SELECT `name` FROM `users` WHERE `location` = '$location'");
while ($row = mysql_fetch_array($result, MYSQL_ASSOC))
{
echo $row['name'];
}
mysql_free_result($result);
?>
At first glance it doesn't seem like a problem, but there's some science behind it...
This approach cannot be Bind Column. For example, in the case of SQL, $location is easily SQL Injection. mysql_escape_string() (note: not used after 5.3.0) and mysql_real_escape_string() were developed to solve this problem, but this way, the whole narrative would become ugly and confusing, and you can imagine what would happen if there were too many fields...
<?php
$query = sprintf("SELECT * FROM users WHERE user='%s' AND password='%s'",
mysql_real_escape_string($user),
mysql_real_escape_string($password));
mysql_query($query);
?>
In addition to Bind Column to solve the above problems, Transaction, Multi Query are also provided with Object oriented style (the PHP-MySQLi example below) and Procedural style (the PHP-MySQL example above). And so on.
<?php
$mysqli = new mysqli($db_host, $db_user, $db_password, $db_name);
$sql = "INSERT INTO `users` (id, name, gender, location) VALUES (?, ?, ?, ?)";
$stmt = $mysqli->prepare($sql);
$stmt->bind_param('dsss', $source_id, $source_name, $source_gender, $source_location);
$stmt->execute();
$stmt->bind_result($id, $name, $gender, $location);
while ($stmt->fetch())
{
echo $id . $name . $gender . $location;
}
$stmt->close();
$mysqli->close();
?>
This is a bit redundant, but it doesn't really matter, because the biggest problem is that it's not an abstract (Abstraction) method, so when the back end changes the database, it's the beginning of the pain...
This is where PDO comes in (note: for Ubuntu and Debian, PDO does not have a direct kit to install, but must be installed via PECL).
roga@carlisten-lx:~$ pecl search pdo
=======================================
Package Stable/(Latest) Local
PDO 1.0.3 (stable) PHP Data Objects Interface.
PDO_4D 0.3 (beta) PDO driver for 4D-SQL database
PDO_DBLIB 1.0 (stable) FreeTDS/Sybase/MSSQL driver for PDO
PDO_FIREBIRD 0.2 (beta) Firebird/InterBase 6 driver for PDO
PDO_IBM 1.3.2 (stable) PDO driver for IBM databases
PDO_INFORMIX 1.2.6 (stable) PDO driver for IBM Informix INFORMIX databases
PDO_MYSQL 1.0.2 (stable) MySQL driver for PDO
PDO_OCI 1.0 (stable) Oracle Call Interface driver for PDO
PDO_ODBC 1.0.1 (stable) ODBC v3 Interface driver for PDO
PDO_PGSQL 1.0.2 (stable) PostgreSQL driver for PDO
PDO_SQLITE 1.0.1 (stable) SQLite v3 Interface driver for PDO
pdo_user 0.3.0 (beta) Userspace driver for PDO
Once installed through PECL, the database can be operated in the following ways:
<?php
$dsn = "mysql:host=$db_host;dbname=$db_name";
$dbh = new PDO($dsn, $db_user, $db_password);
$sql = "SELECT `name`, `location` FROM `users` WHERE `location` = ? , `name` = ?";
$sth = $dbh->prepare($sql);
$sth->execute(array($location, $name));
$result = $sth->fetch(PDO::FETCH_OBJ);
echo $result->name . $result->location;
$dbh = NULL;
?>
At first glance, PDO's code doesn't seem to be any shorter, so what are the benefits?
1. When PDO connects to a database, use Connection String to determine which database to connect to.
2. PDO can determine the setting of the connection via PDO::setAttribute, such as Persistent Connection, return the wrong way (Exception, E_WARNING, NULL). Even the case of the return field name... And so on.
2. PDO supports Bind Column. In addition to the basic Prepare, Execute, Bind can also have a single column and specify the column type.
4. PDO is Abstraction Layer, so even if you change the storage medium, it takes the least effort.
Unfortunately, these things have been around for a long time, but they are still not popular enough. I think maybe it's because people are used to reading books, but those books tend to introduce the simplest and most traditional ways. As a result, many people still use MySQL to connect directly to the database.
However, for now, my personal favorite way to connect to databases is through DBI, such as ActiveRecord and Propel ORM(Object-Relational Mapping).
For example, take ActiveRecord as an example. If such an SQL statement is to be realized...
INSERT INTO `users` (id, name, gender, location) VALUES(1, 'roga', 'male', 'tpe')
Written in PDO:
<?php
$sql = "INSERT INTO `users` (id, name, gender, location) VALUES(?, ?, ?, ?)";
$sth = $dbh->prepare($sql);
$sth->execute(array(1, 'roga', 'male', 'tpe'));
?>
But in the case of ActiveRecord, it is:
<?php
$user = new User();
$user->id = 1;
$user->name = 'roga';
$user->gender = 'male';
$user->location = 'tpe';
$user->save();
?>
The latter is a lot simpler grammatically and significantly less dependent on the SQL language! (for questions on SQL implementation from different databases, please refer to Comparison of different SQL implementations)
The above is a simple introduction, if there are errors and omissions are welcome to add.
mysql is a non-persistent join function and mysqli is an eternal join function. That is to say,
While mysql opens 1 connected process per link, mysqli runs mysqli multiple times using the same 1 connected process, reducing server overhead
Some friends use new mysqli('localhost', 'usenamer', 'password', 'databasename') when programming. Always quote
Wrong, Fatal error: Class 'mysqli' not found in d:\...
Isn't mysqli a class that comes with php?
win should be changed to php.ini, and php_mysqli.dll should be removed from php_mysqli.dll. I'm going to compile mysqli under linux.
1:Mysqli.dll is a database that allows you to manipulate databases as objects or procedures, and it is easy to use. Here is a comparison of several common operations with mysql.dll.
1:mysql.dll:
$conn = mysql_connect('localhost', 'user', 'password'); // The connection mysql The database
mysql_select_db('data_base'); // Select database
$result = mysql_query('select * from data_base');// Here are the first 2 An optional parameter that specifies the open connection
$row = mysql_fetch_row( $result ) ) // So just to keep things simple, let's just take 1 Rows of data
echo $row[0]; // Output the first 1 The value of
mysqli also has a procedural approach, but it starts with the mysqli prefix, and the rest is pretty much the same. If mysqli operates in a procedural manner, some functions must specify the resource, such as mysqli_query(resource id,SQL statement), and the resource id parameter is placed first, while mysql_query(SQL statement,' optional ') is placed later and can be unspecified. It defaults to the last open connection or resource.
2mysqli.dll(object mode) :
$conn = new mysqli('localhost', 'user', 'password','data_base');
// The connection here is new Come out, finally 1 Three parameters specify the database directly, no mysql_select_db() the
// You can also construct without specifying, and then $conn -> select_db('data_base')
$result = $conn -> query( 'select * from data_base' );
$row = $result -> fetch_row(); // take 1 Rows of data
echo row[0]; // Output the first 1 The value of
2: mysql_fetch_row (), mysql_fetch_array ()
Both of these functions return an array. The difference is that the array returned by the first function only contains values, so we can only get $row[0].
$row[1], which reads the data as an array index, and mysql_fetch_array() returns an array that contains both the first and the key values
We can read the data like this (if the database field is username,passwd) :
$row [' username], $row [' passwd]
Also, if you use ($row as $kay = > $value) to get the database field name directly.
More importantly, mysqli is a new library of functions provided by php5. (i) represents an improvement, which can be performed faster.