14.2.6. Creating and Using InnoDB Tables
To create an InnoDB table, you must specify an
ENGINE = InnoDB option in the CREATE
TABLE statement:
CREATE TABLE customers (a INT, b CHAR (20), INDEX (a)) ENGINE=InnoDB;
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'
Note that 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.
