How to delete all rows or clear tables in MySQL and MariaDB

Clearing all rows from a table keeps the schema intact while removing data that no longer belongs, such as test fixtures, staging loads, or transient job output. A fast, repeatable “empty table” workflow reduces accidental reuse of stale records and simplifies reruns of imports and migrations.

In MySQL and MariaDB, full-table data removal is usually done with either DELETE FROM or TRUNCATE TABLE. DELETE is a data-modification operation that removes rows through the storage engine under its transaction and logging rules, so triggers and foreign key checks behave as expected. TRUNCATE is a fast table-emptying operation (implemented as DDL) that deallocates data, typically resets the AUTO_INCREMENT counter, and avoids row-by-row work.

Both operations are destructive and should be treated as irreversible without a backup or a replica to restore from. TRUNCATE performs an implicit commit and may fail when the table is referenced by a foreign key constraint, while a large DELETE can hold locks for a long time and generate substantial undo/redo and binary log traffic. Confirm the current database context and table name before running any full-table statement.

1. Open a MySQL client session with an account that can modify the target table.

   $ mysql -u root -p
   Enter password:
   Welcome to the MySQL monitor.  Commands end with ; or \g.
   Your MySQL connection id is 46
   Server version: 8.0.44 MySQL Community Server - GPL
   
   Copyright (c) 2000, 2025, Oracle and/or its affiliates.
   
   Oracle is a registered trademark of Oracle Corporation and/or its
   affiliates. Other names may be trademarks of their respective
   owners.
   
   Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
   
   mysql>

   On some installs, mariadb is the client binary name for MariaDB, and -h hostname with -P port selects a remote host and port.

2. Select the database that contains the table.

   mysql> USE table_demo;
   Database changed

   Confirm names with SHOW DATABASES; and SHOW TABLES; before clearing data.

3. Run TRUNCATE TABLE to clear all rows from a table quickly.

   mysql> TRUNCATE TABLE clear_demo;
   Query OK, 0 rows affected (0.07 sec)

   TRUNCATE performs an implicit commit, resets AUTO_INCREMENT, requires DROP privilege on the table, and fails when another table references it via a foreign key.

4. Run DELETE FROM to clear all rows when a truncate is not suitable.

   mysql> DELETE FROM clear_demo;
   Query OK, 0 rows affected (0.00 sec)

   DELETE removes rows through the storage engine, so DELETE triggers fire and foreign keys are enforced, and it can be rolled back on InnoDB before COMMIT when run inside an explicit transaction.

5. Optionally reset the AUTO_INCREMENT counter for an empty table cleared with DELETE FROM.

   mysql> ALTER TABLE clear_demo AUTO_INCREMENT = 1;
   Query OK, 0 rows affected (0.00 sec)
   Records: 0  Duplicates: 0  Warnings: 0

   Set the value to 1 only after confirming the table is empty, otherwise the next value is chosen based on the highest existing auto-increment value.

6. Confirm the table contains zero rows.

   mysql> SELECT COUNT(*) AS rows_in_table FROM clear_demo;
   +---------------+
   | rows_in_table |
   +---------------+
   |             0 |
   +---------------+
   1 row in set (0.00 sec)

7. Check the next AUTO_INCREMENT value for the table when applicable.

   mysql> SHOW TABLE STATUS LIKE 'clear_demo'\G
   *************************** 1. row ***************************
              Name: clear_demo
            Engine: InnoDB
           Version: 10
        Row_format: Dynamic
              Rows: 0
    Avg_row_length: 0
       Data_length: 16384
   Max_data_length: 0
      Index_length: 0
         Data_free: 0
    Auto_increment: 1
       Create_time: 2025-12-24 22:39:15
       Update_time: NULL
        Check_time: NULL
         Collation: utf8mb4_0900_ai_ci
          Checksum: NULL
    Create_options:
           Comment:
   1 row in set (0.01 sec)

   Auto_increment is the next value to be assigned for an auto-increment column.

8. Exit the SQL session.

   mysql> EXIT;
   Bye