Drop tables that are not in a list?
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 frominformation_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?
- The first mysql call gets all the tables without the header row (-N option)
- The output of mysql is piped to Perl
- The Perl script adds a first line to skip foreign key checks since we are dropping the tables
- The script then adds DROP TABLE X for each table, skipping the tables ABC, DEF and XYZ
- 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
精彩评论