Delete all Data from Table MySQL

If the user wants to delete all the records from a table in MySQL, then the user needs to implement a TRUNCATE statement.

Syntax of Truncate Statement

The syntax to implement truncate query in the MySQL RDBMS is as follows:

Functions of Truncate Table

1. TRUNCATE TABLE deleted all the records from the table. If the user wants to remove all the data from the table, then the user must have DROP privilege. TRUNCATE is similar to the DELETE statement in MySQL. It works similarly to the DELETE statement as it deletes all the rows in the table. It is a sequence of DROP and CREATE TABLE statements.
2. If the user wants to increase the efficiency, then the TRUNCATE TABLE is a better option than using the data manipulation language method to remove the data from the table. Thus, implementing TRUNCATE TABLE does not cause ON DELETE triggers to fire. Therefore, it cannot be used in InnoDB tables that contain parent-child foreign key relationships. The user cannot roll back the TRUNCATE statement when performing the DML statement.
3. TRUNCATE TABLE statement can be implemented in the tables that use an atomic data definition language supported storage engine. The query can either be fully completed or rolled back if the server stops when implementing the query in MySQL.
4. TRUNCATE statement is logically implemented as the DELETE statement, but the TRUNCATE statement is considered the definition language statement, whereas DELETE is considered a data manipulation language command.

Difference Between TRUNCATE AND DELETE Statement

The difference between Truncate and Delete statements are as follows:

• If the user implements the truncate statement, it is similar to implementing the DROP statement and re-creating the same table. It is faster to implement than using the DELETE statement, as the user will have to implement the DELETE statement for each row in the table. It will be considered difficult for tables with numerous records.
• The user does not need to implement a COMMIT query when using a TRUNCATE statement implicitly. The user cannot perform a rollback. Therefore, the user should create a copy of the existing table before implementing the TRUNCATE statement on the data.
• The user cannot truncate the table if the current session holds an active table lock.
• The user cannot perform TRUNCATE TABLE in an InnoDB or NDB table if any above tables contain foreign key constraints from other tables referencing the table. The user can implement a TRUNCATE statement if the foreign key constraints are within columns of the same table.
• When the user uses a DELETE statement, it also returns the number of rows affected by the particular query, but when using a TRUNCATE statement, no meaningful value is returned. It shows 0 rows affected, which can be interpreted as no information in MySQL.
• Suppose the table definition for which the query is being implemented is valid. In that case, the TRUNCATE statement will return an empty table even when the data stored in the table, or the index files are corrupted.
• If the user has used an AUTO_INCREMENT value, its value will be returned to its initial value. It applies even to the InnoDB and MyISAM, which generally do not allow users to re-assign sequence values.
• If the TRUNCATE command is implemented with partitioned tables, then the partitioning is preserved while implementing TRUNCATE TABLE. The actual data and the index files are removed and then re-created. The partitioning in windows remains unchanged.
• When the user implements the TRUNCATE TABLE command on the table, it does not invoke ON DELETE triggers.
• The user can also implement a TRUNCATE statement on the corrupted InnoDB table.
• TRUNCATE TABLE performs binary logging and replication as data definition language rather than data manipulation language. This statement is always logged as a statement.
• When the TRUNCATE TABLE statement is used on a table, it closes all handlers for the particular table that was opened using the HANDLER OPEN statement.
• In MySQL 5.7 and earlier, on a system with a large buffer pool and innodb_adaptive_hash_index is enabled, the TRUNCATE TABLE can temporarily drop the system performance. The decrease in performance is due to an LRU scan performed while deleting the adaptive hash index entries for the table. In the latest version, when the truncate statement is implemented on the table, remapping TRUNCATE TABLE to DROP and CREATE TABLE prevents it from performing an LRU scan.
• The command can also be implemented on the summary tables present in the Performance Schema. The effect of the TRUNCATE TABLE on these tables as it resets the summary columns to 0 or NULL. It does not remove rows from the table.
• If the TRUNCATE TABLE command is implemented on the InnoDB table stored in the file-per-table tablespace, it removes the existing tablespace and creates a new one.

Implementation of the TRUNCATE statement

The user can implement the TRUNCATE command on an existing table. We have already discussed the syntax for implementing the TRUNCATE command. It is as follows:

Before implementing the TRUNCATE statement, the user must create a table and add data to the table to ensure the TRUNCATE command is successfully executed.

To create a table in MySQL, use the CREATE TABLE command.

The above query will define the table with a name demo table with three columns StudentID, Student_Name, and Student_Course.

The next step is to store data in the table defined. If the user wants to insert the record in the table, then the user requires to implement an INSERT statement. The syntax to add data in the demotable is as follows:

Type the given query to insert data into the table:

Implementing the above 5 SQL statements in MySQL will have these five rows of information in the demo table. Once the data values are added to the table's respective columns, the user can check by displaying all the records in the table.

The user must implement a SELECT query to view all the records stored in the table. The query to display demotable is as follows:

It will return the given output:

After checking, all the values are successfully recorded in the table. Use the above syntax to implement the TRUNCATE statement to remove all the records from the table. The query to implement the TRUNCATE command is as follows:

It will remove all the existing data from the table. Display the records in the demo table to ensure that the TRUNCATE statement is successfully executed. You can check that all the data is removed from the table by implementing the SELECT query again.

It will not return any data showing that the table is empty. To ensure that the table is empty, try inserting new data in the table. You will see that it will be the first value in the demotable.

Output:

Using the DELETE Keyword

The user can also use the DELETE keyword to remove all rows of data without deleting the table definition. If the user implements the DELETE statement, it ensures that the table definitions, attributes, and indexes remain unaffected by the query.

The syntax for DELETE Statement

Implementing the DELETE Statement

First, create the same table as above for which the user will implement the DELETE statement. Implement the query given below to create and add data to the table.

Once the values are recorded in the table, you can check them by implementing the SELECT query to display the records from the table.

Output:

Finally use the delete keyword to delete all records in the demotable.

The above statement will delete all the data records stored in the demotable. The table definition remains unchanged. To ensure that the table is empty, try inserting new data in the table. You will see that it will be the first value in the demotable.

Output: