How to insert a row if not exists otherwise select and return its ID in both cases in MariaDB?

Question

I have a table with ID primary key (autoincrement) and a unique column Name. Is there an efficient way in MariaDB to insert a row into this table if the same Name doesn't exist, otherwise select the existing row and, in both cases, return the ID of the row with this Name?

Here's a solution for Postgres. However, it seems MariaDB doesn't have the RETURNING id clause. What I have tried so far is brute-force:

INSERT IGNORE INTO services (Name) VALUES ('JohnDoe');
SELECT ID FROM services WHERE Name='JohnDoe';

UPDATE: MariaDB 10.5 has RETURNING clause, however, the queries I have tried so far throw a syntax error:

WITH i AS (INSERT IGNORE INTO services (`Name`) VALUES ('John') RETURNING ID)
SELECT ID FROM i
UNION
SELECT ID FROM services WHERE `Name`='John'

Have you seen this: [MariaDB: INSERT...RETURNING](https://mariadb.com/kb/en/insertreturning/) — Maciej Los, Jul 16 '20 at 09:13
@MaciejLos, will it return the rows that already existed, but would be inserted with `INSERT IGNORE`? — Serge Rogatch, Jul 16 '20 at 09:25

score 1 · Answer 1 · answered Jul 16 '20 at 18:48

For a single row, assuming id is AUTO_INCREMENT.

INSERT INTO t (name)
    VALUES ('JohnDoe')
    ON DUPLICATE KEY id = LAST_INSERT_ID(id);
SELECT LAST_INSERT_ID();

That looks kludgy, but it is an example in the documentation.

Caution: Most forms of INSERT will "burn" auto_inc ids. That is, they grab the next id(s) before realizing that the id won't be used. This could lead to overflowing the max auto_inc size.

It is also wise not to put the normalization inside the transaction that does the "meat" of the code. It ties up the table unnecessarily long and runs extra risk of burning ids in the case of rollback.

For batch updating of a 'normalization' table like that, see my notes here: http://mysql.rjweb.org/doc.php/staging_table#normalization (It avoids burning ids.)

How to insert a row if not exists otherwise select and return its ID in both cases in MariaDB?

1 Answers1