Geek asks and answers

Delete tables that are not on the list?

I need to drop about 20 thousand tables. I know table names that should NOT be deleted.

If I have the table names "a, b, c, d, e, f, g ...", how can I delete all the tables that are not in the list of "a, b, c"?

+3
source share
7 answers

Try this to get the SQL DROP command set:

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 have 20,000-n DROP statements.

+2
source

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!

+3
source

  • SHOW TABLES ( information_schema.TABLES)
  • , .
  • /, DROP TABLE

SQL script,

linux shell ninja, , , uniq, xargs .., . .

+2

select information_schema.tables , ( if). , drop / . ( - - )

select, - :

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

: 20k ?????

+1

, , .

  • ,
  • DROP DATABASE
  • , .
0

. ABC, DEF 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

?

  • mysql ( -N)
  • mysql Perl
  • Perl script ,
  • script DROP TABLE X , ABC, DEF XYZ
  • Exiting Perl goes to mysql, modifying the same database
0
source

There is a much simpler way. In one line at the command prompt, 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 receive two warnings about the inclusion of passwords in the command. If this could be a problem, you can clear the command history to “remove evidence” :-)

history -c
0
source

More articles:


All Articles