TRUNCATE: Truncate data from table

TRUNCATE TABLE table_name;

Removes all rows from a table or specified partitions of a table.

mysql> SELECT * FROM employee;
+----+---------+------+-------------+---------------------+------------+
| id | name    | age  | salary      | mailId              | city       |
+----+---------+------+-------------+---------------------+------------+
|  1 | Hari    |   28 |  12345.6700 | hari@hari.com       | Bangalore  |
|  2 | Sandesh |   31 |  98345.0000 | sandesh@sandesh.com | Trivendram |
|  3 | Phalgum |   33 | 119345.6700 | phalgun@hari.com    | Hyderabad  |
|  4 | Manju   |   36 |  87666.8700 | manju@sandesh.com   | Bangalore  |
|  5 | Rakesh  |   26 |  38000.0000 | rakesh@hari.com     | Bangalore  |
|  6 | Sankalp |   38 |  87645.6700 | sankalp@sankalp.com | Chenai     |
|  7 | Vadiraj |   40 |  12345.6700 | vadi@hari.com       | Bangalore  |
|  8 | Prasob  |   37 |  12345.6700 | prasob@sandesh.com  | Trivendram |
|  9 | Kesav   | NULL | 123457.8900 | NULL                | NULL       |
+----+---------+------+-------------+---------------------+------------+
9 rows in set (0.00 sec)

mysql> TRUNCATE TABLE employee;
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT * FROM employee;
Empty set (0.00 sec)

TRUNCATE Vs DELETE

After performing a DELETE operation you need to COMMIT or ROLLBACK the transaction to make the change permanent or to undo it. If you delete data using TRUNCATE command, changes are permanent.

Previous                                                 Next                                                 Home