When inserting into mysql table from delphi Firedac i need the new Autoincrement id-CodePudding

I have a table with an auto incremental ID. Multiple users are going to insert into the table. There are no identifiable fields I can use to find the record I just added and need the auto-id of the record. What is the easiest way to get the latest ID of the newly added record?

One idea is to generate a GUID in the app and add it to the record and use that to identify the newly added record, is there any better way to do this?

CodePudding user response：

We can use a trigger to record the last insert id as well as the database user doing the insert statement.Note: This does not distinguish the Application users, as it's possible that multiple application users use the same DB user account to do their jobs)

create table test (id int primary key auto_increment, content varchar(10));
create table record (id int,content varchar(10),`user` varchar(20));

delimiter //
create trigger record_insert_id after insert on test for each row
begin
if not exists (select 1 from record where `user`=user()) then
insert record values(new.id,new.content,user());
else
update record set id=new.id,content=new.content where `user`=user();
end if; 
end//
delimiter ;

-- have user1 insert:
insert test (content) values('aaa'),('bbb');

-- have user2 insert:
insert test (content) values('xxx'),('yyy');

-- have user3 insert:
insert test (content) values('abcxyz');

select * from record;

-- result set:
# id, content, user
2, bbb, user1@localhost
4, yyy, user2@localhost
5, abcxyz, user3@localhost

CodePudding user response：

Use

SELECT LAST_INSERT_ID()

to get the LAST ID of the last row that has been inserted or updated in a table.