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

Previous Page Print Page