MariaDB 10.3 adds even more development features than MySQL 8.0
Last Friday (May 25-th) MariaDB 10.3.7 was released as stable (General Availability). I was finally been able to check the release notes and (not surprisingly) I found some features that we're still missing in MySQL (including the released a month ago 8.0.11 GA for which I wrote). I'd like to highlight the following notable changes related to development (of which I'm interested the most):
- System-versioned tables (a.k.a. AS OF);
- Aggregate stored functions (CREATE AGGREGATE FUNCTION and FETCH GROUP NEXT ROW inside loops);
- FOR loop (FOR ... DO ... END FOR statement and FOR ... LOOP ... END LOOP statement in sql_mode=ORACLE, see MDEV-10581 and MDEV-12098);
- Sequences (as alternative to AUTO INCREMENT for "more control of how the numbers are generated");
- INTERSECT and EXCEPT set operations;
- ROW data type (for variables representing group of fields - i.e. rows or records - in stored routines);
- TYPE OF and ROW TYPE OF anchored data types (for variables representing columns or rows in stored routines);
- DELETE and UPDATE statements based on a subquery on the same table;
- Stored packages (with Oracle-like CREATE PACKAGE and CREATE PACKAGE BODY statements in sql_mode=ORACLE).
Wow. They've been working hard, that's for sure. I'll have yet to test all these new features myself, but I'm quite excited to see them available in "one of the most popular database servers". Unfortunately, neither one of these is available in MySQL. Here below is my list of what's still missing in MySQL (and MariaDB):
Features | MariaDB | MySQL | Standard | |
---|---|---|---|---|
SQL | Modify table data based on subquery on the same table | 10.3.2 (2017-11-09) | n/a | |
EXCEPT or MINUS and INTERSECT set operators | 10.3.0 (2017-04-16) | n/a | SQL:2003 | |
Temporal queries (AS OF ) | 10.3.4 (2018-01-18) | n/a | SQL:2011 | |
DDL | CHECK constraints | 10.2.1 (2016-07-04) | n/a | SQL:1999 |
Partitioning of tables with foreign keys | n/a | n/a | ||
Temporal tables (WITH SYSTEM VERSIONING ) | 10.3.4 (2018-01-18) | n/a | SQL:2011 | |
CREATE OR REPLACE for tables, triggers and stored routines | 10.0.8 (2014-02-10), 10.1.4 (2015-04-13) and 10.1.3 (2015-03-02) | n/a | ||
Enable/disable for triggers | n/a | n/a | ||
Sequences | 10.3.0 (2017-04-16) | n/a | SQL:2003 | |
A working schema rename (i.e. RENAME {DATABASE | SCHEMA} ) statement | n/a | n/a | ||
Views | Saving of the exact view statement in the server | n/a | n/a | n/a |
Fully updatable views with INSTEAD OF triggers | n/a | n/a | SQL:2008 | |
Materialized views | n/a | n/a | ||
Stored routines | Default arguments | n/a | n/a | |
FOR loop | 10.3.3 (2017-12-23) | n/a | ||
Anonymous code blocks (or compound statements outside routines) | 10.1.1 (2014-10-17) | n/a | ||
Records, column and row anchored types | 10.3.0 (2017-04-16) | n/a | ||
Stored modules or packages | 10.3.5 (2018-02-26) | n/a | ||
Statement and schema level triggers | n/a | n/a | ||
Debugging facilities | n/a | n/a | n/a | |
External routines in JavaScript, Perl, PHP, etc. | n/a | n/a |
It's quite apparent from the above table that MySQL has more gray spots than MariaDB. I've added also the related SQL standard where applicable, so in this regards MariaDB has broader standards support. It seems to me than in its latest stable version MariaDB has implemented more "Oracle like" features, than MySQL which is developed by Oracle.
So is it worth it changing the database? I believe I'll continue using both MySQL and MariaDB (which is default database in most Linux distributions) and the reasons for this are that these are becoming more and more different with the time. There are development features in MySQL (e.g. JSON data type, the Document store), which do not exist in MariaDB and vice versa. I'm still quite used using MySQL Workbench for daily work with MySQL databases, but the support for MariaDB in it is fading and unless MySQL adds the same SQL features, I doubt the new development functionalities would be usable from the Workbench, which impacts my work.
No comments:
Post a Comment