delete entire table

Question

Please bear with me, I am quite new at PHP, so I am not so good yet. I am trying to make a button, which has the function that can delete all rows in MySQL database.

I have two columns in my numtable: "n_id(primary key)" and "num". They are both INT.

As it is now I have filled out 3 values in MySQL database:

n_id   num
1       4
2       9
3       47

index.html:

<form method = "post" action = "delete.php">
    <input name="delete" type="submit" id="delete" value="Delete">
</form>

delete.php:

$query = ("DELETE FROM numtable WHERE id="delete");

I have looked a lot around stack overflow, but I can only find questions, where it is a single row there has to be deleted, and not the entire table. My code is definitely wrong, but what is it that I need more?

I can see that it is also a possibility to use TRUNCATE instead of DELETE in the SQL query. As I understand it, it is best to use TRUNCATE if the database has very big data, and delete if the database is not so big. Is that correct?

Answer 1

If you need to delete all records from the table - then just omit where condition:

DELETE FROM numtable

Answer 2

DELETE FROM table is all you need if you don't want to truncate

The difference between DELETE and truncate is interesting.

• DELETE : Clear rows one by one checking the condition.

• TRUNCATE : Clear all rows quickly. Internally it destroys and recreate tables.

Check this summary, it's really aclarative:

Also DELETE FROM table WHERE 1 can do the trick if there is any "whole-table-operations" protection.

To read more about DELETE and TRUNCATE follow these links:

Delete, Truncate or Drop to clean out a table in MySQL

http://hubpages.com/technology/differences-among-TRUNCATE-DROP-and-DELETE-commands-of-Oracle-MySQL-Microsoft-DB-or-any-other-database

Answer 3

It is best to use truncate when you want to remove all the rows from the table. The following are three ways of removing all data from tables:

truncate table t
delete from t
drop t

What is the difference? Drop removes the table entirely from the database. This includes all triggers, constraints, indexes, and so on. The table is not there, so you can re-create it if you like.

Delete and Truncate delete all the rows in the table, while keeping the structure of the table. This is handy for emptying the table, while keeping triggers, indexes, constraints, and so on. The difference is that delete logs all the transactions (assuming that the underlying database/storage engine does). Hence, it is slower. Truncate does not do logging and does not fire deletion triggers, so it is typically much faster.

This is a simplified explanation. The documentation does a good job explaining the differences between truncate and delete.

Answer 4

Using truncate is generally much faster as it zeroes the table, but it will block as it is a DDL. Read more about TRUNCATE and some of the downsides (e.g. it cannot be undone)

If you expect writes to continue and you have sufficient data then you may be better doing:

create table table_name_new like table_name;
rename table_name to table_name_YYYYMMDD, table_name_new to table_name;

You can then either backup the data, or drop the table entirely without impacting writes to the original table.

Answer 5

There WHERE in a SELECT, UPDATE or DELETE statement identifies which rows to apply the operation to - hence you just need a WHERE clause which identifies all rows. (Note that with MySQL the LIMIT clause also constrains the number of rows the operation is applied to.)

Usually applying an operation to all rows done by omitting the WHERE clause completely - which is equivalent to applying a filter condition which is true for all rows.

However, erasing each row from a table is much slower than explicitly asking the database to deallocate the storage space assigned to a table - hence most SQL rDBMS also implement the TRUNCATE command.

Dropping and recreating the table is also an effective solution (IIRC its faster on Oracle the TRUNCATE).

Answer 6

index.html:
<form method = "post" action = "delete.php">
    <input name="delete" type="submit" id="delete" value="Delete">
</form>

For delete all record

delete.php: $query = ("DELETE FROM numtable");

For delete selected record

 $query = ("DELETE FROM numtable [Where Clause] ");