Pregunta MySQL selecciona para eliminar


Editar: Encontré una solución aquí http://mysql.bigresource.com/Track/mysql-8TvKWIvE/ Suponiendo que la selección demore mucho tiempo en ejecutarse, ¿esto bloqueará la tabla durante mucho tiempo?

AJUSTE DE LA SESIÓN TRANSACCIÓN NIVEL DE AISLAMIENTO REPETIBLE LEÍDO;
INICIAR LA TRANSACCIÓN;
SELECCIONE foo FROM bar WHERE wee = 'yahoo!';
BORRAR DE la barra WHERE wee = 'yahoo!';
COMETER;

Deseo usar un criterio para seleccionar las filas en mysql, devolverlas a mi aplicación como conjunto de resultados y luego eliminarlas. ¿Cómo puede hacerse esto? Sé que puedo hacer lo siguiente, pero es demasiado ineficiente:

seleccione * de MyTable t donde _critera_.
// obtener el conjunto de resultados y luego
eliminar de MyTable t donde t.id in (... resultado ...)

¿Necesito usar una transacción? ¿Hay una sola solución de consulta?


5
2018-02-06 07:46


origen


Respuestas:


¿Necesito usar una transacción? ¿Hay una sola solución de consulta?

Sí, necesitas usar una transacción. No puede eliminar y seleccionar filas en una sola consulta (es decir, no hay manera de "regresar" o "seleccionar" las filas que ha eliminado).

No necesariamente necesitas hacer el REPEATABLE READ opción - Creo que también puede seleccionar las filas FOR UPDATE, aunque este es un nivel más alto de bloqueo. REPEATABLE READ parece ser el nivel más bajo de bloqueo que podría usar para ejecutar esta transacción de manera segura. Pasa a ser el predeterminado para InnoDB.

Cuánto afecta esto a su tabla depende de si tiene un índice en el wee columna o no. Sin eso, creo que MySQL tendría que bloquear escribe toda la tabla.

Otras lecturas:


3
2018-02-06 08:50



Lo necesitaba SELECT algunas filas según algunos criterios, hacer algo con los datos, y luego DELETE esas mismas filas atómicamente, es decir, sin borrando cualquier fila que cumpla con los criterios pero se insertaron después del SELECT.

Al contrario de otras respuestas, REPEATABLE READ es no suficiente. Referirse a Lecturas constantes sin bloqueo. En particular, tenga en cuenta esta frase:

La instantánea del estado de la base de datos se aplica a SELECT declaraciones dentro de una transacción, no necesariamente a declaraciones DML. Si inserta o modifica algunas filas y luego confirma esa transacción, DELETE o UPDATE declaración emitida desde otro concurrente REPEATABLE READ La transacción podría afectar esas filas recién confirmadas, aunque la sesión no pudo consultarlas.

Puedes probarlo tú mismo:

Primero crea una tabla:

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

Inicie una transacción y examine la tabla (ahora se llamará sesión 1):

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

Inicie otra sesión (sesión 2) e inserte una fila. Tenga en cuenta que esta sesión está en modo de confirmación automática.

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

Verás tu fila recién insertada. Luego, regrese a la sesión 1 nuevamente:

SELECT * FROM x;
DELETE FROM x;
COMMIT;

En la sesión 2:

SELECT * FROM x;

Verás que, aunque no obtienes nada de la SELECT En la sesión 1, borras una fila. En la sesión 2 verá que la mesa está vacía al final. Tenga en cuenta el siguiente resultado de la sesión 1 en particular:

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)

Esta prueba se realizó con MySQL 5.5.12.

Para una solucion correcta

  1. Utilizar SERIALIZABLE nivel de aislamiento de transacción. Sin embargo, tenga en cuenta que la sesión 2 se bloqueará en la INSERT.
  2. Parece que SELECT...FOR UPDATE también hará el truco. No he estudiado el manual al 100% en profundidad para entender esto, pero funcionó cuando lo probé. La ventaja es que no tiene que cambiar el nivel de aislamiento de la transacción. Una vez más, la sesión 2 bloqueará en la INSERT.
  3. Elimine las filas individualmente después de SELECT. Básicamente, debe incluir una columna única (la clave principal sería buena) en el SELECT y luego usa DELETE FROM x WHERE i IN (...), o algo similar, donde IN contiene una lista de claves del SELECTconjunto de resultados de. La ventaja es que no necesita usar ninguna transacción y la sesión 2 no se bloqueará en ningún momento. La desventaja es que tiene más datos para enviar y recibir en el servidor SQL. Además, no sé si eliminar las filas individualmente es tan eficiente como usar el mismo WHERE cláusula como el original SELECT, pero si el original SELECTes WHERE La cláusula fue complicada o lenta, la eliminación individual puede ser más rápida, por lo que podría ser otra ventaja.

Para editorializar, esta es una de esas cosas que es tan peligrosa que, a pesar de estar documentada, casi podría considerarse un "error". Pero bueno, los diseñadores de MySQL no me preguntaron a mí (ni a nadie más, al parecer).


3
2018-02-15 18:57



No hay una sola solución de consulta. Utilizar

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

1
2018-02-06 07:55



Haz una declaración selecta. Mientras lo recorre, crea una lista de cadenas de ID únicas. Luego pase esta lista de nuevo a mySQL usando IN.


1
2018-06-02 18:28



Puede seleccionar sus filas en una tabla temporal, luego eliminar usando los mismos criterios que su selección. Puesto que SELECT FROM WHERE FOR UPDATE también devuelve un conjunto de resultados, puede modificar SELECT FOR UPDATE a SELECT INTO tmp_table FOR UPDATE. A continuación, elimine las filas seleccionadas, ya sea utilizando sus criterios originales o utilizando los datos en la tabla temporal como criterio.

Algo como esto (pero no lo he revisado para sintaxis)

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;

Ahora sus registros han desaparecido de la tabla original, pero también tiene una copia en su tabla temporal, que puede guardar o eliminar.


1
2017-11-26 21:50



Ejecute la instrucción SELECT con la cláusula WHERE y luego use la misma cláusula WHERE en la instrucción DELETE también. Suponiendo que no hubo cambios provisionales en los datos, las mismas filas deberían eliminarse.

EDITAR: Sí, podría configurar esto como una transacción única para que no haya modificaciones en las tablas mientras lo hace.


0
2018-02-06 07:58