Mysql choose to remove

Question

Edit: I found a solution here http://mysql.bigresource.com/Track/mysql-8TvKWIvE/ assuming the selection is time consuming, will this lock the table for a long time?

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

I want to use criteria to select rows in mysql, return them to my application as a result set, and then delete these rows. How can this be done? I know I can do the following, but this is too inefficient:

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

Do I need to use a transaction? Is there one solution for queries?

Answer 1

Do I need to use a transaction? Is there one solution for queries?

Yes, you need to use a transaction. You cannot delete and select rows in a single query (i.e. there is no way to “return” or “select” deleted rows).

You do not have to use a parameter REPEATABLE READ- I believe you can also select rows FOR UPDATE, although this is a higher level of blocking. REPEATABLE READseems to be the lowest lock level that can be used to securely complete this transaction. By default, this value is for InnoDB.

, , wee . , , MySQL .

:

• - ( )
• http://dev.mysql.com/doc/refman/5.0/en/set-transaction.html
• http://dev.mysql.com/doc/refman/5.0/en/innodb-locking-reads.html

Answer 2

SELECT , - , DELETE , , , SELECT.

REPEATABLE READ . . . , :

SELECT , DML. , DELETE UPDATE, REPEATABLE READ, , .

:

:

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

( 1):

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

( 2) . , .

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

. 1:

SELECT * FROM x;
DELETE FROM x;
COMMIT;

2:

SELECT * FROM x;

, , SELECT 1, . 2 , . 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.

• SERIALIZABLE. , 2 INSERT.
• , SELECT...FOR UPDATE . 100% , , , . , . , 2 INSERT.
• SELECT. ( ) SELECT, DELETE FROM x WHERE i IN (...) - , IN SELECT, , , 2 . , SQL-. , , , WHERE, SELECT, SELECT WHERE , , .

, , , , "". , MySQL ( - , -).

Answer 3

.

select * from MyTable t where _critera_
//get the resultset and then
delete from MyTable where _critera_

Answer 4

select. , . mySQL, IN.

Answer 5

, , , . SELECT FROM WHERE FOR UPDATE , SELECT FOR UPDATE SELECT INTO tmp_table FOR UPDATE. , , .

- ( )

START TRANSACTION;
SELECT a,b into TMP_TABLE FROM table_a WHERE a=1 FOR UPDATE;
DELETE FROM table_a 
  USING table_a JOIN TMP_TABLE ON (table_a.a=TMP_TABLE.a, table_a.b=TMP_TABLE.b)
  WHERE 1=1;
COMMIT;

Now your records are deleted from the source table, but you also have a copy in the temporary table that you can save or delete.

Answer 6

Run the SELECT statement with the WHERE clause, and then use the same WHERE clause in the DELETE clause. Assuming there were no temporary changes to the data, the same rows should be deleted.

EDIT: Yes, you can set this as a single transaction so that there are no changes to the tables.