MySQL Drop Table

It is very easy to drop a table in MySQL, but you must be very careful when performing the drop table operation, because all data will disappear after the drop command is executed.

Syntax

The following is the general syntax for dropping a MySQL table:

DROP TABLE table_name;     -- 直接删除表，不检查是否存在

or

DROP TABLE [IF EXISTS] table_name;  -- 会检查是否存在，如果存在则删除

Parameter description:

  • table_nameIs the name of the table to be dropped.
  • IF EXISTSIs an optional clause, indicating that the drop operation is performed only if the table exists, to avoid errors caused by the table not existing.
-- 删除表，如果存在的话
DROP TABLE IF EXISTS mytable;

-- 直接删除表，不检查是否存在
DROP TABLE mytable;

Please replacemytablewith the name of the table you want to drop.

If you only want to delete all the data in the table but keep the table structure, you can use the TRUNCATE TABLE statement:

TRUNCATE TABLE table_name;

This will clear all the data in the table, but will not drop the table itself.

Notes:

  • Backup data: Before dropping the table, make sure you have backed up the data, if you need it.
  • Foreign key constraints: If the table has foreign key constraints with other tables, you may need to drop the foreign key constraints first, or ensure that the dependencies are properly handled.

Example

The following example drops the data table example_tbl:

Example

root@host# mysql -u root -p
Enter password:*******
mysql> USE EXAMPLE;
DATABASE changed
mysql> DROP TABLE example_tbl;
Query OK, 0 ROWS affected (0.8 sec)
mysql>

Using PHP Script to Drop Table

PHP uses the mysqli_query function to drop a MySQL data table.

This function has two parameters and returns TRUE on successful execution, otherwise it returns FALSE.

Syntax

mysqli_query(connection,query,resultmode);
Parameter Description
connection Required. Specifies the MySQL connection to be used.
query Required. Specifies the query string.
resultmode

Optional. A constant. Can be any of the following values:

  • MYSQLI_USE_RESULT (use this if you need to retrieve a large amount of data)
  • MYSQLI_STORE_RESULT (default)

Example

The following example uses a PHP script to drop the data table example_tbl:

Drop Database

<?php $dbhost = 'localhost'; //MySQL server host address $dbuser = 'root'; //MySQL username $dbpass = '123456'; //MySQL username password $conn = mysqli_connect($dbhost, $dbuser, $dbpass); if(! $conn ) { die('Connection failed:' . mysqli_error($conn)); } echo 'Connection successful<br />'; $sql = "DROP TABLE example_tbl"; mysqli_select_db( $conn, 'EXAMPLE' ); $retval = mysqli_query( $conn, $sql ); if(! $retval ) { die('Failed to drop the data table:' . mysqli_error($conn)); } echo "Data table dropped successfully\n"; mysqli_close($conn); ?>

After successful execution, we use the following command, and the example_tbl table will no longer be visible:

mysql> show tables;
Empty set (0.01 sec)
Other extensions