14.6.1.1 创建 InnoDBtable
要创建InnoDB
table,请使用CREATE TABLE语句。
CREATE TABLE t1 (a INT, b CHAR (20), PRIMARY KEY (a)) ENGINE=InnoDB;
如果将InnoDB
定义为默认存储引擎(默认情况下为默认),则无需指定ENGINE=InnoDB
子句。要检查默认存储引擎,请发出以下语句:
mysql> SELECT @@default_storage_engine;
+--------------------------+
| @@default_storage_engine |
+--------------------------+
| InnoDB |
+--------------------------+
如果计划使用mysqldump或复制在默认存储引擎不是InnoDB
的服务器上重播CREATE TABLE语句,则可能仍使用ENGINE=InnoDB
子句。
可以在system tablespace,file-per-tabletable 空间或general tablespace中创建InnoDB
table 及其索引。启用innodb_file_per_table(这是默认设置)后,将在每个 table 每个文件 table 空间中隐式创建InnoDB
table。相反,禁用innodb_file_per_table时,会在InnoDB
系统 table 空间中隐式创建InnoDB
table。要在常规 table 空间中创建 table,请使用创建 table...table 空间语法。有关更多信息,请参见第 14.6.3.3 节“常规 table 空间”。
创建InnoDB
table 时,MySQL 在 MySQL 数据目录下的数据库目录中创建.frm file。有关.frm
文件的更多信息,请参见InnoDBtable 和.frm 文件。对于在每个 table 文件 table 空间中创建的 table,默认情况下,MySQL 还在数据库目录中创建一个.ibdtable 空间文件。在InnoDB
系统 table 空间中创建的 table 是在现有ibdata file中创建的,该 table 位于 MySQL 数据目录中。在常规 table 空间.ibd file中创建在常规 table 空间中创建的 table。常规 table 空间文件可以在 MySQL 数据目录内部或外部创建。有关更多信息,请参见第 14.6.3.3 节“常规 table 空间”。
在内部,InnoDB
将每个 table 的条目添加到InnoDB
数据字典中。该条目包括数据库名称。例如,如果在test
数据库中创建了 tablet1
,则数据库名称的数据字典条目为'test/t1'
。这意味着您可以在不同的数据库中创建一个具有相同名称(t1
)的 table,并且该 table 名不会在InnoDB
内部冲突。
InnoDBtable 和.frm 文件
MySQL 在数据库目录的.frm files中存储 table 的数据字典信息。与其他 MySQL 存储引擎不同,InnoDB
还将有关 table 的信息编码在系统 table 空间内的自身内部数据字典中。当 MySQL 删除 table 或数据库时,它将删除一个或多个.frm
文件以及InnoDB
数据字典中的相应条目。您不能仅通过移动.frm
文件在数据库之间移动InnoDB
table。有关移动InnoDB
table 的信息,请参见第 14.6.1.4 节“移动或复制 InnoDBtable”。
InnoDBtable 和行格式
InnoDB
table 的默认行格式由innodb_default_row_format配置选项定义,该选项的默认值为DYNAMIC
。 Dynamic
和Compressed
行格式允许您利用InnoDB
功能,例如 table 压缩和长列值的有效页外存储。要使用这些行格式,必须启用innodb_file_per_table(MySQL 5.6.6 的默认设置),并且innodb_file_format必须设置为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;
或者,您可以使用创建 table...table 空间语法在常规 table 空间中创建InnoDB
table。常规 table 空间支持所有行格式。有关更多信息,请参见第 14.6.3.3 节“常规 table 空间”。
CREATE TABLE t1 (c1 INT PRIMARY KEY) TABLESPACE ts1 ROW_FORMAT=DYNAMIC;
创建 table...table 空间语法还可以用于在系统 table 空间中创建Dynamic
行格式的InnoDB
table,以及Compact
或Redundant
行格式的 table。
CREATE TABLE t1 (c1 INT PRIMARY KEY) TABLESPACE = innodb_system ROW_FORMAT=DYNAMIC;
有关InnoDB
行格式的更多信息,请参见第 14.11 节“ InnoDB 行格式”。有关如何确定InnoDB
table 的行格式和InnoDB
行格式的物理特性的信息,请参见第 14.11 节“ InnoDB 行格式”。
InnoDBtable 和主键
始终为InnoDB
table 定义primary key,并指定一个或多个列:
-
被最重要的查询引用。
-
永远不会空白。
-
永远不要有重复的值。
-
一旦插入,就很少更改值。
例如,在包含有关人的信息的 table 中,您不会在(firstname, lastname)
上创建主键,因为一个以上的人可以具有相同的名称,某些人的姓氏为空,有时人们会更改其名称。有这么多的约束,通常没有明显的列集可以用作主键,因此您需要创建一个新的具有数字 ID 的列,以用作全部或部分主键。您可以声明auto-increment列,以便在插入行时自动填写升序值:
# 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));
尽管该 table 无需定义主键即可正常工作,但是主键涉及性能的许多方面,并且对于任何大型或经常使用的 table 而言都是至关重要的设计方面。建议您始终在CREATE TABLE语句中指定主键。如果创建 table,加载数据,然后稍后运行ALTER TABLE以添加主键,则此操作比创建 table 时定义主键要慢得多。
查看 InnoDBtable 属性
要查看InnoDB
table 的属性,请发出显示 table 格状态语句:
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)
有关显示 table 格状态输出的信息,请参见第 13.7.5.36 节“ SHOW TABLE STATUS 语句”。
也可以使用InnoDB
Information Schema 系统 table 查询InnoDB
table 属性:
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)