How can I get last insert id for MySQL

[5221txp]

In PostgreSQL we can return the id after inserting record such as
INSERT INTO users ( username, email, password ) VALUES ( $1, $2, $3 ) RETURNING id, username, email

But the syntax doesn't work in MySQL and MySQL doesn't have any equivalent syntax. So I have to make a separate select to get last insert id
SELECT LAST_INSERT_ID()

Is there any more convenient way to do this?

[xiaopengli89]

I have the same issue, and SELECT LAST_INSERT_ID() return no rows.

[5221txp]

actually i use SELECT LAST_INSERT_ID() AS id and got correct insert id

[xiaopengli89]

It's my mistake, SELECT LAST_INSERT_ID() AS id working very well.

[mehcode]

Currently, this is the best option:

SELECT LAST_INSERT_ID() as id

But I'm sure we can do better. Currently execute returns the affected rows directly. Perhaps we could instead return something that has affected_rows() and last_insert_id(). The latter would make sense for MySQL and SQLite and 2 of 3 is enough for us to try and support it. This is better than LAST_INSERT_ID as it's populated by the protocol and not a function call. I think there is a small behavior difference too ( the function call in MySQL doesn't change it's value if you override an auto-increment but the protocol populated value should ).

Thoughts @abonander ?

[abonander]

I can't find where in the protocol it gives the last inserted ID.

[mehcode]

@abonander MySQL gives it in the OK packet.

[Freax13]

there are a couple of ways to approach this:

1. Result<(u64, u64), Error>
2. Result<(u64, Option<u64>), Error>
3. use an associated type with the DB like Result<DB::ExecResult, Error>

[mehcode]

I've been thinking this over.

trait Executor {
  fn execute(&mut self, statement: impl Execute) -> impl Statement;

  // [...]
}

trait Statement where Self: Future<Output = crate::Result<()>> {
  type Database: Database;

  async fn affected_rows(self) -> crate::Result<u64>;

  fn fetch(self) -> impl Cursor;

  async fn fetch_one<T>(self) -> crate::Resul<T>;
  async fn fetch_scalar<T>(self) -> crate::Result<T>;
}

impl Statement for MySqlStatement { ... }

impl MySqlStatement {
  async fn last_insert_id(self) -> crate::Result<u64>;
}

• Makes fetch methods a provided on Executor as it can simply be self.execute(query).fetch(). This brings us full circle and enables users to treat the execute method as a swiss army knife of querying.

• Allows for database-specific extensions to the <DB>Statement object such as last_insert_id() in MySQL.

Unresolved question:

• Should Statement implement Future ? In other words..

  conn.execute("BEGIN").await?; // is this okay?

[pymongo]

Wrap INSERT and SELECT LAST_INSERT_ID() in a transaction
to ensure two query in a same MySqlConnection.

let username = "test";
let mut db_conn_with_transaction = db_pool.begin().await.unwrap();
sqlx::query("INSERT INTO users (username) VALUES (?);")
    .bind(username)
    .execute(&mut db_conn_with_transaction)
    .await
    .unwrap();
let last_insert_id = sqlx::query_as::<_, (u32,)>("SELECT LAST_INSERT_ID();")
    .fetch_one(&mut db_conn_with_transaction)
    .await
    .unwrap()
    .0;
dbg!(last_insert_id);
assert_ne!(last_insert_id, 0);
db_conn_with_transaction.commit().await.unwrap();

In MySQL general log

INSERT and INSERT and SELECT LAST_INSERT_ID() in a same session id(46030)

2020-05-20T11:54:19.591863Z	46030 Query	BEGIN
2020-05-20T11:54:19.600911Z	46030 Prepare	INSERT INTO users (username) VALUES (?)
2020-05-20T11:54:19.601129Z	46030 Execute	INSERT INTO users (username) VALUES ('test')
2020-05-20T11:54:19.603276Z	46030 Prepare	SELECT LAST_INSERT_ID()
2020-05-20T11:54:19.603418Z	46030 Execute	SELECT LAST_INSERT_ID()
2020-05-20T11:54:19.603637Z	46030 Query	COMMIT

[mehcode]

Please see #508 and leave feedback

[mehcode]

Now available on master

[Stargateur]

Cross link: https://stackoverflow.com/q/63755877/7076153

[vincascm]

use Google search will be here, so add a little example here

sqlx::query("insert into xxx ...")
    .execute(&db)
    .await?
    .last_insert_id();

for MySQL

[moom-en]

For mssql, how to get the inserted id value?