[+/-]
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:
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.
© 2013, Oracle Corporation and/or its affiliates
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.
In Mysql prior to v4 (3.23), you MUST use "TYPE = " instead of "ENGINE = ".
Convert all databases to InnoDB (except "mysql" and "information_schema").
mysql -B -N -e "SHOW TABLES" "$database" \#!/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 \
| while read table; do
echo "+ Converting Table $table ($database)"
mysql -B -N -e "ALTER TABLE $table ENGINE = InnoDB" "$database"
done
done
exit 0
If you have access to the information_schema, then you can do this:
#use information_schema;
#select TABLE_NAME, ENGINE from TABLES where TABLE_SCHEMA='db';
Add your own comment.