The difference between Mysql,mysqli and PDO

Introduced:

• Php-mysql is the most primitive Extension of PHP operation MySQL Database
• The php-mysqli I represents improvement, which raises the relative advanced function, and in Extension, it also increases security.

• PDO is supported after PHP5.1, and he uses a consistent interface for accessing the database, and there are a lot of operations 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, the compatibility stability is necessarily higher than the MySQL Extension, you can directly use the PECL Upgrade PDO command upgrade

It is recommended to use PDO for database operations, MySQL extension as an aid.

Php-mysql is the most primitive PHP operation MySQL Extension , php-mysqli I for improvement, the more advanced phase of the function, in Extension, itself also increased security. The PDO (PHP Data Object) is provided with a abstraction Layer to manipulate the repository, it is not really see what is the difference, so just read the program directly ...

First, let's take a look at a code written in Php-mysql, a sample of which is commonly used around the world:

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

At first glance there is no problem, but in fact there are some academic questions ...

This is not a way to Bind Column, as the SQL in the previous example says, $location place is susceptible to SQL injection.

And then it's on display. Mysql_escape_string () (Note: 5.3.0 discard) and mysql_real_escape_string () to solve this problem, the whole of the narrative will become complex and ugly, and if the linked fields is much more, Can think of how 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 is a lot of progress in Php-mysqli , except through the Bind Column to solve the problem, but also Transaction, Multi Query, and at the same time provide the Object oriented style (the following PHP-MYSQLI) and procedural style (written in the example above Php-mysql) two ways to write ... Wait a minute

<?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); Here the first parameter identifies the type of the following parameters, D: number, S: Character$stmt-execute (); $stmt->bind_result ($id,$name,$gender,$location);  while($stmt-Fetch ()) {          Echo $id.$name.$gender.$location; }         $stmt-Close (); $mysqli-Close (); ?>

But there are some missing points here, such as Bind Result, which is a bit superfluous, but it's not really necessary, because the biggest problem is that it's not an abstract (abstraction) approach, so when the backend is changed to the repository, it's the beginning of the pain ...

As PDO appears , after the PDO is installed, you can manipulate the repository in the following ways:

<?PHP$dsn= "mysql:host=$db _host;d bname=$db _name"; $DBH=NewPDO ($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, there seems to be no shorter code for PDO, so what's really good?

1. PDO connects to the library by Connection String to determine which repositories to connect to.
2. PDO can determine the setting of the connection through Pdo::setattribute, such as persistent Connection, the way to return the wrong (Exception, e_warning, NULL). Even the size of the linked fields-bit name ... Wait a minute.
2. PDO supports the function of bind Column, in addition to the basic Prepare, Execute, can bind a single linked fields bit, and specify linked fields-bit type.
4. PDO is a abstraction Layer so even replacing the storage medium requires a minimum of effort.

Unfortunately, although these things have been Yi for a long time now, they are still not large enough. I think maybe it's because everyone is used to reading the books in the workshop, but those books tend to be the simplest and most traditional way of learning. Many people are still using MySQL to directly connect the repository.

Let's take a note: There's no deep understanding of the use

Pass the DBI to connect to the repository, like ActiveRecord and Propel ORM (object-relational Mapping).

For example, take ActiveRecord as an example, if you want to actually do this SQL in a narrative ...

INSERT INTO `users` (id, name, gender, location) VALUES(1, ‘roga‘, ‘male‘, ‘tpe‘)

To use PDO to write is:

<? PHP          $sql = "INSERT into ' users ' (ID, name, gender, location) VALUES (?,?,?,?)" ;       $sth $dbh->prepare ($sql);          $sth->execute (array(1, ' Roga ', ' Male ', ' TPE '));     ? >  

But in ActiveRecord, it is:

<? PHP          $user New User ();          $user->id = 1;       $user->name = ' Roga ';       $user->gender = ' male ';       $user->location = ' TPE ';          Save ($user);     ? >  

The latter is a lot more succinct in the language, and also greatly reduces the dependency on SQL language!

(Active record (Chinese name: Activity Records) is a domain model pattern, characterized by a model class corresponding to a table in the relational database , and one instance of the model class corresponding to a row of records in the table.

The Active record is very similar to row Gateway (row record entry), but the former is a domain model and the latter is a data source schema.

Relational databases often express entity relationships through foreign keys, and the active Record also maps this relationship as an object's association and aggregation at the data source level. ）

The difference between Mysql,mysqli and PDO