CockroachDB SQL开发基础 ——创建表的更多有趣示例 (2)

     在上一篇文章中，介绍了CockroachDB中创建表的更多一些示例，包括：“主键”、“外键”、“Check约束”这些SQL标准中的特性用法；还结合示例介绍了如何创建带有计算列和能够映射KV存储表的方法。本文将介绍CockroachDB中创建表时的另外一些示例，包括：创建带有二级索引和倒排索引的表、创建带有ID值自动生成的表（3种方式）、从Select语句创建表以及创建具有多列族的表（这是CRDB自己的扩展）。

1. 创建带有二级索引和倒排索引的表

     在CRDB中，能够在创建表的同时创建二级索引或倒排索引。二级索引就是传统数据库中的非主键索引；在CRDB中倒排索引用于为类型是JSON的字段创建索引，以提高在JSON类型字段上检索的性能。

      我们看一个例子，这个例子还综合了先前介绍的检查约束和缺省值等特性：

CREATE TABLE product_information( 
    product_id           INT PRIMARY KEY, 
    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 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;

   

2. 从Select语句创建表

    可以执行CREATE TABLE AS语句从SELECT FROM语句执行的结果集创建一个表。让看如下的示例：

    CREATE TABLE customers_ny AS SELECT * FROM customers WHERE state = 'NY';
 

3. 创建带有自动生成ID值的表

    在CRDB中，创建带有自动生成ID值的表有三种方式，下面我们就结合示例说明这三种方式的用法：

   (1) 使用UUID列，并以gen_random_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;

        查询结果如下图：

    (2) 使用SERIAL数据类型

         在CRDB中，SERIAL数据类型实际是INT类型并带有unique_rowid()函数作为缺 省值）的别名。我们看如下示例：

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

        实际上相当于：

        CREATE TABLE t21 (id INT PRIMARY KEY DEFAULT unique_rowid(), name STRING);

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

        SELECT * FROM t2;

        查询结果如下图：

    (3) 使用SEQUENCE对象

    我们看如下示例：

    CREATE SEQUENCE customer_seq;

    CREATE TABLE customer_list (id INT PRIMARY KEY DEFAULT nextval('customer_seq'), 
       customer string, 
       address string
    );

    INSERT INTO customer_list (customer, address) VALUES 
        ('Lauren', '123 Main Street'),('Jesse', '456 Broad Ave'),('Amruta', '9876 Green Parkway');

    SELECT * FROM customer_list;

   查询结果如下图：

4. 创建具有多列族的表

    当我们在CRDB中创建一个表时，表的所有列都被存储在底层KV存储的一个列族中。这种缺省方式在大多上情况下是高效的。但是，当频繁更新的列与很少更新的列在同一个列族中时，很少更新的列也会在每次更新时被重写。特别是当很少进行更新的列长度比较大时，把它们拆分放在另外的列族中是一种更好的方式。

    为了在表创建时，指定不同的列族，需要使用FAMILY关键字，我们看一个示例：

 CREATE TABLE test(
    id INT PRIMARY KEY,
    last_accessed TIMESTAMP,
    data BYTES,
    FAMILY f1 (id, last_accessed),
    FAMILY f2 (data)
);

    在test表中，data字段用来存储blob数据，last_accessed字段用来存储数据被最近一次访问的时间戳（这个字段的值会比较频繁地更新）。由于blob数据一般来说很少改变，所以我们把这两种不同访问特点的字段分别放在两个列族中。这种方式可以看作“行列混合”的表。

 

参考链接：

    https://www.cockroachlabs.com/docs/stable/create-table.html

    https://www.cockroachlabs.com/docs/stable/column-families.html

    https://www.cockroachlabs.com/docs/stable/sql-faqs.html#how-do-i-auto-generate-unique-row-ids-in-cockroachdb