What are the important differences between MySQL TRUNCATE and DROP commands?

The most important difference between MySQL TRUNCATE and DROP commands is that the TRUNCATE command does not destroy the structure of the table, while the DROP command does.

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 in the following case, its structure still remains in the database and it also has the primary key initialized.

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 the DROP command on the table, the structure in the database is also deleted.

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

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

The above is the detailed content of What are the important differences between MySQL TRUNCATE and DROP commands?. For more information, please follow other related articles on the PHP Chinese website!