MySQL tips

change_collation.sql

SELECT CONCAT("ALTER TABLE ", TABLE_NAME," CHARSET=utf8, COLLATE=utf8_general_ci;") AS    ExecuteTheString
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_SCHEMA="dababase"
AND TABLE_TYPE="BASE TABLE"

change_engine.sql

SELECT  CONCAT('ALTER TABLE `', table_name, '` ENGINE=InnoDB;') AS sql_statements
FROM    information_schema.tables AS tb
WHERE   table_schema = @DATABASE_NAME
AND     `ENGINE` = 'MyISAM'
AND     `TABLE_TYPE` = 'BASE TABLE'
ORDER BY table_name DESC;

drop_tables_by_prefix.sql

SELECT CONCAT( 'DROP TABLE ', GROUP_CONCAT(table_name) , ';' ) 
    AS statement FROM information_schema.tables 
    WHERE table_schema = 'database_name' AND table_name LIKE 'myprefix_%';

find_prefix.sql

SELECT *
FROM information_schema.columns
WHERE table_schema = 'database_name'
	AND column_key = 'PRI'
    AND column_name LIKE 'prefix_%'

show_all_views.sql

SHOW FULL TABLES IN database_name WHERE TABLE_TYPE LIKE 'VIEW';

sqlite_date.sql

SELECT datetime(data_column/1000, 'unixepoch', 'localtime') AS data_t FROM table;

truncate_all_tables_in_multiple_databases.sql

SELECT Concat('TRUNCATE TABLE `',table_schema,'`.`',TABLE_NAME, '`;') 
FROM INFORMATION_SCHEMA.TABLES where  table_schema in ('db1_name','db2_name');

truncate_tables_with_foreign_keys.sql

SET FOREIGN_KEY_CHECKS=0;
TRUNCATE table1;
TRUNCATE table2;
SET FOREIGN_KEY_CHECKS=1;