When MySQL 5.7 came out in 2015 with support for JSON fields and tools to manipulate JSON formatted data, I was very excited. I have found JSON support very useful when I want to store a chunk of data in a relational database when I’m not sure what that data is going to be, like a stack traces.
Now I have even more to look forward to in the upcoming release of MySQL 8.0. With two more release candidates listed, we still have to wait for the general availability release. However, I have outlined the most exciting changes.
JSON Improvements
You can now use JSON_ARRAYAGG instead of group concat! At some point in time, you have likely used group_concat to get all of the values for a grouping. With JSON_ARRAYAGG you can get the results as a JSON array instead of a delimited string! The MySQL documentation has a good example of how to use JSON_ARRAYAGG and its sister function JSON_OBJECTAGG.
Extract a object path without using JSON_EXTRACT. You can use field->’$.scans[*].level’ in place of JSON_EXTRACRT(field, ‘$.scans[*].level’). This shorter notation is much cleaner than using a function.
This also provides support for ranges in arrays and adds a last keyword. This makes it a lot easier to interact with JSON arrays. field->’$.scans[last]’, field->’$.scans[2 to 4]’, and field->’$.scans[last-1]’ are all valid, getting the last element, elements 2 through 4, and the second to last element.
The JSON_TABLE function simplifies taking JSON data and breaking it out like a relational table. This is a game-changer in handling json arrays in MySQL, as it bridges the gap between an object data store and relational database. Much like the aggregate JSON functions, MYSQL has a few good examples where they document JSON_TABLE.
Nothing is more frustrating than trying to dig through some JSON to find one specific line. JSON_PRETTY will reformat your JSON field or string to be easy to read.
You can also now do partial in place updates to JSON column values. With this change MySQL is starting to act more like an object store. You don’t have to replace the whole field to change part of a JSON object or array stored in a field.
Regular Expression Changes
The next version of MySQL brings Multibyte safe regular expressions. The regular expression library now supports Unicode, and you can now safely use regular expressions on multibyte characters. A few new functions look like they will be useful for both queries and data clean up as well.
- REGEXP_LIKE functions like REGEXP or RLIKE in older versions of MySQL
- REGEXP_INSTR returns the starting index of the match much like using REGEXP or RLIKE. This is similar to REGEXP except it returns the string index and allows you to skip x matches in case you want to see if your expression matches 4 times instead of at least once.
- REGEXP_REPLACE is a straightforward search-replace. Sadly, it does not support mutable replacements with regular expression groups. This could be very useful for data clean up.
- REGEXP_SUBSTR returns the matching part of your regular expression.
Roles for Permissions
In the past when you wanted to grant many applications permissions to tables, you had to deal with each user individually. There was no way to set the same permissions to more than one user. The issue was worse when you wanted to add additional permissions, as you had to edit every user. MYSQL 8.0 adds support for roles, so you can grant permissions to a role and then apply a role to a user!
CREATE ROLE 'report';
CREATE ROLE 'application';
GRANT SELECT ON site.* TO 'report';
GRANT SELECT ON archive.* TO 'report';
GRANT SELECT,UPDATE,DELETE ON db.* TO 'application';
CREATE USER 'reporting';
CREATE USER 'app';
GRANT 'report' TO 'reporting';
GRANT 'report', 'application' TO 'app';Set Persist
Too often I have changed a system variable in my development environment, but for one reason or another I don’t want to update the configuration file. With MSQL 8.0, you can persist variables between reboots by using SET PERSIST. As part of this change, you now can see the source of a system variable by looking at the performance_schema.variables_info table.
Invisible Indexes
MySQL 8.0 has added support for invisible indexes like Oracle. Over time applications change, and you should evaluate your current indexes to determine where to add new ones. If you make an index invisible, you can partly see how much a given index is helping your application by hiding it from the query optimizer. You will still have the performance hit when the index needs to be updated due to data changes like inserts/updates, so it’s not quite the same as removing the index.
Changed Default Character Set
For the longest time, the default character set in MySQL has been latin1. Many developers use utf8 because they don’t know about its shortcomings in MySQL. Going forward, the default will be utf8mb4 and will hopefully help developers with larger 4-byte characters that utf8 didn’t support. The good news for people who have used utf8 is that utf8mb4 is simply a super-set of utf8. The transition should be painless.
Changes to the Data Dictionary
MySQL 8.0 has also changed storage for the data dictionary. The virtual INFORMATION_SCHEMA database will still be there, but upgrades will be handled in a different manner. In previous versions of MySQL, when you ran an update you needed to run mysql_upgrade after you start the server. This ensured any needed data dictionary changes have been applied. Starting in MySQL 8.0 the server will automatically upgrade the data dictionary without the need to run mysql_upgrade. Your data dictionary may be auto-upgraded, but there is a parameter you can pass to MySQL to skip automatic data dictionary upgrades.
Moving Forward with MySQL 8.0
MySQL 8.0 offers exciting new features for developers looking for more flexibility within their work. I’m looking forward to its release and putting its capabilities to use!
Great read, Brian!