where should not be there in example, MySQL

Language: Layout:

-- -------------------------------------------------------------------------------------
    -- USAGE
    -- -------------------------------------------------------------------------------------
    -- CALL delete_recursive(<schema name>, <table name>, <WHERE clause>, <delete flag>);
    -- where:
    -- <schema name> is the name of the MySQL schema
    -- <table name> is the name of the base table to delete records from
    -- <WHERE clase> is a SQL WHERE clause to filter which records that are to be deleted
    -- <delete flag> is either TRUE or FALSE: If TRUE, the records *will* be deleted.
    --               If FALSE, the SQL will be output without actually deleting anything.
    -- Example:
    -- CALL delete_recursive('mydb', 'mytable', 'mypk IN (1, 2, 3)', TRUE);
    DROP PROCEDURE IF EXISTS delete_recursive;
    CREATE PROCEDURE delete_recursive(schema_name VARCHAR(64),
                                      tbl_name VARCHAR(64),
                                      where_clause TEXT,
                                      do_delete BIT)
    BEGIN
      DECLARE next_schema_name, next_tbl_name VARCHAR(64);
      DECLARE from_clause, next_where_clause, next_col_names, ref_col_names TEXT;
      DECLARE done INT DEFAULT FALSE;
      DECLARE cursor1 CURSOR FOR
        SELECT TABLE_SCHEMA, TABLE_NAME, COLUMN_NAMES, REF_COLUMN_NAMES FROM temp_kcu;
      DECLARE cursor2 CURSOR FOR
        SELECT table_schema, table_name, where_sql FROM temp_deletes ORDER BY id;
      DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;

-- Set maximum recursion depth
      SET @@SESSION.max_sp_recursion_depth = 255;

-- Increment current recursion depth since the stored procedure has been entered.
      SET @recursion_depth = IFNULL(@recursion_depth + 1, 0);

-- Create temporary table for storing the deletes if it doesn't already exist
      IF @recursion_depth = 0 THEN
        DROP TEMPORARY TABLE IF EXISTS temp_deletes;
        CREATE TEMPORARY TABLE temp_deletes (
          id INT NOT NULL AUTO_INCREMENT,
          table_schema VARCHAR(64),
          table_name VARCHAR(64),
          where_sql TEXT,
          Notes TEXT,
          PRIMARY KEY(id)
        );
      END IF;

-- Construct FROM clause (including the WHERE clause) for this table.
      SET from_clause = 
        CONCAT(' FROM ', schema_name, '.', tbl_name, ' WHERE ', where_clause);

-- Find out whether there are any foreign keys to this table
      SET @query = CONCAT('SELECT COUNT(*) INTO @count', from_clause);
      PREPARE stmt FROM @query;
      EXECUTE stmt;
      DEALLOCATE PREPARE stmt;

