PoGo's LinkShaared links2013-02-01T13:36:14+01:00https://wtf.roflcopter.fr/links/pogo/https://wtf.roflcopter.fr/links/pogo/https://wtf.roflcopter.fr/links/pogo/ShaarliScript SQL migration InnoDBhttps://wtf.roflcopter.fr/links/pogo/?NNLMbw2013-02-01T13:36:14+01:002013-02-01T13:36:14+01:00 #Affiche le nombre de tables non InnoDB par database
SELECT table_schema, engine, COUNT(*) FROM information_schema.tables WHERE table_schema NOT IN ('information_schema', 'mysql', 'performance_schema') AND engine <> 'InnoDB' GROUP BY table_schema, engine;
#Affiche le moteur des tables / db
select concat(table_schema, '.', table_name), engine from information_schema.tables WHERE table_schema = '<db_name>';
#Génération de la commande SQL pour afficher l'index de chaque table non InnoDB
select concat('SHOW INDEX from ', table_schema, '.', table_name, ' where index_type=\'FULLTEXT\';') from information_schema.tables WHERE engine <> 'InnoDB' AND table_schema = '<db_name>';
#Génération de la commande SQL pour migrer les tables vers InnoDB
select concat('ALTER TABLE ', table_schema, '.', table_name, ' ENGINE=InnoDB;') from information_schema.tables WHERE engine <> 'InnoDB' AND table_schema = '<db_name>';
#On affiche le moteur de chaque tables pour s'assurer que la migration s'est bien passée
select concat(table_schema, '.', table_name), engine from information_schema.tables WHERE table_schema = '<db_name>';