This tutorial highlights how to use MySQL TRUNCATE statements to eliminate all the facts in a databases desk.
MySQL TRUNCATE assertion is part of the Info Definition Language statements. Nevertheless, its features are identical to the DELETE assertion, generating it look a aspect of Information Manipulation Language.
To use the TRUNCATE assertion, you ought to have the Drop privileges on the database.
Characteristics of Truncate
The next are some of the salient characteristics of the TRUNCATE statement that tends to make it different from the DELETE statement:
- A truncate procedure cannot be rolled back as it performs an implicit commit.
- It operates by deleting the table and re-producing it, preserving its framework but not the knowledge.
- Truncate supports corrupt tables, removing all the facts and restoring an vacant table.
- It does not invoke any delete triggers.
- It preserves a table’s partitioning
- The TRUNCATE assertion does not return any details about the affected rows—meaning that the return benefit is .
The typical syntax for using the TRUNCATE statement is:
Note: You can skip the Table search term, and the TRUNCATE assertion will perform equally. Even so, it is improved to include the Desk key phrase to stay clear of confusion with the Truncate functionality.
Example Use Circumstance
Let’s look at an example of how to use the TRUNCATE statement.
For this case in point, I will use the employees’ desk furnished in the useful resource down below:
Initially, decide on a several values from the desk to validate it is not vacant:
The output is proven beneath:
Now that we have verified that the table is comprehensive of data, permit us test to truncate the desk as:
We initially set the Foreign_Important_Examine variable to Bogus because the TRUNCATE statement fails if the table has constraints from other tables.
After we have eliminated the capability to check out constraints from other tables, we call the TRUNCATE assertion to clear away the information.
You can affirm by clicking find:
Warning: Do not eliminate the look at for constraints in tables in a authentic-globe database.
This guidebook walked you as a result of how to use the TRUNCATE statement in MySQL to take out knowledge in a table. I hope the tutorial was practical.