MariaDB: DELETE … RETURNING

UPDATE: This post is obsolete. Please check a more recent article on my professional website:
MariaDB RETURNING Statements

MariaDB 10.0.5 supports DELETE ... RETURNING, like PostgreSQL. It deletes some rows and returns the specified columns (or, more generally, the specified SELECT expressions) from the deleted rows. As for regular DELETEs, Com_delete is incremented and Com_select is not.

Let’s see what this feature does and what it does not.

You cannot:

• Cannot use it with INSERT or CREATE TABLE to create a table containing data from the deleted rows (a sort of delete log, or easy-to-restore backup).
• Cannot use it as a subquery.
• Cannot use it as a cursor, and there is no INTO. So you cannot process the results in a stored routine, because there is no way to copy deleted data into variables.
• Cannot use aggregating functions. But why should you? If the purpose is to get a row count, you can use SELECT ROW_COUNT().

You can:

• Include a subquery, virtual column, stored function, or anything else in the RETURNING clause.
• RETURNING * works, AS works.
• Use it as a prepared statement.
• Use it in a stored procedure. But in practice, you can only return a resultset.

Some of the things I included in this list should be obvious: I just stated that some documented features can work together. But I’m afraid this is not obvious in MySQL and MariaDB: they have great features, but in many cases they don’t work together.

UPDATE: Some of the limitations can be workarounded with this trick.

Thanks, MariaDB team!

DELETE RETURNING is another reason to use MariaDB. And I see a task for UPDATE ... RETURNING.

Enjoy!

MariaDB: ALTER TABLE IF [NOT] EXISTS

Since when I remember (and I can remember the 3.23 version!) MySQL always supported the IF EXISTS and IF NOT EXISTS options for many DDL statements. They help developers in writing install and uninstall scripts: with IF NOT EXISTS, a table is created if needed, and there is no error (so, the script’s execution goes on) if the table is already there.

However, the case when a table exists but must be modified is problematic, because the ALTER statements never supported those options… until MariaDB 10. The old way to avoid errors was:

DROP TABLE IF EXISTS tab_name;
CREATE TABLE tab_name ... ;

But this way it is not possible to preserve current table’s data.

The cleanest solution was probably writing a script, or a Stored Procedure, which queries the information_schema database to check if a column exists, and conditionally CREATE/DROP it. Or, to avoid such queries, a more error-prone script could read the database version in use and upgrade it.

MariaDB 10.0.2 supports IF EXISTS and IF NOT EXISTS options for several ALTER TABLE sub-commands. Here’s the list of those clauses (copied from the MariaDB KnowledgeBase):

ADD COLUMN
ADD INDEX
ADD FOREIGN KEY
ADD PARTITION
 
DROP COLUMN
DROP INDEX
DROP FOREIGN KEY
DROP PARTITION
 
CHANGE COLUMN
MODIFY COLUMN

Also, these options are supported for CREATE INDEX and DROP INDEX.

Note that the rarely used ALTER COLUMN syntax has not an IF EXISTS option, so the longer MODIFY syntax must be used instead.

Also note that the PostgreSQL syntax is not supported; thus, this will not work:

ALTER TABLE IF EXISTS tab_name ... ;

At this point, you are probably wondering: “But then, is this feature totally useless for me? Is there still no way to alter a table only if it exists?”. Hey, don’t be dramatic! The solution is easy, and it’s probably better than Postgres-style:

-- create the table as we want it to be
CREATE TABLE IF NOT EXISTS tab_name ... ;
-- if an older table version exists, update it
ALTER TABLE name
    DROP COLUMN IF NOT EXISTS col_name col,
    DROP INDEX IF EXISTS idx_name;

This feature is not compatible with Oracle MySQL, and MariaDB’s executable comments cannot help here. You may write two different upgrade scripts, one for MariaDB and one for Oracle MySQL… but this feature should simplify your work, not make it harder.

So you can just switch to MariaDB and leave Oracle MySQL. This is an option even for public software projects. MariaDB has more interesting features, it is more open, and is probably the future. Also, migrating to MariaDB is amazingly simple: in most cases, all you need to do is to install MariaDB. So, don’t be prisoner of Oracle just because MySQL is still the most widely used DBMS: things will change.

Enjoy!