Geek asks and answers

Drop all tables except one in MySql

I have 100 tables in my database, I want to save only one.

I tried something like the request below:

DROP ALL TABLES EXCEPT my_table

But it does not seem to exist. any idea?

+5
source share
6 answers

You can create a DROP TABLE statement with several tables listed and run a query using prepared MySQL reports -

SET @tables = NULL;
SELECT GROUP_CONCAT(table_schema, '.', table_name) INTO @tables FROM information_schema.tables 
  WHERE table_schema = 'Database1' AND table_name <> 'my_table';

SET @tables = CONCAT('DROP TABLE ', @tables);
PREPARE stmt1 FROM @tables;
EXECUTE stmt1;
DEALLOCATE PREPARE stmt1;
+11
source

You cannot delete multiple tables in MySQL.

The easiest way in your case is to export the table that you want to save (using a tool such as mysqldump), then drop and recreate the database.

+4
source

, :

C:\Program Files (x86)\Parallels\Plesk\Databases\MySQL\data\user_db\...

? , , ,

+1

, GROUP_CONCAT. : group_concat_max_len

: GROUP_CONCAT

: MYSQL: LONG

+1

,

SELECT CONCAT( 'DROP TABLE ', table_name , ';' ) AS statement FROM information_schema.tables WHERE table_name not like 'table_to_keep' and table_schema like 'your_schema' into outfile 'path_to_file;
source path_to_file;
0

mysqldump, !

mysqldump -u[USERNAME] -p[PASSWORD]--add-drop-table --no-data [DATABASE] |
grep ^DROP |
grep -v 'my_table' |
mysql -u[USERNAME] -p[PASSWORD] [DATABASE]
0

More articles:


All Articles