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:
Feature request: MDEV-5092 – Implement UPDATE with result set.
MariaDB RETURNING statements
Let’s go through the supported
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 jo**@do********.uk has been removed from our archives”.
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;
DELETEs do not support
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_INCREMENTprimary key. This is the most common reason to use
DEFAULTvalues assigned by the database, and not know by the application. Since
DEFAULTvalues are logically part of a column definition, it is correct to write them in the database. And since
DEFAULTvalues 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
DEFAULTvalues that cannot be changed by the user.
BEFORE INSERTtriggers may modify the values we insert.
INSERT RETURNINGwill return the modified values.
- We hardly know for sure which rows are inserted when we run
INSERT ... SELECT. Using
INSERT SELECT RETURNINGsolves 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 (
VALUESclause) if the row does not exist, and a different value (
UPDATEclause) if the row exists.
- We don’t modify a value in case the row exists: it value won’t change, but it is unknown.
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.
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.
REPLACE RETURNING works like
Checking DELETEd or INSERTed rows
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.
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
RETURNING limitations and features that work fine with
This article will be updated if some limitations are lifted or
UPDATE RETURNING is implemented.
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:
CREATE TABLE SELECT.
SELECT * FROM (.
INSERT RETURNING) WHERE ...
- With cursors in stored procedures.
RETURNINGdoes not support aggregate functions (for example, this is not valid:
The following features and syntax can be used in a
RETURNING *(return all columns).
- Built-in functions and operators, eg:
RETURNING UPPER(name), price - 1.
- Stored functions, eg:
RETURNING can also be used in these contexts:
- Prepared statements.
- Stored procedures body.
RETURNING can often be used to run one less query, making our applications faster and saving resources.
I admire your piece of work, thankyou for all the interesting posts.
Amazing how under used or even known RETURNING is, and the lengths you would have go to without it. (When I say would I actually mean HAVE. Have gone to.. we all do it until we know that whiff in the air making your face scrunch further with every keystroke is “code smell”. When writing your own code has the same effect on your face as fresh dog poop! )
Enjoying the read, clear, simple and immediately understood.so many many tabs to get to.