The difference and choice between php_mysql, Php_mysqli and Pdo_mysql

PHP and MySQL connection has three kinds of API interface, namely: PHP mysql extension, php mysqli extension, PHP data Object (PDO), below for the above three kinds of connection methods to do a summary, in order to choose the best solution under different scenarios.  I. Characteristics and comparisons

• php's MySQL extension is designed to allow PHP applications with Mysql interaction. The MySQL extension provides a process-oriented interface and is designed for MySQL4.1.3 or earlier versions. Therefore, this extension can interact with MySQL4.1.3 or the updated database service side, but does not support some of the features provided by the late MySQL service side. Because it is too old and unsafe, it has been completely replaced by the later mysqli.
• php mysqli extension, which we sometimes call MySQL enhanced extension, can be used to New advanced features in MySQL4.1.3 or later. Its features are: Object-oriented interface, prepared statement support, multi-statement execution support, transaction support, enhanced debugging capability, embedded service support, preprocessing method completely solved the sql injection . However, it also has the disadvantage of only supporting MySQL database. This is certainly the best option if you don't operate other databases.
• PDO is the abbreviation for PHP Data objects, which is a database abstraction layer specification in PHP applications. PDO provides a unified API interface that allows your PHP application to not care about the type of database server system you want to connect to. In other words, if you use the PDO API, you can seamlessly switch database servers whenever you need them, such as from Oracle to MySQL, and you just need to modify very little PHP code. It functions similar to interfaces such as JDBC, ODBC, DBI, and so on. Similarly, it solves the SQL injection problem and has good security. However, he also has shortcomings, some multi-statement execution query is not supported (but this situation is very small).

frustration has also made a list of comparisons between the three of them:

PHP mysqli Extensions	PDO (using PDO MySQL driver and MySQL native driver)	mysql extension for PHP
Introduction of PHP Version	5.0	5.0	3.0 ago
Does the php5.x contain	Is	Is	Is
MySQL Development status	Active	Active in the PHP5.3	Maintenance only
Recommended use in MySQL new project	Recommendation-Preferred	Suggestions	Not recommended
Character set support for the API	Is	Is	Whether
Support for server-side prepare statements	Is	Is	Whether
Support for client Prepare statements	Whether	Is	Whether
Stored Procedure Support Scenarios	Is	Is	Whether
Multi-statement execution support scenarios	Is	Most	Whether
Do you support all MySQL4.1 features above	Is	Most	Whether

MySQL Extensions for PHP (pros and cons)

Design and development allows an early extension of PHP applications to interact with the MySQL database. The MySQL extension provides a process-oriented interface;

and is designed for MySQL4.1.3 or earlier versions. Thus, although this extension can be associated with MySQL4.1.3 or newer numbers;

The library server, but does not support some of the features provided by the post-MySQL server.

PHP mysqli Extensions

Mysqli extensions, which we sometimes call MySQL enhanced extensions, can be used for new advanced features in MySQL4.1.3 or newer versions;

Mysqli extensions are included in PHP 5 and later;

MYSQLI Extension has a series of advantages, compared to the MySQL extension is mainly: object-oriented interface, prepared statement support, multi-statement execution support, transaction support, enhanced debugging capabilities, embedded service support.

PHP Data Objects (PDO)

PHP Data Objects are a Database abstraction layer specification in PHP applications. PDO provides a unified API interface so that your PHP application does not care about the type of database server system you want to connect to. In other words, if you use the PDO API, you can seamlessly switch the database server whenever you need to.

Here are a few of the ways I've done this week for database connections:

MySQL Connection:

