创建表

您可以使用 CREATE TABLE 语句来创建表。本文主要介绍非分区表的创建。

前提条件

当前用户具有用户级或数据库级 CREATE 权限。

创建表

语法

CREATE TABLE [IF NOT EXISTS] table_name
      (table_definition_list) [table_option_list] [partition_option] [AS] select;

CREATE TABLE [IF NOT EXISTS] table_name
      LIKE table_name;

table_definition_list:
    table_definition [, table_definition ...]

table_definition:
      column_definition
    | [CONSTRAINT [constraint_name]] PRIMARY KEY index_desc
    | [CONSTRAINT [constraint_name]] UNIQUE {INDEX | KEY} 
            [index_name] index_desc
    | [CONSTRAINT [constraint_name]] FOREIGN KEY 
            [index_name] index_desc 
            REFERENCES reference_definition 
            [match_action][opt_reference_option_list]
    | {INDEX | KEY} [index_name] index_desc

column_definition_list:
    column_definition [, column_definition ...]

column_definition:
     column_name data_type
         [DEFAULT const_value] [AUTO_INCREMENT]
         [NULL | NOT NULL] [[PRIMARY] KEY] [UNIQUE [KEY]] comment
   | column_name data_type
         [GENERATED ALWAYS] AS (expr) [VIRTUAL | STORED]
         [opt_generated_column_attribute] 

index_desc:
   (column_desc_list) [index_type] [index_option_list]

match_action:
   MATCH {SIMPLE | FULL | PARTIAL}

opt_reference_option_list:
   reference_option [,reference_option ...]

reference_option:
   ON {DELETE | UPDATE} {RESTRICT | CASCADE | SET NULL | NO ACTION | SET DEFAULT}

column_desc_list:
    column_desc [, column_desc ...]

column_desc:
     column_name [(length)] [ASC | DESC]

index_type:
    USING BTREE

index_option_list:
    index_option [ index_option ...]

index_option:
      [GLOBAL | LOCAL]
    | block_size
    | compression
    | STORING(column_name_list)
    | comment

table_option_list:
    table_option [ table_option ...]

table_option:
      [DEFAULT] {CHARSET | CHARACTER SET} [=] charset_name
    | [DEFAULT] COLLATE [=] collation_name
    | primary_zone
    | replica_num
    | table_tablegroup
    | block_size
    | compression
    | AUTO_INCREMENT [=] INT_VALUE
    | comment
    | DUPLICATE_SCOPE [=] "none|zone|region|cluster"
    | LOCALITY [=] "locality description"
    | ROW_FORMAT [=] REDUNDANT|COMPACT|DYNAMIC|COMPRESSED|DEFAULT
    | PCTFREE [=] num
    | parallel_clause

parallel_clause:
    {NOPARALLEL | PARALLEL integer}

partition_option:
      PARTITION BY HASH(expression)
      [subpartition_option] PARTITIONS partition_count
    | PARTITION BY KEY([column_name_list])
      [subpartition_option] PARTITIONS partition_count
    | PARTITION BY RANGE {(expression) | COLUMNS (column_name_list)}
      [subpartition_option] (range_partition_list)
    | PARTITION BY LIST {(expression) | COLUMNS (column_name_list)}
      [subpartition_option] PARTITIONS partition_count

subpartition_option:
      SUBPARTITION BY HASH(expression)
      SUBPARTITIONS subpartition_count
    | SUBPARTITION BY KEY(column_name_list)
      SUBPARTITIONS subpartition_count
    | SUBPARTITION BY RANGE {(expression) | COLUMNS (column_name_list)}
      (range_subpartition_list)
    | SUBPARTITION BY LIST(expression)

range_partition_list:
    range_partition [, range_partition ...]

range_partition:
    PARTITION partition_name
    VALUES LESS THAN {(expression_list) | MAXVALUE}

range_subpartition_list:
    range_subpartition [, range_subpartition ...]

range_subpartition:
    SUBPARTITION subpartition_name
    VALUES LESS THAN {(expression_list) | MAXVALUE}

expression_list:
    expression [, expression ...]

column_name_list:
    column_name [, column_name ...]

partition_name_list:
    partition_name [, partition_name ...]

