Best New Features of MySQL 8

MySQL’s policy has traditionally been to produce a new general release of MySQL Community Server every 18-24 months. Believe it or not, the last general release for MySQL 5.7 was October 21, 2015, so we’re more than due for a new version of MySQL.  Well, as it happens, MySQL has unveiled a few release candidates for version 8 of their popular database server.   Although Oracle has not committed to a release date as of yet, insiders believe that the production version is likely to arrive sometime in 2018.  

Even though there is no official software release to download and use yet, the list of features for MySQL 8.0 was officially announced, which makes this the perfect time to talk about it and share some of the exciting enhancements it contains.

Why the Jump in Version Numbers?

The first thing that struck me with the new release candidates was the jump in version numbers.  There are a couple of reasons for the skipping over versions 6 and 7.  MySQL Server 6.0.11-alpha was announced in May of 2009 as the last release of the 6.0 line, but was eventually dropped.  Features developed for 6.0 were incorporated into subsequent releases.  Version 7.0 is reserved for the clustering version of MySQL. 

Features Overview

Before we get into the details of each feature, let’s just review what they entail:

• First-class support for Unicode 9.0.
• Window functions and recursive SQL syntax, for queries that previously weren’t possible or would have been difficult to write.
• Expanded support for native JSON data and document-store functionality.

Unicode Support

Unicode support in 8.0 has been upgraded from latin1 to utf8mb4, using the latest 9.0 standard, with language-specific collations to match.  The reason is simple: UTF8 is the dominating character encoding for the web and this move will make life easier for the vast majority of MySQL users. 

Faster than the now-deprecated utf8mb3 character set, utf8mb4 supports more flexible collations and case sensitivity.  Moreover, the improved Unicode will support non-Western character sets as well as emojis!  That means that you can work with any of Unicode 9’s emojis, like bacon!

Unicode 9 emojis

Window Functions and Recursive CTEs

Many other implementations of SQL support window functions already.  If you are not familiar with window functions, you can think of them as a GROUP BY that does not affect the results.  It allows you to perform aggregate calculations across multiple rows while still having access to the individual rows from the query.  Sure, it’s possible to do this in MySQL without window function support, but it’s cumbersome and slow.  To overcome this deficit, MySQL 8.0 adds window functions via the standard OVER keyword, in much the same way it is implemented in competing products like PostgreSQL.

Here’s an example that aggregates sales performance of an employee, while enumerating through a list of individual sales figures.  Notice that each row of the result set contains both the sales value as well as the aggregate of each employee’s sales:

SELECT
  employee,
  date,
  sales,
  SUM(sale) OVER (PARTITION BY employee) AS total_sales
 FROM glengarry_sales;

 +----------------+------------+-------+-------------+
 | employee       | date       | sales | total_sales |
 +----------------+------------+-------+-------------+
 | Ricky Roma     | 1994-03-01 |   200 |         900 |
 | Ricky Roma     | 1994-04-01 |   300 |         900 |
 | Ricky Roma     | 1994-05-01 |   400 |         900 |
 | Shelley Levine | 1994-03-01 |   400 |        1200 |
 | Shelley Levine | 1994-04-01 |   300 |        1200 |
 | Shelley Levine | 1994-05-01 |   500 |        1200 |
 +----------------+------------+-------+-------------+

A partition, which is similar to a grouping, lets a window function only see the rows of a subset of the total set of rows, as you can see.  An important property of all window functions is they cannot see rows outside the partition of the row for which they are invoked.

Window functions offer two main benefits:

1. They are extremely powerful.
2. They can reduce code complexity significantly.

There are other window functions available.  For instance, the LAG() and LEAD() functions provide access to the previous and next row in a result set, respectively.

A similar feature, Common Table Expressions, or CTEs for short, let you perform recursive operations as part of a query, without having to resort to cursors or other resource-intensive workarounds.

CTEs have several uses, including:

1. replacing derived tables
2. generating series
3. manipulating hierarchical data

CTEs can be either recursive or non-recursive.  Item number 3 above, manipulating hierarchical data, requires recursive CTEs because of their iterative nature.

Hierarchies, which may be arbitrarily deep, are difficult to handle with traditional SQL.  There are two established approaches:

1. A stored procedure with WHILE loops and recursive procedure calls to
1. find an item,
2. then find its sub-items,
3. then find sub-sub-items of the sub-items
2. Alternatively, we can assume a maximum depth of N and then an N-table JOIN may be employed.

Consider a table of Heavy Metal genres, with three columns named genre_id, genre_name, and parent_id, where every row is a music genre having a unique ID, a name, and the ID of its parent genre. For example:

+----------+----------------------+-----------+
 | genre_id | genre_name           | parent_id |
 +----------+----------------------+-----------+
 |        1 | Heavy Metal          |      NULL | --root parent
 |        2 | NWOBHM               |         1 |
 |        3 | Glam Metal           |         1 |
 |        4 | Power Metal          |         2 |
 |        5 | Speed Metal          |         2 |
 |        6 | Thrash Metal         |         4 |
 |        7 | Death Metal          |         4 |
 etc ...

Note that, in the above table:

• “Heavy Metal” (ID 1) has no parent (ID NULL) and it thus the root.
• “NWOBHM” (ID 2) belongs to parent having ID 1, which is “Heavy Metal”.
• “Glam Metal” (ID 3) also belongs to parent having ID 1, which is “Heavy Metal”.
• “Power Metal” (ID 4) and “Speed Metal” (ID 5) both belong to parent having ID 2, which is “NWOBHM”.
• “Thrash Metal” (ID 6) and “Death Metal” (ID 7) belong to parent having ID 4, which is “Power Metal”.

Here’s a Recursive CTE that retrieves a full tree:

WITH RECURSIVE cte AS
 (
   # seed SELECT
   SELECT genre_id, genre_name 
   FROM heavy_metal_genres 
   WHERE parent_id IS NULL
   UNION ALL
   # recursive SELECT
   SELECT hmg.genre_id, hmg.genre_name 
   FROM heavy_metal_genres hmg 
     JOIN cte ON cte.genre_id=hmg.parent_id # find children
 )
 SELECT genre_name FROM cte;

+-------------+----------------------+
 | genre_id    | genre_name           |
 +-------------+----------------------+
 |           1 | Heavy Metal          |  --root parent
 |           2 | NWOBHM               |
 |           6 | Thrash Metal         |
 |           3 | Glam Metal           |
 |           4 | Power Metal          |
 |           5 | Speed Metal          |
 |           7 | Death Metal          |
 |           9 | Stoner Metal         |
 |          10 | Nu Metal             |
 |           8 | Progressive Metal    |
 +-------------+----------------------+

Conclusion

In today’s article, we reviewed just a few of the many new features that will be part of the MySQL 8 release.  Over the next several months, we’ll explore other features and cover some of those presented today in more detail.

See all articles by Rob Gravelle