What is Modern SQL?

The best introduction for Modern SQL is modern-sql.com. Make sure to go through the slides or watch the video. Many of the newer features in the standard make it iesier to create a clean and portable (standards complient) solution. This also helps in compatibility with other databases like SQLite and PostgreSQL.

What is the current state of Modern SQL in MySQL?


See also: What’s New in SQL:2016 on modernsql.com

Feature Oracle MySQL MariaDB Percona Server WebscaleSQL
IS JSON        
ON ERROR        
JSON_ARRAY Yes      
JSON Path Partly      


Feature Oracle MySQL MariaDB Percona Server WebscaleSQL
AS OF   MDEV-12894 Will be in 10.3    


Feature Oracle MySQL MariaDB Percona Server WebscaleSQL
FETCH FIRST Non-standard LIMIT Only, Bug #78929 Non-standard LIMIT Only    
ARRAY Bug #13830 MDEV-6121    
NORMALIZE1 WL#2048 Bug #71563      


Feature Oracle MySQL MariaDB Percona Server WebscaleSQL
FILTER Thirdparty, filter_plugin for 5.7      
OVER a.k.a. window functions Bug #28957 Bug #35893 MDEV-6115 MariaDB 10.2.0 work-in-progress    
MERGE Bug #9018      
BETWEEN Bug #46867      


Feature Oracle MySQL MariaDB Percona Server WebscaleSQL
LATERAL Bug #78930      
WITH Bug #16244 Will be in 8.02 MDEV-8308    
WITH RECURSIVE3 Will be in 8.02      
GROUPING Yes, since 8.0.1 details      

Other non-standard behavior

Storage Engine API and CONNECT and CSV

The Storage Engine API allows you to create a wrapper for your data and use MySQL as SQL frontend for it. It also allows you to combine this data with data stored in MySQL. It is mainly used to create different backends for MySQL with slightly different characteristics.

The CONNECT storage engine in MariaDB allows you to connect to different data sources without the need of writing any code.

The CSV storage engine allows you to use SQL to query a CSV file.

The Federated and FederatedX storage engines allow you to connect to another MySQL server and combine data from multiple servers.

This is all very useful, but none of these follow the SQL/MED (Management of External Data) standard syntax.

Documentation about standards

To update the documentation about standards support: Bug #75954

Other standards

The SQL/JSON standard is being created. Some info is available here: http://jtc1bigdatasg.nist.gov/_workshop/08_SQL_Support_for_JSON_abstract.pdf.

The SQL/XML is part of the bigger SQL standard. MySQL has support for XML and XPath, but to my knowledge this is not standard compliant.

The SQL/PSM standard (Part of SQL:1999) is amazingly well supported in MySQL.

The level of implementation of the OpenGIS standard is rapidly improving. Both MySQL 5.7 and MariaDB 10.1 have enhanced GIS features.

MySQL has good support for X/Open X/A distributed transactions (as Resource Manager).

MySQL 5.7+ support GeoJSON.

MySQL has support for IEEE 754. The documentation on the level of compliance is not complete: Bug #57519. See also 0.30000000000000004.com

What can I do?

Click ‘Affects me’ on bugs.mysql.com or Vote on the MariaDB JIRA. You could also help to implement the features or pay someone else to do that.

Or let others know about this:

Fork me on GitHub

  1. See also: Unicode Normalization in SQL by David E. Wheeler 

  2. See also: Iterview with Manyi Lu by Dave Avery (Percona).  2

  3. See also: WITH RECURSIVE and MySQL by Guilhem Bichot.