read/CreateInnoDBTables

15.6.1.1 Creating InnoDB Tables

To create an InnoDB table, use the CREATE TABLE statement.

1	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.

如果你计划使用 mysqldump/复制将 CREATE TABLE 语句复现在另外一个默认存储引擎不是 InnoDB 的服务器上, 就坚持使用 ENGINE=InnoDB 子句 (不过我看 mysqldump 出的语句中会显式指定存储引擎类型)

An 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 15.6.3.3, “General Tablespaces”.

InnoDB 表及它的索引可以创建在 system tablespace, file-per-table tablespace 或 general tablespace. 当 innodb_file_per_table 启用, InnoDB 表默认创建在单独的 file-per-rable 表空间中, 相反, 则创建在 system tablespace 中.

使用 CREATE TABLE … TABLESPACE 语法可以创建在 general tablespace 中

When you create a table in a file-per-table tablespace, MySQL creates an .ibd tablespace file in a database directory under the MySQL data 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 15.6.3.3, “General Tablespaces”.

当你创建一个在 file-per-table 表空间中的表时, MySQL 在主目录下的单个数据库目录中创建 .idb 文件

在 InnoDB system 表空间中则创建在一个在 MySQL 主目录下已存在的 ibdata 文件中(PS. 那么 ibdata 文件是什么时候创建的? 如何增长?)

Internally, InnoDB adds an entry for each table to the 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.

InnoDB Tables and Row Formats

The default row format for InnoDB tables is defined by the innodb_default_row_format configuration option, which has a default value of DYNAMIC. Dynamic andCompressed 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).

InnoDB 表的行格式化由 innodb_default_row_format 定义, 默认是 DYNAMIC

Dynamic 和 Compressed 行格式化提供了 InnoDB 高级特性, 比如压缩表, 高效的长列值页外存储

使用这些行格式化, 必须启用 innodb_file_per_table

1
2
3

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;

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 15.6.3.3, “General Tablespaces”.

另外, 你可以使用 CREATE TABLE … TABLESAPCE 语法在 general tablespace 创建 InnoDB 表. general tablespace 支持所有的行格式化

1	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 Compact orRedundant row format.

CREATE TABLE … TABLESPACE 语法也可以被用作在 system tablespace 中创建 Dynamic 格式化的表, 以及 Compacr 或 Redundant 格式化

1	CREATE TABLE t1 (c1 INT PRIMARY KEY) TABLESPACE = innodb_system ROW_FORMAT=DYNAMIC;

For more information about InnoDB row formats, see Section 15.10, “InnoDB Row Formats”. For how to determine the row format of an InnoDB table and the physical characteristics of InnoDB row formats, see Section 15.10, “InnoDB Row Formats”.

InnoDB Tables and Primary Keys

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.

通常会为 InnoDB 表定义一个主键, 说明列具有: 被最重要的查询引用, 永远不为空, 永远不重复, 一旦插入, 则很少改动

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:

比如, 在一个包含民众信息的表中, 不能创建以 (firstname, lastname) 组合的值为主键, 因为名字很可能重复, 一些人没有 lastname, 一些人会改变他们的名字.

涉及到这么多的限制, 进程没有一个明显列的集合能作为主键. 所以可以创建一个新的数字列用作整个或部分主键, 声明为 auto-increament 可以使行在插入时自增

# 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.

尽管没有定义主键表也可以正常运行, 但是主键包含了许多性能方面和重要的为大容量或频繁使用表做的设计, 建议你总是在表中指定一个主键.

在创建和加载表之后使用 ALTER TABLE 语句增加主键, 速度会远慢于一开始创建时指定主键

Viewing InnoDB Table Properties

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: utf8mb4_0900_ai_ci
       Checksum: NULL
 Create_options:
        Comment:

For information about SHOW TABLE STATUS output, see Section 13.7.6.36, “SHOW TABLE STATUS Syntax”.

InnoDB table properties may also be queried using the InnoDB Information Schema system tables:

mysql> SELECT * FROM INFORMATION_SCHEMA.INNODB_TABLES WHERE 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

For more information, see Section 15.14.3, “InnoDB INFORMATION_SCHEMA Schema Object Tables”.

15.6.1.1 Creating InnoDB Tables

InnoDB Tables and Row Formats

InnoDB Tables and Primary Keys

Viewing InnoDB Table Properties