CREATE TABLE用于在数据库创建新表。

所需权限

用户必须具有父数据库的CREATE权限。

概要

基础

图片

扩展

图片

图片

基于SELECT语句的结果创建表,请使用CREATE TABLE AS

参数

Parameter Description
IF NOT EXISTS 仅当数据库中不存在同名表时才创建新表; 如果确实存在,则不返回错误。

请注意,IF NOT EXISTS仅检查表名; 它不检查现有表是否具有新表的相同列,索引,约束等。
table_name 要创建的表的名称,在其数据库中必须是唯一的,并遵循identifier rules. 如果没有设置默认父数据库,则必须将名称格式设置为database.name

UPSERTINSERT ON CONFLICT语句使用名为excluded的临时表来处理执行期间的唯一性冲突。 因此,不建议对任何表使用名称excluded
column_def 定义列的逗号分隔列表。 每列需要名称/标识符和数据类型; 列级约束或其他列限定(例如计算列)的指定是可选项。 列名在表中必须是唯一的,但可以与索引或约束具有相同的名称。

在列级别定义的任何主键,唯一和检查约束将作为表创建的一部分移动到表级别。 使用SHOW CREATE TABLE语句在表级别查看它们。
index_def 可选项,定义索引的逗号分隔列表。 对于每个索引,必须指定要索引的列; 可选择指定名称。 索引名称在表中必须是唯一的,并遵循标识符规则。 请参阅下面的创建具有辅助索引和倒排索引的表示例。

CREATE INDEX语句可用于创建索引,与建表分开。
family_def 可选项,定义列族的逗号分隔列表。 列族名称在表中必须是唯一的,但可以与列,约束或索引具有相同的名称。

列族是一组列,它们作为单个键值对存储在基础键值存储中。 CockroachDB自动将列分组到列族中,以确保有效的存储和性能。 但是,有时你可能希望手动将列分配给族,更多详细信息,请参见列族文档。
table_constraint 可选项,表级约束的逗号分隔列表。 约束名称在表中必须是唯一的,但可以与列,列族或索引具有相同的名称。
opt_interleave 你可以通过交错表来优化查询性能,这会更改CockroachDB存储数据的方式。
opt_partition_by New in v2.0: 一个仅限企业版的选项,允许你在行级别定义表分区。 你可以按列表或按range定义表分区。 有关更多信息,请参阅定义表分区文章。

表级复制

默认情况下,表在默认复制区域中创建,但可以放入特定的复制区域。 有关更多信息,请参阅为表创建复制区

行级复制(New in v2.0)

CockroachDB允许企业用户定义表分区,从而提供行级控制数据的存储方式和位置。 有关更多信息,请参见为表分区创建复制区

分区所需的主键与传统的主键不同。 要定义用于分区的主键,将主键中的唯一标志符作为前缀,按照你想要嵌套子分区的顺序,添加到你想要进行分区和子分区的表的所有列。 有关详细信息,请参阅使用主键进行分区

示例

创建表(没有定义主键)

在CockroachDB中,每个表都需要一个主键。 如果未明确定义,则会自动添加INT类型的名为rowid的列作为主键,并使用unique_rowid()函数确保新行始终默认为唯一的rowid值。 自动为主键创建索引。

严格地说,主键的唯一索引并没有创建; 它由数据存储层的key来区分,因此不需要额外的空间。 但是,在使用SHOW INDEX等命令时,它会显示为正常的唯一索引。

> CREATE TABLE logon (
    user_id INT,
    logon_date DATE
);

> SHOW COLUMNS FROM logon;
+------------+------+------+---------+---------+
|   Field    | Type | Null | Default | Indices |
+------------+------+------+---------+---------+
| user_id    | INT  | true | NULL    | {}      |
| logon_date | DATE | true | NULL    | {}      |
+------------+------+------+---------+---------+
(2 rows)
> SHOW INDEX FROM logon;
+-------+---------+--------+-----+--------+-----------+---------+----------+
| Table |  Name   | Unique | Seq | Column | Direction | Storing | Implicit |
+-------+---------+--------+-----+--------+-----------+---------+----------+
| logon | primary | true   |   1 | rowid  | ASC       | false   | false    |
+-------+---------+--------+-----+--------+-----------+---------+----------+
(1 row)

创建表(定义主键)

在此示例中,我们创建一个包含三列的表。 一列是主键,另一列是唯一约束,第三列没有约束。 具有唯一约束的主键和列将自动创建索引。

