593 shaares
1 result
tagged
sql
Trouver les tables non InnoDB et les migrer
#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>';
flush tables;
Merci David :)
#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>';
flush tables;
Merci David :)