Chapter 15. InnoDB Tables
This chapter describes the InnoDB table type that is supported by the InnoDB storage engine. It covers the following topics:
Questions on the material in this chapter make up approximately 10% of the exam.
The InnoDB storage engine is one of the table handlers that MySQL provides. InnoDB was introduced to MySQL during the 3.23 release series and is a standard feature in binary distributions as of MySQL 4. It has several notable features:
InnoDB supports transactions, with commit and rollback. It provides full ACID (atomicity, consistency, isolation, durability) compliance. Multi-versioning is used to isolate transactions from one another. If the MySQL server or the server host crashes, InnoDB performs automatic crash recovery.
InnoDB manages contention for tables using multi-versioning and row-level locking. This provides good query concurrency even when clients are performing a mix of reads and writes.
InnoDB supports foreign keys and referential integrity, including cascaded deletes and updates.
Each InnoDB table is represented on disk by an .frm format file in the database directory, along with data and index information stored in a tablespace that is shared among InnoDB tables. The tablespace is stored in machine-independent format. It is implemented such that table sizes can exceed the maximum file size allowed by the filesystem.
To create an InnoDB table, nothing more is necessary than to add a TYPE option to the end of the CREATE TABLE statement:
CREATE TABLE t (i INT) TYPE = InnoDB;
To convert an existing table from some other type to InnoDB, use ALTER TABLE:
ALTER TABLE t TYPE = InnoDB;