> CREATE TABLE logoff (
    user_id INT PRIMARY KEY,
    user_email STRING UNIQUE,
    logoff_date DATE
);

> SHOW COLUMNS FROM logoff;
+-------------+--------+-------+---------+---------------------------------+
|    Field    |  Type  | Null  | Default |             Indices             |
+-------------+--------+-------+---------+---------------------------------+
| user_id     | INT    | false | NULL    | {primary,logoff_user_email_key} |
| user_email  | STRING | true  | NULL    | {logoff_user_email_key}         |
| logoff_date | DATE   | true  | NULL    | {}                              |
+-------------+--------+-------+---------+---------------------------------+
(3 rows)
> SHOW INDEX FROM logoff;
+--------+-----------------------+--------+-----+------------+-----------+---------+----------+
| Table  |         Name          | Unique | Seq |   Column   | Direction | Storing | Implicit |
+--------+-----------------------+--------+-----+------------+-----------+---------+----------+
| logoff | primary               | true   |   1 | user_id    | ASC       | false   | false    |
| logoff | logoff_user_email_key | true   |   1 | user_email | ASC       | false   | false    |
| logoff | logoff_user_email_key | true   |   2 | user_id    | ASC       | false   | true     |
+--------+-----------------------+--------+-----+------------+-----------+---------+----------+
(3 rows)

创建具有二级索引和倒排索引的表(New in v2.0)

在此示例中,我们在表创建期间创建两个二级索引。 二级索引允许使用除主键之外的键有效地访问数据。 此示例还演示了许多列级和表级约束。

倒排索引是v2.0中新增的功能,允许有效访问JSONB列中的无schema数据。

此示例还演示了许多列级和表级约束:

> CREATE TABLE product_information (
    product_id           INT PRIMARY KEY NOT NULL,
    product_name         STRING(50) UNIQUE NOT NULL,
    product_description  STRING(2000),
    category_id          STRING(1) NOT NULL CHECK (category_id IN ('A','B','C')),
    weight_class         INT,
    warranty_period      INT CONSTRAINT valid_warranty CHECK (warranty_period BETWEEN 0 AND 24),
    supplier_id          INT,
    product_status       STRING(20),
    list_price           DECIMAL(8,2),
    min_price            DECIMAL(8,2),
    catalog_url          STRING(50) UNIQUE,
    date_added           DATE DEFAULT CURRENT_DATE(),
    misc                 JSONB,     
    CONSTRAINT price_check CHECK (list_price >= min_price),
    INDEX date_added_idx (date_added),
    INDEX supp_id_prod_status_idx (supplier_id, product_status),
    INVERTED INDEX details (misc)
);

> SHOW INDEX FROM product_information;
+---------------------+--------------------------------------+--------+-----+----------------+-----------+---------+----------+
|        Table        |                 Name                 | Unique | Seq |     Column     | Direction | Storing | Implicit |
+---------------------+--------------------------------------+--------+-----+----------------+-----------+---------+----------+
| product_information | primary                              | true   |   1 | product_id     | ASC       | false   | false    |
| product_information | product_information_product_name_key | true   |   1 | product_name   | ASC       | false   | false    |
| product_information | product_information_product_name_key | true   |   2 | product_id     | ASC       | false   | true     |
| product_information | product_information_catalog_url_key  | true   |   1 | catalog_url    | ASC       | false   | false    |
| product_information | product_information_catalog_url_key  | true   |   2 | product_id     | ASC       | false   | true     |
| product_information | date_added_idx                       | false  |   1 | date_added     | ASC       | false   | false    |
| product_information | date_added_idx                       | false  |   2 | product_id     | ASC       | false   | true     |
| product_information | supp_id_prod_status_idx              | false  |   1 | supplier_id    | ASC       | false   | false    |
| product_information | supp_id_prod_status_idx              | false  |   2 | product_status | ASC       | false   | false    |
| product_information | supp_id_prod_status_idx              | false  |   3 | product_id     | ASC       | false   | true     |
| product_information | details                              | false  |   1 | misc           | ASC       | false   | false    |
| product_information | details                              | false  |   2 | product_id     | ASC       | false   | true     |
+---------------------+--------------------------------------+--------+-----+----------------+-----------+---------+----------+
(12 rows)

关于索引还可阅读以下资料:

使用自动生成的唯一行ID创建表

要自动生成唯一行ID,请使用带有gen_random_uuid()函数的UUID列作为默认值:

CREATE TABLE t1 (id UUID PRIMARY KEY DEFAULT gen_random_uuid(), name STRING);

INSERT INTO t1 (name) VALUES ('a'), ('b'), ('c');

