| MySQL 8 Improves JSON |
| Written by Kay Ewbank |
| Tuesday, 24 April 2018 |
|
MySQL 8 has been released with improvements including better mobile support, stronger support for Unicode, and schemaless JSON support. The new release also adds a transactional data dictionary, and runs up to twice as fast as 5.7. MySQL 8 also offers a new document store with NoSQL support.
This is a major release, and for developers some of the more useful improvements are in overall SQL support. For a start, you can now make use of SQL Windows functions. These are similar to grouped aggregate functions, but where a grouped aggregate returns a single row, window functions perform the aggregation for each row in the result set. The functions that have been added start with the ability to use the 'standard' SQL aggregate functions (Count, Max, Avg etc) as window functions. In addition there are specialized window functions, specifically Rank, Dense_Rank, Percent_Rank, Cume_Dist, Ntile, Row_Number, and First_Value. The next useful change is the addition of support for recursive Common Table Expressions (CTEs). A CTE is similar to a derived table that can be referenced multiple times, and a recursive CTE is a set of rows which is built iteratively. You start from a set of rows, apply some process that derives new rows, then reapply the process to the new rows. Each time new rows are derived and processed, until you reach a point where no new rows are added. Elsewhere in the 'main' SQL areas, you can now use NoWait and Skip Locked in the SQL locking clause; indexes in descending order are supported; and a new Grouping() function has been added that distinguishes super-aggregate rows from regular grouped rows. While the SQL improvements are probably the most widely useful improvement, the JSON improvements are impressive, with new functions and better performance when sorting and grouping JSON values. The additional functions are JSON table and aggregate functions. The table functions mean you can use SQL techniques with JSON data, so you can create a relational view using JSON_TABLE. This maps the result of a JSON data evaluation into relational rows and columns that can then be queried using SQL as though it were a regular relational table, including the ability to Join to other tables. In addition to treating the data like a standard SQL table, support has been added for JSON Aggregate functions, specifically JSON_ARRAYAGG to generate JSON arrays and JSON_OBJECTTAGG to generate JSON objects. There's also a couple of JSON Merge functions, one that removes duplicates from the merge, the other that preserves all values including duplicates. Sorting performance for JSON has been improved, and support added for partial update for JSON Remove, Set and Replace functions. This means that if only parts of a JSON document are updated, replicas stay consistent even when the full document is not retransmitted. GIS support has been added including meta-data support for Spatial Reference System (SRS), as well as SRS aware spatial datatypes, spatial indexes, and spatial functions. This means MySQL 8 can work with latitude and longitude coordinates to carry out operations such as calculating the distances between two points on the earth's surface. Other changes include improvements to DDL statements so they are atomic and crash safe as metadata is stored in a single, transactional data dictionary powered by InnoDB; and remote management support and new instant DDL. Security has also been improved with OpenSSL improvements, new default authentication, and support for SQL Roles.
More InformationRelated ArticlesMySQL Reaches Milestone 8 Release Improved Performance In MySQL Developer Milestone Release MySQL 5.7 Hits 1 million NoSQL Queries per Second Amazon Launches Supercharged MySQL Alternative
To be informed about new articles on I Programmer, sign up for our weekly newsletter, subscribe to the RSS feed and follow us on Twitter, Facebook or Linkedin.
Comments
or email your comment to: comments@i-programmer.info |
