Creating InnoDB Tables

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 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 Data_free section in the output of SHOW TABLE STATUS (or the Comment section prior to MySQL 5.1.24). For example:


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.

Download this Manual
User Comments
  Posted by dan f on June 22, 2003
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.
  Posted by Otheus on July 13, 2006
In Mysql prior to v4 (3.23), you MUST use "TYPE = " instead of "ENGINE = ".
  Posted by Guy Baconniere on November 27, 2008
Convert all databases to InnoDB (except "mysql" and "information_schema").


if [ ! -f "$HOME/.my.cnf" ]; then
echo "Please create a ~/.my.cnf first"
exit 1

mysql -B -N -e "SHOW DATABASES" mysql \
| grep -E -v '^(mysql|information_schema)$' \
| while read database; do
mysql -B -N -e "SHOW TABLES" "$database" \
| while read table; do
echo "+ Converting Table $table ($database)"
mysql -B -N -e "ALTER TABLE $table ENGINE = InnoDB" "$database"

exit 0

  Posted by Ryan Hendrickson on April 25, 2012
If you have access to the information_schema, then you can do this:
#use information_schema;
Sign Up Login You must be logged in to post a comment.