Czy można usunąć wszystkie puste tabele z mojej ogromnej bazy danych (mysql)?

Szukam polecenia sql, aby automatycznie usunąć wszystkie puste tabele.

Obecnie mam w zestawie danych 305 tabel, a około 30% z nich to stare puste tabele, które nie będą używane w mojej nowej aplikacji.

Aby wyjaśnic; Wszystkie tabele są typu = MyISAM

Pomysł prawdopodobnie polegałby na szukaniu pustych tabel przy użyciu INFORMATION_SCHEMA.TABLES

SELECT * 
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_ROWS =  '0'
AND TABLE_SCHEMA = 'my_database_only'

Wtedy możesz wygenerować zapytanie SQL za pomocą

SELECT CONCAT('DROP TABLE ', GROUP_CONCAT(table_name), ';') AS query
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_ROWS = '0'
AND TABLE_SCHEMA = 'my_database_only';

Szybki i nieco brudny, ale powinien działać.

Ponieważ wszystkie tabele są MyISAM, dzięki temu moja odpowiedź jest łatwiejsza do wyrażenia.

Najpierw musisz wysłać zapytanie do Information_SCHEMA dla tabel, które mają zero wierszy:

SELECT table_schema,table_name FROM information_schema.tables
WHERE table_rows = 0 AND table_schema NOT IN
('information_schema','mysql','performance_schema');

Następnie sformułuj zapytanie, aby usunąć puste tabele:

SELECT CONCAT('DROP TABLE ',table_schema,'.',table_name,';') DropTableCommand
FROM information_schema.tables
WHERE table_rows = 0 AND table_schema NOT IN
('information_schema','mysql','performance_schema');

Teraz zrzuć polecenia do zewnętrznego pliku tekstowego SQL.

SQL="SELECT CONCAT('DROP TABLE ',table_schema,'.',table_name,';') DropTableCommand"
SQL="${SQL} FROM information_schema.tables WHERE table_rows = 0 AND table_schema"
SQL="${SQL} NOT IN ('information_schema','mysql','performance_schema')"
mysql -uroot -p -ANe"${SQL}" > DropTables.sql

Przejrzyj zawartość za pomocą jednego z poniższych

• less DropTables.sql
• cat DropTables.sql

Jeśli jesteś zadowolony z jego zawartości, uruchom skrypt:

mysql -uroot -p < DropTables.sql

lub zaloguj się do mysql i uruchom go w następujący sposób:

mysql> source DropTables.sql

Spróbuj !!!

CAVEAT : Ta technika działa tylko z tabelą MyISAM, ponieważ liczba wierszy tabeli MyISAM jest fizycznie przechowywana w .MYDtabelach. Tabela metadanych INFORMACJE_SCHEMA.TABLES zawsze czyta to i aktualizuje. NIE WYPRÓBUJ TEGO W INNODB !!!

AKTUALIZACJA 2014-02-05 11:46 EST

Jest powód, który wykluczyłem ('information_schema','mysql','performance_schema')

mysqlSchemat ma pustych tabel w nim. Niektóre MyISAM, niektóre InnoDB, niektóre CSV.

Na przykład, oto moje tabele w schemacie mysql dla MySQL 5.6.15 na moim pulpicie

mysql> select table_name,engine,table_rows
    -> from information_schema.tables
    -> where table_schema='mysql';
+---------------------------+--------+------------+
| table_name                | engine | table_rows |
+---------------------------+--------+------------+
| columns_priv              | MyISAM |          0 |
| db                        | MyISAM |          2 |
| event                     | MyISAM |          0 |
| func                      | MyISAM |          0 |
| general_log               | CSV    |          2 |
| help_category             | MyISAM |         40 |
| help_keyword              | MyISAM |        485 |
| help_relation             | MyISAM |       1090 |
| help_topic                | MyISAM |        534 |
| innodb_index_stats        | InnoDB |          0 |
| innodb_table_stats        | InnoDB |          0 |
| ndb_binlog_index          | MyISAM |          0 |
| plugin                    | MyISAM |          0 |
| proc                      | MyISAM |          0 |
| procs_priv                | MyISAM |          0 |
| proxies_priv              | MyISAM |          1 |
| servers                   | MyISAM |          0 |
| slave_master_info         | InnoDB |          0 |
| slave_relay_log_info      | InnoDB |          0 |
| slave_worker_info         | InnoDB |          0 |
| slow_log                  | CSV    |          2 |
| tables_priv               | MyISAM |          0 |
| time_zone                 | MyISAM |          0 |
| time_zone_leap_second     | MyISAM |          0 |
| time_zone_name            | MyISAM |          0 |
| time_zone_transition      | MyISAM |          0 |
| time_zone_transition_type | MyISAM |          0 |
| user                      | MyISAM |          6 |
+---------------------------+--------+------------+
28 rows in set (0.01 sec)

mysql>

Jeśli niektóre z tych tabel miały zniknąć (jak columns_priv, proc_priv, tables_priv, itd.), Mechanizm grant może nie działać poprawnie lub może powodować mysqld nie uruchamia się. Również nie chcesz bić mysql.proc, ponieważ jest to fizyczny dom przechowywanych procedur. Inne mechanizmy mogą nie działać, jeśli chcesz ich użyć, takie jak replikacja CrashSafe przy użyciu tabel InnoDB w schemacie mysql lub jeśli chcesz wstawić informacje o strefie czasowej.

AKTUALIZACJA 2014-02-05 12:36 EST

Chciałbym pochwalić Toma Despa za jego odpowiedź z konkretnego powodu: jego składnia może upuścić bez użycia zewnętrznego skryptu . Korzystając z jego pomysłu, pozwól mi przechwycić polecenie DROP TABLE do zmiennej zdefiniowanej przez użytkownika.

SELECT CONCAT('DROP TABLE ',GROUP_CONCAT(DBTB),';')
INTO @DropCommand
FROM (SELECT CONCAT(table_schema,'.',table_name) DBTB
FROM information_schema.tables
WHERE table_rows = 0 AND table_schema NOT IN
('information_schema','mysql','performance_schema')) A;
SELECT @DropCommand;

Jeśli wynik SELECT @DropCommand;jest prawidłowy, wykonaj polecenie w następujący sposób:

PREPARE s FROM @DropCommand;
EXECUTE s;
DEALLOCATE PREPARE s;

To eliminuje dwie rzeczy:

• potrzeba zewnętrznego pliku tekstowego SQL
• uruchamianie osobnego polecenia DROP TABLE dla każdej tabeli