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 username -p
   Enter password: ********
   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 database_name;
   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 table_name;
   Query OK, 0 rows affected (0.03 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 table_name;
   Query OK, 100 rows affected (0.20 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 table_name AUTO_INCREMENT = 1;
   Query OK, 0 rows affected (0.01 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 table_name;
   +---------------+
   | 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 'table_name'\G
   *************************** 1. row ***************************
   Name: table_name
   Engine: InnoDB
   Auto_increment: 1
   ##### snipped #####

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

8. Exit the SQL session.

   mysql> EXIT;
   Bye