How to clear a table in MySQL or MariaDB

Clearing a table lets you rerun imports, integration tests, and staging jobs without rebuilding the table definition, indexes, or grants. When the rows need to disappear but the object itself must stay in place, an empty-table workflow is safer than dropping and recreating the table.

In MySQL and MariaDB, full-table clearing is usually done with either TRUNCATE TABLE or DELETE FROM. TRUNCATE is a fast DDL-style reset that empties the table and restarts AUTO_INCREMENT, while DELETE removes rows through the normal DML path and reports how many rows were removed.

The difference matters before you press Enter. Current MySQL and MariaDB releases still require the DROP privilege for TRUNCATE, still reject it with ERROR 1701 when another table references the target through a foreign key, and still skip ON DELETE triggers. DELETE needs only the DELETE privilege and can run inside an explicit transaction on InnoDB, but a large full-table delete generates more row-by-row work, undo, and binary-log traffic than a truncate.

1. Create a logical backup of the table if the data might need to be restored later.

   $ mysqldump --user root --password --host db.example.net --port 3306 table_demo clear_demo > clear_demo-backup.sql
   Enter password:

   Current MariaDB installs commonly use mariadb-dump instead of mysqldump for the same task.

2. Open the SQL client with an account that can empty the target table.

   $ mysql --user root --password --host db.example.net --port 3306
   Enter password:
   Welcome to the MySQL monitor.  Commands end with ; or \g.
   mysql>

   Current MariaDB installs often provide mariadb as the client binary name. The SQL statements below are the same once you are connected.

3. Select the database, confirm the table name, and check how many rows are about to be removed.

   mysql> USE table_demo;
   DATABASE changed
    
   mysql> SHOW TABLES LIKE 'clear_demo';
   +-----------------------------------+
   | Tables_in_table_demo (clear_demo) |
   +-----------------------------------+
   | clear_demo                        |
   +-----------------------------------+
   1 ROW IN SET (0.00 sec)
    
   mysql> SELECT COUNT(*) AS rows_in_table FROM clear_demo;
   +---------------+
   | rows_in_table |
   +---------------+
   |             3 |
   +---------------+
   1 ROW IN SET (0.00 sec)

   Use the fully qualified form table_demo.clear_demo when you do not want to change the current database first.

4. Use TRUNCATE TABLE for the fastest full reset when no other table references the target and no row-by-row trigger work is required.

   mysql> TRUNCATE TABLE clear_demo;
   Query OK, 0 ROWS affected (0.01 sec)

   TRUNCATE requires the DROP privilege, performs an implicit commit, resets AUTO_INCREMENT to its start value, and commonly reports 0 rows affected because it does not return a meaningful deleted-row count.

   Current MySQL and MariaDB releases return ERROR 1701 (42000): Cannot truncate a table referenced in a foreign key constraint when another table points at the target, and TRUNCATE does not fire DELETE triggers.

5. Use DELETE FROM instead when foreign keys, DELETE triggers, or transaction rollback behavior matter more than raw speed.

   mysql> DELETE FROM clear_demo;
   Query OK, 3 ROWS affected (0.00 sec)

   DELETE needs the DELETE privilege, fires DELETE triggers, and can be rolled back on InnoDB before COMMIT when it runs inside an explicit transaction.

6. Reset the AUTO_INCREMENT counter only if you used DELETE FROM and want the next insert to restart from the beginning.

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

   Run this only after confirming the table is empty. If rows remain, the next generated value still advances past the highest existing identifier.

7. Verify that the table is empty and that the next AUTO_INCREMENT value matches the path you chose.

   mysql> SELECT COUNT(*) AS rows_in_table FROM clear_demo;
   +---------------+
   | rows_in_table |
   +---------------+
   |             0 |
   +---------------+
   1 ROW IN SET (0.00 sec)
    
   mysql> SHOW TABLE STATUS LIKE 'clear_demo'\G
   *************************** 1. ROW ***************************
              Name: clear_demo
            Engine: InnoDB
              ROWS: 0
    AUTO_INCREMENT: 1
   ##### snipped #####
   1 ROW IN SET (0.01 sec)

   Auto_increment is the next value that will be assigned. After DELETE FROM without the optional reset step, InnoDB usually keeps the next value above the previous high-water mark.

8. Exit the client session.

   mysql> EXIT;
   Bye