How to check if a table exists in MySQL and then delete it in batches

1. I searched for a long time on the Internet but couldn't find whether the judgment table existed, so I deleted it vaguely. But in the end, we found a roundabout way to save the country.

Requirement: Delete the table with the suffix _ to be deleted.

1. The first step is to find all such tables and assemble the SQL to delete the table

Select CONCAT( 'DROP TABLE IF EXISTS ', table_name, ';' ) 
FROM information_schema.tables 
Where table_name LIKE '%_to be deleted';

Execution Results

2. Step 2: Execute the delete statements one by one. If you are capable, you can write a script to execute something.

3. SQL parsing

3-1: concat string concatenation

3-2: DROP TABLE IF EXISTS equi_accp_info__to be deleted; -- Check if this table exists, and delete it if it exists

Supplementary knowledge: mysql determines whether a table field exists and then modifies it

I won't say much nonsense, let's just look at the code~

-- Check if the thresholdMin field exists in the vrv_paw_rule table. If it does not exist, add it. If it does exist, modify the field type DELIMITER ??
DROP PROCEDURE IF EXISTS schema_change??
CREATE PROCEDURE schema_change()
BEGIN
IF NOT EXISTS (SELECT * FROM information_schema.columns WHERE table_schema = DATABASE() AND table_name = 'vrv_paw_rule' AND column_name = 'thresholdMin') THEN
  ALTER TABLE vrv_paw_rule ADD COLUMN thresholdMin BIGINT;
ELSE  
  ALTER TABLE vrv_paw_rule MODIFY COLUMN thresholdMin BIGINT;
END IF; 
END??
DELIMITER ;

CALL schema_change();

delimit command:

It tells the MySQL interpreter whether the command has ended and whether MySQL can execute it, that is, change the input terminator.

By default, the delimiter is a semicolon ";".

In the command line client, if a line of commands ends with a semicolon,

Then after pressing Enter, MySQL will execute the command.

But sometimes, you don't want MySQL to do this. Because you may enter more statements and the statements may contain semicolons.

By default, it is not possible to wait until the user has entered all of the statements before executing the entire statement.

Because mysql will automatically execute once it encounters a semicolon.

In this case, you can use delimiter and replace the delimiter with other symbols, such as // or $$.

At this time, the role of delimiter is to simply encapsulate the entire small statement.

This command is mostly used to define subroutines, trigger programs, and other MySQL built-in programs.

The above operation of MySQL to determine whether a table exists and then delete it in batches is all the content that the editor shares with you. I hope it can give you a reference. I also hope that you will support 123WORDPRESS.COM.