4.2 表的创建和删除

在HybridDB for MySQL中可以通过类似MySQL语法来创建表

1. 创建表

CREATE TABLE db_name.table_name (
    auto_id bigint [NOT NULL] AUTO_INCREMENT,
    col1 boolean [NULL | NOT NULL] [DEFAULT default_value],
    col2 tinyint [NULL | NOT NULL] [DEFAULT default_value],
    col3 smallint [NULL | NOT NULL] [DEFAULT default_value],
    col4 int [NULL | NOT NULL] [DEFAULT default_value],
    col5 bigint [NULL | NOT NULL] [DEFAULT default_value],
    col6 float [NULL | NOT NULL] [DEFAULT default_value],
    col7 double [NULL | NOT NULL] [DEFAULT default_value],
    col8 varchar [NULL | NOT NULL] [DEFAULT default_value],
    col9 timestamp [NULL | NOT NULL] [DEFAULT default_value|current_timestamp],  
    col10 date [NULL | NOT NULL] [DEFAULT default_value|current_date],
    col11 multivalue [NULL | NOT NULL] [DEFAULT default_value] [delimiter_tokenizer ': ,'|nlp_tokenizer 'ik'] [value_type 'varchar int'],
    col12 geo2d [NULL | NOT NULL] [DEFAULT default_value] [delimiter_tokenizer ','],
    [key col3_index(col3)],
    [key col4_index(col4)],
    [clustering key col5_col6_cls_index(col5,col6)],
    [primary key (col1, col3)]
)
DISTRIBUTE BY HASH(col1) | DISTRIBUTE BY BROADCAST
[PARTITION BY VALUE(col9 | YEARMONTHDAY(col9)) PARTITIONS 365]
[INDEX_ALL='Y'|'N']
[ENGINE='CSTORE']

建表详细数据类型支持请参考3.2节, 建表语法详细说明:

  • 1)表名定义

    只能由字母+数字+下划线组成,不支持中文及其他字符,表名最长64个字符。

  • 2)自增类型定义

    auto_id bigint NOT NULL AUTO_INCREMENT 用于定义自增列,必须是bigint类型;HybridDB提供唯一值,但是不能保证单调递增。递增列在建表过程中是可选的,通常用户如果没有比较的分布键,可以通过新增自增列并其做为分区key来达到均匀打散数据的目标。

  • 3) 普通列定义

    col4 int col8 varchar 用于定义整数列和字符串列。需要注意的是,HybridDB对于每个类型默认有精度定义,因此对于MySQL中类似 col8 varchar(8)中的精度定义会忽略掉。列名最长64个字符。

  • 4)一级分区定义 / 事实表

    DISTRIBUTE BY HASH(col1) 用于定义表的分布键,表明该表是事实表(分区表),按照col1的HASH值来做分布键, HybridDB也支持多个字段作为分布键。分布键的选择通常考虑考虑数据分布的均匀(比如auto_id)、事实表之间的join key的对齐(比如按照user_id/order_id等)

  • 5)维度表(广播表)定义

    DISTRIBUTE BY BROADCAST 用于定义表用于定义维度表(广播表),维度表会在每个节点都冗余一份,所以建议维度表的数据量不要太大。

  • 6)二级分区定义

    PARTITION BY VALUE(col9 | YEARMONTHDAY(col9)) PARTITIONS 365 用于定义二级分区, 通常是按照天或者周、月、年等时间单位来定义,PARTITION BY VALUE(col9) 表示按照col11的值来做二级分区值,要求col9必须是数字类型。PARTITION BY YEARMONTHDAY(col9) 表示按照col9格式化为20170101这种日期的数字格式。PARTITIONS 365 表示二级分区最多保留的个数,比如数据保存356天,第366天写入后会自动过期掉第1天的数据。 注意:二级分区自动过期是Shard级别,也就是每个Shard会保留365个分区,因为数据分布不一定均匀,这会导致从表级来看,数据中包含了多余365天的数据,不能基于此来保证无条件查询的范围一定只有365个分区 注意:自动过期的时机不是一定的,属于内部机制,单个Shard也可能超过365个分区,一般数小时会触发一次调度并自动过期掉

  • 7)智能查询索引定义

    key col3_index(col3) 或者 INDEX_ALL='Y' 用于定于智能查询索引,key col3_index(col3)表示给第三列建名称为col3_index的索引。INDEX_ALL='Y'是直接定义所有列都自动索引(全索引),在成本不是特别高数据量不是特别大的情况下,建议通过 INDEX_ALL='Y'一键自动全索引。

  • 8)主键定义

    primary key (col1, col3) 用于定义主键,用于支持Delete和Update操作。需要注意,主键一定要包含分区键,并且顺序上尽可能把分区键放到组合主键定义的前面。

  • 9) 聚集索引定义

    clustering key col5_col6_cls_index(col5,col6) 定义了col5 col6的聚集索引,需要注意的是,聚集索引实际是排序索引,索引的的类型和列的顺序关系严格相关。因此 col5 col6col6 col5是不同的排序索引。聚集索引有助于加速一定会带某个列做为where条件的查询。

  • 10) 多值列定义

    col11 multivalue delimiter_tokenizer ',' value_type 'varchar 定义了只有一个子列的多值列,单个cell的值用,分隔,每个值的类型为varchar,比如值A,B,C; 而col11 multivalue delimiter_tokenizer ': ,' value_type 'varchar int'则定义了有2个子列的多值列,单个cell先用:分隔再用,分隔,比如值: A:100,B:80 。

  • 11). 全文索引列定义

    col11 multivalue nlp_tokenizer 'ik'定义了ik做为分词器的全文索引列。

  • 12). 经纬度列定义

    col12 geo2d delimiter_tokenizer ','定义了col12为经纬度列,经度和纬度的分隔符为,

  • 13)指定引擎

    指定引擎定义,默认CSTORE表示是列式存储引擎

