mysql普通键描述_MySQL的SQL语句 - 数据定义语句（14）- CREATE TABLE 语句 (3)

表选项

表选项用于优化表的行为。在大多数情况下，不必指定这些选项。除非另有说明，否则这些选项适用于所有存储引擎。不适用于给定存储引擎的选项也可以作为表定义的一部分。如果以后使用 ALTER TABLE 将表转换为使用其他存储引擎，则应用这些选项。

● ENGINE

使用下表中显示的名称指定表的存储引擎。引擎名称可以不加引号或用引号引起来。可以识别名称 'DEFAULT' 但会忽略它。

存储引擎

描述

InnoDB

具有行锁定和外键的事务安全表。是新表的默认存储引擎。

MyISAM

二进制便携式存储引擎，主要用于只读或以读为主的工作负载。

MEMORY

此存储引擎的数据仅存储在内存中。

CSV

表以逗号分隔值的格式存储行数据。

ARCHIVE

归档存储引擎。

EXAMPLE

示例引擎。

FEDERATED

访问远程表的存储引擎。

HEAP

这是 MEMORY 的同义词。

MERGE

当做一个表使用的 MyISAM 表的集合。也被称为 MRG_MyISAM。

NDB

集群、容错、基于内存的表，支持事务和外键。也称为 NDBCLUSTER。

默认情况下，如果指定的存储引擎不可用，则该语句将失败并报错。可以通过从服务器 SQL 模式中删除 NO_ENGINE_SUBSTITUTION 来覆盖此行为，这样 MySQL 允许使用默认存储引擎替换指定的引擎。通常在这种情况下，默认引擎是 InnoDB，这是 default_storage_engine 系统变量的默认值。当禁用 NO_ENGINE_SUBSTITUTION，则如果未接受存储引擎，则会出现警告。

● AUTO_INCREMENT

表的初始 AUTO_INCREMENT 值。在MySQL 8.0中，这适用于 MyISAM、MEMORY、InnoDB 和 ARCHIVE 存储引擎表。要为不支持 AUTO_INCREMENT 表选项的引擎设置第一个自动增量值，请在创建表后插入一个比所需值小1的“虚拟”行，然后删除该虚拟行。

对于在 CREATE TABLE 语句中支持 AUTO_INCREMENT 表选项的引擎，还可以使用 ALTER TABLE tbl_name AUTO_INCREMENT = N 重置 AUTO_INCREMENT 值。不能将该值设置为低于列中当前的最大值。

● AVG_ROW_LENGTH

表平均行长度的近似值。只需要对行大小可变的大型表进行设置。

在创建MyISAM表时，MySQL使用 MAX_ROWS 和 AVG_ROW_LENGTH 选项的乘积来决定生成的表有多大。如果不指定这两个选项，默认情况下 MyISAM 数据和索引文件的最大大小为256TB。(如果您的操作系统不支持这么大的文件，那么表的大小将受到文件大小的限制。)如果希望降低指针大小以使索引更小更快，并且您实际上不需要大文件，那么您可以通过设置 myisam_data_pointer_size 系统变量来减小默认指针大小。如果希望所有表都能超过默认限制，并且可以接受表稍微慢一些，大一些，可以通过设置这个变量来增加默认指针大小。将该值设置为7可以允许表的大小最多为65,536TB。

● [DEFAULT] CHARACTER SET

指定表的默认字符集。CHARSET 是 CHARACTER SET 的同义词。如果字符集名称为 DEFAULT，则使用数据库字符集。

● CHECKSUM

如果希望MySQL为所有行维护一个实时校验和(也就是说，当表发生变化时，MySQL会自动更新一个校验和)，请将此设置为1。这使得表的更新速度稍慢，但也更容易找到损坏的表。CHECKSUM TABLE 语句报告校验和。(仅限MyISAM)

● [DEFAULT] COLLATE

指定表的默认排序规则。

● COMMENT

表的注释，最长2048个字符。

