MySQL 8 Improves JSON
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. 

mysql

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.

mysql

More Information

MySQL site

Related Articles

 MySQL Reaches Milestone 8 Release

MySQL 5.7

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, FacebookGoogle+ or Linkedin.

 

Banner

 


Machine Learning Added To Azure HDInsight
13/07/2018

Microsoft has announced the general availability of Machine Learning (ML) Services 9.3 on Azure HDInsight. The new release has added support for Python, hence the name change from R Server to ML Servi [ ... ]



Add An Airbag To Your Mobile
08/07/2018

This ingenious protective case for a mobile phone is a great example of thinking outside the box. The design relies on feedback from sensors that detect that it's falling. Now why didn't we think of t [ ... ]


More News

 

justjsquare

 



 

Comments




or email your comment to: comments@i-programmer.info

 
 

   
RSS feed of news items only
I Programmer News
Copyright © 2018 i-programmer.info. All Rights Reserved.
Joomla! is Free Software released under the GNU/GPL License.