Usage and differences of MySQL, mysqli, and PDO in PHP

MySQL extension for PHP Advantages and Disadvantages

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.

MySQL Connection:

<? PHP $conn mysql_connect  die ("Database connection Error"); mysql_select_db $conn ); mysql_query ("Set names ' UTF8 '"); echo"database connection succeeded";? >

Mysqli Connection:

<? PHP $conn Mysqli_connect (' localhost ', ' root ', ' ', ' BBS '); if (! $conn ) {    diemysqli_connect_error());} Else {    echo"database connection succeeded";}? >

PDO Connection:

<? PHP Try {    $pdo=new  PDO ("Mysql:host=localhost;dbname=bbs", "Root", "");} Catch $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 PHP operation MySQL Database The most primitive Extension,php-mysqli i for improvement, mention the relative advanced function, in Extension, itself Added 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. That is, 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 '); always error, 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 the #;,linux to the mysqli compiled into.

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‘"); while($row=Mysql_fetch_array($result,Mysql_assoc)) {    Echo $row[' Name '];}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 mysql_real_escape_string ($user); mysql_real_escape_string ($passwordmysql_query($query);? >

A lot of progress has been made in php-mysqli, except through Bind Column to solve the above problem, but also Transaction, Multi Query, and also provides an Object oriented style (below this paragraph php-mysq Li example) and procedural style

<?PHP$mysqli=NewMysqli ($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 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 domestic open source programs are using the MySQL extension provided by the function connection database, to query. 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=NewPDO ($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); 

Output Result:

 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.

http://blog.csdn.net/u010973051/article/details/50820905

Usage and differences of MySQL, mysqli, and PDO in PHP