可以使用 table_option COMMENT 子句设置表的 InnoDB MERGE_THRESHOLD 值。

设置 NDB_TABLE 选项。创建NDB表的 CREATE TABLE 语句中的表注释或更改NDB表的 CREATE TABLE 语句也可以用于指定一到四个 NDB_TABLE 选项 NOLOGGING、READ_BACKUP、PARTITION_BALANCE 或 FULLY_REPLICATED 作为一组名称-值对，如果需要，用逗号分隔，紧跟在字符串 NDB_TABLE= 之后，作为引号内注释文本的开始。使用此语法的示例语句如下所示(强调的文本)：

1. CREATE TABLE t1 (

2. c1 INT NOT NULL AUTO_INCREMENT PRIMARY KEY,

3. c2 VARCHAR(100),

4. c3 VARCHAR(100) )

5. ENGINE=NDB

6.COMMENT="NDB_TABLE=READ_BACKUP=0,PARTITION_BALANCE=FOR_RP_BY_NODE";

带引号的字符串中不允许有空格。字符串不区分大小写。

注释显示为 SHOW CREATE TABLE 输出的一部分。注释的文本也从 MySQL information_schema.tables 表的 TABLE_COMMENT 列获取。

NDB 表的 ALTER TABLE 语句也支持这种注释语法。请记住，与 ALTER TABLE 一起使用的表注释将替换表中可能已经存在的任何现有注释。

NDB 表不支持在表注释中设置 MERGE_THRESHOLD 选项(设置会被忽略)。

● COMPRESSION

用于 InnoDB 表页级压缩的压缩算法。支持的值包括 Zlib、LZ4 和 None。COMPRESSION 属性是通过透明页压缩特性引入的。页压缩只支持独立文件表空间的 InnoDB 表，并且仅在支持稀疏文件和文件打洞的 Linux 和 Windows 平台上可用。

● CONNECTION

FEDERATED 表的连接字符串。

● DATA DIRECTORY, INDEX DIRECTORY

对于InnoDB，DATA DIRECTORY='DIRECTORY' 子句允许在数据目录之外创建表。要使用 DATA DIRECTORY 子句，必须启用 innodb_file_per_table 变量。必须指定完整目录路径。从 MySQL8.0.21 开始，指定的目录必须为 InnoDB 所知。

创建 MyISAM 表时，可以使用 DATA DIRECTORY='directory' 子句和 INDEX DIRECTORY='directory' 子句。它们分别指定 MyISAM 表的数据文件和索引文件的放置位置。与 InnoDB 表不同，MySQL 在创建带有 DATA DIRECTORY 或 INDEX DIRECTORY 选项的 MyISAM 表时，不会创建与数据库名称对应的子目录。在指定的目录中创建文件。

必须具有 FILE 权限才能使用 DATA DIRECTORY 或 INDEX DIRECTORY 表选项。

重要

对于分区表，忽略表级 DATA DIRECTORY 和 INDEX DIRECTORY 选项。

只有在不使用 --skip-symbolic-links 选项时，这些选项才起作用。您的操作系统还必须有一个线程安全的 realpath() 调用。

如果在创建 MyISAM 表时没有使用 DATA DIRECTORY 选项，则会在数据库目录中创建 .MYD 文件。默认情况下，如果 MyISAM 在这种情况下找到一个存在的 .MYD 文件，会覆盖它。对于没有 INDEX DIRECTORY 选项创建的表，.MYI 文件也是如此。要禁止此行为，请使用 --keep_files_on_create 选项启动服务器，在这种情况下，MyISAM 不会覆盖现有文件，而是返回一个错误。

如果使用 DATA DIRECTORY 或 INDEX DIRECTORY 选项创建 MyISAM 表时发现存在 .MYD 或 .MYI 文件，则 MyISAM 始终返回错误。它不会覆盖指定目录中的文件。

重要

不能将包含 MySQL 数据目录的路径名用于 DATA DIRECTORY 或 INDEX DIRECTORY。这包括分区表和单独的表分区。

