获取 MySQL 中最后删除的 ID
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/8583106/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Get last deleted ID in MySQL
提问by pillarOfLight
Similar to LAST_INSERT_ID(), is there a nice mechanism in MySQL to get the last deleted ID after a row has been deleted?
与 类似LAST_INSERT_ID()，MySQL 中是否有一种很好的机制可以在删除行后获取最后删除的 ID？
采纳答案by paulsm4
By "ID", I assume you mean "auto-increment"?
通过“ID”，我假设您的意思是“自动增量”？
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:
但是，您可以创建一个“触发器”来为您保存此信息：
回答by Tamkeen
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
回答by newtover
It depends on how you make deletions. But if you have an integer id column, you can use the following hack:
这取决于您如何进行删除。但是如果你有一个整数 id 列，你可以使用以下技巧：
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:
但是您应该确保 MySQL 短路了前一种情况，并且不会优化先运行user_id = LAST_INSERT_ID(user_id)。也就是说，您可以将查询调整为：
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 =)
PS我不问你为什么需要这个。很可能，你不应该想要它 =)
回答by Kirby
If you happen to be calling your MySQL database from JDBC, then you can execute a SELECTand call ResultSet.deleteRow()as you read through the results and grab the id's.
如果您碰巧从 JDBC 调用您的 MySQL 数据库，那么您可以在阅读结果并获取 ID 时执行 aSELECT和 call ResultSet.deleteRow()。
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');
回答by Daneel S. Yaitskov
The hack with last_insert_id was already mentioned, but that answer misses the fact that it could aggregate!
已经提到了使用 last_insert_id 的 hack，但是那个答案忽略了它可以聚合的事实！
last insert id has fixed size but for small keys it could be used.
last insert id 具有固定大小，但对于小键可以使用它。
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)
