For what I know this is not in the SQL:2003 standard.
http://www.savage.net.au/SQL/sql-2003-2.bnf.html#insert%20statement

This might save a roundtrip. But I do think last_insert_id sent to the client anyways, so a 'SELECT LAST_INSERT_ID()' should not be needed.

This could also allow multiple columns like it does in PostgreSQL. This could be efficient if defaults are used.

Example:

postgres=# CREATE TABLE t2(id SERIAL, name VARCHAR(100), language CHAR(2) DEFAULT 'en');

CREATE TABLE

postgres=# INSERT INTO t2(name) VALUES('test') RETURNING id,language;

 id | language 

----+----------

  1 | en

(1 row)

INSERT 0 1

So from a client perspective normally you would need to do this:

• insert a new record
• do a select based on last_insert_id to get the language for the new row
• do something in the code which uses the language.

Often the solution to this is to keep all defaults client side to prevent the select, but then ad-hoc sql or other scripts might use other defaults. It should be possible to keep the defaults in the database w/o losing efficiency.

In reply to Daniël, this is extremely beneficial when you are bulk adding rows plus their relationships when innodb_autoinc_lock_mode = 2 (required for Galera clusters). As the insert IDs cannot be guaranteed with innodb_autoinc_lock_mode=2 the last insert id is not sufficient when adding multiple rows: To add 100 rows the way Daniël described it would cause 200 extra round trips.

Also, afaik the protocol only returns the LAST auto increment id of your query, not the auto increment ids of the rows in between.

With this feature implemented, you can multi-row insert 100 rows and get the corresponding ids back in 1 rtt.

Another new possibility with this feature: when doing an INSERT IGNORE .... RETURNING query you can see which rows actually inserted successfully, and trigger stuff in the application based on these results.

Very nice work rucha174 and everybody else involved, I am looking forward to it being released.

sanja, serg,

Could you please specify here in JIRA description:

• whether INSERT IGNORE ... RETURNING (also mentioned in the previous comment) is supported, and if so, what it should return – what was attempted to be inserted, or what actually was;
• whether REPLACE ... RETURNING is supported, and if so, what it should return – what was newly inserted or everything;
• what should happen when certain values are ignored upon INSERT (e.g. they were attempted to be inserted into virtual columns) – should they be returned or not;
• what should happen if INSERT failed half-way (e.g. while inserting into a non-transactional table and hitting a duplicate key, but can be on various reasons);

probably more questions to follow

see https://github.com/MariaDB/server/compare/bb-10.5-serg...bb-10.5-10014

Generally all is OK except returning non-inserted data, so tests should be added:

• INSERT IGNORE with some errors and it should not return what was not inserted
• INSERT with trigger and ON DUPLICATE which change inserted data and return should return changed values
• INSERT inside stored procedure (with multiple sets return)
• INSERT inside function and trigger (even if it is an error)
• "insert t1 set a=(select a+3);" will be turned to "insert t1 set a=(a+3)", so if we allow subqueries it will be better to put something which does not transform and have additional table "insert t1 set a=(select c from t2 where a=b)"
• in the following query it would be better to return val to see if it was changed

  INSERT IGNORE INTO ins_duplicate(id,val) VALUES (3,'c'),(4,'d') ON DUPLICATE

  KEY UPDATE val='g' RETURNING id;

post-review fixes: https://github.com/MariaDB/server/commit/b4626e12e2df239051ab6738b4cbfb6c6e10ac3a

With this feature implemented, you can multi-row insert 100 rows and get the corresponding ids back in 1 rtt.

Is the result set guaranteed to be in the same order as value list?