IF @count > 0 THEN
        -- There are foriegn keys to this table so all linked rows must be deleted first:
        -- Firstly, fill a temporary table with the foreign key metadata.
        DROP TEMPORARY TABLE IF EXISTS temp_kcu;
        SET @query = CONCAT(
          'CREATE TEMPORARY TABLE temp_kcu AS ',
          'SELECT TABLE_SCHEMA, TABLE_NAME, ',
          'GROUP_CONCAT(CONCAT(COLUMN_NAME) SEPARATOR '', '') AS COLUMN_NAMES, ', 
          'GROUP_CONCAT(CONCAT(REFERENCED_COLUMN_NAME) SEPARATOR '', '')
            AS REF_COLUMN_NAMES ',
          'FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE ',
          'WHERE REFERENCED_TABLE_SCHEMA = ''', schema_name,
          ''' AND REFERENCED_TABLE_NAME = ''', tbl_name, ''' ',
          'GROUP BY CONSTRAINT_NAME');
        PREPARE stmt FROM @query;
        EXECUTE stmt;
        DEALLOCATE PREPARE stmt;

-- Loop through all foreign keys to this table using a cursor.
        OPEN cursor1;
        read_loop: LOOP
          FETCH cursor1 INTO next_schema_name, next_tbl_name, next_col_names, ref_col_names;
          IF done THEN
            -- No more rows so exit the loop.
            LEAVE read_loop;
          END IF;

-- Recursively call the stored procedure to delete linked rows
          -- for this foreign key.
          IF INSTR(next_col_names, ',') = 0 THEN
            SET next_where_clause = CONCAT(
              next_col_names, ' IN (SELECT ', ref_col_names, from_clause, ')');
          ELSE
            SET next_where_clause = CONCAT(
              '(', next_col_names, ') IN (SELECT ', ref_col_names, from_clause, ')');
          END IF;
          CALL delete_recursive(
            next_schema_name, next_tbl_name, next_where_clause, do_delete);
        END LOOP;
        CLOSE cursor1;
      END IF;

-- Build the DELETE statement
      SET @query = CONCAT(
        'DELETE FROM ', schema_name, '.', tbl_name, ' WHERE ', where_clause);

-- Get the number of primary key columns
      SET @pk_column_count = (SELECT COUNT(*)
                              FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE
                              WHERE TABLE_SCHEMA = schema_name
                                AND TABLE_NAME = tbl_name
                                AND CONSTRAINT_NAME = 'PRIMARY');
      IF @pk_column_count = 0 THEN
        -- No primary key so just output the number of rows to be deleted
        SET @query = CONCAT(
          'SET @notes = CONCAT(''No primary key; number of rows to delete = '',
          (SELECT COUNT(*) FROM ', schema_name, '.', tbl_name, ' WHERE ',
          where_clause, '))');
        PREPARE stmt FROM @query;
        EXECUTE stmt;
        DEALLOCATE PREPARE stmt;
      ELSEIF @pk_column_count = 1 THEN
        -- 1 primary key column.
        -- Output the primary keys of the records to be deleted
        SET @pk_column = (SELECT COLUMN_NAME
                          FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE
                          WHERE TABLE_SCHEMA = schema_name
                            AND TABLE_NAME = tbl_name
                            AND CONSTRAINT_NAME = 'PRIMARY');
        SET @pk_column_csv = CONCAT('CONCAT('''''''', ', @pk_column, ', '''''''')');
        SET @query = CONCAT(
          'SET @notes = (SELECT CONCAT(''', @pk_column, ' IN ('', GROUP_CONCAT(',
          @pk_column_csv, ' SEPARATOR '', ''), '')'') FROM ',
          schema_name, '.', tbl_name, ' WHERE ', where_clause, ')');
        PREPARE stmt FROM @query;
        EXECUTE stmt;
        DEALLOCATE PREPARE stmt;
      ELSE
        -- Multiple primary key columns.
        -- Output the primary keys of the records to be deleted.
        SET @pk_columns = (SELECT GROUP_CONCAT(COLUMN_NAME SEPARATOR ', ')
                           FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE
                           WHERE TABLE_SCHEMA = schema_name
                             AND TABLE_NAME = tbl_name
                             AND CONSTRAINT_NAME = 'PRIMARY');
        SET @pk_columns_csv = (SELECT CONCAT('CONCAT(''('''''', ', GROUP_CONCAT(COLUMN_NAME
                                 SEPARATOR ', '''''', '''''', '), ', '''''')'')')
                               FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE
                               WHERE TABLE_SCHEMA = schema_name
                                 AND TABLE_NAME = tbl_name
                                 AND CONSTRAINT_NAME = 'PRIMARY');      
        SET @query = CONCAT(
         'SET @notes = (SELECT CONCAT(''(', @pk_columns,
         ') IN ('', GROUP_CONCAT(', @pk_columns_csv, ' SEPARATOR '', ''), '')'') FROM ',
          schema_name, '.', tbl_name, ' WHERE ', where_clause, ')');
        PREPARE stmt FROM @query;
        EXECUTE stmt;
        DEALLOCATE PREPARE stmt;
      END IF;

IF @notes IS NULL THEN
        SET @notes = 'No affected rows.';
      END IF;

-- Save details of the DELETE statement to be executed
      INSERT INTO temp_deletes (table_schema, table_name, where_sql, Notes)
      VALUES (schema_name, tbl_name, where_clause, @notes);

IF @recursion_depth = 0 THEN
        -- Output the deletes.
        SELECT CONCAT('DELETE FROM ', schema_name, '.', table_name,
                      ' WHERE ', where_sql) `SQL`,
               Notes
        FROM temp_deletes ORDER BY id;

IF do_delete THEN
          -- Perform the deletes: Loop through all delete queries using a cursor.
          SET done = FALSE;
          OPEN cursor2;
          read_loop: LOOP
            FETCH cursor2 INTO schema_name, tbl_name, where_clause;
            IF done THEN
              -- No more rows so exit the loop.
              LEAVE read_loop;
            END IF;

SET @query = CONCAT(
              'DELETE FROM ', schema_name, '.', tbl_name, ' WHERE ', where_clause);

PREPARE stmt FROM @query;
            EXECUTE stmt;
            DEALLOCATE PREPARE stmt;
          END LOOP;
          CLOSE cursor2;
        END IF;

-- Tidy up
        DROP TEMPORARY TABLE IF EXISTS temp_deletes;
      END IF;

-- Decrement current recursion depth since the stored procedure is being exited.
      SET @recursion_depth = @recursion_depth - 1;
    END;

-- -------------------------------------------------------------------------------------
    -- END OF STORED PROCEDURE CODE. EXAMPLE CALLING CODE BELOW.
    -- -------------------------------------------------------------------------------------

DROP TABLE IF EXISTS table4;
    DROP TABLE IF EXISTS table3;
    DROP TABLE IF EXISTS table2;
    DROP TABLE IF EXISTS table1;

CREATE TABLE table1 (id INT,
                         PRIMARY KEY (id));
    CREATE TABLE table2 (pk1 INT, pk2 VARCHAR(255), table1_id INT,
                         PRIMARY KEY (pk1, pk2),
                         FOREIGN KEY (table1_id) REFERENCES table1(id));
    CREATE TABLE table3 (id INT, table1_id INT, table2_pk1 INT, table2_pk2 VARCHAR(255),
                         PRIMARY KEY (id),
                         FOREIGN KEY (table1_id) REFERENCES table1(id),
                         FOREIGN KEY (table2_pk1, table2_pk2) REFERENCES table2(pk1, pk2));
    CREATE TABLE table4 (table1_id INT,
                         FOREIGN KEY (table1_id) REFERENCES table1(id));

INSERT INTO table1 (id) VALUES (1);
    INSERT INTO table1 (id) VALUES (2);
    INSERT INTO table2 (pk1, pk2, table1_id) VALUES (1, 'A', 1);
    INSERT INTO table2 (pk1, pk2, table1_id) VALUES (2, 'B', 1);
    INSERT INTO table2 (pk1, pk2, table1_id) VALUES (3, 'C', 2);
    INSERT INTO table2 (pk1, pk2, table1_id) VALUES (4, 'D', 2);
    INSERT INTO table3 (id, table1_id, table2_pk1, table2_pk2) VALUES (1, 1, 1, 'A');
    INSERT INTO table3 (id, table1_id, table2_pk1, table2_pk2) VALUES (2, 2, 1, 'A');
    INSERT INTO table3 (id, table1_id, table2_pk1, table2_pk2) VALUES (3, 1, 2, 'B');
    INSERT INTO table3 (id, table1_id, table2_pk1, table2_pk2) VALUES (4, 2, 2, 'B');
    INSERT INTO table3 (id, table1_id, table2_pk1, table2_pk2) VALUES (5, 1, 3, 'C');
    INSERT INTO table3 (id, table1_id, table2_pk1, table2_pk2) VALUES (6, 2, 3, 'C');
    INSERT INTO table3 (id, table1_id, table2_pk1, table2_pk2) VALUES (7, 1, 4, 'D');
    INSERT INTO table3 (id, table1_id, table2_pk1, table2_pk2) VALUES (8, 2, 4, 'D');
    INSERT INTO table4 (table1_id) VALUES (1);

SELECT 'table1 BEFORE deletion:' AS `_`;
    SELECT * FROM table1;
    SELECT 'table2 BEFORE deletion:' AS `_`;
    SELECT * FROM table2;
    SELECT 'table3 BEFORE deletion:' AS `_`;
    SELECT * FROM table3;

CALL delete_recursive('rextester', 'table1', 'id = 1', TRUE);

SELECT 'table1 AFTER deletion:' AS `_`;
    SELECT * FROM table1;
    SELECT 'table2 AFTER deletion:' AS `_`;
    SELECT * FROM table2;
    SELECT 'table3 AFTER deletion:' AS `_`;
    SELECT * FROM table3;


absolute service time: 4,62 sec