[+/-]
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
.
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
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
Add your own comment.