Back to: Structured Query Language (SQL)
MySQL is an open source relational database management system (RDBMS) that’s used to store and manage data. Its reliability, performance, scalability, and ease of use make MySQL a popular choice for developers. In fact, you’ll find it at the heart of demanding, high-traffic applications such as Facebook, Netflix, Uber, Airbnb, Shopify, and Booking.com.
MySQL is the world’s most popular open source database management system. Databases are the essential data repositories for all software applications. For example, whenever someone conducts a web search, logs into an account, or completes a transaction, a database stores the information so it can be accessed in the future. MySQL excels at this task.
MySQL Use Cases
MySQL use cases include managing customer and product data for ecommerce websites, helping content management systems serve web content, securely tracking transactions and financial data, and powering social networking sites by storing user profiles and interactions.
MySQL’s ability to handle large data sets and complex queries makes it a key technology across industries and use cases, including the following:
Ecommerce. Many of the world’s largest ecommerce applications—including Uber and Booking.com — run their transactional systems on MySQL. It’s a popular choice for managing user profiles, credentials, user content, and financial data, including payments, along with fraud detection.
Social platforms. Facebook, X (formerly Twitter), and LinkedIn are among the world’s largest social networks, and they all rely on MySQL.
Content management. Unlike single-purpose document databases, MySQL enables both SQL and NoSQL with a single database. The MySQL Document Store enables CRUD operations and harnesses the power of SQL to query data from JSON documents for reporting and analytics.
This website nscvce.com.au uses MySQL to store the content you are presently reading.
SaaS and ISVs. More than 2,000 ISVs, OEMs, and VARs, including Ericsson and IBM, rely on MySQL as the embedded database to make their applications, hardware, and appliances more competitive; bring products to market faster; and lower their cost of goods sold. MySQL is also the database behind popular SaaS applications, such as Zendesk. Other popular applications using MySQL include apps for online gaming, digital marketing, retail point-of-sale systems, and Internet of Things monitoring systems.
MariaDB vs MySQL
A final word about MySQL – MariaDB and MySQL are both open-source relational databases, but MariaDB is generally considered to be more scalable and performant. MariaDB is also a fork of MySQL, so it shares many architectural features.
When choosing between MariaDB and MySQL, you can consider things like:
- Scalability: MariaDB’s improved scalability makes it a good choice for managing large-sized data.
- Vendor lock-in: MariaDB’s open-source nature helps avoid vendor lock-in with Oracle.
- Support: If you want to rely on timely support, MySQL’s enterprise edition might be a better choice.
- Cost: Licensing costs and support levels are important factors to consider.
| MariaDB | MySQL | |
|---|---|---|
| Licensing | Fully open-source | Available under GPL or proprietary license |
| Ownership | Developed independently of Oracle | Owned and distributed by Oracle |
| Features | More features than MySQL, including sequence storage engines and virtual columns | Includes JSON_TABLE for structuring JSON data as a table |
| Performance | Often faster than MySQL | Offers enterprise editions with advanced replication, automated failover, and performance monitoring |
| Support | Backed by an open source community | Supported by Oracle, which provides access to their support infrastructure |
Displaying Data from MySQL on the Web
It’s worth taking a step back for a clear picture of our ultimate goal. We have two powerful tools at our disposal: the PHP scripting language and the MySQL database engine. It’s important to understand how these will fit together.
The purpose of using MySQL for our website is to allow the content to be pulled dynamically from the database to create web pages for viewing in a regular browser. So, at one end of the system you have a visitor to your site using a web browser to request a page. That browser expects to receive a standard HTML document in return. At the other end you have the content of your site, which sits in one or more tables in a MySQL database that only understands how to respond to SQL queries (commands).
As shown in the image above, the PHP scripting language is the go-between that speaks both languages. It processes the page request and fetches the data from the MySQL database using SQL queries. It then spits it out dynamically as the nicely formatted HTML page that the browser expects.
Just so it’s clear and fresh in your mind, this is what happens when there’s a visitor to a page on your website:
- The visitor’s web browser requests the web page from your web server.
- The web server software (typically Apache or NGINX) recognises that the requested file is a PHP script, so the server fires up the PHP interpreter to execute the code contained in the file.
- Certain PHP commands connect to the MySQL database and request the content that belongs in the web page.
- The MySQL database responds by sending the requested content to the PHP script.
- The PHP script stores the content into one or more PHP variables, then uses
echostatements to output the content as part of the web page. - The PHP interpreter finishes up by handing a copy of the HTML it has created to the web server.
- The web server sends the HTML to the web browser as it would a plain HTML file, except that instead of coming directly from an HTML file, the page is the output provided by the PHP interpreter. The browser has no way of knowing this, however. As far as the browser is concerned, it’s requesting and receiving a web page like any other.