MySQL 5.6 Reference Manual Including MySQL NDB Cluster 7.3-7.4 Reference Guide
To create an InnoDB
table, use the
CREATE TABLE
statement.
CREATE TABLE t1 (a INT, b CHAR (20), PRIMARY KEY (a)) ENGINE=InnoDB;
You do not need to specify the ENGINE=InnoDB
clause if InnoDB
is defined as the default
storage engine, which it is by default. To check the default
storage engine, issue the following statement:
mysql> SELECT @@default_storage_engine;
+--------------------------+
| @@default_storage_engine |
+--------------------------+
| InnoDB |
+--------------------------+
You might still use ENGINE=InnoDB
clause if you
plan to use mysqldump or replication to replay
the CREATE TABLE
statement on a
server where the default storage engine is not
InnoDB
.
An InnoDB
table and its indexes can be created
in the system
tablespace or in a
file-per-table
tablespace. When
innodb_file_per_table
is enabled,
which is the default as of MySQL 5.6.6, an
InnoDB
table is implicitly created in an
individual file-per-table tablespace. Conversely, when
innodb_file_per_table
is
disabled, an InnoDB
table is implicitly created
in the InnoDB
system tablespace.
When you create an InnoDB
table, MySQL creates
a .frm file in the database
directory under the MySQL data directory. For more information
about .frm
files, see
InnoDB Tables and .frm Files. For a table created in a
file-per-table tablespace, MySQL also creates an
.ibd tablespace file in the
database directory, by default. A table created in the
InnoDB
system tablespace is created in an
existing ibdata file, which
resides in the MySQL data directory.
Internally, InnoDB
adds an entry for each table
to the InnoDB
data dictionary. The entry
includes the database name. For example, if table
t1
is created in the test
database, the data dictionary entry for the database name is
'test/t1'
. This means you can create a table of
the same name (t1
) in a different database, and
the table names do not collide inside InnoDB
.
MySQL stores data dictionary information for tables in
.frm files in database
directories. Unlike other MySQL storage engines,
InnoDB
also encodes information about the
table in its own internal data dictionary inside the system
tablespace. When MySQL drops a table or a database, it deletes
one or more .frm
files as well as the
corresponding entries inside the InnoDB
data
dictionary. You cannot move InnoDB
tables
between databases simply by moving the .frm
files. For information about moving InnoDB
tables, see Section 14.6.1.4, “Moving or Copying InnoDB Tables”.
The default row format of
an InnoDB
table is
Compact
. Although this row format is fine for
basic experimentation, consider using the
Dynamic
or
Compressed
row format to take advantage of InnoDB
features such as table compression and efficient off-page
storage of long column values. Using these row formats requires
that innodb_file_per_table
is
enabled (the default as of MySQL 5.6.6) and that
innodb_file_format
is set to
Barracuda:
SET GLOBAL innodb_file_per_table=1; SET GLOBAL innodb_file_format=barracuda; CREATE TABLE t3 (a INT, b CHAR (20), PRIMARY KEY (a)) ROW_FORMAT=DYNAMIC; CREATE TABLE t4 (a INT, b CHAR (20), PRIMARY KEY (a)) ROW_FORMAT=COMPRESSED;
For more information about InnoDB
row
formats, see Section 14.11, “InnoDB Row Formats”. For how to
determine the row format of an InnoDB
table
and the physical characteristics of InnoDB
row formats, see Section 14.11, “InnoDB Row Formats”.
Always define a primary
key for an InnoDB
table, specifying
the column or columns that:
Are referenced by the most important queries.
Are never left blank.
Never have duplicate values.
Rarely if ever change value once inserted.
For example, in a table containing information about people, you
would not create a primary key on (firstname,
lastname)
because more than one person can have the
same name, some people have blank last names, and sometimes
people change their names. With so many constraints, often there
is not an obvious set of columns to use as a primary key, so you
create a new column with a numeric ID to serve as all or part of
the primary key. You can declare an
auto-increment column
so that ascending values are filled in automatically as rows are
inserted:
# The value of ID can act like a pointer between related items in different tables. CREATE TABLE t5 (id INT AUTO_INCREMENT, b CHAR (20), PRIMARY KEY (id)); # The primary key can consist of more than one column. Any autoinc column must come first. CREATE TABLE t6 (id INT AUTO_INCREMENT, a INT, b CHAR (20), PRIMARY KEY (id,a));
Although the table works correctly without defining a primary
key, the primary key is involved with many aspects of
performance and is a crucial design aspect for any large or
frequently used table. It is recommended that you always specify
a primary key in the CREATE TABLE
statement. If you create the table, load data, and then run
ALTER TABLE
to add a primary key
later, that operation is much slower than defining the primary
key when creating the table.
To view the properties of an InnoDB
table,
issue a SHOW TABLE STATUS
statement:
mysql> SHOW TABLE STATUS FROM test LIKE 't%' \G;
*************************** 1. row ***************************
Name: t1
Engine: InnoDB
Version: 10
Row_format: Compact
Rows: 0
Avg_row_length: 0
Data_length: 16384
Max_data_length: 0
Index_length: 0
Data_free: 0
Auto_increment: NULL
Create_time: 2015-03-16 16:26:52
Update_time: NULL
Check_time: NULL
Collation: latin1_swedish_ci
Checksum: NULL
Create_options:
Comment:
1 row in set (0.00 sec)
For information about SHOW TABLE
STATUS
output, see
Section 13.7.5.37, “SHOW TABLE STATUS Statement”.
InnoDB
table properties may also be queried
using the InnoDB
Information Schema system
tables:
mysql> SELECT * FROM INFORMATION_SCHEMA.INNODB_SYS_TABLES WHERE NAME='test/t1' \G
*************************** 1. row ***************************
TABLE_ID: 42
NAME: test/t1
FLAG: 1
N_COLS: 5
SPACE: 24
FILE_FORMAT: Antelope
ROW_FORMAT: Compact
ZIP_PAGE_SIZE: 0
1 row in set (0.02 sec)
For more information, see Section 14.15.3, “InnoDB INFORMATION_SCHEMA System Tables”.