SELECT * FROM t1;
+--------------------------------------+------+
|                  id                  | name |
+--------------------------------------+------+
| 60853a85-681d-4620-9677-946bbfdc8fbc | c    |
| 77c9bc2e-76a5-4ebc-80c3-7ad3159466a1 | b    |
| bd3a56e1-c75e-476c-b221-0da9d74d66eb | a    |
+--------------------------------------+------+
(3 rows)

或者,可以使用带有uuid_v4()函数的BYTES列作为默认值:

CREATE TABLE t2 (id BYTES PRIMARY KEY DEFAULT uuid_v4(), name STRING);

INSERT INTO t2 (name) VALUES ('a'), ('b'), ('c');

SELECT * FROM t2;

+---------------------------------------------------+------+
|                        id                         | name |
+---------------------------------------------------+------+
| "\x9b\x10\xdc\x11\x9a\x9cGB\xbd\x8d\t\x8c\xf6@vP" | a    |
| "\xd9s\xd7\x13\n_L*\xb0\x87c\xb6d\xe1\xd8@"       | c    |
| "\uac74\x1dd@B\x97\xac\x04N&\x9eBg\x86"           | b    |
+---------------------------------------------------+------+
(3 rows)

在任何一种情况下,生成的ID都是128位,足够大所以几乎不会生成重复值。 此外,一旦表增长超出单个kv range(默认情况下超过64MB),新ID将分散在所有表的range内,因此可能跨越不同的节点。 这意味着多个节点将共担负载。

如果有必要将生成的ID存储在相同的kv range内,可以使用SERIAL数据类型,它是INT的别名,默认值是unique_rowid()

CREATE TABLE t3 (id SERIAL PRIMARY KEY, name STRING);

INSERT INTO t3 (name) VALUES ('a'), ('b'), ('c');

SELECT * FROM t3;

+--------------------+------+
|         id         | name |
+--------------------+------+
| 293807573840855041 | a    |
| 293807573840887809 | b    |
| 293807573840920577 | c    |
+--------------------+------+
(3 rows)

在insert时,unique_rowid()函数从执行insert的节点的时间戳和ID生成默认值。 除非在每个节点每秒生成非常大量的ID(100,000+)的情况下,否则这种时间排序的值可认为是全局唯一的。

创建具有外键约束的表

外键约束保证列仅使用它引用的列中已存在的值,该列必须来自另一个表。 此约束强制实现两个表之间的引用完整性(referential integrity)。

管理外键有许多规则,但最重要的两个是:

New in v2.0: 你可以包含一个外键操作,来当引用的列发生更新或删除时执行的操作。 默认操作是ON UPDATE NO ACTIONON DELETE NO ACTION

在这个例子中,我们使用ON DELETE CASCADE(当删除由外键约束引用的行时,也删除所有相关行)。

> CREATE TABLE customers (
    id INT PRIMARY KEY,
    name STRING
  );
> CREATE TABLE orders (
    id INT PRIMARY KEY,
    customer_id INT REFERENCES customers(id) ON DELETE CASCADE
  );
> SHOW CREATE TABLE orders;
+--------+---------------------------------------------------------------------------------------------------------------------+
| Table  |                                                     CreateTable                                                     |
+--------+---------------------------------------------------------------------------------------------------------------------+
| orders | CREATE TABLE orders (␤                                                                                              |
|        |     id INT NOT NULL,␤                                                                                               |
|        |     customer_id INT NULL,␤                                                                                          |
|        |     CONSTRAINT "primary" PRIMARY KEY (id ASC),␤                                                                     |
|        |     CONSTRAINT fk_customer_id_ref_customers FOREIGN KEY (customer_id) REFERENCES customers (id) ON DELETE CASCADE,␤ |
|        |     INDEX orders_auto_index_fk_customer_id_ref_customers (customer_id ASC),␤                                        |
|        |     FAMILY "primary" (id, customer_id)␤                                                                             |
|        | )                                                                                                                   |
+--------+---------------------------------------------------------------------------------------------------------------------+
> INSERT INTO customers VALUES (1, 'Lauren');
> INSERT INTO orders VALUES (1,1);
> DELETE FROM customers WHERE id = 1;
> SELECT * FROM orders;
+----+-------------+
| id | customer_id |
+----+-------------+
+----+-------------+

创建一个KV存储镜像的表

CockroachDB是一个基于事务性和强一致性的KV存储的分布式SQL数据库。 虽然无法直接访问KV存储,但你可以使用两列“简单”表直接镜像访问,其中一组作为主键:

