Three main APIs for connecting php to the MySQL database server: mysql, mysqli, and pdo

Source: Internet
Author: User
Tags mysql functions
Overview this section briefly introduces the options available when you need to interact with the Mysql database during PHP application development. What is API? An application interface (abbreviated as ApplicationProgrammingInterface) defines classes, methods, functions, variables, and so on... SyntaxHighlighte



This section briefly introduces the options available when the PHP application needs to interact with the Mysql database during the PHP application development process.

What is API?

An Application Interface (abbreviated as Application Programming Interface) defines classes, methods, functions, variables, and so on. all the content you need to call to complete a specific task in your Application. The APIs required for PHP applications to interact with databases are usually exposed through PHP extensions (called by PHP programmers on the terminal ).

APIs can be process-oriented or object-oriented. For process-oriented APIs, we call functions to complete tasks. for object-oriented APIs, we instantiate classes and call methods on the objects obtained after instantiation. For these two interfaces, the latter is usually the first choice, because it is more modern and brings us a good code structure.

Several APIs are available when you build a PHP application that needs to be connected to the MySQL server. This document discusses these available APIs and how to choose the best solution for your application.

What is a connector?

In the MySQL document, the termConnectorIt is interpreted as "a piece of software code that allows your application to connect to the MySQL database server ". MySQL provides connectors in many languages, including PHP.

When your PHP application needs to interact with a database server, you need to write PHP code to complete a series of activities such as "connecting to the database server", "querying the database", and other database-related functions. Your PHP application will use software that provides these APIs, or use intermediate libraries as needed to process the interaction between your application and the database server. This software is generally considered a connector that allows your referenceConnectionTo the database server.

What is a driver?

A driver is a piece of software code designed to interact with a specific type of database server. The driver may call some libraries, such as the MySQL client library or the MySQL Native driver library. These libraries implement the underlying protocols used for interaction with the MySQL database server.

In this example, the PDO (abbreviation of PHP Database Object) Database abstraction layer can be used to drive a variety of specific databases. One of the drivers is the pdo mysql driver, which is the interface with the MySQL server.

Sometimes there is no distinction between connector and driver. In MySQL documentation, the term "driver" is used as a software code that provides specific database parts as a connector package.

What is expansion?

You will find many otherExtension. PHP code is a core and some optional extensions constitute the core functions. MySQL-related extensions of PHP, suchMysqli,MysqlAll are implemented based on the PHP extension framework.

A typical function of extension is to expose an API to PHP programmers and allow them to extend their functions. Of course, some extensions developed based on the PHP Extension Framework do not expose API interfaces to PHP programmers.

For example, the PDO MySQL driver extension does not expose API interfaces to PHP programmers, but provides an interface to the PDO layer at the upper layer.

The term API and the extension describe different things, because the extension may not need to expose an API interface to the programmer.

What is the main API provided in PHP for MySQL?

When you consider connecting to the MySQL database server, there are three main APIs available:

  • MySQL extension in PHP

  • Mysqli extension of PHP

  • PHP Data object (PDO)

Both have their own advantages and disadvantages. The following is a brief introduction to the key aspects of each API.

What is MySQL extension in PHP?

This is an early extension of design and development that allows PHP applications to interact with MySQL databases.MysqlExtension provides a process-oriented interface and is designed for MySQL 4.1.3 or earlier versions. Therefore, although this extension can interact with MySQL4.1.3 or the updated database server, it does not support some features provided by the MySQL server later.


If you are using MySQL4.1.3 or the updated server version,StrongWe recommend that you useMysqliExtension replaces it.


MysqlThe expanded source code is in the PHP extension directory.Ext/mysql.

ForMysqlFor more information about extensions, see MySQL.

What is the mysqli extension of PHP?

MysqliExtension, which is often called MySQLEnhancedExtension, which can be used to use advanced features in MySQL4.1.3 or later versions.MysqliExtensions are included in PHP 5 and later versions.

MysqliExpansion has a series of advantages overMysqlMajor improvements to scaling include:

  • Object-oriented interface

  • Supported prepared statements)

  • Multi-statement execution support

  • Transaction Support

  • Enhanced debugging capability

  • Embedded Service Support



If you use MySQL4.1.3 or the latest version,StrongWe recommend that you use this extension.


It provides an object-oriented interface and a process-oriented interface.

MysqliExtension is built using the PHP Extension Framework. its source code is under the PHP source code directory.Ext/mysqli.

ForMysqliFor more information about extensions, see Mysqli.

What is PDO?

PHP Data objects are a database abstraction layer specification in PHP applications. PDO provides a unified API so that your PHP application does not care about the type of the database server to be connected. That is to say, if you use the pdo api, you can seamlessly switch the database server as needed, for example, from Firebird to MySQL, you only need to modify a few PHP code.

Examples of other database abstraction layers include JDBC in Java applications and DBI in Perl.

Of course, PDO also has its own advantages, such as a clean, simple, and portable API, its primary drawback is that it will limit your inability to use all the advanced database features provided by the MySQL server later. For example, PDO does not allow MySQL-supported multi-statement execution.

PDO is implemented based on the PHP Extension Framework. its source code is in the PHP source code directory.Ext/pdo.

For more information about PDO, see PDO.

What is a PDO MySQL drive?

The MySQL driver of PDO is not a set of APIs, at least from the perspective of PHP programmers. In fact, the MySQL driver of PDO is in its own lower layer and provides specific Mysql functions. Programmers directly call the pdo api, while PDO uses the PDO MySQL driver to complete interaction with the MySQL server.

The MySQL driver of PDO is one of the many PDO drivers. Other available PDO drivers include Firebird and PostgreSQL.

The MySQL driver of PDO is implemented based on the PHP extension framework. Its source code is in the PHP source code directoryExt/pdo_mysql. It does not expose APIs to PHP programmers.

For more information about MySQL extensions of PDO, see MySQL (PDO ).

What is the MySQL Native driver of PHP?

To interact with the MySQL database server,MysqlExtension,MysqliExtended, the PDO MySQL driver uses the underlying library that implements the necessary protocol. Previously, only the MySQL client library andLibmysql.

However,LibmysqlThe interfaces are not optimized for interaction with PHP applications,LibmysqlIt was originally designed for C applications. For this reason, the MySQL Native driverMysqlnd,LibmysqlA modified version of PHP is developed.

Mysql,MysqliAnd the PDO Mysql driver can be configured and used separately.LibmysqlOrMysqlnd.MysqlndAs a library specially designed for PHP systems, it compares both memory and speed.LibmysqlIt is greatly improved. We hope you will try these improvements.


The MySQL Native driver can only be used in MySQL server version 4.1.3 or later.


The MySQL Native driver is implemented based on the PHP extension framework. The source code is located in the PHP source code directoryExt/mysqlnd. It does not expose interfaces to PHP programmers.

Feature comparison

The following table compares the functions of three major MySQL connection methods in PHP:

  Mysqli extension of PHP PDO (using the PDO MySQL driver and MySQL Native driver) Mysql extension in PHP
Introduced PHP version 5.0 5.0 Before 3.0
Whether PHP5.x contains Yes Yes Yes
MySQL development status Active Active in PHP5.3 Maintenance only
Recommended usage in new MySQL projects Suggestion-preferred Suggestions Not recommended
API character set support Yes Yes No
Support for server prepare statements Yes Yes No
Support for client prepare statements No Yes No
Stored Procedure support Yes Yes No
Multi-statement execution support Yes Majority No
Whether all functions above MySQL4.1 are supported

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.