mysql
MySQL – How to drop all tables starting with a prefix?
Solution
SELECT CONCAT('DROP TABLE ', TABLE_SCHEMA, '.', TABLE_NAME, ';') FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME LIKE 'prefix%' AND TABLE_SCHEMA = 'my_database' INTO outfile '/tmp/drop_my_tables.sql'; SOURCE /tmp/drop_my_tables.sql;
MySQL Dump with condition
Solution
mysqldump -u root -p pass my_database my_table --where="id IN (100, 101)" > my_table_dump.sql
MySQL – Search for tables by column name
Solution
SELECT DISTINCT TABLE_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE COLUMN_NAME LIKE '%name%' AND TABLE_SCHEMA = 'my_database';
How to see how much disk space a MySQL table is taking up?
Solution
SELECT table_schema as `Database`, table_name AS `Table`, ROUND(((data_length + index_length) / 1024 / 1024), 2) `Size in MB` FROM information_schema.TABLES ORDER BY (data_length + index_length) DESC;