● DELAY_KEY_WRITE

如果要将表的键更新延迟到表关闭，请将此值设置为1。(仅限MyISAM)

● ENCRYPTION

ENCRYPTION 子句为 InnoDB 表启用或禁用页级数据加密。在启用加密之前，必须安装和配置 keyring 插件。在 MySQL8.0.16 之前，只有在独立表空间中创建表时才能指定 ENCRYPTION 子句。从 MySQL 8.0.16 开始，在通用表空间中创建表时也可以指定 ENCRYPTION 子句。

从 MySQL 8.0.16 开始，如果没有指定 ENCRYPTION 子句，表将继承默认的模式加密。如果启用 table_encryption_privilege_check 变量，使用 ENCRYPTION 子句创建具有不同于默认模式加密设置的表时，则需要 TABLE_ENCRYPTION_ADMIN 权限。在通用表空间中创建表时，表和表空间加密必须匹配。

从 MySQL 8.0.16 开始，当使用不支持加密的存储引擎时，不允许使用除了 'N' 和 '' 值之外的其他值指定 ENCRYPTION 子句。此前的版本允许。

● ENGINE_ATTRIBUTE 和 SECONDARY_ENGINE_ATTRIBUTE 选项(从MySQL 8.0.21开始提供)用于指定主存储引擎和辅助存储引擎的表属性。这些选项保留供将来使用。

允许的值是包含有效JSON文档的字符串文本或空字符串('')。拒绝无效的JSON。

CREATE TABLE t1 (c1 INT) ENGINE_ATTRIBUTE='{"key":"value"}';

ENGINE_ATTRIBUTE 和 SECONDARY_ENGINE_ATTRIBUTE 值可以重复。在这种情况下，使用最后指定的值。

服务器不会检查 ENGINE_ATTRIBUTE 和 SECONDARY_ENGINE_ATTRIBUTE 值，也不会在更改表的存储引擎时清除它们。

● INSERT_METHOD

如果要在 MERGE 表中插入数据，则必须使用 INSERT_METHOD 指定要插入数据的表。INSERT_METHOD 是一个仅对 MERGE 表有用的选项。使用 FIRST 或 LAST 值可使插入操作转到第一个或最后一个表，或使用值 NO 阻止插入。

● KEY_BLOCK_SIZE

对于 MyISAM 表，KEY_BLOCK_SIZE 是可选项，用于指定索引键块的大小(以字节为单位)。该值被视为提示；如果需要，可以使用不同的大小。为单个索引定义指定的 KEY_BLOCK_SIZE 值将覆盖表级 KEY_BLOCK_SIZE 值。

对于 InnoDB 表，KEY_BLOCK_SIZE 指定用于压缩 InnoDB 表的页大小(以千字节为单位)。KEY_BLOCK_SIZE 值被视为一个提示；如果需要，InnoDB 可以使用不同的大小。KEY_BLOCK_SIZE 只能小于或等于 innodb_page_size 值。值0表示默认的压缩页面大小，它是 innodb_page_size 值的一半。根据 innodb_page_size，可能的 KEY_BLOCK_SIZE 值包括0、1、2、4、8和16。

Oracle 推荐在为 InnoDB 表指定 KEY_BLOCK_SIZE 时启用 innodb_strict_mode。启用 innodb_strict_mode 时，指定无效的 KEY_BLOCK_SIZE 值将返回错误。如果禁用 innodb_strict_mode，则无效的 KEY_BLOCK_SIZE 值会导致警告，并忽略 KEY_BLOCK_SIZE 选项。

SHOW TABLE STATUS 语句返回的 Create_options 列报告表使用的实际 KEY_BLOCK_SIZE 值，SHOW CREATE TABLE 也一样。

InnoDB 只支持表级别的 KEY_BLOCK_SIZE。

innodb_page_size 值为 32KB 和 64KB 时，不支持 KEY_BLOCK_SIZE。InnoDB 表压缩不支持这些页大小。

InnoDB 在创建临时表时不支持 KEY_BLOCK_SIZE 选项。

