MySQL 8.0. The Latest Features | Thorough Overview
1. JSON Enhancements
Many developers have criticized MySQL for insufficient JSON functionality. However, everything is different now because JSON has undergone some major enhancements.
Let’s start with the new utility functions, JSON_STORAGE_SIZE( ) and JSON_STORAGE_FREE(). Another addition is JSON_PRETTY().
That’s not it, because ->> has also been added, which is the equivalent for JSON_UNQUOTE(JSON_EXTRACT()). Some new aggregation functions, such as JSON_OBJECTAGG() and JSON_ARRAYAGG() have also been integrated into MySQL 8.0. Even better, now we have a JSON_TABLE() function for JSON documents. It accepts the JSON data and returns it as a relational table, with specifiable columns and casting.
2. Data Dictionary
Earlier versions of MySQL worked on storing the dictionary on disk. Now, it has taken a step further and stores a dictionary in InnoDB tables. This improvement’s main reason is to upgrade the performance of the database.
3. Document Store
This is one of the most anticipated upgrades within MySQL 8.0. Now, the document store has become exactly what developers expected it to be. It combines the best of two languages, SQL and NoSQL. This way, developers can take advantage of the top-notch benefits of both.
How does the MySQL document store work?
Well, it can handle all sorts of work, from CRUD commands to merging several tables into one – all is possible now. It follows the ACID properties, so you can learn it effortlessly if you are familiar with the MySQL environment.
4. Window Functions and CTEs (Common Table Expressions)
There have been various Window functions added into MySQL 8.0.4. Here are some of them:
- CUME_DIST()
- DENSE_RANK()
- FIRST_VALUE()
- LAG()
- LAST_VALUE()
- LEAD()
- NTH_VALUE()
- NTILE()
- PERCENT_RANK()
- RANK()
- ROW_NUMBER
In addition to that, WITH queries, also known as CTEs (Common Table Expressions), have been added to MySQL 8. It is a volatile result set within a single statement. The CTE can be called again with that statement.
5. Improved Optimizer Model and Hints
The latest MySQL 8.0 upgrade allows the Optimizer Cost Model to view cached data (actual amount located in memory). Thus, you can always check how much of it is left on the disk.
In addition to that, Optimizer hints are also available for the optimizer_switch variable. Although it was available in previous versions, the Oracle team has improved it significantly. Before, you had to call the variable after each subsequent query. Now, you can control optimizer hints per query.
MySQL has opened a new path for better functionality for developers. Those new to the DBMS can still catch up and learn new features for better database performance. In their turn, the experienced developers can do more with the improved options.