MySQL 基础教程

MySQL 高级教程

MySQL SQL 语句

MySQL 笔记

MySQL 创建数据表操作语句(CREATE TABLE)详解


MySQL 的 CREATE TABLE 语句用来根据指定的名称创建表,默认情况下,MySQL 创建表采用 InnoDB 存储引擎,若表已经存在,则会报错,需要结合 IF NOT EXISTS 来避免错误。

CREATE TABLE

语法

CREATE TABLE 语句有 3 种形式:

  1. 常规表创建;
  2. 从另一张表复制表结构及同步数据;
  3. 从另一张表只复制表结构。

具体如下:

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) }

选项/修饰符

  • TEMPORARY:创建临时表时的关键字,临时表仅在当前会话(session)中可见,并且在会话关闭时,会自动删除。
  • IF NOT EXISTS表不存在时,CREATE 语句生效,主要是为了避免表已经存在时的错误。
  • tbl_name:指定的表名,如果要在当前会话的所选数据库创建表,只需指定表名,否则,若要在其它数据库创建表,需要指定库名(前提是要有相应的权限),如 db_name.tbl_name
  • create_definition:创建表的结构定义,结构如下:
    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}]
            [ENGINE_ATTRIBUTE [=] 'string']
            [SECONDARY_ENGINE_ATTRIBUTE [=] 'string']
            [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]
      }
    • index_type:
          USING {BTREE | HASH}
    • key_part: {col_name [(length)] | (expr)} [ASC | DESC]
    • index_option: {
          KEY_BLOCK_SIZE [=] value
        | index_type
        | WITH PARSER parser_name
        | COMMENT 'string'
        | {VISIBLE | INVISIBLE}
        |ENGINE_ATTRIBUTE [=] 'string'
        |SECONDARY_ENGINE_ATTRIBUTE [=] 'string'
      }
    • 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
    • check_constraint_definition:
          [CONSTRAINT [symbol]] CHECK (expr) [[NOT] ENFORCED]
  • table_options:表的相关选项,如编码格式,自增起始值等等:
    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
        | ENGINE_ATTRIBUTE [=] 'string'
        | 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}
        | SECONDARY_ENGINE_ATTRIBUTE [=] 'string'
        | 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_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]
  • IGNORE | REPLACE:两选项表示从另一个表复制表时,唯一键冲突时的行处理方式。
  • [AS] query_expression:从另一个表创建一个表的表达式,主要是 SELECT 语句:
    query_expression:
        SELECT ...   (Some valid select or union statement)
  • LIKE:只复制表结构,不同步数据时使用的关键字,示例如下:
    CREATE TABLE new_tbl LIKE orig_tbl;

示例

创建用户表示例:

CREATE TABLE IF NOT EXISTS `user` (
  `uid` bigint(20) unsigned NOT NULL COMMENT '用户 id',
  `user_name` varchar(64) NOT NULL DEFAULT '' COMMENT '用户名称',
  `user_email` varchar(128) NOT NULL DEFAULT '' COMMENT '用户 email 地址',
  `status` tinyint(3) unsigned NOT NULL DEFAULT '0' COMMENT '状态,0、待审核,1、有效,2、无效',
  `create_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  `update_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '修改时间',
  PRIMARY KEY (`uid`),
  UNIQUE KEY `uniq_user_email` (`user_email`),
  KEY `idx_status` (`status`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='用户表';