How to delete multiple tables in batches in mysql?

Mysql method to delete multiple tables in batches: use the "DROP TABLE" statement, just write the table names at the end and separate them with commas; the syntax format is "DROP TABLE [IF EXISTS] Table name 1 [, table name 2, table name 3...]".

mysql batch delete multiple tables

Use the DROP TABLE statement to delete one or Multiple data tables, the syntax format is as follows:

DROP TABLE [IF EXISTS] 表名1 [ ,表名2, 表名3 ...]

The description of the syntax format is as follows:

• Table name 1, table name 2, table name 3... means The name of the data table to be deleted. DROP TABLE can delete multiple tables at the same time. Just write the table names at the end and separate them with commas.

• IF EXISTS is used to determine whether the table exists before deleting it. If IF EXISTS is not added, MySQL will prompt an error and interrupt the execution of the SQL statement when the data table does not exist; after adding IF EXISTS, when the data table does not exist, the SQL statement can be executed smoothly, but a warning will be issued.

Two points to note:

• The user must have the permission to execute the DROP TABLE command, otherwise the data table will not be deleted.

• When a table is deleted, the user's permissions on the table will not be automatically deleted.

Example:

Query the data table in the database

mysql> SHOW TABLES;
+--------------------+
| Tables_in_test_db  |
+--------------------+
| tb_emp1            |
| tb_emp2            |
| tb_emp3            |
+--------------------+
2 rows in set (0.00 sec)

You can see it from the running results Out, there are 3 data tables tb_emp1, tb_emp2 and tb_emp3 in the database.

Let's delete the data tables tb_emp1 and tb_emp3. The input SQL statement and the execution result are as follows:

mysql> DROP TABLE tb_emp1,tb_emp3;
Query OK, 0 rows affected (0.22 sec)
mysql> SHOW TABLES;
+--------------------+
| Tables_in_test_db  |
+--------------------+
| tb_emp2            |
+--------------------+
1 rows in set (0.00 sec)

As you can see from the execution result, the name no longer exists in the data table list of the test_db database. For the tables tb_emp1 and tb_emp3, the delete operation was successful.

Recommended tutorial: mysql video tutorial

The above is the detailed content of How to delete multiple tables in batches in mysql?. For more information, please follow other related articles on the PHP Chinese website!