[phpmyadmin] Delete all records in a table of MYSQL in phpMyAdmin

I use wampserver 2.2. When I want to delete all records of a table in phpMyAdmin (select all) it deletes only one record not all records. Why it does not delete all records?

This question is related to phpmyadmin

The answer is


You have 2 options delete and truncate :

  1. delete from mytable

    This will delete all the content of the table, not reseting the autoincremental id, this process is very slow. If you want to delete specific records append a where clause at the end.

  2. truncate myTable

    This will reset the table i.e. all the auto incremental fields will be reset. Its a DDL and its very fast. You cannot delete any specific record through truncate.


An interesting fact.

I was sure TRUNCATE will always perform better, but in my case, for a db with approx 30 tables with foreign keys, populated with only a few rows, it took about 12 seconds to TRUNCATE all tables, as opposed to only a few hundred milliseconds to DELETE the rows. Setting the auto increment adds about a second in total, but it's still a lot better.

So I would suggest try both, see which works faster for your case.


'Truncate tableName' will fail on a table with key constraint defined. It will also not reindex the table AUTO_INCREMENT value. Instead, Delete all table entries and reset indexing back to 1 using this sql syntax:

DELETE FROM tableName;
ALTER TABLE tableName AUTO_INCREMENT = 1

Use this query:

DELETE FROM tableName;

Note: To delete some specific record you can give the condition in where clause in the query also.

OR you can use this query also:

truncate tableName;

Also remember that you should not have any relationship with other table. If there will be any foreign key constraint in the table then those record will not be deleted and will give the error.


Go to the Sql tab run one of the below query:

delete from tableName;

Delete: will delete all rows from your table. Next insert will take next auto increment id.

or

truncate tableName;

Truncate: will also delete the rows from your table but it will start from new row with 1.

A detailed blog with example: http://sforsuresh.in/phpmyadmin-deleting-rows-mysql-table/


write the query: truncate 'Your_table_name';


You can delete all the rows with this command.But remember one thing that once you run truncate command you cannot rollback it.

  truncate tableName;

  • Visit phpmyadmin
  • Select your database and click on structure
  • In front of your table, you can see Empty, click on it to clear all the entries from the selected table.

demo-database-structure

Or you can do the same using sql query:

Click on SQL present along side Structure

TRUNCATE tablename; //offers better performance, but used only when all entries need to be cleared
or
DELETE FROM tablename; //returns the number of rows deleted

Refer to DELETE and TRUNCATE for detailed documentaion