CREATE TABLE kv (k INT PRIMARY KEY, v BYTES);

当这样的“简单”表没有索引或外键时,INSERT / UPSERT / UPDATE / DELETE语句转换为开销最小的kv操作(single digit percent slowdowns)。 例如,以下用于在表中添加或替换行的UPSERT将转换为单个键值Put操作:

UPSERT INTO kv VALUES (1, b'hello')

此SQL表方法还为你提供定义明确的查询语言,已知的事务模型,以及在需要时向表中添加更多列的灵活性。

SELECT语句创建一个表

你可以使用CREATE TABLE AS语句从SELECT语句的结果创建一个新表,例如:

> SELECT * FROM customers WHERE state = 'NY';
+----+---------+-------+
| id |  name   | state |
+----+---------+-------+
|  6 | Dorotea | NY    |
| 15 | Thales  | NY    |
+----+---------+-------+
> CREATE TABLE customers_ny AS SELECT * FROM customers WHERE state = 'NY';

> SELECT * FROM customers_ny;
+----+---------+-------+
| id |  name   | state |
+----+---------+-------+
|  6 | Dorotea | NY    |
| 15 | Thales  | NY    |
+----+---------+-------+

使用计算列创建表( New in v2.0

在这个例子中,让我们创建一个带有计算列的简单表:

CREATE TABLE names (
    id INT PRIMARY KEY,
    first_name STRING,
    last_name STRING,
    full_name STRING AS (CONCAT(first_name, ' ', last_name)) STORED
  );

然后插入几行数据:

INSERT INTO names (id, first_name, last_name) VALUES
    (1, 'Lola', 'McDog'),
    (2, 'Carl', 'Kimball'),
    (3, 'Ernie', 'Narayan');
SELECT * FROM names;
+----+------------+-------------+----------------+
| id | first_name |  last_name  |   full_name    |
+----+------------+-------------+----------------+
|  1 | Lola       | McDog       | Lola McDog     |
|  2 | Carl       | Kimball     | Carl Kimball   |
|  3 | Ernie      | Narayan     | Ernie Narayan  |
+----+------------+-------------+----------------+

full_name列是从first_namelast_name列计算的,无需定义视图。

使用分区创建表(New in v2.0)

分区所需的主键与传统的主键不同。 要定义用于分区的主键,将主键中的唯一标志符作为前缀,按照你想要嵌套子分区的顺序,添加到你想要进行分区和子分区的表的所有列。 有关详细信息,请参阅使用主键进行分区

按列表创建包含分区的表

在此示例中,我们创建一个表并按列表定义分区:

> CREATE TABLE students_by_list (
    id SERIAL,
    name STRING,
    email STRING,
    country STRING,
    expected_graduation_date DATE,   
    PRIMARY KEY (country, id))
    PARTITION BY LIST (country)
      (PARTITION north_america VALUES IN ('CA','US'),
      PARTITION australia VALUES IN ('AU','NZ'),
      PARTITION DEFAULT VALUES IN (default));

使用range创建包含分区的表

在此示例中,我们创建一个表并按range定义分区。

> CREATE TABLE students_by_range (
   id SERIAL,
   name STRING,
   email STRING,                                                                                           
   country STRING,
   expected_graduation_date DATE,                                                                                      
   PRIMARY KEY (expected_graduation_date, id))
   PARTITION BY RANGE (expected_graduation_date)
      (PARTITION graduated VALUES FROM (MINVALUE) TO ('2017-08-15'),
      PARTITION current VALUES FROM ('2017-08-15') TO (MAXVALUE));

显示表的定义

要显示表的定义,请使用SHOW CREATE TABLE语句。 CreateTable列的内容是一个包含嵌入换行符的字符串,在回显时会生成格式化输出。

> SHOW CREATE TABLE logoff;
+--------+----------------------------------------------------------+
| Table  |                       CreateTable                        |
+--------+----------------------------------------------------------+
| logoff | CREATE TABLE logoff (␤                                   |
|        |     user_id INT NOT NULL,␤                               |
|        |     user_email STRING(50) NULL,␤                         |
|        |     logoff_date DATE NULL,␤                              |
|        |     CONSTRAINT "primary" PRIMARY KEY (user_id),␤         |
|        |     UNIQUE INDEX logoff_user_email_key (user_email),␤    |
|        |     FAMILY "primary" (user_id, user_email, logoff_date)␤ |
|        | )                                                        |
+--------+----------------------------------------------------------+
(1 row)

See Also