The world's most ubiquitous and flexible open source relational database
MySQL is the most widely adopted open source relational database and serves as the primary relational data store for many popular websites, applications, and commercial products. With more than 20 years of community-backed development and support, MySQL is a reliable, stable, and secure SQL-based database management system. The MySQL database is suitable for a wide variety of use cases, including mission critical apps, dynamic websites, and as an embedded database for software, hardware, and appliances.
AWS supports MySQL in a variety of ways, including a fully managed database service, Amazon Relational Database Service (RDS) for MySQL. Amazon Aurora with MySQL compatibility is also built using MySQL, and Amazon RDS supports the popular MySQL fork project, MariaDB. You can also host MySQL on Amazon EC2 and self-manage the database, or browse the 3rd party MySQL offerings on AWS Marketplace.
History of MySQL
MySQL Cluster is a real-time open source transactional database designed for fast, always-on access to data under high throughput conditions. MySQL Cluster Plus, everything in MySQL Enterprise Edition. MySQL is developed, marketed and supported by MySQL AB, which is a Swedish company. This tutorial will give you a quick start to MySQL and make you comfortable with MySQL programming. This tutorial is prepared for the beginners to help them understand the basics-to-advanced concepts related to MySQL languages.
Ease-of-use and performance
MySQL database has found favor with web developers due to its ease-of-use and productivity features, including triggers, stored procedures, and updatable views. MySQL includes utilities like mysqldump, a backup program; mysqladmin, an administrative client; and MySQL Workbench, a GUI for management and migration work.
Over time, MySQL has boosted its performance capabilities with features including B-tree disk tables with index compression, thread-based memory allocation, and optimized nested-loop joins. Row-level locking and consistent reads in the storage engine give MySQL additional performance benefits for multi-user concurrency.
Reliability and security
MySQL's InnoDB transactional storage engine adheres to the ACID model, with capabilities that improve data protection, including point-in-time recovery and autocommit. InnoDB offers additional data integrity through support for foreign key constraints, preventing data inconsistencies across tables.
MySQL includes hardened and flexible security features, including host-based verification and encryption of password traffic. InnoDB offers additional security benefits, with data-at-rest tablespace encryption using a two-tier encryption key architecture.
Open source license
MySQL is available under an open source license (the GNU General Public License) allowing you to freely use and modify the source code. Managed versions of MySQL, like Amazon RDS for MySQL, carry no additional licensing costs.
MySQL's large, global community of contributors and enthusiasts bring many additional and long-tail benefits to using the database system. For example, the MySQL community stays on top of security issues and bug fixes, contributing to the overall resilience of the software. MySQL user groups, events, forums, and mailing lists provide a built-in network for education and support.
General purpose OLTP database
MySQL is suitable for any application requiring a transactional SQL engine, including mission-critical apps and heavy trafficked websites. MySQL adheres to ACID principles and includes extensions to ANSI/ISO Standard SQL, as well as support for XML and JSON. MySQL also supports high-availability database clustering, and can handle terabyte-sized databases. Popular applications that use the MySQL database include TYPO3, MODx, Joomla, WordPress, phpBB, MyBB, and Drupal.
eCommerce applications
MySQL is one of the most popular transactional engines for eCommerce platforms. MySQL is particularly useful for managing customer data, transactions, and product catalogs. In eCommerce solutions, MySQL is often used in conjunction with other, non-relational databases, including document and key-value stores for syncing order data, and storing non-product data.
LAMP open source stack
MySQL is integral to countless applications running on the LAMP open source software stack (LAMP stands for Linux, Apache, MySQL, and PHP/Python/Perl). LAMP is a ubiquitous web services solution stack and is widely considered the platform of choice for dynamic websites and high-performance web applications.
MariaDB
MariaDB is a popular fork of MySQL from MariaDB, which was founded by the original developers of MySQL who intended it to remain free and open source under the GNU GPL. While MariaDB is maintained separately from MySQL, it retains a high degree of MySQL compatibility, including library binary equivalency, and exact matching with APIs. AWS supports MariaDB with the fully managed database service, Amazon RDS for MariaDB.
Percona Server
Percona Server is another popular fork of MySQL from Percona. Percona Server includes XtraDB, Percona's fork of the InnoDB Storage Engine.
Language support
MySQL supports most leading programming languages and protocols, including:
MySQL vs. PostgreSQL
MySQL and PostgreSQL are both popular open source relational databases. Traditionally, MySQL has been perceived as easy to use and fast, whereas PostgreSQL has been perceived as feature-rich and more comparable to commercial databases like Oracle. However, the current major versions of MySQL and PostgreSQL both offer many robust features and capabilities in the way of performance, reliability, security, and more. You can learn more about PostgreSQL and try Amazon RDS for PostgreSQL using the AWS Free Tier.
MySQL Hosting Options on AWS
Self-managed MySQL on Amazon EC2
Fully Managed MySQL on Amazon RDS
The MySQLi Extension (MySQL Improved) is a relational database driver used in the PHPscripting language to provide an interface with MySQLdatabases.
There are three main API options when considering connecting to a MySQL database server:
- PHP's MySQL Extension
- PHP's MySQLi Extension
- PHP Data Objects (PDO)
The PHP code consists of a core, with optional extensions to the core functionality. PHP's MySQL-related extensions, such as the MySQLi extension, and the MySQL extension, are implemented using the PHP extension framework. An extension typically exposes an API to the PHP developer, to allow its facilities to be used programmatically. However, some extensions which use the PHP extension framework do not expose an API to the PHP developer.
The PDO MySQL driver extension, for example, does not expose an API to the PHP developer, but provides an interface to the PDO layer above it.
MySQLi is an improved version of the older PHP MySQL driver, offering various benefits.[1]
The authors of the PHP scripting language recommend using MySQLi when dealing with MySQL server versions 4.1.3 and newer (takes advantage of new functionality).[1]
Technical details[edit]
The MySQLi extension provides various benefits with respect to its predecessor, the most prominent of which, (according to the PHP website,[1] ) are:
- An object-oriented interface
- Support for prepared statements
- Support for multiple statements
- Support for transactions
- Enhanced debugging support
- Embedded server support
- More powerful Functionality
Comparison of features [1][edit]
| PHP's MySQLi Extension | PDO | PHP's MySQL Extension | |
|---|---|---|---|
| PHP version introduced | 5.0 | 5.0 | pre-3.0 |
| Included with PHP 5.x | Yes | Yes | Yes |
| Included with PHP 7.x | Yes | Yes | No |
| MySQL development status | Active development | Active development as of PHP 5.3 | Deprecated as of PHP 5.5 Removed in PHP 7.0 |
| Recommended by MySQL for new projects | Yes - preferred option | Yes | No |
| API supports Charsets | Yes | Yes | No |
| API supports server-side Prepared Statements | Yes | Yes | No |
| API supports client-side Prepared Statements | No | Yes | No |
| API supports Stored Procedures | Yes | Yes | No |
| API supports Multiple Statements | Yes | Most | No |
| Supports all MySQL 4.1+ functionality | Yes | Most | No |
Start guide [2][edit]
Dual procedural and object-oriented interface[edit]
The MySQLi extension features a dual interface. It supports the procedural and object-oriented programming paradigms. Os x version 10.12 download.
Users migrating from the old MySQL extension may prefer the procedural interface. The procedural interface is similar to that of the old MySQL extension. In many cases, the function names differ only by prefix. Some MySQLi functions take a connection handle as their first argument, whereas matching functions in the old MySQL interface took it as an optional last argument.
Example. Easy migration from the old MySQL extension
Connections[edit]
The MySQL server supports the use of different transport layers for connections. Connections use TCP/IP, Unix domain sockets or Windows named pipes.
The hostname localhost has a special meaning. It is bound to the use of Unix domain sockets. It is not possible to open a TCP/IP connection using the hostname localhost you must use 127.0.0.1 instead.
Example. Special meaning of localhost
Output
Executing statements[edit]
Statements can be executed with the mysqli_query(), mysqli_real_query() and mysqli_multi_query() functions. The mysqli_query() function is the most common, and combines the executing statement with a buffered fetch of its result set, if any, in one call. Calling mysqli_query() is identical to calling mysqli_real_query() followed by mysqli_store_result().
Example: Connecting to MySQL
Buffered result sets
After statement execution results can be retrieved at once to be buffered by the client or by read row by row. Client-side result set buffering allows the server to free resources associated with the statement results as early as possible. Generally speaking, clients are slow consuming result sets. Therefore, it is recommended to use buffered result sets. mysqli_query() combines statement execution and result set buffering.
PHP applications can navigate freely through buffered results. Navigation is fast because the result sets are held in client memory. Please, keep in mind that it is often easier to scale by client than it is to scale the server.
Example: Navigation through buffered results
The above example will output:
Unbuffered result sets
If client memory is a short resource and freeing server resources as early as possible to keep server load low is not needed, unbuffered results can be used. Scrolling through unbuffered results is not possible before all rows have been read.
Example: Navigation through unbuffered results
Result set values data types
The mysqli_query(), mysqli_real_query() and mysqli_multi_query() functions are used to execute non-prepared statements. At the level of the MySQL Client Server Protocol, the command COM_QUERY and the text protocol are used for statement execution. With the text protocol, the MySQL server converts all data of a result sets into strings before sending. This conversion is done regardless of the SQL result set column data type. The MySQL client libraries receive all column values as strings. No further client-side casting is done to convert columns back to their native types. Instead, all values are provided as PHP strings.
Example: Text protocol returns strings by default
The above example will output:
It is possible to convert integer and float columns back to PHP numbers by setting the MYSQLI_OPT_INT_AND_FLOAT_NATIVE connection option, if using the mysqlnd library. If set, the mysqlnd library will check the result set meta data column types and convert numeric SQL columns to PHP numbers, if the PHP data type value range allows for it. This way, for example, SQL INT columns are returned as integers.
Example: Native data types with mysqlnd and connection option
The above example will output:
Prepared statements[edit]
The MySQL database supports prepared statements. A prepared statement or a parameterized statement is used to execute the same statement repeatedly with high efficiency.
Basic workflow
The prepared statement execution consists of two stages: prepare and execute. At the prepare stage a statement template is sent to the database server. The server performs a syntax check and initializes server internal resources for later use.
The MySQL server supports using anonymous, positional placeholder with ?.
See example in.[3]
Stored procedures [4][edit]
The MySQL database supports stored procedures. A stored procedure is a subroutine stored in the database catalog. Applications can call and execute the stored procedure. The CALL SQL statement is used to execute a stored procedure.
Parameter
Stored procedures can have IN, INOUT and OUT parameters, depending on the MySQL version. The MySQLi interface has no special notion for the different kinds of parameters.
IN parameter
Input parameters are provided with the CALL statement. Please, make sure values are escaped correctly.
See examples in.[4]
Multiple statements [5][edit]
MySQL optionally allows having multiple statements in one statement string. Sending multiple statements at once reduces client-server round trips but requires special handling.
Multiple statements or multi queries must be executed with mysqli_multi_query(). The individual statements of the statement string are separated by semicolon. Then, all result sets returned by the executed statements must be fetched.
The MySQL server allows having statements that do return result sets and statements that do not return result sets in one multiple statement.
See examples in [5]
API support for transactions [6][edit]
The MySQL server supports transactions depending on the storage engine used. Since MySQL 5.5, the default storage engine is InnoDB. InnoDB has full ACID transaction support.
Transactions can either be controlled using SQL or API calls. It is recommended to use API calls for enabling and disabling the auto commit mode and for committing and rolling back transactions.
Examples here.[6]
Metadata [7][edit]
A MySQL result set contains metadata. The metadata describes the columns found in the result set. All metadata sent by MySQL is accessible through the MySQLi interface. The extension performs no or negligible changes to the information it receives. Differences between MySQL server versions are not aligned.
Meta data is access through the mysqli_result interface.
View more here.[7]
The MySQLi extension and persistent connections [1][edit]
Mysql Workbench
Persistent connection support was introduced in PHP 5.3 for the MySQLi extension. Support was already present in PDO MYSQL and ext/mysql. The idea behind persistent connections is that a connection between a client process and a database can be reused by a client process, rather than being created and destroyed multiple times. This reduces the overhead of creating fresh connections every time one is required, as unused connections are cached and ready to be reused.
Unlike the MySQL extension, MySQLi does not provide a separate function for opening persistent connections. To open a persistent connection you must prepend p: to the hostname when connecting.
The problem with persistent connections is that they can be left in unpredictable states by clients. For example, a table lock might be activated before a client terminates unexpectedly. A new client process reusing this persistent connection will get the connection 'as is'. Any cleanup would need to be done by the new client process before it could make good use of the persistent connection, increasing the burden on the programmer.
The persistent connection of the MySQLi extension however 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 prepared statements (always happens with PHP)
- Close handler
- Release locks acquired with GET_LOCK()
This ensures that persistent connections are in a clean state on return from the connection pool, before the client process uses them.
The MySQLi extension does this cleanup by automatically calling the C-API function mysql_change_user().
The automatic cleanup feature has advantages and disadvantages though. The advantage is that the developer no longer needs to worry about adding cleanup code, as it is called automatically. However, the disadvantage is that the code could potentially be a little slower, as the code to perform the cleanup needs to run each time a connection is returned from the connection pool.
It is possible to switch off the automatic cleanup code, by compiling PHP with MYSQLI_NO_CHANGE_USER_ON_PCONNECT defined.
References[edit]
- ^ abcde'Overview - Manual'. PHP. Retrieved August 11, 2014.
- ^'PHP: Quick start guide - Manual'. In2.php.net. Retrieved August 11, 2014.
- ^'PHP: Prepared Statements - Manual'. In2.php.net. Retrieved August 11, 2014.
- ^ abJonathon (May 24, 2013). 'PHP: Stored Procedures - Manual'. In2.php.net. Retrieved August 11, 2014.
- ^ aberic dot noel at gmail dot com (March 31, 2014). 'PHP: Multiple Statements - Manual'. In2.php.net. Retrieved August 11, 2014.
- ^ ab'PHP: API support for transactions - Manual'. In2.php.net. Retrieved August 11, 2014.
- ^ ab'PHP: Metadata - Manual'. In2.php.net. Retrieved August 11, 2014.
Mysql Docker
Further reading[edit]
Mysql Community Server
