MySQL and MariaDB tables separate stored rows from the structure that applications use. Clearing a table removes all its rows while retaining its structure, but TRUNCATE TABLE and transactional DELETE have different consequences.

In MySQL and MariaDB, TRUNCATE TABLE is the normal full-table reset because it empties the table quickly and restarts AUTO_INCREMENT. It requires the DROP privilege, causes an implicit commit, and cannot be rolled back. Removing the table definition itself is a separate operation covered by How to delete a table in MySQL or MariaDB.

Use DELETE FROM when DELETE triggers must run or the removal needs an explicit InnoDB transaction. Foreign keys still apply, so referencing rows can block deletion or cause changes in child tables. Recovery after an irreversible reset or a committed deletion requires a restorable backup covering the target table and every affected table.

Related: How to create a MySQL or MariaDB database backup
Related: How to delete a table in MySQL or MariaDB

Steps to delete all rows from a table in MySQL or MariaDB:

  1. Back up the target table and every table its foreign-key actions can change using the database backup guide. 
    $ mysqldump --host=db.example.net --port=3306 --user=dbadmin --password --single-transaction --quick table_demo > table_demo-before-clear.sql
Enter password:

    The database-wide dump covers table_demo only. All affected child tables, including indirect descendants and tables in other databases, must be covered by the backup before deletion. A dump of clear_demo alone cannot recover committed child-table changes.

    Use mariadb-dump instead of mysqldump on MariaDB hosts. The snapshot option provides a consistent backup for InnoDB tables; nontransactional tables need separate backup coordination. Backup privileges can differ from the privileges needed to empty a table.

  2. Open the SQL client against the target database with an account that can empty the table. 
    $ mysql --host=db.example.net --port=3306 --user=dbadmin --password table_demo
Enter password:

    Use mariadb instead of mysql on MariaDB hosts. The SQL statements are the same once connected. The names table_demo and clear_demo stand for your database and table.

  3. Confirm the current database before removing rows. 
    mysql> SELECT DATABASE() AS current_database;

    Stop if this is not the intended database. A fully qualified name such as table_demo.clear_demo removes dependence on the current database selection.

  4. Confirm the exact table name. 
    mysql> SHOW TABLES LIKE 'clear_demo';

    The result must contain the intended table name exactly. The underscore in a LIKE pattern can also match another character.

  5. Count the rows that are about to be removed. 
    mysql> SELECT COUNT(*) AS rows_in_table FROM clear_demo;
+---------------+
| rows_in_table |
+---------------+
|             3 |
+---------------+
  6. Inspect the table's storage engine before choosing the clearing operation. 
    mysql> SHOW TABLE STATUS LIKE 'clear_demo'\G
*************************** 1. ROW ***************************
           Name: clear_demo
         Engine: InnoDB
##### snipped #####

    Rollback requires transactional tables such as InnoDB, including tables modified by triggers or cascading actions. A transaction cannot undo changes to nontransactional tables.

  7. Clear the table with TRUNCATE TABLE when no incoming foreign key, DELETE trigger requirement, or rollback requirement applies.

    This removes every row immediately, commits any pending transaction, and resets AUTO_INCREMENT to its start value. It requires DROP privilege and does not fire DELETE triggers. The transactional DELETE steps are an alternative to this operation. 

    mysql> TRUNCATE TABLE clear_demo;

    An InnoDB table referenced by a foreign key from another table cannot be truncated, even if that other table is empty. The server returns error 1701. A self-referencing foreign key is permitted. A successful TRUNCATE commonly reports 0 rows affected; the final row count proves emptiness.

  8. Start a transaction in the same client session if you skipped TRUNCATE and need the DELETE path.

    Foreign keys remain enforced during DELETE. Referencing rows with RESTRICT or NO ACTION can block it with error 1451. ON DELETE CASCADE can remove child rows, and ON DELETE SET NULL can change child references. Committed child-table changes require backups of those tables for recovery. Do not disable foreign-key checks to force the table clear. 

    mysql> START TRANSACTION;

    The remaining transaction steps apply only to this path. The DELETE privilege is required, and the AUTO_INCREMENT counter is retained.

  9. Delete every row inside the open transaction. 
    mysql> DELETE FROM clear_demo;

    Omitting WHERE targets every row. Until COMMIT, ROLLBACK can restore the deleted InnoDB rows. ALTER TABLE and TRUNCATE TABLE would implicitly commit the transaction, ending that rollback opportunity.

  10. Check the pending row count in the same session before committing the deletion. 
    mysql> SELECT COUNT(*) AS rows_in_table FROM clear_demo;
+---------------+
| rows_in_table |
+---------------+
|             0 |
+---------------+
  11. Commit the DELETE transaction only if the table is empty and all resulting changes are intended. 
    mysql> COMMIT;

    If the deletion should be canceled, use ROLLBACK instead of COMMIT. The original three rows return in this case. After cancellation, stop without resetting the counter. 

    mysql> ROLLBACK;
mysql> SELECT COUNT(*) AS rows_in_table FROM clear_demo;
+---------------+
| rows_in_table |
+---------------+
|             3 |
+---------------+
  12. Reset AUTO_INCREMENT to 1 only if the empty table's DELETE transaction has been committed and restarting the sequence is intended.

    This optional ALTER TABLE requires ALTER privilege and causes an implicit commit. It is outside the rollback-capable deletion, and reusing old identifiers can affect applications that retain them. 

    mysql> ALTER TABLE clear_demo AUTO_INCREMENT = 1;

    This is unnecessary after TRUNCATE. Existing identifiers prevent a restart at 1 if rows have been inserted since deletion.

  13. Verify that the table is empty after the chosen clearing operation. 
    mysql> SELECT COUNT(*) AS rows_in_table FROM clear_demo;
+---------------+
| rows_in_table |
+---------------+
|             0 |
+---------------+

    The table still exists and the row count is zero. Concurrent application writes can repopulate the table.

Share this guide