partition_count | subpartition_count:
    INT_VALUE

参数说明

创建非分区表

创建非分区表是指创建只有一个分区的表。

创建非分区表的示例语句如下：

示例1：创建 student 表。

CREATE TABLE student(
  id int,
  name varchar(18),
  sex char(1),
  age int,
  address varchar(200),
  email varchar(100),
  date date,
  PRIMARY KEY (id)
);

示例2：创建 staff 表。

CREATE TABLE staff(
  id int not null auto_increment,
  name varchar(18),
  sex char(1) default '0' check(
    sex = '0'
    or sex = '1'
  ),
  age int not null,
  address varchar(200),
  email varchar(100) unique,
  date date,
  wages number(7, 3),
  Entry_time TIMESTAMP(6),
  PRIMARY KEY (id),
  index idx (date)
);

示例中创建了 2 个表，并同时对表中的列定义了一些约束信息，包括在不同列上创建的主键和外键等。

创建表的列时请选择正确的数据类型。关于具体数据类型的使用，请参见 《SQL 参考》。

说明

• 基于性能和后期维护的需要，建议建表时为表设计主键或者唯一键。如果没有合适的字段作为主键，可以在创建表时不指定主键，待表创建成功后系统会为无主键表指定自增列作为隐藏主键。
• 由于 ALTER TABLE 语句不支持在后期增加主键，故在创建表时就需要设置主键。

复制已有表的数据创建新表

复制表数据

可以使用 CREATE TABLE AS SELECT 语句复制表的数据，但是结构并不完全一致，并且会丢失约束、索引、默认值、分区等信息。

示例

将 staff 表的数据复制到 staff_copy 表。语句如下:

CREATE TABLE staff_copy AS SELECT * FROM staff;

• 表数据对比

  • 原 staff 表数据：

    obclient> select * from staff;
    +----+--------+------+-----+-------------------+----------------------+------------+---------+----------------------------+
    | id | name   | sex  | age | address           | email                | date       | wages   | Entry_time                 |
    +----+--------+------+-----+-------------------+----------------------+------------+---------+----------------------------+
    |  1 | 张三   | 0    |  15 | 杭州市XX小区      | 1********@QQ.com     | 2022-02-22 | 100.000 | 2022-02-25 23:59:59.999990 |
    |  2 | 李四   | 1    |  15 | 北京市XX小区      | 2********@163.com    | 2022-02-28 | 100.000 | 2022-02-27 23:59:59.999990 |
    |  3 | 王五   | 1    |  15 | 上海市XX小区      | 3********@shouhu.com | 2022-02-28 | 100.000 | 2022-04-28 23:59:59.999990 |
    +----+--------+------+-----+-------------------+----------------------+------------+---------+----------------------------+
    3 rows in set
    

  • staff_copy 表数据：

    obclient> select * from staff_copy;
    +----+--------+------+-----+-------------------+----------------------+------------+---------+----------------------------+
    | id | name   | sex  | age | address           | email                | date       | wages   | Entry_time                 |
    +----+--------+------+-----+-------------------+----------------------+------------+---------+----------------------------+
    |  1 | 张三   | 0    |  15 | 杭州市XX小区      | 1********@QQ.com     | 2022-02-22 | 100.000 | 2022-02-25 23:59:59.999990 |
    |  2 | 李四   | 1    |  15 | 北京市XX小区      | 2********@163.com    | 2022-02-28 | 100.000 | 2022-02-27 23:59:59.999990 |
    |  3 | 王五   | 1    |  15 | 上海市XX小区      | 3********@shouhu.com | 2022-02-28 | 100.000 | 2022-04-28 23:59:59.999990 |
    +----+--------+------+-----+-------------------+----------------------+------------+---------+----------------------------+
    3 rows in set
    

