创建InnoDB表
要创建InnoDB
表,请使用以下CREATE TABLE
语句。
CREATE TABLE t1 (a INT, b CHAR (20),PRIMARY KEY (a))ENGINE =InnoDB;
如果将ENGINE=InnoDB
子句InnoDB
定义为默认存储引擎(默认情况下为默认引擎),则无需指定该子句。要检查默认存储引擎,请发出以下语句:
mysql>SELECT @@default_storage_engine; +-------------------------- + | @@default_storage_engine | +-------------------------- + | InnoDB | +-------------------------- +
ENGINE=InnoDB
如果计划使用mysqldump或复制CREATE TABLE
在没有默认存储引擎的服务器上重播该语句,则可能仍使用子句InnoDB
。
一个InnoDB
表及其索引可以在创建系统表空间,在一个文件中,每个表的表空间,或在一般的表空间。当innodb_file_per_table
启用,这是默认的,一InnoDB
台是隐含在一个单独的文件,每个表的表空间中创建。相反,如果innodb_file_per_table
禁用该功能,则会InnoDB
在InnoDB
系统表空间中隐式创建一个表。要在常规表空间中创建表,请使用CREATE TABLE ... TABLESPACE
语法。有关更多信息,请参见“通用表空间”。
在每个表文件表空间中创建表时,默认情况下,MySQL在MySQL数据目录下的数据库目录中创建.ibd表空间文件。在InnoDB
系统表空间中创建的表是在现有ibdata文件中创建的,该文件位于MySQL数据目录中。在常规表空间中创建的表在现有的常规表空间.ibd文件中创建。常规表空间文件可以在MySQL数据目录内部或外部创建。有关更多信息,请参见“通用表空间”。
在内部,InnoDB
将每个表的条目添加到数据字典。该条目包括数据库名称。例如,如果t1
在test
数据库中创建了 table ,则数据库名称的数据字典条目为'test/t1'
。这意味着您可以t1
在不同的数据库中创建一个具有相同名称()的表,并且该表名不会在内碰撞InnoDB
。
InnoDB表和行格式
InnoDB
表的默认行格式由innodb_default_row_format
配置选项定义,该选项的默认值为DYNAMIC
。Dynamic
和Compressed
行格式允许你利用的InnoDB
功能,如表压缩和长列的值的高效关闭页的存储。要使用这些行格式,innodb_file_per_table
必须启用(默认)。
SET GLOBAL innodb_file_per_table=1;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 ;
或者,您可以使用CREATE TABLE ... TABLESPACE
语法InnoDB
在常规表空间中创建表。常规表空间支持所有行格式。有关更多信息,请参见“通用表空间”。
CREATE TABLE t1 (c1 INTPRIMARY KEY )TABLESPACE ts1ROW_FORMAT =DYNAMIC ;
CREATE TABLE ... TABLESPACE
语法还可以用于在系统表空间中创建InnoDB
具有Dynamic
行格式的表,以及具有Compact
或Redundant
行格式的表。
CREATE TABLE t1 (c1 INTPRIMARY KEY )TABLESPACE = innodb_systemROW_FORMAT =DYNAMIC ;
有关InnoDB
行格式的更多信息,请参见“ InnoDB行格式”。有关如何确定InnoDB
表的行格式以及行格式的物理特征InnoDB
,请参见“ InnoDB行格式”。
InnoDB表和主键
始终为表定义主键InnoDB
,并指定一个或多个满足以下条件的列:
- 被最重要的查询引用。
- 永远不会空白。
- 永远不要有重复的值。
- 一旦插入,就很少更改值。
例如,在包含有关人员的信息的表中,您将不会创建主键,(firstname, lastname)
因为一个以上的人员可以具有相同的名称,某些人员的姓氏为空,有时人们会更改其名称。有这么多的约束,通常没有明显的列集可以用作主键,因此您需要创建一个新的具有数字ID的列,以用作全部或部分主键。您可以声明一个自动增量列,以便在插入行时自动填写升序值:
# The value of ID can act like a pointer between related items in different tables.CREATE TABLE t5 (id INTAUTO_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 INTAUTO_INCREMENT , a INT, b CHAR (20),PRIMARY KEY (id,a));
尽管表没有定义主键即可正常工作,但主键涉及性能的许多方面,并且对于任何大型或经常使用的表都是至关重要的设计方面。建议您始终在CREATE TABLE
语句中指定主键。如果创建表,加载数据,然后稍后运行ALTER TABLE
以添加主键,则该操作比创建表时定义主键要慢得多。
参见InnoDB表属性
要参见InnoDB
表的属性,请发出以下SHOW TABLE STATUS
语句:
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 : utf8mb4_0900_ai_ci Checksum : NULL Create_options : Comment :
有关SHOW TABLE STATUS
输出的信息,请参见“ SHOW TABLE STATUS语句”。
InnoDB
还可以使用InnoDB
Information Schema系统表查询表属性:
mysql>SELECT *FROM INFORMATION_SCHEMA.INNODB_TABLESWHERE NAME ='test/t1' \G *************************** 1. row *************************** TABLE_ID : 45 NAME : test/t1 FLAG : 1 N_COLS : 5 SPACE : 35 ROW_FORMAT : Compact ZIP_PAGE_SIZE : 0 SPACE_TYPE : Single
有关更多信息,请参见“ InnoDB INFORMATION_SCHEMA架构对象表”。