Delete all tables except some mysql

A possible solution with two variants:

Variants are examples of white lists or blacklists: variant A contains a black list that indicates which tables should be deleted (the rest will be left as is), while variant B contains a white list with the tables that they must be maintained (and the others will be truncated).

Depending on the number of tables to be deleted or each time the list of tables to be deleted is updated, you could decide on one or the other. If you know that the tables that should not be deleted are going to be constant but that the ones that are going to be deleted may vary, I would choose option B.

Here is an example of how it could be done:

  

In this example:

     

• stackoverflow is the name of the database where the tables to be truncated are located.

  

• tables_to_clear is the table that contains the names of the tables to be truncated.

  

DELIMITER $$

DROP PROCEDURE IF EXISTS truncar_tablas $$
CREATE PROCEDURE truncar_tablas()
BEGIN

    DECLARE x INT;
    DECLARE numtablas INT;
    DECLARE nombretabla VARCHAR(100);

    SET FOREIGN_KEY_CHECKS = 0;

    SELECT COUNT(nombre) INTO numtablas FROM tablas_a_borrar;
    SET x = 0;

    -- hacemos un bucle que leerá todos los nombres de las tablas
    truncate_loop: LOOP

        -- si ya hemos atravesado todas las tablas, salimos del bucle
        IF x > numtablas THEN
            LEAVE truncate_loop;
        END IF;

        -- seleccionamos el nombre de la tabla
        SELECT nombre
        INTO   nombretabla
        FROM   tablas_a_borrar
        LIMIT  x, 1;

        -- construímos dinámicamente el TRUNCATE para la siguiente tabla
        SELECT CONCAT('TRUNCATE ',GROUP_CONCAT(CONCAT('stackoverflow.',table_name)),';')
        INTO @codigotruncar 
        FROM information_schema.tables 
        WHERE 'stackoverflow' = database()
          AND table_name IN (SELECT DISTINCT nombre FROM tablas_a_borrar WHERE nombre = nombretabla); 

        -- preparamos y ejecutamos el SQL dinamicamente
        PREPARE stmt FROM @codigotruncar;
        EXECUTE stmt;
        DEALLOCATE PREPARE stmt;

        SET x = x + 1;

    END LOOP;

    SET FOREIGN_KEY_CHECKS = 1;

END $$

DELIMITER ;

Afterwards you should only call the stored procedure in this way: call truncar_tablas; to truncate all the tables specified in the table tablas_a_borrar .

Update:

The same solution implemented within a store procedure with SQL using EXECUTE in a loop over a cursor with the tables to be truncated.

This store procedure expects a table named excepciones with the list of tables that should not be truncated in a field called nombre_tabla , the list of tables is taken from the system table information_schema.tables

It would be more or less like this:

CREATE PROCEDURE truncateAlgunas() BEGIN
  DECLARE done BOOLEAN DEFAULT FALSE;
  DECLARE nombreTabla VARCHAR(64);
  DECLARE cur CURSOR FOR 
              SELECT table_name 
              FROM information_schema.tables 
              WHERE table_name NOT IN (SELECT nombreTabla FROM excepciones);
  DECLARE CONTINUE HANDLER FOR NOT FOUND SET done := TRUE;

  OPEN cur;

  truncateLoop: LOOP
    FETCH cur INTO nombreTabla;
    IF done THEN
      LEAVE truncateLoop;
    END IF;
    PREPARE statement FROM CONCAT("TRUNCATE TABLE ", nombreTabla);
    EXECUTE statement;
    DEALLOCATE PREPARE statement;
  END LOOP truncateLoop;

  CLOSE cur;
END

And then you just run the store procedure. This is the same as I said before but implemented in mysql.

Note: I could not prove it, but it should work without major changes.

Previous answer

As far as you can not use parameters for the names of the tables (or columns) (eg: TRUNCATE TABLE @tablename ), so focus it discard.

A possible solution would be, using any other language, and by hand concatenate the name of the table to the statement truncate table .

List<String> tablas = { "a", "b", "c" };
List<String> excepciones = { "b" };
String sentencia;

for(var tabla in tablas) {
    if (!excepciones.contains(tabla)) {
        sentencia += "TRUNCATE TABLE " + tabla + ";"; 
    }
}

mySqlConnection.execute(sentencia);

This could also be done using SQL. Also, depending on the origin (reliability) of the list of tables and exceptions, you should check the value of tabla by control characters to avoid sql injection .

More info at this post from OS in English

The simplest way and how I do it is through the IDs, they solve my life when there are numerical values, I only do:

DELETE FROM "tabla" where "codigoid" < "135"

In this example my is the table and the column is codigoid and I am asking you to delete all the values below 135, maybe you can try something similar to start