How to delete all data from table in MySQL or MariaDB

Managing data in databases is a common administrative task, and at times, you may need to remove all records from a specific table. This can be to reset the table, clear out test data, or repurpose the table for a different dataset.

In MySQL and MariaDB, the popular relational database management systems, you can efficiently achieve this using the SQL DELETE statement. But a faster method is using the TRUNCATE command, which removes all records and resets auto-increment values. It's important to back up your database before making any significant changes, especially when deleting data.

Using the TRUNCATE command is especially beneficial in cases where you want to quickly delete all records without logging individual row deletions, as in the case of the DELETE command.

1. Log in to the MySQL or MariaDB server using the command line or a management tool.

   $ mysql -u username -p
     - Enter password:

   Replace username with your MySQL or MariaDB username.

2. Choose the relevant database where the table resides.

   USE database_name;

3. Use the TRUNCATE command to clear all data from the table.

   TRUNCATE TABLE table_name;

   Be cautious with the TRUNCATE command as it will remove all records permanently from the specified table.

4. Optionally, if you prefer to use the DELETE command:

   DELETE FROM table_name;

   While the DELETE command can clear all records, it does not reset auto-increment values, and the operation can be slower compared to TRUNCATE because it logs each row deletion.

5. Confirm the table is empty by querying it.

   SELECT * FROM table_name;

   The result should be an empty set, confirming that all data has been removed.

6. Exit the MySQL or MariaDB server once done.

   EXIT;

Executing these steps ensures that all data from the specified table is removed, giving you a clean slate to work with or to repurpose the table for other datasets. Always remember to back up your database or table before performing deletion tasks to avoid any unforeseen data losses.