MySql truncate table

MySql truncate table

MySql truncate table statement is used to delete all the rows in mysql table and resets the table (similar like squence of drop table and create table).

TRUNCATE is a Data Definition Language (DDL) command and there is no where clause supported.

truncate command fails If any foreign key constraints associated in table.

Number of rows affected is 0 when using truncate to delete the rows.

DELETE trigger will not be invoked when using truncate.

COMMIT or ROLLBACK is not required for truncate command and changes cannot be rolled back once TRUNCATE is done.

Truncate syntax,

TRUNCATE [TABLE] <table-name>

TABLE Keyword is optional and it's recommended to use.

Lets see the example of using TRUNCATE command using mysql table 'tblemployee1'.

mysql> select * from tblemployee1;
+--------+----------+---------+--------+
| emp_id | emp_name | dept_id | salary |
+--------+----------+---------+--------+
|      1 | emp1     |      10 |  10000 |
|      2 | emp2     |      11 |  20000 |
|      3 | emp3     |      10 |  15000 |
|      4 | emp4     |      11 |  12000 |
+--------+----------+---------+--------+
4 rows in set (0.00 sec)

mysql> truncate table tblemployee1;
Query OK, 0 rows affected (0.52 sec)

mysql> select * from tblemployee1;
Empty set (0.00 sec)

mysql> insert into tblemployee1 (emp_id, emp_name, dept_id, salary) select * from tblemployee;
Query OK, 4 rows affected (0.01 sec)
Records: 4  Duplicates: 0  Warnings: 0

mysql> select * from tblemployee1;
+--------+----------+---------+--------+
| emp_id | emp_name | dept_id | salary |
+--------+----------+---------+--------+
|      1 | emp1     |      10 |  10000 |
|      2 | emp2     |      11 |  20000 |
|      3 | emp3     |      10 |  15000 |
|      4 | emp4     |      11 |  12000 |
+--------+----------+---------+--------+
4 rows in set (0.00 sec)

mysql> truncate tblemployee1;
Query OK, 0 rows affected (0.54 sec)

mysql> select * from tblemployee1;
Empty set (0.00 sec)

mysql>

« Previous Next »