PHPMyAdmin: deleting all rows from MySql table

Posted on October 20, 2017 by Suresh Kamrushi in Mysql

Basically, you can delete rows in MySQL table by using below two queries, which are explained as :

Example: I have createded a “user” table with some sample data.

select Query

Now if run delete query as below, it will delete all rows in it but structure and next auto increment value will remain unchange.

DELETE FROM `user`

When you insert a new record it will take next auto increment value i.e 5.

row_after_delete_query

Additionally if you want to delete specific rows you can add conditions to your query like below:

DELETE FROM `user` WHERE `id` = 1

Now if you look at TRUNCATE it will reset your auto increment id. next time you insert a new record it will start with 1 again. Now run the below query to truncate the data:

TRUNCATE user

And browse the rows in table after inserting a new record, it will start again with 1.
It is usefull when you want to start the application as a freash. It will remove all your test/junk data inserted while testing.
truncate_query
With Truncate you can’t delete specific rows as it is with delete. So Truncate will restric user from doing so but delete allow user to delete rows based on conditions provided in where clause.

Hope this will help some to understand better DELETE and TRUNCATE statements.

Tagged as ,