Menu
In MySQL, there is no way to specify a storage engine for a certain database, only for single tables. However, you can specify a storage engine to be used during one session with:
SET storage_engine=InnoDB; So you don't have to specify it for each table.
How do I confirm, if indeed all the tables are using InnoDB?
367
To determine which engine a database table is currently using, type the following command at the mysql> prompt. Replace database with the name of the database that you want to check: Copy SELECT TABLE_NAME, ENGINE FROM information_schema. TABLES where TABLE_SCHEMA = 'database';
MyISAM is a non-transactional storage type, and any write option needs to be rolled back manually (if needed). InnoDB is a transaction storage type that automatically rollbacks the writes if they are not completed.
If you use SHOW CREATE TABLE, you have to parse the engine out of the query.
Selecting from the INFORMATION_SCHEMA database is poor practice, as the devs reserve the right to change its schema at any time (though it is unlikely).
The correct query to use is SHOW TABLE STATUS - you can get information on all the tables in a database:
SHOW TABLE STATUS FROM `database`; Or for a specific table:
SHOW TABLE STATUS FROM `database` LIKE 'tablename'; One of the columns you will get back is Engine.
193
SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = 'db name' AND ENGINE != 'InnoDB' If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
