What is the significant difference between MySQL TRUNCATE and DROP command?

The most significant difference between MySQL TRUNCATE and DROP command is that TRUNCATE command will not destroy table’s structure but in contrast DROP command will destroy table’s structure too.

Example

mysql> Create table testing(id int PRIMARY KEY NOT NULL AUTO_INCREMENT,Name Varchar(20));
Query OK, 0 rows affected (0.24 sec)

mysql> Insert into testing(Name) Values('Ram'),('Mohan'),('John');
Query OK, 3 rows affected (0.12 sec)
Records: 3 Duplicates: 0 Warnings: 0

mysql> Select * from testing;
+----+-------+
| id | Name  |
+----+-------+
| 1  | Ram   |
| 2  | Mohan |
| 3  | John  |
+----+-------+
3 rows in set (0.00 sec)

Now after TRUNCATING the ‘testing’ table as follows, still its structure remains in the database and it also initializes the PRIMARY KEY.

mysql> Truncate table testing;
Query OK, 0 rows affected (0.04 sec)

mysql> DESCRIBE testing;
+-------+-------------+------+-----+---------+----------------+
| Field | Type        | Null | Key | Default | Extra          |
+-------+-------------+------+-----+---------+----------------+
| id    | int(11)     | NO   | PRI | NULL    | auto_increment |
| Name  | varchar(20) | YES  |     | NULL    |                |
+-------+-------------+------+-----+---------+----------------+
2 rows in set (0.21 sec)

But when we apply DROP command on a table then the structure also deleted from the database.

mysql> Drop table testing;
Query OK, 0 rows affected (0.08 sec)

mysql> DESCRIBE testing;
ERROR 1146 (42S02): Table 'query.testing' doesn't exist

Monica Mona

Published on 19-Feb-2018 08:57:46

• Related Questions & Answers
• What is the significant difference between MySQL TRUNCATE() and ROUND() function?
• What is the difference between MySQL TRUNCATE and DELETE command?
• What is the significant difference between MySQL LIKE and equal to (=) operator?
• What is MySQL TRUNCATE command used for?
• What is MySQL DROP command used for?
• What is the difference between ls() command and objects() command in R?
• What are the most significant differences between MySQL functions and procedures?
• What is the difference between SQL and MySQL?
• Difference between DELETE and DROP SQL
• Difference between Delete and truncate in sql query
• What is the difference between MySQL NOW() and SYSDATE()?
• What is the use of MySQL TRUNCATE() function?
• Difference Between DELETE and DROP in SQL
• Difference between grep and fgrep command
• What is the difference between CHAR and NCHAR in MySQL?