2. 新增和删除列

支持秒级新增列,语法如下:

ALTER TABLE db_name.table_name ADD COLUMN col15 bigint
ALTER TABLE db_name.table_name ADD COLUMN col15 bigint default 1234
ALTER TABLE db_name.table_name ADD COLUMN col15 bigint comment 'col15评论'
ALTER TABLE db_name.table_name ADD COLUMN col15 bigint default 1234 comment 'col15评论'
#修改评论
ALTER TABLE db_name.table_name MODIFY [COLUMN] col15 [column_type] comment 'col15评论'
#修改 NULL/NOT NULL
ALTER TABLE db_name.table_name MODIFY [COLUMN] col15 [column_type] {NULL | NOT NULL}
#修改 DEFAULT
ALTER TABLE db_name.table_name MODIFY [COLUMN] col15 column_type DEFAULT 'col15默认值'
完整:
ALTER TABLE db_name.table_name MODIFY [COLUMN] col15 colunm_type [NULL | NOT NULL] [DEFAULT 'col15默认值'] [COMMENT 'col15评论']

注意:修改DEFAULT值时要把列的值类型加上,目前即使带上了column_type 也不支持修改列类型

暂时 不支持 删除列

3. 新增和删除索引


ALTER TABLE db_name.table_name ADD CLUSTERING KEY c_k(c3, c4);

ALTER TABLE db_name.table_name DROP CLUSTERING KEY c_k;

ALTER TABLE db_name.table_name ADD KEY k(col1);

ALTER TABLE db_name.table_name DROP KEY k;

注: 一张表只支持一个Clustering key

4. 删除表

删除表是同步操作,语法如下:

DROP TABLE db_name.table_name;

5. 清空表

清空表是同步操作,语法如下:

TRUNCATE TABLE db_name.table_name;

6. 清空表的某个分区(二级分区)

清空表的某个分区是同步操作,语法如下:

TRUNCATE TABLE db_name.table_name PARTITION partition_name;

分区名(partition_name)必须是引擎内部统一分区值, 可以通过如下的SQL语句获取某个表的所有分区(此SQL暂时无法获取分区信息,有需要请钉钉联系DBA):

select partition_name from information_schema.partitions where table_name = 'your_table_name' order by partition_name desc limit 100;

表分区(二级分区)清空的举例如下:

truncate table tbl partition 20170103
truncate table tbl partition 20170103,20170104,20170108

7. 表元信息查看

desc table_name

show create table table_name

8. 建表样例

1) 建一个分区表

create table test (
     id bigint auto_increment,
     name varchar,
     value int,
     ts timestamp
)
DISTRIBUTE BY HASH(id)
INDEX_ALL='Y'
ENGINE='CSTORE'

2) 建一个二级分区表

create table test2 (
     id bigint auto_increment,
     name varchar,
     value int,
     ts timestamp
)
DISTRIBUTE BY HASH(id)
PARTITION BY VALUE(YEARMONTHDAY(ts)) PARTITIONS 30
INDEX_ALL='Y'
ENGINE='CSTORE'

9. 注意事项

  • 默认编码为utf-8,相当于MySQL中的utf8mp4;暂不支持其他编码格式
  • DDL不支持表和列的重命名
  • ddl是online的,即在做ddl时无需锁表,查询不受影响
  • 表名建议小写,默认忽略大小写, 表名只接受[0-9a-zA-Z_], 不支持中文
  • 目前支持的最大表数目为10000张

results matching ""

    No results matching ""