Get last deleted ID in MySQL

Question

Similar to LAST_INSERT_ID(), is there a nice mechanism in MySQL to get the last deleted ID after a row has been deleted?

Why do you need values that are not longer in database? For a more accurate answer this is a essential issue. — dani herrera, Dec 22 '11 at 09:39
@daniherrera: I have a table with billions of rows and I want to delete some of them without taking down the server, hence without holding a lock for too long. I've written a script to delete in batches, I need the last deleted id to make the search faster on the next batch. — Benoit Duffez, Oct 14 '22 at 12:25
I was able to use `returning` with MariaDB, but MySQL users may find this method interesting: https://stackoverflow.com/a/33115929/334493 — Benoit Duffez, Oct 14 '22 at 12:25

score 12 · Accepted Answer · answered Dec 20 '11 at 23:05

By "ID", I assume you mean "auto-increment"?

Since you can delete any arbitrary row (or set of rows) at any time: no, there's no way to tell WHICH row (or rows) you most recently deleted.

You can, however, create a "trigger" to save this information for you:

http://dev.mysql.com/doc/refman/5.0/en/triggers.html

score 5 · Answer 2 · edited Dec 08 '15 at 17:38

Instead of creating a trigger, you need to use this each and every time when you delete

declare @table1 table(id int identity,name varchar(50))
     insert into @table1 (name)  values('abc')
     insert into @table1 (name)  values('xyz')
      insert into @table1 (name) values('pqr')
       insert into @table1 (name)  values('wqe')
     delete from  @table1 output deleted.name,deleted.id where  id=3

score 2 · Answer 3 · answered Dec 22 '11 at 10:16

It depends on how you make deletions. But if you have an integer id column, you can use the following hack:

DELETE FROM users
WHERE login = 'newtover' AND user_id = LAST_INSERT_ID(user_id);

SELECT LAST_INSERT_ID();

But you should make sure that MySQL short-circuits the former condition and does not optimize to run user_id = LAST_INSERT_ID(user_id) first. That is you can adjust the query to something like:

DELETE FROM users
WHERE login = 'newtover' AND IFNULL(user_id, 0) = LAST_INSERT_ID(user_id);

P.S. I do not ask why you might need this. Most probably, you should not want it =)

score 0 · Answer 4 · answered Dec 08 '15 at 17:52

If you happen to be calling your MySQL database from JDBC, then you can execute a SELECT and call ResultSet.deleteRow() as you read through the results and grab the id's.

import java.sql.*;

public class Delete {
    public static void main(String... args) throws SQLException {
        try(Connection conn = DriverManager.getConnection("jdbc:mysql://localhost/test?user=john&password=denver");
            PreparedStatement statement = conn.prepareStatement("select id from some_table where event=?")
        ) {
            statement.setString(1, "test");
            try(ResultSet result = statement.executeQuery()) {
                System.out.println("deleting id " + result.getLong("id"));
                result.deleteRow();
            }
            conn.commit();
        }
    }
}

Example table

create table some_table(
  id bigint(12),
  event varchar(100)
);

insert into some_table values(1, 'test');
insert into some_table values(2, 'test');
insert into some_table values(3, 'test');
insert into some_table values(4, 'other');

score 0 · Answer 5 · answered Jun 23 '17 at 10:45

The hack with last_insert_id was already mentioned, but that answer misses the fact that it could aggregate!

last insert id has fixed size but for small keys it could be used.

mysql> insert into t1 () values (),(),(),(),(),(),();
Query OK, 7 row affected (0.00 sec)

mysql> select * from t1;
+---+
| n |
+---+
| 1 |
| 2 |
| 3 |
| 4 |
| 5 |
| 6 |
| 7 |
+---+
7 rows in set (0.00 sec)
select last_insert_id(0); -- clear accumulator
+-------------------+
| last_insert_id(0) |
+-------------------+
|                 0 |
+-------------------+
1 row in set (0.00 sec)

-- keys will be separated by zeroes
mysql> delete from t1 
        where last_insert_id(last_insert_id() 
                             * pow(10, 2 + floor(log(n)/log(10))) + n)
        limit 6;  
Query OK, 6 rows affected (0.00 sec)

mysql> select last_insert_id();
+------------------+
| last_insert_id() |
+------------------+
|      10203040506 |
+------------------+
1 row in set (0.00 sec)

-- rows deleted
mysql> select * from t1 limit 1;
+---+
| n |
+---+
| 7 |
+---+
1 row in set (0.00 sec)

Get last deleted ID in MySQL

5 Answers5

Linked

Related