MySQL Reference Manual - 4.0.20

[ < ]

[ > ]

[ << ]

[ Up ]

[ >> ]

[Top]

[Contents]

[Index]

[ ? ]

16.7 Creating `InnoDB` Tables

Suppose that you have started the MySQL client with the command mysql test. To create an InnoDB table, you must specify and ENGINE = InnoDB or TYPE = InnoDB option in the table creation SQL statement:

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

The SQL statement creates a table and an index on column a in the InnoDB tablespace that consists of the data files 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 to its own data dictionary an entry for table 'test/customers'. This means you can create a table of the same name customers in some other database, and the table names will 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. An example:

SHOW TABLE STATUS FROM test LIKE 'customers'

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