• 首页
  • vue
  • TypeScript
  • JavaScript
  • scss
  • css3
  • html5
  • php
  • MySQL
  • redis
  • jQuery
  • CREATE TABLE语句

    CREATE [TEMPORARY] TABLE [IF NOT EXISTS] tbl_name
        (create_definition,...)
        [table_options]
        [partition_options]
    
    CREATE [TEMPORARY] TABLE [IF NOT EXISTS] tbl_name
        [(create_definition,...)]
        [table_options]
        [partition_options]
        [IGNORE | REPLACE]
        [AS] query_expression
    
    CREATE [TEMPORARY] TABLE [IF NOT EXISTS] tbl_name
        { LIKE old_tbl_name | (LIKE old_tbl_name) }
    
    create_definition:
        col_name column_definition
      | {INDEX|KEY} [index_name] [index_type] (key_part,...)
          [index_option] ...
      | {FULLTEXT|SPATIAL} [INDEX|KEY] [index_name] (key_part,...)
          [index_option] ...
      | [CONSTRAINT [symbol]] PRIMARY KEY
          [index_type] (key_part,...)
          [index_option] ...
      | [CONSTRAINT [symbol]] UNIQUE [INDEX|KEY]
          [index_name] [index_type] (key_part,...)
          [index_option] ...
      | [CONSTRAINT [symbol]] FOREIGN KEY
          [index_name] (col_name,...)
          reference_definition
      | check_constraint_definition
    
    column_definition:
        data_type [NOT NULL | NULL] [DEFAULT {literal | (expr)} ]
          [AUTO_INCREMENT] [UNIQUE [KEY]] [[PRIMARY] KEY]
          [COMMENT 'string']
          [COLLATE collation_name]
          [COLUMN_FORMAT {FIXED|DYNAMIC|DEFAULT}]
          [STORAGE {DISK|MEMORY}]
          [reference_definition]
          [check_constraint_definition]
      | data_type
          [COLLATE collation_name]
          [GENERATED ALWAYS] AS (expr)
          [VIRTUAL | STORED] [NOT NULL | NULL]
          [UNIQUE [KEY]] [[PRIMARY] KEY]
          [COMMENT 'string']
          [reference_definition]
          [check_constraint_definition]
    
    data_type:
        (see Chapter 11, Data Types)
    
    key_part: {col_name [(length)] | (expr)} [ASC | DESC]
    
    index_type:
        USING {BTREE | HASH}
    
    index_option:
        KEY_BLOCK_SIZE [=] value
      | index_type
      | WITH PARSER parser_name
      | COMMENT 'string'
      | {VISIBLE | INVISIBLE}
    
    check_constraint_definition:
        [CONSTRAINT [symbol]] CHECK (expr) [[NOT] ENFORCED]
    
    reference_definition:
        REFERENCES tbl_name (key_part,...)
          [MATCH FULL | MATCH PARTIAL | MATCH SIMPLE]
          [ON DELETE reference_option]
          [ON UPDATE reference_option]
    
    reference_option:
        RESTRICT | CASCADE | SET NULL | NO ACTION | SET DEFAULT
    
    table_options:
        table_option [[,] table_option] ...
    
    table_option:
        AUTO_INCREMENT [=] value
      | AVG_ROW_LENGTH [=] value
      | [DEFAULT] CHARACTER SET [=] charset_name
      | CHECKSUM [=] {0 | 1}
      | [DEFAULT] COLLATE [=] collation_name
      | COMMENT [=] 'string'
      | COMPRESSION [=] {'ZLIB'|'LZ4'|'NONE'}
      | CONNECTION [=] 'connect_string'
      | {DATA|INDEX} DIRECTORY [=] 'absolute path to directory'
      | DELAY_KEY_WRITE [=] {0 | 1}
      | ENCRYPTION [=] {'Y' | 'N'}
      | ENGINE [=] engine_name
      | INSERT_METHOD [=] { NO | FIRST | LAST }
      | KEY_BLOCK_SIZE [=] value
      | MAX_ROWS [=] value
      | MIN_ROWS [=] value
      | PACK_KEYS [=] {0 | 1 | DEFAULT}
      | PASSWORD [=] 'string'
      | ROW_FORMAT [=] {DEFAULT|DYNAMIC|FIXED|COMPRESSED|REDUNDANT|COMPACT}
      | STATS_AUTO_RECALC [=] {DEFAULT|0|1}
      | STATS_PERSISTENT [=] {DEFAULT|0|1}
      | STATS_SAMPLE_PAGES [=] value
      | TABLESPACE tablespace_name [STORAGE {DISK|MEMORY}]
      | UNION [=] (tbl_name[,tbl_name]...)
    
    partition_options:
        PARTITION BY
            { [LINEAR] HASH(expr)
            | [LINEAR] KEY [ALGORITHM={1|2}] (column_list)
            | RANGE{(expr) | COLUMNS(column_list)}
            | LIST{(expr) | COLUMNS(column_list)} }
        [PARTITIONS num]
        [SUBPARTITION BY
            { [LINEAR] HASH(expr)
            | [LINEAR] KEY [ALGORITHM={1|2}] (column_list) }
          [SUBPARTITIONS num]
        ]
        [(partition_definition [, partition_definition] ...)]
    
    partition_definition:
        PARTITION partition_name
            [VALUES
                {LESS THAN {(expr | value_list) | MAXVALUE}
                |
                IN (value_list)}]
            [[STORAGE] ENGINE [=] engine_name]
            [COMMENT [=] 'string' ]
            [DATA DIRECTORY [=] 'data_dir']
            [INDEX DIRECTORY [=] 'index_dir']
            [MAX_ROWS [=] max_number_of_rows]
            [MIN_ROWS [=] min_number_of_rows]
            [TABLESPACE [=] tablespace_name]
            [(subpartition_definition [, subpartition_definition] ...)]
    
    subpartition_definition:
        SUBPARTITION logical_name
            [[STORAGE] ENGINE [=] engine_name]
            [COMMENT [=] 'string' ]
            [DATA DIRECTORY [=] 'data_dir']
            [INDEX DIRECTORY [=] 'index_dir']
            [MAX_ROWS [=] max_number_of_rows]
            [MIN_ROWS [=] min_number_of_rows]
            [TABLESPACE [=] tablespace_name]
    
    query_expression:
        SELECT ...   (Some valid select or union statement)
    

    CREATE TABLE创建具有给定名称的表。您必须具有CREATE该表的特权。

    默认情况下,表是使用InnoDB存储引擎在默认数据库中创建的。如果该表存在,没有默认数据库或该数据库不存在,则会发生错误。

    MySQL对表的数量没有限制。基础文件系统可能会对表示表的文件数量有所限制。各个存储引擎可能会强加特定于引擎的约束。InnoDB最多允许40亿张桌子。

    有关表的物理表示的信息,请参见“CREATE TABLE语句创建文件”。

    CREATE TABLE创建表时,原始语句(包括所有规范和表选项)由MySQL存储。有关更多信息,请参见“ CREATE TABLE语句保留”。

    CREATE TABLE语句有多个方面,本节以下主题对此进行了描述:

    • 表名
    • 临时表
    • 表克隆和复制
    • 列数据类型和属性
    • 索引,外键和检查约束
    • 表格选项
    • 表分区

    表名

    • tbl_name

      可以将表名指定为db_name.tbl_name在特定数据库中创建表。不管是否存在默认数据库(假定数据库存在),此方法都有效。如果使用带引号的标识符,请分别为数据库和表名加上引号。例如,写`mydb`.`mytbl`而不是`mydb.mytbl`

      “架构对象名称”中给出了允许的表名称的规则。

    • IF NOT EXISTS

      如果表存在,则防止发生错误。但是,没有验证现有表的结构与该CREATE TABLE语句指示的结构相同。

    临时表

    您可以TEMPORARY在创建表时使用关键字。一个TEMPORARY表仅在当前会话中可见,并且在关闭会话时会自动删除。有关更多信息,请参见“ CREATE TEMPORARY TABLE语句”。

    表克隆和复制

    • LIKE

      用于CREATE TABLE ... LIKE根据另一个表的定义创建一个空表,包括在原始表中定义的任何列属性和索引:

      CREATE TABLE new_tbl LIKE orig_tbl;
      

      有关更多信息,请参见“ CREATE TABLE ... LIKE语句”。

    • [AS]query_expression

      要从另一个表创建一个表,请在SELECT语句末尾添加一条CREATE TABLE语句:

      CREATE TABLE new_tbl AS SELECT * FROM orig_tbl;
      

      有关更多信息,请参见“ CREATE TABLE ... SELECT语句”。

    • IGNORE|REPLACE

      IGNOREREPLACE选项指示如何处理使用复制表时复制唯一键值的行SELECT声明。

      有关更多信息,请参见“ CREATE TABLE ... SELECT语句”。

    列数据类型和属性

    每个表都有4096列的硬限制,但是对于给定的表,有效最大值可能会更少,并且取决于“表的列数和行大小的限制”中讨论的因素。

    • data_type

      data_type表示列定义中的数据类型。有关可用于指定列数据类型的语法的完整说明,以及有关每种类型的属性的信息,请参见数据类型

      • 某些属性并不适用于所有数据类型。AUTO_INCREMENT仅适用于整数和浮点类型。在此之前的MySQL 8.0.13,DEFAULT并不适用于BLOBTEXTGEOMETRY,和JSON类型。
      • 字符数据类型(CHARVARCHAR,的TEXT类型,ENUMSET,和任何同义词)可以包括CHARACTER SET指定该列的字符集。CHARSET是的同义词CHARACTER SET。可以使用COLLATE属性以及任何其他属性来指定字符集的排序规则。有关详细信息,请参见字符集,归类和Unicode。例:

        CREATE TABLE t (c CHAR(20) CHARACTER SET utf8 COLLATE utf8_bin);
        

        MySQL 8.0在字符的字符列定义中解释长度规范。对于长度BINARYVARBINARY是字节。

      • CHARVARCHARBINARY,和VARBINARY列,索引可以创建仅使用列值的前导部分,使用语法来指定一个索引前缀长度。和列也可以建立索引,但是必须给出前缀长度。对于非二进制字符串类型,前缀长度以字符形式给出;对于二进制字符串类型,前缀长度以字节形式给出。即,索引项由所述第一的每个列的值的字符,和col_name(length)BLOBTEXTlengthCHARVARCHARTEXT列,并且所述第一length对每个列的值的字节BINARYVARBINARYBLOB列。像这样仅索引列值的前缀可以使索引文件更小。有关索引前缀的更多信息,请参见“ CREATE INDEX语句”。

        InnoDBMyISAM存储引擎支持在BLOBTEXT列上建立索引。例如:

        CREATE TABLE test (blob_col BLOB, INDEX(blob_col(10)));
        

        如果指定的索引前缀超过最大列数据类型的大小,CREATE TABLE则按以下方式处理索引:

        • 对于非唯一索引,可能会发生错误(如果启用了严格的SQL模式),或者索引长度被减小到最大列数据类型大小之内,并且会产生警告(如果未启用严格的SQL模式)。
        • 对于唯一索引,无论采用哪种SQL模式,都会发生错误,因为减小索引长度可能会导致插入不符合指定唯一性要求的非唯一条目。
      • JSON列无法建立索引。您可以通过在生成的列上创建索引来解决此限制,该索引从列中提取标量值JSON。有关详细示例,请参见索引生成的列以提供JSON列索引。
    • NOT NULL | NULL

      如果既未指定也NULLNOT NULL指定,则将该列视为NULL已指定。

      在MySQL 8.0中,只有InnoDBMyISAMMEMORY存储引擎支持在可以有列的索引NULL值。在其他情况下,必须将索引列声明为,NOT NULL否则将导致错误结果。

    • DEFAULT

      指定列的默认值。有关默认值处理的更多信息,包括列定义不包含显式DEFAULT值的情况,请参见“数据类型默认值”。

      如果启用了NO_ZERO_DATENO_ZERO_IN_DATESQL模式,并且根据该模式使用的日期值默认值不正确,CREATE TABLE则在未启用严格SQL模式的情况下产生警告,而在启用严格模式的情况下产生错误。例如,NO_ZERO_IN_DATE启用后c1 DATE DEFAULT '2010-00-00'会产生警告。

    • AUTO_INCREMENT

      整数或浮点列可以具有Additional属性AUTO_INCREMENT。当您将NULL(推荐)值或0插入索引AUTO_INCREMENT列时,该列将设置为下一个序列值。通常,这是value+1,其中value是表中当前列的最大值。AUTO_INCREMENT序列以开头1

      AUTO_INCREMENT在插入行后检索值,请使用LAST_INSERT_ID()SQL函数或mysql_insert_id()C API函数。请参见“信息函数”和“ mysql_insert_id()”。

      如果NO_AUTO_VALUE_ON_ZERO启用了SQL模式下,可以存储0AUTO_INCREMENT列,0而不会产生一个新的序列值。请参见“服务器SQL模式”。

      AUTO_INCREMENT每个表只能有一个列,必须对其进行索引,并且不能有DEFAULT值。一AUTO_INCREMENT列正常工作,只有当它仅包含正值。插入负数被视为插入非常大的正数。这样做是为了避免数字从正数“换”为负数时出现精度问题,并确保您不会意外得到AUTO_INCREMENT包含的列0

      对于MyISAM表,您可以AUTO_INCREMENT在多列键中指定第二列。请参见“使用AUTO_INCREMENT”。

      为了使MySQL与某些ODBC应用程序兼容,可以AUTO_INCREMENT使用以下查询找到最后插入的行的值:

      SELECT * FROM tbl_name WHERE auto_col IS NULL
      

      此方法要求sql_auto_is_null变量未设置为0。请参见“服务器系统变量”。

      有关信息InnoDB,并AUTO_INCREMENT请参见第15.6.1.6,“AUTO_INCREMENT处理InnoDB中”。有关AUTO_INCREMENTMySQL复制的信息,请参见“InnoDB中的AUTO_INCREMENT处理”。

    • COMMENT

      可以使用COMMENT选项指定列的注释,最长为1024个字符。注释由SHOW CREATE TABLESHOW FULL COLUMNS语句显示。

    • COLUMN_FORMAT

      在NDB Cluster中,还可以使用来为NDB表的各个列指定数据存储格式COLUMN_FORMAT。允许列格式FIXEDDYNAMIC以及DEFAULTFIXED用于指定固定宽度存储,DYNAMIC允许DEFAULT列为可变宽度,并导致列使用由列的数据类型确定的固定宽度或可变宽度存储(可能被ROW_FORMAT说明符覆盖)。

      对于NDB表,默认值COLUMN_FORMATFIXED

      在NDB群集中,用定义的列的最大可能偏移为COLUMN_FORMAT=FIXED8188字节。有关更多信息和可能的解决方法,请参见“NDB群集中与数据库对象相关联的限制”。

      COLUMN_FORMAT目前对使用以外的存储引擎的表的列没有影响NDB。MySQL 8.0默默忽略COLUMN_FORMAT

    • STORAGE

      对于NDB表,可以使用STORAGE子句指定列是存储在磁盘上还是存储在内存中。STORAGE DISK使该列存储在磁盘上,并STORAGE MEMORY导致使用内存中存储。使用的CREATE TABLE语句必须仍然包含一个TABLESPACE子句:

      mysql> CREATE TABLE t1 (
      ->     c1 INT STORAGE DISK,
      ->     c2 INT STORAGE MEMORY
      -> ) ENGINE NDB;
      ERROR 1005 (HY000): Can't create table 'c.t1' (errno: 140)
      
      mysql> CREATE TABLE t1 (
      ->     c1 INT STORAGE DISK,
      ->     c2 INT STORAGE MEMORY
      -> ) TABLESPACE ts_1 ENGINE NDB;
      Query OK, 0 rows affected (1.06 sec)
      

      对于NDB表格,STORAGE DEFAULT相当于STORAGE MEMORY

      STORAGE子句对使用除以外的存储引擎的表无效NDB。该STORAGE关键字仅在NDB Cluster随附的mysqld构建中受支持。在任何其他版本的MySQL中,该STORAGE关键字均无法识别,因为尝试使用该关键字会导致语法错误。

    • GENERATED ALWAYS

      用于指定生成的列表达式。有关生成的列的信息,请参见“创建表和生成的列”。

      存储的生成列可以被索引。InnoDB支持虚拟生成的列上的二级索引。请参见“二级索引和生成的列”。

    索引,外键和检查约束

    有几个关键字适用于创建索引,外键和CHECK约束。有关以下描述的一般背景,请参见“ CREATE INDEX语句”,“外键约束”和“ CHECK约束”。

    • CONSTRAINT symbol

      可以给该子句命名以约束。如果没有给出该子句,或者在关键字之后不包含a ,则MySQL将自动生成约束名称,但以下情况除外。如果使用该值,则每个模式(数据库),每个约束类型都必须是唯一的。重复会导致错误。另请参见“标识符长度限制”中有关生成的约束标识符的长度限制的讨论。CONSTRAINT symbolsymbolCONSTRAINTsymbolsymbol

      注意

      如果没有在外键定义中提供该子句,或者在关键字之后不包含a ,则MySQL使用外键索引名称,直到MySQL 8.0.15,然后自动生成约束名称。CONSTRAINT symbolsymbolCONSTRAINT

      SQL标准指定所有类型的约束(主键,唯一索引,外键,检查)都属于同一名称空间。在MySQL中,每个约束类型在每个模式都有自己的名称空间。因此,每种约束类型的名称在每个架构中必须唯一,但是不同类型的约束可以具有相同的名称。

    • PRIMARY KEY

      唯一索引,其中所有键列必须定义为NOT NULL。如果未将其显式声明为NOT NULL,则MySQL会如此隐式(无声地)声明它们。一个表只能有一个PRIMARY KEY。a的名称PRIMARY KEY始终为PRIMARY,因此不能用作任何其他种类的索引的名称。

      如果没有,PRIMARY KEY而应用程序PRIMARY KEY在表中要求,则MySQL返回第一个UNIQUE没有NULL列的索引作为PRIMARY KEY

      InnoDB表中,请保持PRIMARY KEY简短,以最大程度地减少辅助索引的存储开销。每个辅助索引条目都包含对应行的主键列的副本。(请参见“聚集索引和二级索引”。)

      在创建的表中,PRIMARY KEY首先放置a ,然后放置所有UNIQUE索引,然后放置非唯一索引。这可以帮助MySQL优化器确定要使用的索引的优先级,还可以更快地检测重复的UNIQUE键。

      A PRIMARY KEY可以是多列索引。但是,您不能使用PRIMARY KEY列规范中的key属性创建多列索引。这样做只会将该单列标记为主要列。您必须使用单独的子句。PRIMARY KEY(key_part,...)

      如果表的PRIMARY KEYUNIQUE NOT NULL索引由具有整数类型的单个列组成,则可以_rowid用来引用SELECT语句中的索引列,如Unique Indexes中所述。

      在MySQL中,a的名称PRIMARY KEYPRIMARY。对于其他指标,如果不指定一个名称,该指数被分配相同的名称作为第一个索引列,有一个可选的后缀(_2_3...),以使其独特。您可以使用来参见表的索引名称。请参见“ SHOW INDEX语句”。SHOW INDEX FROM tbl_name

    • KEY | INDEX

      KEY通常是的同义词INDEXPRIMARY KEY还可以像KEY在列定义中给定键属性一样指定键属性。这样做是为了与其他数据库系统兼容。

    • UNIQUE

      一个UNIQUE索引创建的约束,使得该指数的所有值必须是不同的。如果您尝试添加键值与现有行匹配的新行,则会发生错误。对于所有引擎,UNIQUE索引允许NULL包含的列具有多个值NULL。如果为UNIQUE索引中的列指定前缀值,则列值在前缀长度内必须唯一。

      如果表的PRIMARY KEYUNIQUE NOT NULL索引由具有整数类型的单个列组成,则可以_rowid用来引用SELECT语句中的索引列,如Unique Indexes中所述。

    • FULLTEXT

      一个FULLTEXT指标是用于全文搜索索引的一种特殊类型。仅InnoDBMyISAM存储引擎支持FULLTEXT索引。他们只能从创建CHARVARCHARTEXT列。索引总是在整个列上进行;不支持列前缀索引,并且如果指定,则将忽略任何前缀长度。有关操作的详细信息,请参见“全文搜索功能”。甲WITH PARSER子句可以被指定为一个index_option如果全文索引和搜索操作需要特殊处理,则将解析器插件与索引相关联的值。此子句仅对FULLTEXT索引有效。InnoDBMyISAM支持全文分析器插件。有关更多信息,请参见全文分析器插件和“编写全文分析器插件”。

    • SPATIAL

      您可以SPATIAL在空间数据类型上创建索引。只有InnoDBMyISAM表格才支持空间类型,索引列必须声明为NOT NULL。请参见“空间数据类型”。

    • FOREIGN KEY

      MySQL支持外键和外键约束,这些外键使您可以跨表交叉引用相关数据,外键约束则可以使这些扩展数据保持一致。有关定义和选项的信息,请参见reference_definitionreference_option

      使用InnoDB存储引擎的分区表不支持外键。有关更多信息,请参见“分区限制”。

    • CHECK

      CHECK子句允许检查约束的创建,以检查表行中的数据值。请参见“检查约束”。

    • key_part

      • key_part规格可以与结束ASCDESC以指定的索引值是否被存储在升序或降序排序。如果未指定订单说明符,则默认值为升序。
      • length属性定义的前缀,对于InnoDB使用REDUNDANTCOMPACT行格式的表,最长可以为767个字节。对于InnoDB使用DYNAMICCOMPRESSED行格式的表,前缀长度限制为3072字节。对于MyISAM表,前缀长度限制为1000个字节。

        前缀限制以字节为单位。然而,前缀长度为索引规范CREATE TABLEALTER TABLECREATE INDEX语句解释为非二进制串类型的字符数(CHARVARCHARTEXT对于二进制串类型),并且字节数(BINARYVARBINARYBLOB)。为使用多字节字符集的非二进制字符串列指定前缀长度时,请考虑到这一点。

      • 与MySQL 8.0.17开始,expr用于key_part规范可以采取的形式来创建上的一个多值索引列。多值索引,提供有关多值索引的创建,使用以及限制和限制的详细信息。(CAST json_path AS type ARRAY)JSON
    • index_type

      一些存储引擎允许您在创建索引时指定索引类型。index_type说明符的语法为。USING type_name

      例:

      CREATE TABLE lookup
        (id INT, INDEX USING BTREE (id))
        ENGINE = MEMORY;
      

      首选位置USING在索引列列表之后。可以在列列表之前给出它,但是不赞成在该位置使用该选项,并且在以后的MySQL版本中将删除该支持。

    • index_option

      index_option值指定索引的其他选项。

      • KEY_BLOCK_SIZE

        对于MyISAM表,KEY_BLOCK_SIZE可以选择指定用于索引键块的字节大小。该值被视为提示;如有必要,可以使用其他大小。KEY_BLOCK_SIZE为单个索引定义指定的值将覆盖表级KEY_BLOCK_SIZE值。

        有关表级KEY_BLOCK_SIZE属性的信息,请参见表选项。

      • WITH PARSER

        WITH PARSER选项只能与FULLTEXT索引一起使用。如果全文索引和搜索操作需要特殊处理,它将解析器插件与索引关联。InnoDBMyISAM支持全文分析器插件。如果您的MyISAM表带有关联的全文分析器插件,则可以将该表转换为InnoDBusing ALTER TABLE

      • COMMENT

        在MySQL 8.0中,索引定义可以包含最多1024个字符的可选注释。

        您可以InnoDBMERGE_THRESHOLD使用该index_optionCOMMENT子句为单个索引设置值。请参见“为索引页配置合并阈值”。

      有关允许index_option值的更多信息,请参见“ CREATE INDEX语句”。有关索引的更多信息,请参见“ MySQL如何使用索引”。

    • reference_definition

      有关reference_definition语法的详细信息和示例,请参见“外键约束”。

      InnoDBNDB表支持检查外键约束。被引用表的列必须始终明确命名。无论ON DELETEON UPDATE外键的行动的支持。有关更多详细信息和示例,请参见“外键约束”。

      对于其他存储引擎,MySQL Server解析并忽略语句中的FOREIGN KEYand REFERENCES语法CREATE TABLE。请参见“外键约束差异”。

      重要

      对于熟悉ANSI / ISO SQL标准的用户,请注意,包括在内的任何存储引擎都不会InnoDB识别或强制执行MATCH引用完整性约束定义中使用的子句。使用显式MATCH子句将不会具有指定的效果,并且还会导致ON DELETEON UPDATE子句被忽略。由于这些原因,MATCH应避免指定。

      MATCHSQL标准中的子句控制NULL与主键进行比较时如何处理复合(多列)外键中的值。InnoDB本质上实现了定义的语义MATCH SIMPLE,允许外键全部或部分地NULL。在这种情况下,允许插入包含此类外键的(子表)行,并且该行与引用的(父)表中的任何行都不匹配。使用触发器可以实现其他语义。

      另外,MySQL要求对引用的列进行索引以提高性能。但是,InnoDB不强制要求将引用的列声明为UNIQUENOT NULLNULL对于诸如UPDATE或的操作,未很好地定义对非唯一键或包含值的键的外键引用的处理DELETE CASCADE。建议您使用仅引用同时为UNIQUE(或PRIMARY)和的键的外键NOT NULL

      MySQL解析但忽略“内联REFERENCES规范”(如SQL标准所定义),其中引用被定义为列规范的一部分。MySQL REFERENCES仅在作为单独FOREIGN KEY规范的一部分指定时才接受子句。

    • reference_option

      有关信息RESTRICTCASCADESET NULLNO ACTION,和SET DEFAULT选项,请参阅第13.1.20.6,“外键约束”。

    表格选项

    表格选项用于优化表格的行为。在大多数情况下,您无需指定任何一个。除非另有说明,否则这些选项适用于所有存储引擎。不适用于给定存储引擎的选项可以被接受并记住作为表定义的一部分。如果您以后用于ALTER TABLE将表转换为使用其他存储引擎,则将应用这些选项。

    • ENGINE

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

      储存引擎描述
      InnoDB具有行锁定和外键的事务安全表。新表的默认存储引擎。如果您具有MySQL经验但不熟悉,请参阅InnoDB存储引擎,尤其是“ InnoDB简介”InnoDB
      MyISAM二进制便携式存储引擎,主要用于只读或以只读为主的工作负载。请参见“ MyISAM存储引擎”。
      MEMORY该存储引擎的数据仅存储在内存中。请参见“ MEMORY存储引擎”。
      CSV以逗号分隔值格式存储行的表。请参见“ CSV存储引擎”。
      ARCHIVE归档存储引擎。请参见“ ARCHIVE存储引擎”。
      EXAMPLE一个示例引擎。请参见“设置存储引擎”。
      FEDERATED访问远程表的存储引擎。请参见“FEDERATED存储引擎”。
      HEAP这是的同义词MEMORY
      MERGEMyISAM用作一个表的表的集合。也称为MRG_MyISAM。请参见“ MERGE存储引擎”。
      NDB群集的,基于内存的容错表,支持事务和外键。也称为NDBCLUSTER。请参见MySQL NDB Cluster 8.0

      默认情况下,如果指定了不可用的存储引擎,则该语句失败并显示错误。您可以通过NO_ENGINE_SUBSTITUTION从服务器SQL模式中删除(请参见“服务器SQL模式”)来覆盖此行为,以便MySQL允许将指定的引擎替换为默认的存储引擎。通常,在这种情况下,该值为InnoDB,这是default_storage_engine系统变量的默认值。当NO_ENGINE_SUBSTITUTION被禁用,如果存储引擎规格不兑现出现警告。

    • AUTO_INCREMENT

      AUTO_INCREMENT表的初始值。在MySQL 8.0,这个工程的MyISAMMEMORYInnoDB,和ARCHIVE表格。要为不支持该AUTO_INCREMENT表选项的引擎设置第一个自动增量值,请在创建表后插入一个“哑”行,其值比期望值小一,然后删除该哑行。

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

    • AVG_ROW_LENGTH

      表的平均行长度的近似值。您仅需要为具有可变大小行的大型表设置此选项。

      创建MyISAM表时,MySQL使用MAX_ROWSAVG_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个字符。

      您可以InnoDBMERGE_THRESHOLD使用table_optionCOMMENT子句设置表的值。请参见“为索引页配置合并阈值”。

      设置NDB_TABLE选项。在该表注释CREATE TABLE创建一个NDB表或ALTER TABLE它会改变一个也可以用于指定一到四个声明NDB_TABLE选项NOLOGGINGREAD_BACKUPPARTITION_BALANCE,或FULLY_REPLICATED为一组名称-值对的,用逗号分隔,如果需要的话,紧跟着NDB_TABLE=以引号引起来的注释文本的字符串。此处显示了使用此语法的示例语句(强调文本):

      CREATE TABLE t1 (
          c1 INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
          c2 VARCHAR(100),
          c3 VARCHAR(100) )
      ENGINE=NDBCOMMENT="NDB_TABLE=READ_BACKUP=0,PARTITION_BALANCE=FOR_RP_BY_NODE";
      

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

      注释显示为的输出的一部分SHOW CREATE TABLE。注释的文本也可以作为MySQL Information Schema TABLES表的TABLE_COMMENT列使用。

      表的ALTER TABLE语句也支持此注释语法NDB。请记住,与一起使用的表注释ALTER TABLE将替换表以前可能具有的所有现有注释。

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

      有关完整的语法信息和示例,请参见“设置NDB_TABLE选项”。

    • COMPRESSION

      用于InnoDB表的页面级压缩的压缩算法。支持的值包括ZlibLZ4,和None。该COMPRESSION属性是通过透明页面压缩功能引入的。仅存InnoDB在于每个表文件表空间中的表支持页面压缩,并且仅在支持稀疏文件和打孔的Linux和Windows平台上可用。有关更多信息,请参见“ InnoDB页面压缩”。

    • CONNECTION

      FEDERATED表的连接字符串。

      注意

      较旧的MySQL版本对COMMENT连接字符串使用了一个选项。

    • DATA DIRECTORYINDEX DIRECTORY

      对于InnoDB,该子句允许在数据目录之外创建表。必须启用该变量才能使用该子句。必须指定完整目录路径。有关更多信息,请参见“在外部创建表”。DATA DIRECTORY='directory'innodb_file_per_tableDATA DIRECTORY

      在创建MyISAM表时,可以使用该条款,该条款,或两者兼而有之。它们分别指定将表的数据文件和索引文件放在何处。与表不同的是,当使用或选项创建表时,MySQL不会创建与数据库名称相对应的子目录。在指定的目录中创建文件。DATA DIRECTORY='directory'INDEX DIRECTORY='directory'MyISAMInnoDBMyISAMDATA DIRECTORYINDEX DIRECTORY

      您必须具有FILE使用DATA DIRECTORYINDEX DIRECTORY表选项的特权。

      重要

      对于分区表,将忽略表级DATA DIRECTORYINDEX DIRECTORY选项。错误32091)

      这些选项仅在不使用时才起作用--skip-symbolic-links。您的操作系统还必须有一个有效的线程安全realpath()调用。有关更多完整信息,请参见“在Unix上为MyISAM表使用符号链接”。

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

      如果MyISAM使用DATA DIRECTORYINDEX DIRECTORY选项创建表,并且找到现有文件.MYD.MYI文件,则MyISAM始终返回错误。它不会覆盖指定目录中的文件。

      重要

      您不能使用带有DATA DIRECTORY或的包含MySQL数据目录的路径名INDEX DIRECTORY。这包括分区表和单个表分区。(请参阅Bug#32167。)

    • DELAY_KEY_WRITE

      如果要延迟表的密钥更新直到关闭表,请将其设置为1。请参见“服务器系统变量”中对delay_key_write系统变量的描述。(仅限)MyISAM

    • ENCRYPTION

      ENCRYPTION子句启用或禁用表的页面级数据加密InnoDB。必须先安装和配置密钥环插件,然后才能启用加密。在MySQL 8.0.16之前,ENCRYPTION只能在每个表文件表空间中创建表时指定该子句。从MySQL 8.0.16开始,ENCRYPTION在常规表空间中创建表时也可以指定该子句。

      从MySQL 8.0.16开始,如果ENCRYPTION未指定子句,则表会继承默认的模式加密。如果table_encryption_privilege_check启用了该变量,TABLE_ENCRYPTION_ADMIN则需要特权来创建带有ENCRYPTION不同于默认模式加密的子句设置的表。在常规表空间中创建表时,表和表空间的加密必须匹配。

      从MySQL 8.0.16开始,使用不支持加密的存储引擎时,请使用ENCRYPTION'N'''不允许的值指定子句。以前,该条款已被接受。

      有关更多信息,请参见“ InnoDB静态数据加密”。

    • INSERT_METHOD

      如果要将数据插入MERGE表中,则必须指定INSERT_METHOD要在其中插入行的表。INSERT_METHODMERGE仅对表有用的选项。使用FIRST或的值LAST使插入到第一个或最后一个表,或使用值NO防止插入。请参见“ MERGE存储引擎”。

    • KEY_BLOCK_SIZE

      对于MyISAM表,KEY_BLOCK_SIZE可以选择指定用于索引键块的字节大小。该值被视为提示;如有必要,可以使用其他大小。KEY_BLOCK_SIZE为单个索引定义指定的值将覆盖表级KEY_BLOCK_SIZE值。

      对于InnoDB表,KEY_BLOCK_SIZE指定要用于压缩表的页面大小(以千字节为单位)。该值被视为提示;如有必要,可以使用其他大小。只能小于或等于该值。值0表示默认的压缩页面大小,是该值的一半。取决于,可能的值包括0、1、2、4、8和16。有关更多信息,请参见“ InnoDB表压缩”。InnoDBKEY_BLOCK_SIZEInnoDBKEY_BLOCK_SIZEinnodb_page_sizeinnodb_page_sizeinnodb_page_sizeKEY_BLOCK_SIZE

      Oracle建议使innodb_strict_mode指定时KEY_BLOCK_SIZEInnoDB表。当innodb_strict_mode被启用,指定一个无效的KEY_BLOCK_SIZE值返回一个错误。如果innodb_strict_mode禁用KEY_BLOCK_SIZEKEY_BLOCK_SIZE选项,则无效值将导致警告,并且该选项将被忽略。

      Create_options响应的列将SHOW TABLE STATUS报告KEY_BLOCK_SIZE该表实际使用的列,也是如此SHOW CREATE TABLE

      InnoDBKEY_BLOCK_SIZE在表级别支持。

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

      InnoDBKEY_BLOCK_SIZE创建临时表时不支持该选项。

    • MAX_ROWS

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

      重要

      不建议使用MAX_ROWSwith NDB表控制表分区的数量。为了向后兼容,它在更高版本中仍受支持,但将来的版本中可能会删除它。改用PARTITION_BALANCE;请参阅设置NDB_TABLE选项。

      NDB存储引擎将这个值作为最大。如果您打算创建非常大的NDB簇表(含数百万行),你应该使用这个选项,以确保NDB分配足够数量的指数中的时隙中用于存储表的设置主键的哈希值的哈希表,其中是您希望插入表中的行数。MAX_ROWS = 2 *rowsrows

      最大值MAX_ROWS为4294967295;较大的值将被截断到此限制。

    • MIN_ROWS

      您计划在表中存储的最小行数。该MEMORY存储引擎使用此选项,因为一个关于内存使用提示。

    • PACK_KEYS

      仅对MyISAM表格生效。如果要使用较小的索引,请将此选项设置为1。这通常会使更新速度变慢,读取速度也会变快。将选项设置为0将禁用所有键打包。将其设置为DEFAULT告诉存储引擎只封装长CHARVARCHARBINARY,或VARBINARY列。

      如果不使用PACK_KEYS,则默认值为打包字符串,但不打包数字。如果您使用PACK_KEYS=1,数字也会被打包。

      在打包二进制数字键时,MySQL使用前缀压缩:

      • 每个密钥都需要一个额外的字节来指示上一个密钥的多少个字节与下一个密钥相同。
      • 指向行的指针直接在键之后以高字节优先顺序存储,以提高压缩率。

      这意味着,如果在连续的两行中有许多相等的键,则所有后面的“相同”键通常仅占用两个字节(包括指向该行的指针)。将其与采用以下键的普通情况storage_size_for_key + pointer_size(指针大小通常为4)进行比较。相反,仅当您拥有许多相同的数字时,您才能从前缀压缩中获得显着的好处。如果所有键完全不同,则如果该键不是可以具有NULL值的键,则每个键要多使用一个字节。(在这种情况下,打包的密钥长度存储在用于标记密钥是否为的同一字节中NULL。)

    • PASSWORD

      此选项未使用。

    • ROW_FORMAT

      定义存储行的物理格式。

      在禁用CREATE TABLE了严格模式的情况下执行语句时,如果您指定了表所用存储引擎不支持的行格式,则会使用该存储引擎的默认行格式来创建表。对应于,表的实际行格式在Row_formatCreate_options列中报告SHOW TABLE STATUSSHOW CREATE TABLE还报告表的实际行格式。

      行格式选择取决于表所使用的存储引擎。

      对于InnoDB表:

      • 默认行格式由定义innodb_default_row_format,默认设置为DYNAMICROW_FORMAT未定义选项或ROW_FORMAT=DEFAULT使用选项时,将使用默认行格式。

        如果ROW_FORMAT未定义选项,或者ROW_FORMAT=DEFAULT使用,则用于重建表的操作也将表的行格式默认更改为innodb_default_row_format。有关更多信息,请参见定义表的行格式。

      • 要更有效地InnoDB存储数据类型,尤其是BLOB类型,请使用DYNAMIC。有关与行格式相关的要求,请参见 DYNAMICDYNAMIC行格式。
      • 要为InnoDB表启用压缩,请指定ROW_FORMAT=COMPRESSEDROW_FORMAT=COMPRESSED创建临时表时不支持该选项。有关与行格式相关的要求,请参见“ InnoDB表和页面压缩”COMPRESSED
      • 仍然可以通过指定REDUNDANT行格式来请求旧版MySQL中使用的行格式。
      • 当您指定非默认ROW_FORMAT子句时,请考虑同时启用innodb_strict_mode配置选项。
      • ROW_FORMAT=FIXED不支持。如果ROW_FORMAT=FIXEDinnodb_strict_mode禁用时指定,则InnoDB发出警告并假定ROW_FORMAT=DYNAMIC。如果ROW_FORMAT=FIXEDinnodb_strict_mode启用时指定if (默认),则InnoDB返回错误。
      • 有关InnoDB行格式的更多信息,请参见“ InnoDB行格式”。

      MyISAM表,该选项值可以是FIXEDDYNAMIC为静态的或可变长度的行格式。myisampack将类型设置为COMPRESSED。请参见“ MyISAM表存储格式”。

      对于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 TABLEALTER TABLE语句启用永久统计ANALYZE TABLE信息后,在将代表性数据加载到表中之后,发出一条语句来计算统计信息。有关持久统计信息功能的更多信息,请参见“配置持久性优化器统计参数”。

    • STATS_SAMPLE_PAGES

      估算索引列的基数和其他统计信息时(例如由所计算出的索引页),要采样的索引页数ANALYZE TABLE。有关更多信息,请参见“配置持久性优化器统计参数”。

    • TABLESPACE

      TABLESPACE子句可用于在现有的常规表空间,每表文件表空间或系统表空间中创建表。

      CREATE TABLE tbl_name ... TABLESPACE [=] tablespace_name
      

      您指定的常规表空间在使用该TABLESPACE子句之前必须存在。有关常规表空间的信息,请参见“通用表空间”。

      tablespace_name是区分大小写的标识符。它可以被引用或不被引用。不允许使用正斜杠字符(“/ ”)。以“ innodb_ ”开头的名称保留作特殊用途。

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

      CREATE TABLE tbl_name ... TABLESPACE [=] innodb_system
      

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

      要在每个表文件表空间中创建一个表,请指定innodb_file_per_table为表空间名称。

      CREATE TABLE tbl_name ... TABLESPACE [=] innodb_file_per_table
      
      注意

      如果innodb_file_per_table启用,则无需指定TABLESPACE=innodb_file_per_table创建InnoDB每表文件表空间。启用InnoDB时,默认情况下在每个表文件表空间中创建表innodb_file_per_table

      DATA DIRECTORY允许该子句使用,CREATE TABLE ... TABLESPACE=innodb_file_per_table但不支持与该TABLESPACE子句组合使用。

      注意

      自MySQL 8.0.13起,不再支持TABLESPACE = innodb_file_per_tableTABLESPACE = innodb_temporary带有的子句,CREATE TEMPORARY TABLE在将来的MySQL版本中将不再支持。

      STORAGE表选项仅采用了与NDB表。STORAGE确定使用的存储类型(磁盘或内存),可以是DISKMEMORY

      TABLESPACE ... STORAGE DISK将表分配给NDB群集磁盘数据表空间。该表空间必须已经使用创建CREATE TABLESPACE。有关更多信息,请参见“ NDB群集磁盘数据表”。

      重要

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

    • UNION

      用于访问一组相同的MyISAM表。这仅适用于MERGE表。请参见“ MERGE存储引擎”。

      您必须具有SELECTUPDATEDELETE特权才能映射到MERGE表。

      注意

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

    表分区

    partition_options可用于控制使用创建的表的分区CREATE TABLE

    并非partition_options本节开头的语法中显示的所有选项都不适用于所有分区类型。请参阅以下各个类型的清单以获取每种类型的特定信息,并请参阅分区,以获取有关MySQL中分区工作原理和用途的更完整信息,以及表创建的其他示例以及与之相关的其他语句到MySQL分区。

    分区可以修改,合并,添加到表中以及从表中删除。有关完成这些任务的MySQL语句的基本信息,请参见“ ALTER TABLE语句”。有关更多详细的描述和示例,请参见“分区管理”。

    • PARTITION BY

      如果使用,则partition_options子句以开头PARTITION BY。此子句包含用于确定分区的函数;该函数返回一个整数值,范围是1到num,其中num是分区数。(一个表可能包含的用户定义的分区的最大数量为1024;此部分后面将讨论的子分区的数量已包括在该最大值中。)

      注意

      子句中expr使用的表达式()PARTITION BY不能引用不在所创建表中的任何列;明确不允许使用此类引用,这些引用会导致语句失败并出现错误。(缺陷#29444)

    • HASH(expr)

      哈希一个或多个列以创建用于放置和定位行的键。expr是使用一个或多个表列的表达式。这可以是产生单个整数值的任何有效MySQL表达式(包括MySQL函数)。例如,这些都是使用的有效CREATE TABLE语句PARTITION BY HASH

      CREATE TABLE t1 (col1 INT, col2 CHAR(5))
          PARTITION BY HASH(col1);
      
      CREATE TABLE t1 (col1 INT, col2 CHAR(5), col3 DATETIME)
          PARTITION BY HASH ( YEAR(col3) );
      

      您不能将VALUES LESS THANVALUES IN子句与一起使用PARTITION BY HASH

      PARTITION BY HASH使用余数expr除以分区数(即模数)。有关示例和更多信息,请参见“ HASH分区”。

      LINEAR关键字需要稍微不同的算法。在这种情况下,作为一个或多个逻辑AND运算的结果,计算存储行的分区的数量。有关线性哈希的讨论和示例,请参见“线性哈希分区”。

    • KEY(column_list)

      这与相似HASH,除了MySQL提供散列功能以确保均匀的数据分布。该column_list参数仅是1个或更多表列的列表(最多16个)。此示例显示了一个简单的表,该表按键进行了分区,分为四个分区:

      CREATE TABLE tk (col1 INT, col2 CHAR(5), col3 DATE)
          PARTITION BY KEY(col3)
          PARTITIONS 4;
      

      对于按键分区的表,可以通过使用LINEAR关键字来进行线性分区。与由分区的表具有相同的效果HASH。也就是说,使用&运算符而不是模数来找到分区号(有关详细信息,请参见“ LINEA HASH分区”和“ KEY分区”)。本示例按键使用线性分区在5个分区之间分配数据:

      CREATE TABLE tk (col1 INT, col2 CHAR(5), col3 DATE)
          PARTITION BY LINEAR KEY(col3)
          PARTITIONS 5;
      

      ALGORITHM={1|2}选项受支持[SUB]PARTITION BY[LINEAR] KEYALGORITHM=1使服务器使用与MySQL 5.1相同的键哈希函数;ALGORITHM=2表示服务器使用默认KEY在MySQL 5.5及更高版本中为新分区表实现和使用的键哈希函数。(使用MySQL 5.5和更高版本中使用的键哈希函数创建的分区表不能被MySQL 5.1服务器使用。)不指定该选项与使用效果相同ALGORITHM=2。此选项主要用于[LINEAR] KEY在MySQL 5.1和更高版本的MySQL之间升级或降级分区表时,或用于创建由KEYLINEAR KEY可以在MySQL 5.1服务器上使用的MySQL 5.5或更高版本的服务器上。有关更多信息,请参见“ ALTER TABLE分区操作”。

      MySQL 5.7(及更高版本)中的 mysqldump将此选项写在版本注释中,如下所示:

      CREATE TABLE t1 (a INT)
      /*!50100 PARTITION BY KEY */ /*!50611 ALGORITHM = 1 */ /*!50100 ()
            PARTITIONS 3 */
      

      这将导致MySQL 5.6.10和更早版本的服务器忽略该选项,否则将导致这些版本中的语法错误。如果您打算加载在MySQL 5.7服务器上进行的转储,在其中使用已分区或再分区KEY到5.6.11之前版本的MySQL 5.6服务器的表,请确保在继续操作之前查阅 MySQL 5.6中的更改。(如果将包含KEY从MySQL 5.7(实际上是5.6.11或更高版本)服务器制作的分区表或子分区表的转储加载到MySQL 5.5.30或更早版本的服务器中,则在此找到的信息也适用。)

      同样在MySQL 5.6.11和更高版本中,ALGORITHM=1在必要时SHOW CREATE TABLE以与mysqldump相同的方式在使用版本注释的输出中显示。即使在创建原始表时指定了此选项,也ALGORITHM=2始终从SHOW CREATE TABLE输出中省略该参数。

      您不能将VALUES LESS THANVALUES IN子句与一起使用PARTITION BY KEY

    • RANGE(expr)

      在这种情况下,expr使用一组VALUES LESS THAN运算符显示一系列值。使用范围分区时,必须使用至少定义一个分区VALUES LESS THAN。您不能VALUES IN与范围分区一起使用。

      注意

      对于由分区的表RANGEVALUES LESS THAN必须与整数文字值或计算结果为单个整数值的表达式一起使用。在MySQL 8.0中,您可以克服在使用定义的表中的此限制PARTITION BY RANGE COLUMNS,如本节稍后所述。

      假设您有一个表,希望根据以下方案在包含年份值的列上进行分区。

      分区号:年份范围:
      01990年以前
      1个1991年至1994年
      21995至1998
      31999至2002
      42003至2005
      52006年及以后

      可以通过CREATE TABLE如下所示的语句来实现实现这种分区方案的表:

      CREATE TABLE t1 (
          year_col  INT,
          some_data INT
      )
      PARTITION BY RANGE (year_col) (
          PARTITION p0 VALUES LESS THAN (1991),
          PARTITION p1 VALUES LESS THAN (1995),
          PARTITION p2 VALUES LESS THAN (1999),
          PARTITION p3 VALUES LESS THAN (2002),
          PARTITION p4 VALUES LESS THAN (2006),
          PARTITION p5 VALUES LESS THAN MAXVALUE
      );
      

      PARTITION ... VALUES LESS THAN ...陈述以连续的方式工作。VALUES LESS THAN MAXVALUE用于指定大于其他指定最大值的“剩余”值。

      VALUES LESS THAN子句以类似于块各case部分switch ... case(在许多编程语言,例如C,Java和PHP中都可以找到)的方式顺序工作。就是说,子句的排列方式必须使每个连续序列中指定的上限VALUES LESS THAN大于上一个连续引用的上限,而引用的那个上限则排在MAXVALUE列表的最后。

    • RANGE COLUMNS(column_list)

      此变体on RANGE有助于使用多个列上的范围条件(即具有诸如WHERE a = 1 AND b < 10或条件WHERE a = 1 AND b = 10 AND c < 10)对查询进行分区修剪。它使您可以通过使用COLUMNS子句中的列列表和每个分区定义子句中的一组列值来指定多列中的值范围。(在最简单的情况下,此集合由一列组成。)和中可以引用的最大列数为16。PARTITION ... VALUES LESS THAN(value_list)column_listvalue_list

      column_list中使用的COLUMNS条款可能只包含列的名称;列表中的每一列必须是以下MySQL数据类型之一:整数类型;字符串类型;和时间或日期列类型。列使用BLOBTEXTSETENUMBIT,或空间数据类型不允许;也不允许使用浮点数类型的列。您也不得在该COLUMNS子句中使用函数或算术表达式。

      VALUES LESS THAN分区定义中使用的子句必须为出现在该COLUMNS()子句中的每一列指定一个文字值。也就是说,用于每个VALUES LESS THAN子句的值列表必须包含与该COLUMNS子句中列出的列相同数量的值。尝试在VALUES LESS THAN子句中使用比该COLUMNS子句中更多或更少的值会导致该语句失败,并出现错误:使用列列表进行分区...不一致。您无法使用NULL中显示的任何值VALUES LESS THAN。可以使用MAXVALUE对于给定的列而不是第一列,不止一次,如本示例所示:

      CREATE TABLE rc (
          a INT NOT NULL,
          b INT NOT NULL
      )
      PARTITION BY RANGE COLUMNS(a,b) (
          PARTITION p0 VALUES LESS THAN (10,5),
          PARTITION p1 VALUES LESS THAN (20,10),
          PARTITION p2 VALUES LESS THAN (50,MAXVALUE),
          PARTITION p3 VALUES LESS THAN (65,MAXVALUE),
          PARTITION p4 VALUES LESS THAN (MAXVALUE,MAXVALUE)
      );
      

      VALUES LESS THAN值列表中使用的每个值必须与相应列的类型完全匹配;没有进行任何转换。例如,您不能将字符串'1'用于与使用整数类型的列匹配的值(必须1改为使用数字),也不能将数字1用于与使用字符串类型的列匹配的值(在这种情况下在这种情况下,必须使用带引号的字符串:)'1'

      有关更多信息,请参见“ RANGE分区”和“分区修剪”。

    • LIST(expr)

      在基于表列分配有限的可能值集(例如州或国家/地区代码)时,这很有用。在这种情况下,可以将与某个州或国家/地区有关的所有行分配给单个分区,也可以为某个州或国家/地区的某个组保留分区。与相似RANGE,不同之处在于仅VALUES IN可用于指定每个分区的允许值。

      VALUES IN与要匹配的值列表一起使用。例如,您可以创建如下分区方案:

      CREATE TABLE client_firms (
          id   INT,
          name VARCHAR(35)
      )
      PARTITION BY LIST (id) (
          PARTITION r0 VALUES IN (1, 5, 9, 13, 17, 21),
          PARTITION r1 VALUES IN (2, 6, 10, 14, 18, 22),
          PARTITION r2 VALUES IN (3, 7, 11, 15, 19, 23),
          PARTITION r3 VALUES IN (4, 8, 12, 16, 20, 24)
      );
      

      使用列表分区时,必须使用至少定义一个分区VALUES IN。您不能VALUES LESS THAN与一起使用PARTITION BY LIST

      注意

      对于由分区的表LIST,与一起使用的值列表VALUES IN必须仅由整数值组成。在MySQL 8.0中,您可以使用分区来克服此限制LIST COLUMNS,本节稍后将对此进行介绍。

    • LIST COLUMNS(column_list)

      此变体on LIST有助于使用多个列上的比较条件(即具有诸如WHERE a = 5 AND b = 5或条件WHERE a = 1 AND b = 10 AND c = 5)的查询来进行分区修剪。它使您可以通过使用COLUMNS子句中的列列表和每个分区定义子句中的一组列值来指定多列中的值。PARTITION ... VALUES IN(value_list)

      管理有关数据类型中使用的列列表中的规则和使用值列表是相同的那些在使用的列清单,并在使用值列表分别,只是在条款,是不允许的,和你可以使用。LIST COLUMNS(column_list)VALUES IN(value_list)RANGE COLUMNS(column_list)VALUES LESS THAN(value_list)VALUES INMAXVALUENULL

      有用于值列表之间的一个重要区别VALUES INPARTITION BY LIST COLUMNS,而不是当它与使用PARTITION BY LIST。与一起使用时PARTITION BY LIST COLUMNSVALUES IN子句中的每个元素都必须是一列值;每个集合中的值数必须与该COLUMNS子句中使用的列数相同,并且这些值的数据类型必须与列的数据类型匹配(并以相同的顺序出现)。在最简单的情况下,集合由单个列组成。column_list和中构成的元素最多可以使用value_list16列。

      以下CREATE TABLE语句定义的表提供了使用LIST COLUMNS分区的表的示例:

      CREATE TABLE lc (
          a INT NULL,
          b INT NULL
      )
      PARTITION BY LIST COLUMNS(a,b) (
          PARTITION p0 VALUES IN( (0,0), (NULL,NULL) ),
          PARTITION p1 VALUES IN( (0,1), (0,2), (0,3), (1,1), (1,2) ),
          PARTITION p2 VALUES IN( (1,0), (2,0), (2,1), (3,0), (3,1) ),
          PARTITION p3 VALUES IN( (1,3), (2,2), (2,3), (3,2), (3,3) )
      );
      
    • PARTITIONS num

      分区数量可以选择用子句指定,其中是分区数量。如果同时使用此子句任何子句,则必须等于使用子句声明的任何分区的总数。PARTITIONS numnumPARTITIONnumPARTITION

      注意

      PARTITIONS在创建由RANGE或分区的表时,无论是否使用子句LIST,都必须PARTITION VALUES在表定义中至少包括一个子句(请参见下文)。

    • SUBPARTITION BY

      分区可以可选地划分为多个子分区。这可以通过使用可选SUBPARTITION BY子句来指示。可以通过HASH或进行子分区KEY。这些可能是LINEAR。这些工作方式与前面针对等效分区类型所述的方式相同。(不可能用LIST或细分RANGE。)

      可以使用SUBPARTITIONS关键字后跟一个整数值来指示子分区的数量。

    • 严格检查PARTITIONSor SUBPARTITIONS子句中使用的值,并且该值必须遵循以下规则:

      • 该值必须是一个非零的正整数。
      • 不允许前导零。
      • 该值必须是整数文字,并且不能为表达式。例如,PARTITIONS 0.2E+01即使0.2E+01计算为,也不允许2。错误15890)
    • partition_definition

      每个分区可以使用partition_definition子句单独定义。组成此子句的各个部分如下:

      • PARTITION partition_name

        指定分区的逻辑名称。

      • VALUES

        对于范围分区,每个分区必须包含一个VALUES LESS THAN子句;对于列表分区,必须VALUES IN为每个分区指定一个子句。这用于确定哪些行要存储在此分区中。见分区类型的讨论分区,语法的例子。

      • [STORAGE] ENGINE

        MySQL的接受[STORAGE] ENGINE了这两个选项PARTITIONSUBPARTITION。当前,可以使用此选项的唯一方法是将所有分区或所有子分区设置为同一存储引擎,并且尝试为同一表中的分区或子分区设置不同的存储引擎将产生错误ERROR 1469 (HY000):此版本的MySQL中不允许在分区中混合使用处理程序。

      • COMMENT

        可选COMMENT子句可用于指定描述分区的字符串。例:

        COMMENT = 'Data for the years previous to 1999'
        

        分区注释的最大长度为1024个字符。

      • DATA DIRECTORYINDEX DIRECTORY

        DATA DIRECTORY并且INDEX DIRECTORY可以用来指示该分区的数据和索引分别存储在的目录。无论是data_dirindex_dir必须是绝对系统路径名。

        您必须具有FILE使用DATA DIRECTORYINDEX DIRECTORY分区选项的特权。

        例:

        CREATE TABLE th (id INT, name VARCHAR(30), adate DATE)
        PARTITION BY LIST(YEAR(adate))
        (
          PARTITION p1999 VALUES IN (1995, 1999, 2003)
            DATA DIRECTORY = '/var/appdata/95/data'
            INDEX DIRECTORY = '/var/appdata/95/idx',
          PARTITION p2000 VALUES IN (1996, 2000, 2004)
            DATA DIRECTORY = '/var/appdata/96/data'
            INDEX DIRECTORY = '/var/appdata/96/idx',
          PARTITION p2001 VALUES IN (1997, 2001, 2005)
            DATA DIRECTORY = '/var/appdata/97/data'
            INDEX DIRECTORY = '/var/appdata/97/idx',
          PARTITION p2002 VALUES IN (1998, 2002, 2006)
            DATA DIRECTORY = '/var/appdata/98/data'
            INDEX DIRECTORY = '/var/appdata/98/idx'
        );
        

        DATA DIRECTORY并且其INDEX DIRECTORY行为与表中CREATE TABLE语句的table_option子句中的行为相同MyISAM

        每个分区可以指定一个数据目录和一个索引目录。如果未指定,则默认情况下,数据和索引存储在表的数据库目录中。

        DATA DIRECTORYINDEX DIRECTORY选项将被忽略创建分区表是否NO_DIR_IN_CREATE有效。

      • MAX_ROWSMIN_ROWS

        可以分别用来指定要存储在分区中的最大和最小行数。为价值观max_number_of_rowsmin_number_of_rows必须为正整数。与具有相同名称的表级选项一样,它们仅充当服务器的“建议”,而不是硬性限制。

      • TABLESPACE

        通过指定可用于指定InnoDB分区的每表文件表空间TABLESPACE `innodb_file_per_table`。所有分区必须属于同一存储引擎。

        不支持将InnoDB表分区放在共享InnoDB表空间中。共享表空间包括InnoDB系统表空间和常规表空间。

    • subpartition_definition

      分区定义可以选择包含一个或多个subpartition_definition子句。每一个都至少由组成,其中是子分区的标识符。除了使用替换关键字外,子分区定义的语法与分区定义的语法相同。SUBPARTITION namenamePARTITIONSUBPARTITION

      子分区必须由HASH或进行KEY,并且只能在RANGELIST分区上进行。请参见“子分区”。

    按生成的列进行分区

    允许按生成的列进行分区。例如:

    CREATE TABLE t1 (
      s1 INT,
      s2 INT AS (EXP(s1)) STORED
    )
    PARTITION BY LIST (s2) (
      PARTITION p1 VALUES IN (1)
    );
    

    分区会将生成的列视为常规列,从而启用了一些变通办法,以解决不允许分区的功能受到限制的问题(请参见“与分区相关函数的限制”)。前面的示例演示了此技术:EXP()不能在PARTITION BY子句中直接使用,但是EXP()允许使用定义的生成列。