MySql: Insert a row and get the content

Execute your insert statement and then you can do this:

SELECT * FROM `items` WHERE `id`= LAST_INSERT_ID()

you can call a stored procedure which will perform the insert and return a resultset in a single call from your app layer to mysql:

Stored procedure call

mysql> call insert_user('bar');
+---------+----------+
| user_id | username |
+---------+----------+
|       1 | bar      |
+---------+----------+
1 row in set (0.02 sec)

$sqlCmd = sprintf("call insert_user('%s')", ...);

Simple example:

drop table if exists users;
create table users
(
user_id int unsigned not null auto_increment primary key,
username varchar(32) unique not null
)
engine=innodb;


drop procedure if exists insert_user;

delimiter #

create procedure insert_user
(
in p_username varchar(32)
)
begin
declare v_user_id int unsigned default 0;

 insert into users (username) values (p_username);

 set v_user_id = last_insert_id();

 -- do more stuff with v_user_id e.g. logs etc...

 select * from users where user_id = v_user_id;

end#

delimiter ;

call insert_user('bar');

No, it's not possible in MySQL (unlike PostgreSQL, SQL Server and PL/SQL in Oracle).

You will have to do it in separate queries.

You can do this using multiple statements if you like to choose that route. Firstly when you connect to your database make sure that multiple statements is set to true:

var connection = mysql.createConnection({
    host: databaseHost,
    user: databaseUser,
    password: databasePassword,
    database: databaseName,
    multipleStatements: true
});

Then you can just define your sql as:

var sql = "your insert statement; your select statement";

Just separate individual statements using the semi colon. Your select result will be results[1] in this example.

MySql: Insert a row and get the content

Related

Recent Posts