● MAX_ROWS

计划存储在表中的最大行数。这不是硬限制，而是向存储引擎提示表必须至少能够存储这么多行。

重要

不推荐 NDB 表使用 MAX_ROWS 来控制表分区的数量。为了向后兼容，它在以后的版本中仍然受支持，但在将来的版本中可能会被删除。请改用 PARTITION_BALANCE。

NDB 存储引擎将此值视为最大值。如果计划创建非常大的NDB集群表(包含数百万行)，则应设置 MAX_ROWS = 2 * rows(其中 rows 是希望插入表中的行数)，确保 NDB 在用于存储表主键哈希的哈希表中分配足够数量的索引槽。

MAX_ROWS 的最大值为 4294967295；更大的值将被截断到该限制。

● MIN_ROWS

计划存储在表中的最小行数。MEMORY 存储引擎使用此选项作为内存使用的提示。

● PACK_KEYS

仅对MyISAM表有效。如果要有较小的索引，请将此选项设置为1。这通常会使更新速度变慢，读取速度更快。将选项设置为0将禁用所有索引压缩。将其设置为 DEFAULT 会告诉存储引擎只压缩长的 CHAR、VARCHAR、BINARY 或 VARBINARY 列。

如果不使用 PACK_KEYS，默认值是压缩字符串，而不是数字。如果使用 PACK_KEYS=1，则数字也会压缩。

在压缩二进制数字键时，MySQL使用前缀压缩：

■ 每个键需要一个额外的字节来指示前一个键有多少字节与下一个键的字节相同。

■ 行指针直接以高字节优先顺序存储在键之后，以提高压缩效果。

这意味着，如果在两个连续的行上有多个相等的键，那么后面的所有“相同”键通常只占用两个字节(包括指向该行的指针)。与普通情况相比，下面的键使用 storage_size_for_key + pointer_size(其中指针大小通常为4)。相反，只有当有许多相同的数字时，前缀压缩才有显著的好处。如果所有键都完全不同，键不是可以有 NULL 值的键(在这种情况下，压缩键长度存储在用于标记键是否为空的同一字节中。)，则每个键多使用一个字节。

● PASSWORD

此选项未使用。

● ROW_FORMAT

定义存储行的物理格式。

在禁用严格模式的情况下创建表时，如果不支持指定的行格式，则使用存储引擎的默认行格式。表的实际行格式在 SHOW TABLE STATUS 语句查询结果的 Row_format 列中可以查到。Create_options 列显示在 CREATE TABLE 语句中指定的行格式，SHOW CREATE TABLE 也一样。

行格式的选择因表使用的存储引擎而异。

对于InnoDB表：

■ 默认行格式由 innodb_default_row_format 定义，默认设置为 DYNAMIC。当未定义 ROW_FORMAT 选项或 ROW_FORMAT=DEFAULT 时，将使用默认行格式。

如果未定义 ROW_FORMAT 选项，或者 ROW_FORMAT=DEFAULT，重建表的操作也会自动将表的行格式更改为 innodb_default_row_format 定义的默认行格式。

■ 为了 InnoDB 更有效地存储数据类型，尤其是 BLOB 类型，请使用 DYNAMIC。

■ 要为InnoDB表启用压缩，请指定 ROW_FORMAT=COMPRESSED。创建临时表时不支持 ROW_FORMAT=COMPRESSED 选项。

■ 老版本的MySQL中使用的行格式仍然可以指定为 REDUNDANT。

■ 当指定一个非默认的 ROW_FORMAT 子句时，请考虑同时启用 innodb_strict_mode 配置选项。

■ 不支持 ROW_FORMAT=FIXED。如果在禁用 innodb_strict_mode 时指定了 ROW_FORMAT=FIXED，InnoDB 会发出警告并假定 ROW_FORMAT=DYNAMIC。如果在启用 innodb_strict_mode 的同时指定了 ROW_FORMAT=FIXED，则 InnoDB 返回错误。

