Which PHP Driver for MySQL should I use
Contents |
[edit] Introduction
PHP includes three "drivers" to connect to the MySQL server:
- MySQL Improved Extension (mysqli, ext/mysqli)
- MySQL Extension (mysql, ext/mysql)
- MySQL PDO driver (PDO_MYSQL)
PHP is a community project. MySQL developers contribute to PHP and help to develop and maintain all of the above three drivers.
[edit] Download
All drivers are contained in the PHP CVS source code repository. Source and binary downloads are available at http://php.net
[edit] Documentation
You can find documentation in the PHP Manual at php.net:
- MySQL Improved Extension (mysqli, ext/mysqli)
- MySQL Extension (mysql, ext/mysql)
- MySQL PDO driver (PDO_MYSQL)
[edit] Make it short: which driver should I use?
Please evaluate the three PHP drivers for MySQL in the following order:
- MySQL Improved Extension (mysqli, ext/mysqli)
- ...
- MySQL PDO driver (PDO_MYSQL)
- ...
- ...
- MySQL Extension (mysql, ext/mysql)
The MySQL Improved Extension - mysqli - is the flagship. It supports all features of the MySQL Server including Charsets, Prepared Statements and Stored Procedures. The driver offers a hybrid API: you can use a procedural or object-oriented programming style based on your preference. mysqli comes with PHP 5 and up. Note that the End of life for PHP 4 is 2008-08-08.
The PHP Data Objects (PDO) are a database access abstraction layer. PDO allows you to use the same API calls for various databases. PDO does not offer any degree of SQL abstraction. PDO_MYSQL is a MySQL driver for PDO. PDO_MYSQL comes with PHP 5. As of PHP 5.3 MySQL developers actively contribute to it. The PDO benefit of a unified API comes at the price that MySQL specific features, for example multiple statements, are not fully supported through the unified API.
Please stop using the first MySQL driver for PHP ever published: ext/mysql. Since the introduction of the MySQL Improved Extension - mysqli - in 2004 with PHP 5 there is no reason to still use the oldest driver around. ext/mysql does not support Charsets, Prepared Statements and Stored Procedures. It is limited to the feature set of MySQL 4.0. Note that the Extended Support for MySQL 4.0 ends at 2008-12-31. Don't limit yourself to the feature set of such old software! Upgrade to mysqli, see also Converting_to_MySQLi. mysql is in maintenance only mode from our point of view.
[edit] Where is the MySQL Native Driver for PHP (mysqlnd) in this list?
mysqlnd is not a new API for PHP users. It operates under the hood of the three above mentioned drivers. mysqlnd is available as of PHP 5.3. mysqlnd is used by default (= automatically) for all of the above mentioned APIs as of PHP 5.3. Read on for details or check the wiki page PHP_MYSQLND.
[edit] Comparison
| MySQL Improved Extension - ext/mysqli | PDO Driver for MySQL - PDO_MYSQLND | Classical MySQL Extension - ext/mysql | |
|---|---|---|---|
| PHP version introduced | 5.0 | 5.0 | before 3.0 |
| Comes with PHP 5.x | yes | yes | yes |
| Comes with PHP 6.0 | yes | yes | yes |
| MySQL activities | active development | active development as of PHP 5.3 | maintenance only |
| MySQL recommended for new projects | yes | yes | no |
| API supports Charsets | yes | yes | no |
| API supports server-side Prepared Statement | yes | yes | no |
| API supports client-side Prepared Statement | no | yes | no |
| API supports Stored Procedures | yes | yes | no |
| API supports Multiple Statement | yes | most | no |
| Supports all of MySQL 4.1+ | yes | most | no |
[edit] What is an API, a driver, a connector and an extension?
If you are familiar with the PHP term "extension" you can skip this section. Same is true if all you wanted was the quick answer given in the previous section. If you want to dig deeper and understand all details but you are not a PHP guru, read the following carefully.
The acronym "API" stands for application programming interface. Developers invoke a piece of software through an interface. An interface consists of functions, methods, classes, constants and so on.
MySQL calls a piece of software, for example a library, a driver or connector if a developer can use it to connect his application to the MySQL server. The terms driver and connector mean the same in MySQL speech. A list of all MySQL connectors can be found at http://www.mysql.com/products/connector/. Each of the drivers listed on the web page provides an API.
The PHP world uses yet another term for "drivers": extensions. PHP consists of a core and optional extensions. The PHP MySQL drivers (mysqli, mysql, PDO_MYSQL) are implemented as extensions (*ext*/mysqli, *ext*/mysql, PDO_MYSQL).
An extension can provide an API to the PHP user or not. Not every extension exports an API to the PHP user. Some extension only functionality accessible for other PHP extensions but not for PHP users. The PDO (PHP Data Objects) extension - *ext*/pdo directory in the PHP source - is such an extension that does not export an API to the PHP user. The MySQL Native Driver for PHP (mysqlnd)] - PHP_MYSQLND - resides in the source directory *ext*/mysqlnd in the PHP source and also does not export an API to the PHP users. More on mysqlnd later.
Get seated. To totally confuse use, I have to add that the equation of MySQL term "driver" = PHP term "extension" for database "drivers" is not the full truth. Technically speaking PDO uses a core-*driver* model. And PDO is database access abstraction layer. The PDO drivers are database "drivers"... here you have the problem. Sometimes a "driver" in PHP speach is called "extension", sometimes (PDO) its called "driver".
[edit] I am a PHP guru, give me details
[edit] Life cycles, end of life
Although you are PHP guru look at the history of the three candidates for a second. ext/mysql is the oldest driver. It has been around even before PHP 3.0 and MySQL 3.23. That's been 10 years in the past around 1998.
ext/mysqli and PDO_MYSQL have been introduced together with PHP 5.0 in 2004. MySQL 4.1 came out in the same year.
Now look at the life cycles:
* PHP 3.0 End of Life - past * PHP 4.0 End of Life - 2008/08/08
* MySQL 3.23 Extended Support Ends - past * MySQL 4.0 Extended Support Ends - 2008-12-31 * MySQL 4.1 Active Support Ends - past * MySQL 4.1 Extended Support Ends - 2009-12-31
Times of ext/mysql are gone. Its a child of the PHP 3.0, MySQL 3.23, MySQL 4.0 generation. Please don't use it for new projects any more. In 2008 you should start with the 5.x versions of PHP and MySQL to get the most of now.
[edit] MySQL activities
MySQL is not actively developing on ext/mysql any more. The addition of the MySQL native driver for PHP (mysqlnd) to ext/mysql did not introduce new API calls to ext/mysql.
We would like to see ext/mysql in maintenance mode only. Due to the huge existing user base it is tricky, if not impossible to remove if from PHP. PHP is a community project. Only the community can decide to remove ext/mysql from future PHP versions or to deprecate it.
ext/mysqli is actively developed. It has been modified to support the MySQL native driver for PHP (mysqlnd). mysqlnd has introduced some small API additions to the flagship driver in 2007/2008.
As of PHP 5.3 MySQL does not only answer technical question of PHP extension developers but actively contributes to the MySQL driver for PDO - PDO_MYSQL. Like the other two drivers PDO_MYSQL supports mysqlnd. PHP_PDO_MYSQLND was the working title of the project to add mysqlnd support to PDO_MYSQL.
[edit] Supported MySQL Servers through the APIs of the drivers
Together with MySQL 4.1 new features have been introduced, among others:
- Charsets
- Prepared Statements
MySQL 5.0 has added:
- Stored Procedures
The new features have caused changes in the API of the MySQL Client Library, the MySQL C-API. PHP extensions - all three drivers are PHP extensions - are written in C. The PHP extensions can use the MySQL Client Library or mysqlnd to connect to the MySQL Server.
The API of ext/mysql has not changed for a long time. Its API does not reflect the additions made to the underlying C library. The API of ext/mysql does not support:
- Charsets
- Prepared Statements
- Stored Procedures
Consequently, ext/mysql limits you to the feature set of MySQL 4.0 ! Whereas both ext/mysqli and PDO_MYSQL give you access to all features of MySQL 4.1 and up. The APIs of ext/mysqli and PDO_MYSQL do support Charsets, Prepared Statements, Stored Procedures and Multi Statements.
The best support gives you ext/mysqli. Its API follows the underlying C library API quite close.
While PDO_MYSQL offers it a lot its in the end limited by the fact that PDO is a database access abstraction layer. PDO uses the same API for all kinds of different databases. It is very tricky to map database specific features to a generic API. But hey, why do you use vendor specific features through an abstraction and break portability?
One such edge-case for the PDO_MYSQL API is mixing statements that return and do not return result sets in one Multi Statement.
[edit] PHPish, hybrid and short APIs
If you look that the three APIs of the three drivers, you will find them to be very different. ext/mysql can be characterized as a very "PHPish" API. ext/mysqli has set new standards in PHP 5.0 and allows you to follow either the procedural or object-oriented programming style. It has a hybird API. PDO's API is rather short and generic.
The three main critics on the ext/mysql API are:
- no access to all MySQL 4.1+ features
- "magic" fallback logic to the last opened handle
- PHPish, API which is bloat with stuff like mysql_select_db()
ext/mysql is using a "magic" fallback logic to the last opened database handle if you do not specify a database handle when running a query using mysql_query(string $query [,resource $link_identifier)] . As you see the $link_identifier - the database handle - is an optional parameter. You can omit it. If you do so, mysql_query() will implicitly use the most recently opened database handle to send the query. One the one hand this is very convenient. On the other hand this can become error prone once your script works with more than one database connection.
The ext/mysqli API is a hybrid API. You can use all function both in an object-oriented and a procedural way. Although the API is feature loaded your code remains rather short when using the object-oriented fashion. Unlike with ext/mysql you always have to explicitly state on what database connection you want to run a query: mixed mysqli_query ( mysqli $link , string $query [, int $resultmode ] ). However, when using the object-oriented variant, the connection is a property of your mysqli object which gives you a signature as short as that of ext/mysql while always having the security not to mix up connections accidently due to hidden "magic": mixed mysqli::query ( string $query [, int $resultmode ] ).
The most compact API is offered by PDO_MYSQL. The price of the compactness is that you might not be able to access each and every MySQL specific feature as it cannot be properly mapped to the generic API of an database access abstration layer.
[edit] What is this MySQL Native Driver for PHP (mysqlnd)
Under the hood, on the C-level, all three PHP extensions use a library to connect to PHP. Upto PHP 5.3 the only suitable C-library to be used by the three drivers was the MySQL Client Library (AKA libmysql).
The MySQL Client Library is a generic C-library. The MySQL Native Driver for PHP (mysqlnd) is the first C-library of any database vendor exclusively optimized for PHP! At compile time you can configure any of the three drivers to use mysqlnd instead of the MySQL Client Library. Support of the MySQL Client Library is still available. mysqlnd is a new option.
mysqlnd is implemented as a PHP extension. You are a guru, because you are reading this. If you are not a PHP guru and you have skipped the section " What is an API, a driver, a connector and an extension? ", read the explanations first.
mysqlnd is contained in every PHP distributed through php.net in the directory ext/mysqlnd of the source tree as of version PHP 5.3. As mysqlnd is part of PHP it makes compiling the three extensions easier. You do not need to install the MySQL Client Library on the build host any more.
mysqlnd is tighly integrated into PHP and highly optimized for PHP. Like ext/mysqli set new standards in the area of the API, mysqlnd is the first to bring "read-only" variables to PHP. In the extreme mysqlnd can save up to 40% memory while not being any slower, but rather faster than the MySQL Client Library.
The use of mysqlnd is transparent for the PHP user. The only limitation is that mysqlnd will not connect to any MySQL Server prior to 4.1.
Read more at PHP_MYSQLND.