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?
SQL:2016
See also: What’s New in SQL:2016 on modernsql.com
| Feature | Oracle MySQL | MariaDB | Percona Server | WebscaleSQL |
|---|---|---|---|---|
| MATCH_RECOGNIZER | ||||
| IS JSON | ||||
| ON ERROR | ||||
| JSON_OBJECT | ||||
| JSON_ARRAY | Yes | |||
| JSON_OBJECTAGG | ||||
| JSON_EXISTS | ||||
| JSON_VALUE | ||||
| JSON_QUERY | ||||
| JSON_TABLE | ||||
| JSON Path | Partly | |||
| LISTAGG |
SQL:2011
| Feature | Oracle MySQL | MariaDB | Percona Server | WebscaleSQL |
|---|---|---|---|---|
| OFFSET | ||||
| AS OF | MDEV-12894 Will be in 10.3 | |||
| WITHOUT OVERLAPS |
SQL:2008
| Feature | Oracle MySQL | MariaDB | Percona Server | WebscaleSQL |
|---|---|---|---|---|
| FETCH FIRST | Non-standard LIMIT Only, Bug #78929 | Non-standard LIMIT Only | ||
| OVER | ||||
| ARRAY | Bug #13830 | MDEV-6121 | ||
| NORMALIZE1 | WL#2048 Bug #71563 |
SQL:2003
| 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 | ||
| WITHIN GROUP | ||||
| MERGE | Bug #9018 | |||
| BETWEEN | Bug #46867 |
SQL:1999
| 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: Tweet
-
See also: Unicode Normalization in SQL by David E. Wheeler ↩
-
See also: Iterview with Manyi Lu by Dave Avery (Percona). ↩ ↩2
-
See also: WITH RECURSIVE and MySQL by Guilhem Bichot. ↩