How to get SQL / MariaDB INSERT result-CodePudding

one thing I can't really find is to get the new ROW as result after a SQL INSERT.

last_inserted_id() only works if it has an INT ID and if I'm not mistaken it also has to be auto incremented. What I tried now is to create a temporary table and INSERT into the two tables.

It works, only now it is missing the correct ID because it has its own value 0 due to the new table and not like the original ROW. I want to include it in my PHP code and it should also work with different DB tables. Ah and I use MariaDB 10.3 .

A consideration would be to insert a statement in between "if have last_insert_id then a SELECT INTO ... where id = last_inserted etc. else the snippet below".

CREATE OR REPLACE TEMPORARY TABLE tmp_school AS SELECT * FROM school LIMIT 0;
INSERT INTO school ( school_name ) VALUES ( 'blubb' );
INSERT INTO tmp_school ( school_name ) VALUES ( 'blubb' );
SELECT * FROM tmp_school;

CodePudding user response：

may be you can try this :

INSERT INTO tmp_school (school_name) VALUES ('blubb') RETURNING school_name ;

It works the same if you insert several values at once in the regular (not temporary) table:

INSERT INTO school (school_name) VALUES ('blubb'), ('abc'), ('bcd') RETURNING school_name ;

Hope this will help, if your MariaDB server version, supports this statement

CodePudding user response：

RETURNING syntax is exactly the same as a SELECT, so you use RETURNING * as well :

INSERT INTO school (school_name) VALUES ('blubb'), ('abc'), ('bcd') 
RETURNING * ;

This query will insert 3 lines into school table and display the same 3 lines