mysql选择删除

Question

编辑:我在这里找到了解决方案 http://mysql.bigresource.com/Track/mysql-8TvKWIvE/假设 select 需要很长时间才能执行，这是否会长时间锁定表？

SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ;START TRANSACTION;SELECT foo FROM bar WHERE wee = 'yahoo!';DELETE FROM bar WHERE wee = 'yahoo!';COMMIT;

我希望使用一个条件来选择 mysql 中的行，将它们作为结果集返回到我的应用程序，然后删除这些行。如何才能做到这一点？我知道我可以执行以下操作，但效率太低:

select * from MyTable t where _critera_.//get the resultset and thendelete from MyTable t where t.id in(...result...)

我需要使用交易吗？有单一的查询方案吗？

Answer 1

我需要根据某些条件SELECT一些行，对数据做一些事情，然后原子地DELETE那些相同的行，也就是说，没有 删除所有符合条件但插入到 SELECT 之后的行。

与其他答案相反，REPEATABLE READ 是不足够的。引用Consistent Nonlocking Reads .特别注意这个标注:

The snapshot of the database state applies to SELECT statements within a transaction, not necessarily to DML statements. If you insert or modify some rows and then commit that transaction, a DELETE or UPDATE statement issued from another concurrent REPEATABLE READ transaction could affect those just-committed rows, even though the session could not query them.

你可以自己试试:

首先创建一个表:

CREATE TABLE x (i INT NOT NULL, PRIMARY KEY (i)) ENGINE = InnoDB;

启动事务并检查表(现在称为 session 1):

SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;
START TRANSACTION;
SELECT * FROM x;

开始另一个 session ( session 2)并插入一行。 请注意此 session 处于自动提交模式。

INSERT INTO x VALUES (1);
SELECT * FROM x;

您将看到新插入的行。然后再次回到 session 1:

SELECT * FROM x;
DELETE FROM x;
COMMIT;

在 session 2 中:

SELECT * FROM x;

您会看到，即使您从 session 1 中的 SELECT 中没有得到任何信息，您也会删除一行。在 session 2 中，您将看到最后的表格是空的。请特别注意 session 1 的以下输出:

mysql> SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;
Query OK, 0 rows affected (0.00 sec)

mysql> START TRANSACTION;
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT * FROM x;
Empty set (0.00 sec)

/* --- insert in session 2 happened here --- */

mysql> SELECT * FROM x;
Empty set (0.00 sec)

mysql> DELETE FROM x;
Query OK, 1 row affected (0.00 sec)

mysql> COMMIT;
Query OK, 0 rows affected (0.06 sec)

mysql> SELECT * FROM x;
Empty set (0.00 sec)

此测试是使用 MySQL 5.5.12 完成的。

对于正确的解决方案

1. 使用SERIALIZABLE 事务隔离级别。但是请注意， session 2 将在 INSERT 上阻塞。
2. 似乎 SELECT...FOR UPDATE 也能解决问题。我没有 100% 深入研究手册来理解这一点，但是当我尝试它时它起作用了。优点是您不必更改事务隔离级别。同样， session 2 将在 INSERT 上阻塞。
3. 在 SELECT 之后单独删除行。基本上，您必须在 SELECT 中包含一个唯一的列(最好是主键)，然后使用 DELETE FROM x WHERE i IN (...)，或类似的东西，其中 IN 包含来自 SELECT 结果集的键列表。优点是你根本不需要使用事务， session 2 不会在任何时候被阻塞。缺点是你有更多的数据来回发送到 SQL 服务器。此外，我不知道单独删除行是否与使用与原始 SELECT 相同的 WHERE 子句一样有效，但如果原始 SELECT 的 WHERE 子句很复杂或很慢，单个删除可能会更快，因此这可能是另一个优势。

社论，这是非常危险的事情之一，即使它被记录在案，它也几乎可以被认为是一个“错误”。但是，嘿，MySQL 的设计者并没有问我(或者其他任何人，显然)。