Delete all records in a table of MYSQL in phpMyAdmin

Question

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?

Louislouisa

depends on your query, did you execute like this? DELETE FROM tableName – Wearable

Highlight the table name and press SHIFT-DEL – Impressment

Answer 1

Go to your db -> structure and do empty in required table. See here:

Valentin

Answer 2

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.

Crummy

Note that truncate will also removed index, learn this the hard way... – Demarcate

Can I do this using Python? I tried in PhpMyAdmin and it works flawlessly. But when I write the same command in Python, it gives me an error saying command denied to user@localhost. I researched and it is bcoz it wants permissions. But the same when I do in PhpMyAdmin, it does not require any permissions. How can I do this and why is this difference – Endometrium

Answer 3

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/

Hearse

Can I do this using Python? I tried in PhpMyAdmin and it works flawlessly. But when I write the same command in Python, it gives me an error saying command denied to user@localhost. I researched and it is bcoz it wants permissions. But the same when I do in PhpMyAdmin, it does not require any permissions – Endometrium

Keep in mind that is unsafe query: 'Delete' statement without 'where' clears all data in the table" – Quinn

Answer 4

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.

Soapwort

Answer 5

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;

Blois

Answer 6

'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

Indoor

This is the proper answer. – Tiddlywinks

Answer 7

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.

Jacobson

Answer 8

• 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.

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

Yetta

simple and fast. and i love the shredder icon – Billen

Easy and simple. – Yetta

Answer 9

write the query: truncate 'Your_table_name';

Nervy

Answer 10

Here is another visual method that has not been mentioned.

1. Select the tableName in the side menu.
2. In the table menu, select Operations
3. At the bottom of operations page, you'll see the Delete Data or table options

Sympathin