Delete data from all tables in MYSQL

sql

database-management

data-erasure

mysql-commands

byNikita Barsukov·Nov 6, 2024

For a quick data purge across all tables in a MySQL database, utilize this succinct routine:

DELIMITER //
CREATE PROCEDURE ClearAllTables()
BEGIN
  DECLARE done INT DEFAULT FALSE;
  DECLARE tbl VARCHAR(255);
  DECLARE cur CURSOR FOR SELECT table_name FROM information_schema.tables WHERE table_schema = SCHEMA();
  DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;
  OPEN cur;
  read_loop: LOOP
    FETCH cur INTO tbl;
    IF done THEN
      LEAVE read_loop;
    END IF;
    SET @sql = CONCAT('TRUNCATE TABLE ', tbl); -- Knock, Knock! Who’s there? Truncate. Truncate who? Truncate all your data. Might want a backup!
    PREPARE stmt FROM @sql;
    EXECUTE stmt;
    DEALLOCATE PREPARE stmt;
  END LOOP;
  CLOSE cur;
END //
DELIMITER ;
CALL ClearAllTables();

Note: This action is final, it erases all tables in the currently active database. Make sure your backup game is strong!

As a precaution, if your tables have dependencies due to foreign keys, it is recommended to temporarily disable foreign key checks. This avoids any integrity issues and ensures a smooth purge.

SET foreign_key_checks = 0; -- Not gonna lie, this scares me a bit!
CALL ClearAllTables();
SET foreign_key_checks = 1; -- Phew! Constraints have left the chat!

Additionally, keep an eye out for any triggers or stored procedures. They aren't affected by this method. You need to take extra steps to handle them correctly!

Advanced strategies for extensive data erase

While truncating tables is a swift method to obliterate all records, it's not the only game in town. Let's dive deep into alternative approaches, dependent on your unique use case, such as constraints management, safe handling of special objects, and efficient automation.

Managing foreign keys and constraints

Foreign keys can make truncating an adventure. By pre-emptively disabling foreign key checks using foreign_key_checks, you can conquer these constraints:

SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, foreign_key_checks = 0; -- This reminds me of turning off my alarm clock, it's gonna bite later
-- Run the ClearAllTables() procedure here
SET foreign_key_checks = @OLD_FOREIGN_KEY_CHECKS; -- Alarm clock on! Now where's that snooze button?

Keeping the database structure intact

Need to expunge data while holding on to the database schema? Use mysqldump with the -d option to avoid dumping data, and go hand in hand with --add-drop-table:

mysqldump -d -uuser -ppass --add-drop-table databasename > databasename.sql
mysql -uuser -ppass databasename < databasename.sql

Automating the whole shebang

For recurring database spring cleaning, a script or stored procedure can execute the truncate chore. Consistent, automated, and fewer facepalm moments!

#!/bin/bash
# A knight in shining armor: the script which handles table truncation
username="user"
password="pass"
database="databasename"

mysql -u"$username" -p"$password" -e "SET foreign_key_checks = 0;" -- Whoever said 'talk is cheap' didn't know database commands!
mysql -u"$username" -p"$password" "$database" < "truncate_all_tables.sql"
mysql -u"$username" -p"$password" -e "SET foreign_key_checks = 1;" -- Everything's back to normal, you can uncover your eyes now.

Prioritizing safety

In any script or operation that alters the database, always use backups and caution. A case of the wrong database or tables purge gets expensive, real quick!

Peeking into optimization and user caution

With large databases, choosing a method to erase data could be a tug of war between TRUNCATE and DELETE. Speed vs. safeguards. Let's talk about it.

Tailoring your toolset

TRUNCATE reinitializes the table and resets the autoincrement. Think of this as a factory reset. But, if you have active triggers, you may have to call in DELETE.

Ensuring data recovery

Include confirmations and safety checks into your scripts. And remember, data resurrection is only possible if you religiously backup!

Avoiding purge in certain scenarios

In special cases, where data is historically significant or cleaned for further use, consider archiving instead of purging. Just because you can purge, doesn't mean you always should!

explain-codes / Sql / Delete data from all tables in MYSQL

Linked

Sql ON DELETE CASCADE, Which Way Does the Deletion Occur?



How to add 'ON DELETE CASCADE' in ALTER TABLE statement



Foreign Key to non-primary key



How to Alter Constraint



Drop all the tables, stored procedures, triggers, constraints and all the dependencies in one sql statement

