The usage and difference of MySQL, mysqli and PDO in PHP "original"

for a beginner PHP's own, the database connection has a lot of doubts, from Java to PHP. Database connection changed, previously only know JDBC Connection database, or directly with the framework call, for the PHP database connection method, and its application. Not very understanding, so consulted a lot of information and their actual operation, write down their own a little summary notes.

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:

1234567891011

<?php$conn = @ mysql_connect("localhost", "root", "") or die("数据库连接错误");mysql_select_db("bbs", $conn);mysql_query("set names ‘utf8‘");echo "数据库连接成功";?>

Mysqli Connection:

123456789101112131415

<?php$conn = mysqli_connect(‘localhost‘, ‘root‘, ‘‘, ‘bbs‘);if(!$conn){die("数据库连接错误" . mysqli_connect_error());}else{echo"数据库连接成功";}?>

PDO Connection:

123456789101112131415

<?phptry{$pdo=new pdo("mysql:host=localhost;dbname=bbs","root","");}catch(PDDException $e){echo"数据库连接错误";}echo"数据库连接成功";?>

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

12345678910111213141516171819

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

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 ...

1234567891011

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

123456789101112131415161718192021222324252627

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

1234567891011121314151617181920212223242526272829303132333435363738394041

<?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′,’健康′,”,’健康120’,‘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.

Reprint, please indicate the source and the corresponding link, this article Permanent address: http://blog.it985.com/3987.html

The usage and difference of MySQL, mysqli and PDO in PHP "original"