There may be times when you need to delete all records from a MySQL table. This could be for several reasons, such as wanting to clean up the data, removing outdated records, or starting over. In this guide, you’ll learn how to delete all records using two SQL statements: DELETE and TRUNCATE.
Differences Between DELETE and TRUNCATE
You can delete all records from a table using either the DELETE or the TRUNCATE statement. The DELETE statement removes rows individually, recording an entry for each deleted row in the transaction log. On the other hand, the TRUNCATE statement is a quicker method for emptying a table. It works by deallocating the data pages used by the table, effectively clearing it out completely.
The DELETE and TRUNCATE statements have several differences:
- Performance: TRUNCATE is faster than DELETE because it does not generate individual row delete statements or log each row deletion.
- Rollback: You can roll back DELETE operations if you’re using transactions, while TRUNCATE operations cannot be rolled back.
- Triggers: The DELETE statement will activate any ON DELETE triggers associated with the table. The TRUNCATE statement will not.
- Space reclaiming: DELETE does not free the space that the table occupies. In contrast, TRUNCATE reclaims the space used by the table for further use.
Note: Always ensure you have a recent backup of your data before executing any delete operations. This safeguard could be a lifesaver if anything goes wrong.
Empty the table with the DELETE Statement
1. Open your MySQL client.
2. Select the database containing the table from which you want to delete records:
USE database_name;
Replace “database_name” with the name of the relevant database.
3. Delete all records from the table using the DELETE command:
DELETE FROM table_name;
Replace “table_name” with the name of the relevant table. If you can’t recall the table name, refer to our guide “How to List Tables in a MySQL Database” (step #2).
Empty the table with the TRUNCATE Statement
1. Open your MySQL client.
2. Select the database containing the table from which you want to delete records:
USE database_name;
Replace “database_name” with the name of the relevant database.
3. Delete all records from the table:
TRUNCATE TABLE table_name;
Replace “table_name” with the name of the relevant table. If you can’t recall the table name, refer to our guide “How to List Tables in a MySQL Database” (step #2).
Conclusion
Searching for a managed database solution? Choose Gcore Managed Database for PostgreSQL so you can focus on your core business while we manage your database.
- 99.9% SLA for uninterrupted service with high-availability architecture
- Adjustable database resources for changing demands
- Currently in free public beta
