Deep analysis of the differences between MySQL and mysqli

First, two functions are used to handle database operations.

The MYSQLI connection is a permanent connection, and MySQL is a non-persistent connection.

The MySQL connection will reopen a new process whenever it is used for the second time, and Mysqli will only use the same process, which can greatly reduce server-side pressure.

Second, mysqli encapsulates some advanced operations, such as transactions, while encapsulating many of the methods available during database operations.

Specific View Http://cn.php.net/mysqli

In actual use, more mysqli transactions are used.

Example:

code example:

$mysqli = new mysqli (' localhost ', ' root ', ' ', ' db_lib2test ');

$mysqli->autocommit (false);/Start Things

$mysqli->query ($sql 1);

$mysqli->query ($sql 2);

if (! $mysqli->errno) {

$mysqli->commit ();

echo ' OK ';

}else{

echo ' err ';

$mysqli->rollback ();

}

The second part, PHP on the mysqli and MySQL difference

Php-mysql is PHP operation MySQL database The most original extension, php-mysqli I representative of improvement, to mention the relative advanced function, in the case of extension, itself also increased security. The PDO (PHP data Object) provides a abstraction layer to manipulate the database.

Example:

code example:

<?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);

?>

This is not a way to Bind Column, which, in the case of previous SQL narratives, is easily injection by SQL. $location. The mysql_escape_string () (note: 5.3.0) and Mysql_real_escape_string () were later developed to solve the problem, but the whole narrative would become mixed and ugly, and if there were more fields, Can imagine what would be the situation ...

Example:

code example:

<?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);

?>

There has been a lot of progress in php-mysqli, in addition to solving the problem through bind column, and it also transaction, multi query, and also provides an object oriented style (the following section php-mysq Li example of the writing) and procedural style (above Php-mysql example) two ways to write ... Wait a minute.

Example:

code example:

<?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 ();

?>

But to see that there are some drawbacks, such as bind result, this is a little extra, but it does not matter, because the biggest problem is that this is not an abstract (abstraction) method, so when the back-end to replace the database, it is the beginning of pain ...

So PDO appeared (note: At present, Ubuntu and Debian, PDO does not have a direct suite to install, but must be installed through the pecl).

code example:

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

When installed through the PECL installation, the database can be manipulated in the following ways:

code example:

<?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 seems to be no shorter, so what is the benefit?

1. PDO connects to the database by connection string to determine which database to connect to.

2. PDO can pdo::setattribute to determine the connection settings, such as persistent connection, return the wrong Way (exception, e_warning, NULL). Even the case of the return field name ... Wait a minute.

2. PDO supports the Bind column function, in addition to the basic prepare, execute, can bind a single field, and specify the field type.

4. PDO is a abstraction layer so it takes less effort to replace the storage medium.

It's a pity, huh? These things have been in the pipeline for a long time, but they are not popular enough. I think it may be because people are accustomed to reading the books in the book, but those books tend to introduce the simplest and most traditional ways. This leads many people to use MySQL as a direct link to the database.

The favorite is to connect to the database through DBI, such as ActiveRecord and Propel ORM (object-relational mapping).

For example, take ActiveRecord as an example, if you want to implement such a SQL narration ...

Insert INTO ' users ' (ID, name, gender, location) values (1, ' Roga ', ' Male ', ' TPE ')

The PDO to write is:

code example:

<?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:

code example:

<?php

$user = new User ();

$user->id = 1;

$user->name = ' Roga ';

$user->gender = ' male ';

$user->location = ' TPE ';

$user->save ();

?>

The latter is not a lot more concise in grammar, but also significantly reduce the dependence on the SQL language! (For SQL implementation problems with different repositories, refer to Comparison of different SQL implementations)

The above is some simple introduction, if there are omissions fallacy also welcome everyone to add.

MySQL is a non-holding relay function and MYSQLI is a permanent connection function. Other words

MySQL each link will open a connected process and mysqli run mysqli multiple times will use the same connection process, thereby reducing the cost of the server

Some friends use new mysqli (' localhost ', usenamer ', ' Password ', ' databasename ') when they are programmed;

Wrong, Fatal Error:class ' mysqli ' not found in D: ...

is the Mysqli class not PHP-led?

Not the default to open, win under to change php.ini, remove Php_mysqli.dll before, Linux to the mysqli compiled in.

One: Mysqli.dll is a way to manipulate the database in an object-or-process manner, and it is easy to use. Here is a comparison of several common operations with Mysql.dll.

1, Mysql.dll (can be understood as a functional approach):

code example:

$conn = mysql_connect (' localhost ', ' user ', ' password '); Connecting to the MySQL database

mysql_select_db (' data_base '); Select Database

$result = mysql_query (' select * from Data_base ');//Here is a second optional parameter that specifies an open connection

$row = Mysql_fetch_row ($result))//For simplicity, only one row of data is taken here

echo $row [0]; Output the value of the first field

Mysqli also has a process-style approach, but it starts with a mysqli prefix, all the same. If Mysqli is manipulated in a procedural way, some functions must specify resources, such as mysqli_query (resource identification, SQL statement), and resource identifier parameters are placed in front, while mysql_query (SQL statement, ' optional ') resource identification is placed behind , and can be unspecified, which defaults to the last open connection or resource.

2, Mysqli.dll (object mode):

code example:

$conn = new mysqli (' localhost ', ' user ', ' password ', ' data_base ');

The connection here is new, and the last parameter is to specify the database directly, without mysql_select_db ().

can also be constructed without specifying, and then $conn-> select_db (' data_base ')

$result = $conn-> query (' SELECT * from Data_base ');

$row = $result-> fetch_row (); Fetch a row of data

Echo Row[0]; Output the value of the first field

II, Mysql_fetch_row (), mysql_fetch_array ()

These two functions return an array, the difference being that the first function returns an array that contains only the values we can only $row[0],

$row [1], which reads the data as an array subscript, and mysql_fetch_array () returns an array that contains both the first and key values

In the form of a pair, you can read the data in this way (if the database field is USERNAME,PASSWD):

code example:

$row [' username '], $row [' passwd ']

Furthermore, if you use ($row as $kay => $value), you can also get the field name of the database directly.

More important, Mysqli is a new library of functions provided by PHP5, and (i) represents an improvement that performs faster.