There are several tables in the mysql database: temp_ids_1 temp_ids_5 temp_ids_8 temp_ids_12, etc.

Those. they differ only in the ID at the end of the table name. Is it possible to write such a query in order to clear all these tables in one fell swoop? Those. sort of

TRUNCATE `temp_ids_*`
  • one
    It is impossible. You need to read the list of tables from information_schema and dynamically generate queries. And as soon as possible, the structure of the database should be revised in such a way that such tasks would not appear in principle - Mike

2 answers 2

There are several options:

1) sql query:

 SELECT CONCAT( 'TRUNCATE TABLE ', GROUP_CONCAT(table_name) , ';' ) AS statement FROM information_schema.tables WHERE table_name LIKE 'temp_ids_%';

Clear all tables that start with "temp_ids_%" to you. There you can make any condition ;-)

2) bash script:

 mysql -Nse 'show tables' -pDB_PASSWORD -u DB_USER DB_NAME | while read table; do mysql -e "TRUNCATE TABLE $table" -pDB_PASSWORD -u DB_USER DB_NAME; done
  • sql query not working. statement is obtained TRUNCATE TABLE temp_ids_1, temp_ids_2, temp_ids_3, temp_ids_9; (the query was executed in phpmyadm, so I see it), but the tables are not cleared - sashaeee
  • There is an option that you are trying to clear the tables, and there are links to them. So you have to disable their control: SET FOREIGN_KEY_CHECKS = 0; There is still one way, if you see that the query is being built normal. Write it to a file, and in bash run the sql from the file: mysql -u myuser -p mydatabase <file_with_sql_truncate.sql - Andrey Verbitskiy
  • Another option is to delete the tables;) And the second query is to create them (CREATE TABLE). SELECT CONCAT ('DROP TABLE', GROUP_CONCAT (table_name), ';') AS statement FROM information_schema.tables WHERE table_schema = 'database' AND table_name LIKE 'temp_ids_%'; I also added a filter by database, as the table names can be repeated, but the bases will be different. - Andrey Verbitskiy

If all the tables have the same structure, create a single table above the MERGE Storage. Truncate such a table will clear all the tables in the join.

True, I have a strange jamb on Server version: 5.7.16-log MySQL Community Server (GPL). After TRUNCATE, the SELECT above the table shows that there are no records, and SELECT from separate tables shows that the records are in place. And only after inserting at least one record into separate tables, it turns out that there are actually no records that were displayed ...

UPD: Ie after the TRUNCATE table you need to execute the FLUSH TABLE table .