MySql : call LAST_INSERT_ID() after insert to get the last generated id.

[mmrath]

Setup

Versions

• Rust: 1.20-nightly
• Diesel: 0.14
• Database: MySQL
• Operating System ALL

Feature Flags

• diesel:
• diesel_codegen:

Problem Description

After insert into a table with auto_incremented primary key, we need to get the generated id after the insert.

What are you trying to accomplish?

In PG the following works.

let tag1: Tag = diesel::insert(&cat_tag)
        .into(tags::table)
        .get_result(conn)
        .expect("Error saving cat tag");

But we don't have anything similar for MySQL

What is the expected output?

Something like the above to work in MySQL

What is the actual output?

Are you seeing any additional errors?

Steps to reproduce

Checklist

• I have already looked over the issue tracker for similar issues.

[sgrif]

LAST_INSERT_ID has many gotchas that I'm not comfortable implicitly relying on. If you'd like to use LAST_INSERT_ID, you can very easily do so with the sql_function! macro.

[iddm]

@sgrif could you be so kind giving an example please? (I am interested in sqlite and postgre-sql).

[mmrath]

@vityafx In case you have not found a solution yet.

in my db module I have

no_arg_sql_function!(last_insert_id, types::Bigint);

I use it as below

let generated_id: i64 = select(db::last_insert_id).first(conn).unwrap();

[haruhinoshana]

@mmrath sir do you have a sample to implement this get last_insert_id im new at diesel and rust itself it will be a big help

[kevinmichaelchen]

@sgrif What's your recommended way of getting the ID of the last inserted record?

[weiznich]

@kevinmichaelchen There is no good solution for this on sqlite and mysql.
There are several bad solutions:

• SELECT id from table ORDER BY id DESC LIMIT 1;
• Use LAST_INSERT_ID that has some issues as noted by Sean above
• Generate the id in your rust code
• Use postgresql and returning 😉

[kevinmichaelchen]

@weiznich The downsides of LAST_INSERT_ID are enumerated here. If you don't use LAST_INSERT_ID in a transaction, then it just returns the last ID of an inserted record in any table.

Generating the ID in Rust code seems fine if the primary key is a UUID.

Overall, I think I'm leaning toward Postgres, since it has the RETURNING keyword and natively supports UUID.

[ysangkok]

@mmrath I would change

no_arg_sql_function!(last_insert_id, types::Bigint); into no_arg_sql_function!(last_insert_id, types::Unsigned<types::Bigint>); since that is what the function actually returns.