18.104.22.168 Creating InnoDB Tables
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 | +--------------------------+
InnoDB table and its indexes can be created in the system tablespace, in a file-per-table tablespace, or in a general tablespace. When
innodb_file_per_table is enabled, which is the default, 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. To create a table in a general tablespace, use
CREATE TABLE ... TABLESPACE syntax. For more information, see Section 22.214.171.124, “General Tablespaces”.
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. A table created in a general tablespace is created in an existing general tablespace .ibd file. General tablespace files can be created inside or outside of the MySQL data directory. For more information, see Section 126.96.36.199, “General Tablespaces”.
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
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 188.8.131.52, “Moving or Copying InnoDB Tables”.
The default row format for
InnoDB tables is defined by the
innodb_default_row_format configuration option, which has a default value of
Compressed row format allow you to take advantage of
InnoDB features such as table compression and efficient off-page storage of long column values. To use these row formats,
innodb_file_per_table must be enabled (the default as of MySQL 5.6.6) and
innodb_file_format must be 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;
Alternatively, you can use
CREATE TABLE ... TABLESPACE syntax to create an
InnoDB table in a general tablespace. General tablespaces support all row formats. For more information, see Section 184.108.40.206, “General Tablespaces”.
CREATE TABLE t1 (c1 INT PRIMARY KEY) TABLESPACE ts1 ROW_FORMAT=DYNAMIC;
CREATE TABLE ... TABLESPACE syntax can also be used to create
InnoDB tables with a
Dynamic row format in the system tablespace, alongside tables with a
Redundant row format.
CREATE TABLE t1 (c1 INT PRIMARY KEY) TABLESPACE = innodb_system ROW_FORMAT=DYNAMIC;
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 15:13:31 Update_time: NULL Check_time: NULL Collation: latin1_swedish_ci Checksum: NULL Create_options: Comment: 1 row in set (0.00 sec)
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: 45 NAME: test/t1 FLAG: 1 N_COLS: 5 SPACE: 35 FILE_FORMAT: Antelope ROW_FORMAT: Compact ZIP_PAGE_SIZE: 0 SPACE_TYPE: Single 1 row in set (0.00 sec)
For more information, see Section 14.16.3, “InnoDB INFORMATION_SCHEMA System Tables”.