TRUNCATE Table - ( MySQL Table & Views )

In MySQL, the TRUNCATE statement is used to delete all records from a table or a view. In this tutorial, we'll discuss the syntax, example, output, explanation, use, important points, and summary of the TRUNCATE statement in MySQL.

Syntax

The syntax for using the TRUNCATE statement in MySQL is as follows:

TRUNCATE [TABLE] table_name [VIEW];

• TABLE is an optional keyword that can be used for clarity, but it is not required.
• table_name is the name of the table or view from which you want to delete the records.

Example

Let's say we have a table called employees with the following data:

| id | first_name | last_name | age |
| 1  | John       | Smith     | 28  |
| 2  | Jane       | Doe       | 32  |
| 3  | Michael    | Johnson   | 40  |

We can delete all records from the employees table using the TRUNCATE statement in MySQL:

TRUNCATE TABLE employees;

After running this statement, the employees table will be empty.

Output

When we run the TRUNCATE statement in MySQL, the output will be a confirmation of the number of affected rows:

Query OK, 3 rows affected

Note that the number of affected rows will vary depending on the number of records in the table or view.

Explanation

The TRUNCATE statement in MySQL deletes all records from a table or view, similar to the DELETE statement without a WHERE clause. However, unlike the DELETE statement, which deletes each row one by one, TRUNCATE deletes all rows in one operation, making it faster and more efficient.

When we run the TRUNCATE statement, MySQL removes all data from the table or view and resets any auto-increment columns to their starting value. However, it does not delete the table or view itself.

Use

The TRUNCATE statement in MySQL is typically used when you need to delete all records from a table or view, without deleting the table or view structure itself. This can be useful in situations where you want to clear out test data in a development environment or you need to reset a table before inserting new data.

It's important to note that TRUNCATE cannot be used on tables with foreign key constraints. To delete data from these tables, you must use the DELETE statement with a WHERE clause.

Important Points

• The TRUNCATE statement in MySQL is used to delete all records from a table or view.
• TRUNCATE is faster and more efficient than the DELETE statement without a WHERE clause because it deletes all records in one operation.
• The TRUNCATE statement resets any auto-increment columns to their starting value.
• TRUNCATE cannot be used on tables with foreign key constraints.

Summary

In this tutorial, we discussed the syntax, example, output, explanation, use, important points, and summary of the TRUNCATE statement in MySQL. TRUNCATE is a powerful statement that can help you quickly delete all records from a table or view in a single operation, making it useful for various situations in application development.