MySQL which stands for Structured Query Language is an open-source relational database management system (RDBMS). It is owned by Oracle Corporation. MySQL database is used by applications like TYPO3, Word Press, MyBB etc. it is used in many high profile websites such as Google, Twitter, YouTube, Facebook and Flicker. MySQL is basically written in C and C++.
MySQLi extension is a relational database driver used in PHP programming language. It is used to provide an interface with the MySQL database. MySQLi is an improved version of older PHP MySQL driver, offering various benefits. The persistent connection of MySQLi extension provides built-in cleanup handling code.
The cleanup carried out by MySQLi includes Rollback active transactions, Close and drop temporary tables, Unlock tables, Reset session variables, Close handler.
PDO which stands for PHP data objects is a general database abstraction layer with support for MSQL. PDO aims to provide a consistent API. That means when a database system is changed the changes should be minimal to reflect this.
Which is better to use with PHP?
The MySQL functions are procedural and they use manual escaping, whereas MySQLi is a replacement for the MySQL functions with object-oriented and procedural versions. PDO provides prepared statements and significant flexibility in how data is returned.
I recommend using PDO, it is well-designed API and lets to easily move to another database. PDO also supports prepared statements, stored procedures, whilst using the MySQL Driver.
When it comes to database support, PDO leads MySQL. PDO supports twelve different drivers whereas MySQLi supports MySQL only. When you have to switch your project to use another database, PDO makes the process transparent but with MySQLi, you have to rewrite every chunk of code.
PDO has a built-in exception class that handles any kind of problems that may occur when processing database queries with MySQLi we need to manually handle exceptions.
Both MySQLi and PDO are quite fast, but MySQLi performs insignificantly faster in benchmarks. Still, the native MySQL extension is even faster than both the MySQLi and PDO. At last, PDO wins with support for twelve different database drivers and named parameters.
From a security point of view, both of them are safe as long as the developer uses them the way they are supposed to be used.