Skip navigation links
13.2.5.1 How to Use Transactions in InnoDB with Different APIs »
Section Navigation      [Toggle]

13.2.5. Creating and Using InnoDB Tables

[+/-]

13.2.5.1. How to Use Transactions in InnoDB with Different APIs
13.2.5.2. Converting Tables from Other Storage Engines to InnoDB
13.2.5.3. AUTO_INCREMENT Handling in InnoDB
13.2.5.4. FOREIGN KEY Constraints
13.2.5.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.


User Comments

Posted by dan f on June 22 2003 11:23am[Delete] [Edit]

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 12:13pm[Delete] [Edit]

In Mysql prior to v4 (3.23), you MUST use "TYPE = " instead of "ENGINE = ".

Posted by Guy Baconniere on November 27 2008 9:57am[Delete] [Edit]

Convert all databases to InnoDB (except "mysql" and "information_schema").

#!/bin/bash

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

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"
done
done

exit 0

Add your own comment.