GPU Al/ML today! NVIDIA A100s & H100s for €1.43/h
en
Get started for free Sign in

Select the Gcore Platform

Recommended
Gcore Edge Solutions
Go to Gcore Platform →
Products:
  • Edge Delivery (CDN)
  • DNS with failover
  • Virtual Machines
  • Bare Metal
  • Cloud Load Balancers
  • Managed Kubernetes
  • AI Infrastructure
  • Edge Security (DDOS+WAF)
  • FaaS
  • Streaming
  • Object Storage
  • ImageStack (Optimize and Resize)
  • Edge Compute (Coming soon)
Gcore Hosting
Go to Gcore Hosting →
Products:
  • VPS Hosting
  • Dedicated Servers
  • SSL Certificates

Share

  1. Learning

How to Delete All Rows in MySQL

Published May 28, 2023 in MySQL Tutorials

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:

  1. Performance: TRUNCATE is faster than DELETE because it does not generate individual row delete statements or log each row deletion.
  2. Rollback: You can roll back DELETE operations if you’re using transactions, while TRUNCATE operations cannot be rolled back.
  3. Triggers: The DELETE statement will activate any ON DELETE triggers associated with the table. The TRUNCATE statement will not.
  4. 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).

Related articles

How to Restart the Network on Ubuntu
How to Restart the Network on Ubuntu

Need to restart your network on Ubuntu? No problem! With a few simple commands, you can quickly restore connectivity and…

How to Start Live Streaming | Beginners Guide
How to Start Live Streaming | Beginners Guide

Setting up and running live streams can feel overwhelming for both newcomers and experienced creators due to technical complexities, the…

How to Create Links in Linux Using the ln Command
How to Create Links in Linux Using the ln Command

When working with Linux, it is common to create links between files to make file management easier. These links act…

Related learning articles

How to Restart the Network on Ubuntu
How to Restart the Network on Ubuntu
Need to restart your network on Ubuntu? No problem! With a few simple commands, you can quickly restore connectivity and...
September 29, 2023 2 min read
How to Start Live Streaming | Beginners Guide
How to Start Live Streaming | Beginners Guide
Setting up and running live streams can feel overwhelming for both newcomers and experienced creators due to technical complexities, the...
September 29, 2023 9 min read
How to Create Links in Linux Using the ln Command
How to Create Links in Linux Using the ln Command
When working with Linux, it is common to create links between files to make file management easier. These links act...
September 29, 2023 3 min read

Subscribe to a useful newsletter

Favorable offers and important news once a month. No spam.