MariaDB RETURNING Statements

Many DBMSs support a way to get information about a rows we just deleted, updated, or inserted. The syntax varies depending on the DBMS, and some of them allow more options than others. Currently MySQL has no similar feature. MariaDB supports the RETURNING keyword for the following statements:

• DELETE, since MariaDB 10.0;
• INSERT, since MariaDB 10.5;
• REPLACE, since MariaDB 10.5.

Feature request: MDEV-5092 – Implement UPDATE with result set.

MariaDB RETURNING statements

Let’s go through the supported RETURNING statements.

DELETE RETURNING

Sometimes we need to run a SELECT just before a DELETE. For example when we want to delete an email from a list and then print the message “The email jody@doctor-who.co.uk has been removed from our archives”.

Using DELETE RETURNING allows us to run one less query. Returning the number of queries that your application runs may be important for performance.

DELETE
    FROM mailing_list
    WHERE id = 24
    RETURNING email AS deleted_email;

Multi-table DELETEs do not support RETURNING.

INSERT RETURNING

INSERT RETURNING is very similar: it inserts a row and returns the columns we want to know, without running an additional SELECT. Actually the values we insert are written in our INSERT statement, but with some exceptions:

• The value of the AUTO_INCREMENT primary key. This is the most common reason to use INSERT RETURNING.
• DEFAULT values assigned by the database, and not know by the application. Since DEFAULT values are logically part of a column definition, it is correct to write them in the database. And since DEFAULT values may change over time (just like any other metadata), it is better to only write them in the database, to avoid the hassle of keeping defaults in sync in different places. But without INSERT RETURNING, the required effort would be unreasonable.
• Generates columns values can be retrieved by the application, for the same reasons. Generated columns can be seen as DEFAULT values that cannot be changed by the user.
• BEFORE INSERT triggers may modify the values we insert. INSERT RETURNING will return the modified values.
• We hardly know for sure which rows are inserted when we run INSERT ... SELECT. Using INSERT SELECT RETURNING solves this problem.

Another case is INSERT ... ON DUPLICATE KEY UPDATE. It should always be used when we need to insert a row or change it if it exists, as it allows to do that with a single statement. (As mentioned elsewhere, we shouldn’t use REPLACE for this).

But then, there are additional reasons why we may not know the value of a column:

• We insert a certain value (VALUES clause) if the row does not exist, and a different value (UPDATE clause) if the row exists.
• We don’t modify a value in case the row exists: it value won’t change, but it is unknown.

Note that INSERT RETURNING works with multi-row inserts too.

MariaDB [test]> INSERT INTO user
    (name, is_smart) VALUES
    ('Boris Johnson', FALSE),
    ('Peter Capaldi', TRUE)
    RETURNING id AS pk;
+----+
| pk |
+----+
|  1 |
|  2 |
+----+

(These values are clearly the output of a random generator)

ORMs often run INSERT followed by a SELECT. This is probably because of the reasons listed above, in particular they probably want to know the the row’s id. My advice is to run INSERT RETURNING instead, when using MariaDB; or some equivalent syntax when using other DBMSs that support it.

REPLACE RETURNING

As mentioned above, I don’t believe it is a good idea to use REPLACE, in most cases. Use INSERT ... ON DUPLICATE KEY UPDATE instead.

That said, REPLACE RETURNING works like INSERT RETURNING.

Checking DELETEd or INSERTed rows

Using DELETE RETURNING or INSERT RETURNING to check how many rows were deleted/inserted is not necessary. We can use mysql_affected_rows() instead (every API has an equivalent function), or ROW_COUNT() in stored procedures. Also, if you use a transactional engine all rows are inserted or no row is.

Using DELETE RETURNING or INSERT RETURNING to check if the operation succeeded is not necessary. We can just check if the statement failed with an error instead.

State of the art

Let’s see RETURNING limitations and features that work fine with RETURNING.

This article will be updated if some limitations are lifted or UPDATE RETURNING is implemented.

RETURNING Limitations

Db2 supports a way to get the resultsets before triggers and foreign keys are executed. MariaDB does not have this feature.

RETURNING statements cannot be used in these contexts:

• UPDATE RETURNING.
• Multi-table DELETEs.
• INSERT SELECT RETURNING.
• CREATE TABLE SELECT RETURNING.
• Subquery: SELECT * FROM (INSERT RETURNING) WHERE ....
• With cursors in stored procedures.
• RETURNING does not support aggregate functions (for example, this is not valid: RETURNING COUNT(*)).

Working features

The following features and syntax can be used in a RETURNING clause:

• RETURNING * (return all columns).
• AS (column aliases).
• Built-in functions and operators, eg: RETURNING UPPER(name), price - 1.
• Stored functions, eg: RETURNING my_stored_func(id).

RETURNING can also be used in these contexts:

• Prepared statements.
• Stored procedures body.

See also

Articles

• MySQL/MariaDB: use SQL properly to run less queries

Conclusions

RETURNING can often be used to run one less query, making our applications faster and saving resources.