14.2.3 Creating and Using InnoDB Tables

14.2.3.1 How to Use Transactions in InnoDB with Different APIs
14.2.3.2 Converting Tables from Other Storage Engines to InnoDB
14.2.3.3 AUTO_INCREMENT Handling in InnoDB
14.2.3.4 InnoDB and FOREIGN KEY Constraints
14.2.3.5 InnoDB and MySQL Replication

To create an InnoDB table, specify an ENGINE = InnoDB option in the CREATE TABLE statement:

CREATE TABLE customers (a INT, b CHAR (20), INDEX (a)) ENGINE=InnoDB;

The older term TYPE is supported as a synonym for ENGINE for backward compatibility, but ENGINE is the preferred term and TYPE is deprecated.

The statement creates a table and an index on column a in the InnoDB tablespace that consists of the data files that you specified in my.cnf. In addition, MySQL creates a file customers.frm in the test directory under the MySQL database directory. Internally, InnoDB adds an entry for the table to its own data dictionary. The entry includes the database name. For example, if test is the database in which the customers table is created, the entry is for 'test/customers'. This means you can create a table of the same name customers in some other database, and the table names do not collide inside InnoDB.

You can query the amount of free space in the InnoDB tablespace by issuing a SHOW TABLE STATUS statement for any InnoDB table. The amount of free space in the tablespace appears in the Comment section in the output of SHOW TABLE STATUS. For example:

SHOW TABLE STATUS FROM test LIKE 'customers'

The statistics SHOW displays for InnoDB tables are only approximate. They are used in SQL optimization. Table and index reserved sizes in bytes are accurate, though.