7.5.5 Creating InnoDB Tables

Suppose you have started the MySQL client with the command mysql test. To create a table in the InnoDB format you must specify TYPE = InnoDB in the table creation SQL command:

CREATE TABLE CUSTOMER (A INT, B CHAR (20), INDEX (A)) TYPE = InnoDB;

This SQL command will create a table and an index on column A into the InnoDB tablespace consisting of the datafiles you specified in `my.cnf'. In addition MySQL will create a file `CUSTOMER.frm' to the MySQL database directory `test'. Internally, InnoDB will add to its own data dictionary an entry for table 'test/CUSTOMER'. Thus you can create a table of the same name CUSTOMER in another database of MySQL, and the table names will not collide inside InnoDB.

You can query the amount of free space in the InnoDB tablespace by issuing the table status command of MySQL for any table you have created with TYPE = InnoDB. Then the amount of free space in the tablespace appears in the table comment section in the output of SHOW. An example:

SHOW TABLE STATUS FROM test LIKE 'CUSTOMER'

Note that the statistics SHOW gives about InnoDB tables are only approximate: they are used in SQL optimisation. Table and index reserved sizes in bytes are accurate, though.

Subsections

• 7.5.5.1 Converting MyISAM Tables to InnoDB
• 7.5.5.2 Foreign Key Constraints

User Comments

You might wonder as I did, "How do I find out if my table is InnoDB or not?"

See the docs on "SHOW TABLE STATUS", for example http://www.mysql.com/doc/en/SHOW_TABLE_STATUS.html.

Add your own comment.