对于MyISAM表，对于静态或可变长度的行格式，选项值可以是 FIXED 或 DYNAMIC。myisampack 将类型设置为 COMPRESSED。

对于 NDB 表，默认的 ROW_FORMAT 是 DYNAMIC。

● STATS_AUTO_RECALC

指定是否自动重新计算 InnoDB 表的持久统计信息。DEFAULT 值导致表的持久统计设置由 innodb_stats_auto_recalc 配置选项确定。值为1时，当表中10%的数据发生更改时，将重新计算统计信息。值为0，将阻止此表的自动重新计算；使用此设置，在对表进行重大变更后，再使用 ANALYZE TABLE 语句重新计算统计信息。

● STATS_PERSISTENT

指定是否为 InnoDB 表启用持久统计信息。DEFAULT 值导致表的持久统计设置由 innodb_stats_persistent 配置选项决定。值为1启用表的持久统计信息，而值0关闭此功能。通过 CREATE TABLE 或 ALTER TABLE 语句启用持久统计信息后，在将数据加载到表中之后，执行 ANALYZE TABLE 语句来计算统计信息。

● STATS_SAMPLE_PAGES

在估计索引列的基数和其他统计信息(如由 ANALYZE TABLE 计算的统计信息)时要采样的索引页数。

● TABLESPACE

TABLESPACE 子句可用于在现有的通用表空间、独立表空间或系统表空间中创建一个表。

1. CREATE TABLE tbl_name ... TABLESPACE [=] tablespace_name

在使用 TABLESPACE 子句之前，指定的通用表空间必须存在。

tablespacename 是区分大小写的标识符。它可以用引号引用，也可以不用引号。不允许使用斜杠字符(“/”)。以“innodb”开头的名称保留以供特殊使用。

要在系统表空间中创建表，请指定 innodb_system 作为表空间名称。

1. CREATE TABLE tbl_name ... TABLESPACE [=] innodb_system

使用 TABLESPACE [=] innodb_system，可以在系统表空间中放置任何未压缩行格式的表，而不管 innodb_file_per_table 设置如何。例如，可以使用 TABLESPACE [=] innodb_system 将带有 ROW_FORMAT=DYNAMIC 设置的表添加到系统表空间中。

要在独立表空间中创建一个表，请指定 innodb_file_per_table 作为表空间名称。

1. CREATE TABLE tbl_name ... TABLESPACE [=] innodb_file_per_table

注意

如果启用了 innodb_file_per_table，则无需指定 TABLESPACE=innodb_file_per_table 来创建 InnoDB 独立表空间。当启用 innodb_file_per_table 时，默认情况下在 file-per-table 表空间中创建 InnoDB 表。

DATA DIRECTORY 子句允许与 CREATE TABLE ... TABLESPACE=innodb_file_per_table 一起使用，但不支持与 TABLESPACE 子句结合使用。从MySQL 8.0.21开始，DATA DIRECTORY 子句中指定的目录必须对 InnoDB 是已知的。

注意

从MySQL 8.0.13开始，不推荐 CREATE TEMPORARY TABLE 语句使用 TABLESPACE = innodb_file_per_table 和 TABLESPACE = innodb_temporary 子句，这些都将在MySQL的未来版本中删除。

STORAGE 表选项仅用于NDB表。STORAGE 确定所使用的存储类型(磁盘或内存)，可以是 DISK 或 MEMORY。

TABLESPACE ... STORAGE DISK 将表分配给NDB群集磁盘数据表空间。表空间必须已经使用 CREATE TABLESPACE 语句创建。

重要

在没有 TABLESPACE 子句的 CREATE TABLE 语句中不能使用 STORAGE 子句。

● UNION

用于访问相同MyISAM表的集合，就像访问一个表一样。这只适用于 MERGE 表。

对于映射到 MERGE 表的表，必须具有 SELECT、UPDATE 和 DELETE 权限。

注意

以前，所有使用的表必须与 MERGE 表本身在同一个数据库中。此限制不再适用。