开发者

Drop tables that are not in a list?

开发者 https://www.devze.com 2023-03-03 03:33 出处:网络
I need to drop about 20k tables.I know the names of the tables that are NOT to be dropped. If I have table names \"a,b,c,d,e,f,g...\", how can开发者_运维问答 I drop all tables that are not in the lis

I need to drop about 20k tables. I know the names of the tables that are NOT to be dropped.

If I have table names "a,b,c,d,e,f,g...", how can开发者_运维问答 I drop all tables that are not in the list "a,b,c"?


You can get a list with this

SELECT CONCAT("DROP TABLE ", table_name, ";") 
FROM information_schema.TABLES 
WHERE table_schema = <whatever your db name is> 
AND table_name NOT IN (<your list>);

Then copy and paste!


Try this to get a resultset of SQL DROP statements:

SELECT CONCAT('DROP TABLE ', TABLE_NAME , ';')
FROM INFORMATION_SCHEMA.TABLES

WHERE TABLE_SCHEMA='YourDatabase'
AND TABLE_NAME NOT IN ('Table1', 'Table2');

Copy and paste the results, and you've got 20,000-n DROP statements.


You need to run select from information_schema.tables and create a cursor that will iterate through the results and perform the appropriate drops (use if statement). This is because drop statement does not support selecting/filtering or other options. (unless something has changed during the last two or three years)

When you're performing the select statement you can use something like:

WHERE table_name NOT IN ('ssss','dddd');

Another thing is: why do you have 20k tables in your database?????


I would suggest that you take the following approach

  • extract a full list of all of the tables one table per line with SHOW TABLES (or from information_schema.TABLES)
  • open the list in a text editor
  • remove the tables that you don't want to remove from the list
  • use a macro or search/replace to turn each line into a DROP TABLE command

Now you have an SQL script that you can run against the database

If you're a linux shell ninja might want to use commandline tools like uniq, xargs, etc to manipulate the files. Using a spreadsheet might be another way to deal with it.


I'm going to suggest a completely different approach if you can get away with the tables that you want to keep being made unavailable for a short period of time.

  • Backup the tables that you want to keep
  • DROP DATABASE the database with all of the tables in
  • Restore the tables that you want to keep from the backups


I ran into this exact same problem today. Here is one approach for skipping tables ABC, DEF and XYZ:

mysql -Nu USER --password=PASSWORD DATABASE -e 'show tables' | \
perl -ne 'BEGIN { print "SET FOREIGN_KEY_CHECKS=0;\n" };
    !/^(ABC|DEF|XYZ)$/ && s/^(.*)/DROP TABLE `\1`;/ && print' | \
mysql -u USER --password=PASSWORD DATABASE

So what the devil is all that?

  1. The first mysql call gets all the tables without the header row (-N option)
  2. The output of mysql is piped to Perl
  3. The Perl script adds a first line to skip foreign key checks since we are dropping the tables
  4. The script then adds DROP TABLE X for each table, skipping the tables ABC, DEF and XYZ
  5. The output from Perl goes to mysql, modifying the same database


There is a much easier way. In one line, on your command line type:

mysql -u root -p[password] --skip-column-names [database] -e "SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA='[database]' AND TABLE_NAME NOT IN ('[table 1]', '[table 2]',..'[table n]');" | cut -f1 | sed -r 's/(.*)/DROP TABLE IF EXISTS \1;/' | mysql -u root -p[password] [database]

You may get two warnings about including passwords in a command. If this may be a problem, you can clear the command history to 'remove the evidence' :-)

history -c
0

精彩评论

暂无评论...
验证码 换一张
取 消

关注公众号