• 表结构对比

  • 原 staff 表结构：

    obclient> desc staff;
    +------------+--------------+------+-----+---------+----------------+
    | Field      | Type         | Null | Key | Default | Extra          |
    +------------+--------------+------+-----+---------+----------------+
    | id         | int(11)      | NO   | PRI | NULL    | auto_increment |
    | name       | varchar(18)  | YES  |     | NULL    |                |
    | sex        | char(1)      | YES  |     | 0       |                |
    | age        | int(11)      | NO   |     | NULL    |                |
    | address    | varchar(200) | YES  |     | NULL    |                |
    | email      | varchar(100) | YES  | UNI | NULL    |                |
    | date       | date         | YES  | MUL | NULL    |                |
    | wages      | decimal(7,3) | YES  |     | NULL    |                |
    | Entry_time | timestamp(6) | YES  |     | NULL    |                |
    +------------+--------------+------+-----+---------+----------------+
    9 rows in set
    

  • staff_copy 表结构：

    obclient> desc staff_copy;
    +------------+--------------+------+-----+---------+-------+
    | Field      | Type         | Null | Key | Default | Extra |
    +------------+--------------+------+-----+---------+-------+
    | id         | int(11)      | NO   |     | NULL    |       |
    | name       | varchar(18)  | YES  |     | NULL    |       |
    | sex        | char(1)      | YES  |     | NULL    |       |
    | age        | int(11)      | NO   |     | NULL    |       |
    | address    | varchar(200) | YES  |     | NULL    |       |
    | email      | varchar(100) | YES  |     | NULL    |       |
    | date       | date         | YES  |     | NULL    |       |
    | wages      | decimal(7,3) | YES  |     | NULL    |       |
    | Entry_time | timestamp(6) | YES  |     | NULL    |       |
    +------------+--------------+------+-----+---------+-------+
    9 rows in set
    

说明

通过复制 staff 表数据示例前后对比可以看出，复制表数据后原 staff 表数据和复制后的 staff_copy 表数据完全一致，但 staff_copy 表结构中的约束、索引、默认值等信息则会丢失。

复制表结构

可以使用 CREATE TABLE LIKE 语句复制表结构，但是不能复制表数据。示例：

将 staff 表的表结构复制到 staff_like 表。

语句如下：

CREATE TABLE staff_like like staff;

• 表结构对比

  • 原 staff 表结构：

    obclient> desc staff;
    +------------+--------------+------+-----+---------+----------------+
    | Field      | Type         | Null | Key | Default | Extra          |
    +------------+--------------+------+-----+---------+----------------+
    | id         | int(11)      | NO   | PRI | NULL    | auto_increment |
    | name       | varchar(18)  | YES  |     | NULL    |                |
    | sex        | char(1)      | YES  |     | 0       |                |
    | age        | int(11)      | NO   |     | NULL    |                |
    | address    | varchar(200) | YES  |     | NULL    |                |
    | email      | varchar(100) | YES  | UNI | NULL    |                |
    | date       | date         | YES  | MUL | NULL    |                |
    | wages      | decimal(7,3) | YES  |     | NULL    |                |
    | Entry_time | timestamp(6) | YES  |     | NULL    |                |
    +------------+--------------+------+-----+---------+----------------+
    9 rows in set
    

  • staff_like 表的表结构：

    obclient> desc staff_like;
    +------------+--------------+------+-----+---------+----------------+
    | Field      | Type         | Null | Key | Default | Extra          |
    +------------+--------------+------+-----+---------+----------------+
    | id         | int(11)      | NO   | PRI | NULL    | auto_increment |
    | name       | varchar(18)  | YES  |     | NULL    |                |
    | sex        | char(1)      | YES  |     | 0       |                |
    | age        | int(11)      | NO   |     | NULL    |                |
    | address    | varchar(200) | YES  |     | NULL    |                |
    | email      | varchar(100) | YES  | UNI | NULL    |                |
    | date       | date         | YES  | MUL | NULL    |                |
    | wages      | decimal(7,3) | YES  |     | NULL    |                |
    | Entry_time | timestamp(6) | YES  |     | NULL    |                |
    +------------+--------------+------+-----+---------+----------------+
    9 rows in set
    

• 表行数对比

  • 原 staff 表行数：

    obclient> select count(*) from staff;
    +----------+
    | count(*) |
    +----------+
    |        3 |
    +----------+
    1 row in set
    

  • staff_like 表行数：

    obclient> select count(*) from staff_like;
    +----------+
    | count(*) |
    +----------+
    |        0 |
    +----------+
    1 row in set (0.01 sec)