How to DROP TABLE based on a pattern in MySQL?

Question

so here is my database.

+------------------------+
| Tables_in_homestead    |
+------------------------+
| 10_UsercardTB          |
| 1_UsercardTB           |
| 3_UsercardTB           |
| 4_UsercardTB           |
| 5_UsercardTB           |
| 6_UsercardTB           |
| 7_UsercardTB           |
| 8_UsercardTB           |
| 9_UsercardTB           |
| failed_jobs            |
| migrations             |
| password_resets        |
| personal_access_tokens |
| users                  |
+------------------------+

how can I drop tables from "1_UsercardTB" to "10_UsercardTB" in a single command?

the pattern is '%_UsercardTB'

Answer 1

But, MySQL does not allow DROP TABLE LIKE statement to delete tables using the pattern matching.

for example there is no command such as:

DROP TABLE LIKE '%target_pattern%'

However, we can achieve the same a bit differently ...

find full guide at following link:

https://www.mysqltutorial.org/mysql-drop-table/

this is the script we can use:

-- set table schema and pattern matching for tables
SET @schema = 'homestead';
SET @pattern = '%_UsercardTB';

-- build dynamic sql (DROP TABLE tbl1, tbl2...;)
SELECT CONCAT('DROP TABLE ',GROUP_CONCAT(CONCAT(@schema,'.',table_name)),';')
INTO @droplike
FROM information_schema.tables
WHERE @schema = database()
AND table_name LIKE @pattern;

-- display the dynamic sql statement
SELECT @droplike;

-- execute dynamic sql
PREPARE stmt FROM @droplike;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;