Delete all the records from a MySQL table?

To delete all the records from a MySQL table, you can use the TRUNCATE statement.

The syntax is as follows −

TRUNCATE TABLE yourTableName;

The above syntax deletes all the records from the table. To understand the above syntax, let us create a table. The following is the query to create a table −

mysql> create table DeleteAllFromTable
   −> (
   −> PersonId int,
   −> PersonName varchar(200)
   −> );
Query OK, 0 rows affected (0.67 sec)

Insert some records in the table with the help of insert command.

The query is as follows −

mysql> insert into DeleteAllFromTable values(100,'Johnson');
Query OK, 1 row affected (0.10 sec)

mysql> insert into DeleteAllFromTable values(101,'John');
Query OK, 1 row affected (0.22 sec)

mysql> insert into DeleteAllFromTable values(102,'Carol');
Query OK, 1 row affected (0.47 sec)

mysql> insert into DeleteAllFromTable values(103,'Sam');
Query OK, 1 row affected (0.19 sec)

The following is the query to display all the records −

mysql> select *from DeleteAllFromTable;

The following is the output −

+----------+------------+
| PersonId | PersonName |
+----------+------------+
|      100 | Johnson    |
|      101 | John       |
|      102 | Carol      |
|      103 | Sam        |
+----------+------------+
4 rows in set (0.00 sec)

Now delete all the records from the table with the help of TRUNCATE command. The query is as follows −

mysql> TRUNCATE TABLE DeleteAllFromTable;
Query OK, 0 rows affected (0.80 sec)

Since we deleted all the records above, therefore no record would be displayed when we will use the SELECT statement −

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

Look at the above output, there are no records in the table.