<?php$conn = @ mysql_connect ("localhost", "root", "") or Die ("Database connection Error"), mysql_select_db ("BBS", $conn); mysql_query (" Set names ' UTF8 '); echo "Database connection succeeded";? >

Mysqli Connection:

<?php$conn = Mysqli_connect (' localhost ', ' root ', ' ', ' BBS '), if (! $conn) {die ("Database connection Error". Mysqli_connect_error ());} Else{echo "database connection succeeded";}? >

PDO Connection:

<?phptry{$pdo =new PDO ("Mysql:host=localhost;dbname=bbs", "Root", "");} catch (Pddexception $e) {echo "Database connection error";} echo "Database connection succeeded";? >

Here are the differences between the three ways I see MySQL, mysqli, and PDO on the website

*************************************************************************************************************** ******************
Php-mysql is the most primitive Extension of PHP operation MySQL Database, php-mysqli I represents improvement,

In addition to the relative advanced function, in terms of Extension, it also adds security.

The PDO (PHP Data Object) provides a abstraction Layer to manipulate the database

1.mysql and Mysqli

Mysqli is a new library of functions provided by PHP5, and (i) represents an improvement that executes faster. Of course it's safer.

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

MySQL each link will open a connected process and mysqli multiple runs mysqli will use the same connection process, thereby reducing the cost of the server some friends use new mysqli when programming (' localhost ', usenamer ', ' Password ', ' databasename ');

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

Mysqli class is not PHP comes with it?

Not by default, win under to change php.ini, remove Php_mysqli.dll before, Linux to put Mysqli in.

One: Mysqli.dll is a database that allows the operation of objects in a way or process, and it is easy to use. Here are a few often

<?phpmysql_connect ($db _host, $db _user, $db _password); mysql_select_db ($dn _name); $result = mysql_query ("select" Name ' from ' The Users ' WHERE ' location ' = ' $location ' ") and while ($row = Mysql_fetch_array ($result, MYSQL_ASSOC)) {echo $row [' Nam E '];} Mysql_free_result ($result);? >

In fact, some knowledge behind ... This method can not Bind Column, the previous example of SQL narration, $location Place is easy to be SQL injection. Then the mysql_escape_string (note: 5.3.0) and mysql_real_escape_string () were developed to solve the problem, but the whole narrative would become complex and ugly, and if there were more columns, You can see what the situation is ...

<?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, except through Bind Column to solve the above problems, but also Transaction, Multi Query,

It also provides an Object oriented style (the php-mysqli example below) and procedural style

<?php$mysqli = new Mysqli ($db _host, $db _user, $db _password, $db _name); $sql = "INSERT into ' users ' (ID, name, gender, Lo cation) VALUES (?,?,?,?) "; $stmt = $mysqli->prepare ($sql) $stmt->bind_param (' dsss ', $source _id, $source _name, $source _gender, $source _ $stmt->execute (), $stmt->bind_result ($id, $name, $gender, $location), while ($stmt->fetch ()) {echo $id. $name. $gender. $location;} $stmt->close (); $mysqli->close ();? >

But see here and found some shortcomings, such as Bind Result, this is a little bit more, but it does not matter, because the biggest problem is that this is not an abstract (abstraction) method, so when the backend to change the database, it is the beginning of pain ... And then PDO appeared.

2.PDO and MySQL

PDO was supported after PHP5.1, and he used a consistent interface for accessing the database. But many of the country's open source programs are

Use the function provided by MySQL extension to connect to the database for querying. PDO is powerful why is the domestic mature PHP system not used?

Asked a few friends why with PDO, the answer is "fast", PDO connection database will be fast? Why use PDO? What is the difference between the two ways? First of all, it is more concerned with performance issues. wrote 1 script tests to insert 1 million data into MySQL.

<?php$link = mysql_connect ("localhost", "root", "root") or Die (' MySQL connect error '); $num = 100000; $dsn = "mysql:host= 127.0.0.1;dbname=performace_test "; $db = new PDO ($dsn, ' root ', ' root ', array (pdo::attr_persistent = true)); Mysql_  Query (' TRUNCATE TABLE ' performace_test '. ' Myquery ', $link); Truncate table$query = "INSERT into ' performace_test '. ' Myquery ' (' goods_id ', ' cat_id ', ' click_count ', ' Goods_number ', ' Goods_weight ', ' goods_sn ', ' goods_name ', ' Goods_reason ', ' brand_name ', ' goods_thumb ', ' brand_id ', ' is_on_sale ', ' WAP_ ' Cod ', ' wap_title ', ' wap_detail ', ' wap_flag ', ' wap_onsale ', ' shop_price ', ' cost_price ', ' channel_rate ', ' Channel_onsale ' ', ' add_time ', ' is_main ', ' last_update ', ' Brand_logo ') VALUES (' 80′, ' 298′, ' 65′, ' 100′, ' 0.125′, ' smt000080′, ' health, ', ' Health ", ' images/201004/thumb_img/80_thumb_g_1272071721054.jpg ', ' 1′, ' 0′, ' 0′,null,null,null, ' 0′, ' 2980.00′, ' 0.00′, ' 1.250000′, ' 1′, ' 1271612064′, ' 0′, ' 1297624384′, ' 1293649512083026412.jpg ') "; $start _time = Microtime (True); for ($i = 0; $i < $num; $i + +) {mysql_query ($querY, $link);} echo "Use MySQL extension:".  (Microtime (True)-$start _time); mysql_query (' TRUNCATE TABLE ' performace_test '. ' Myquery ', $link); Truncate Table$start_time = Microtime (True); for ($i =0; $i < $num; $i + +) {$db->exec ($query);} echo "\r\nuse PDO:". (Microtime (True)-$start _time);? >

Use MySQL extension:95.233189106s

Use pdo:99.1193888187s

There's almost no difference in linking MySQL. The performance loss of PDO can be completely negligible.

But there are a lot of things that the MySQL extension library does not have:

1:pdo real-to-bottom implementation of the unified Interface number Library operation interface

2:PDO supports more advanced DB feature operations, such as the scheduling of stored procedures, which are not supported by MySQL native libraries.

3:pdo is the official PHP pecl library, compatibility stability is necessarily higher than the MySQL Extension, you can directly use the PECL Upgrade PDO command upgrade.

PHP6 The default is to use PDO for database links, MySQL extension will be used as a secondary. So in our daily projects, if the environment permits, use PDO as much as possible to perform MySQL database operations.

The difference and choice between php_mysql, Php_mysqli and Pdo_mysql