语法格式

• 创建表。

  1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20

  CREATE [ [ GLOBAL | LOCAL ] { TEMPORARY | TEMP } | UN LOG GED ] TABLE [ IF NOT EXISTS ] table_name { ( { column_name data_type [ CHARACTER SET | CHARSET charset ] [ compress_mode ] [ COLLATE collation ] [ column_constraint [ ... ] ] | table_constraint | LIKE source_table [ like_option [...] ] } [, ... ] ) | LIKE source_table } [ table_option [ [ , ] ... ] ] [ htap_option ] [ WITH ( {storage_parameter = value} [, ... ] ) ] [ ON COMMIT { PRESERVE ROWS | DELETE ROWS } ] [ ILM ADD POLICY ROW STORE COMPRESS ADVANCED ROW AFTER n { day | month | year } OF NO MODIFICATION [ ON ( EXPR )]] [ TABLESPACE tablespace_name ] [ DISTRIBUTE BY { REPLICATION | HASH ( column_name [, ...] ) | MURMURHASH ( expression ) | KMEANS ( column_name ) distance_type | RANGE ( column_name [, ...] ) { SLICE REFEREN CES tablename | ( slice_less_than_item [, ...] ) | ( slice_start_end_item [, ...] ) } | LIST ( column_name [, ...] ) { SLICE REFERENCES tablename | ( slice_values_item [, ...] ) } } ] [ TO { GROUP groupname | NODE ( nodename [, ... ] ) } ];

  • 其中table_option为：

    { COMMENT [ = ] 'string' |
      AUTO_INCREMENT [ = ] value |
      [ DEFAULT ] CHARACTER SET | CHARSET [ = ] default_charset |
      [ DEFAULT ] COLLATE [ = ] default_collation |
      ENGINE [ = ] { InnoDB | 'InnoDB' | "InnoDB" } }

  • 其中htap_option为：

    { COLVIEW [ PRIORITY { HIGH | LOW | NONE } ] |
      NOCOLVIEW [ PRIORITY { HIGH | LOW | NONE } ]}

  • 其中列约束column_constraint为：

    1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17

    [ CONSTRAINT constraint_name ] { NOT NULL | NULL | CHECK ( expression ) | DEFAULT default_expr | ON UPDATE update_expr | GENERATED ALWAYS AS ( generation_expr ) [STORED] | AUTO_INCREMENT | COMMENT 'string' | COLVIEW | NOCOLVIEW | UNIQUE [KEY] index_parameters | PRIMARY KEY index_parameters | ENCRYPTED WITH ( COLUMN_ENCRYPTION_KEY = column_encryption_key, ENCRYPTION_TYPE = encryption_type_value ) | REFERENCES reftable [ ( refcolumn ) ] [ MATCH FULL | MATCH PARTIAL | MATCH SIMPLE ] [ ON DELETE action ] [ ON UPDATE action ] } [ DEFERRABLE | NOT DEFERRABLE | INITIALLY DEFERRED | INITIALLY IMMEDIATE ]

  • 其中列的压缩可选项compress_mode为：

    1	{ DELTA | PREFIX | DICTIONARY | NUMSTR | NOCOMPRESS }

  • 其中表约束table_constraint为：

    1 2 3 4 5 6

    [ CONSTRAINT [ constraint_name ] ] { CHECK ( expression ) | UNIQUE [ index_name ] [ USING method ] ( { { column_name [ ( length ) ] | ( expression ) } [ ASC | DESC ] }[, ... ] ) index_parameters | PRIMARY KEY [ USING method ] ( { column_name [ ASC | DESC ] } [, ... ] ) index_parameters [ DEFERRABLE | NOT DEFERRABLE | INITIALLY DEFERRED | INITIALLY IMMEDIATE ] { [ COMMENT 'string' ] [ ... ] }

  • 其中like选项like_option为：

    1	{ INCLUDING | EXCLUDING } { DEFAULTS | GENERATED | CONSTRAINTS | INDEXES | STORAGE | COMMENTS | PARTITION | RELOPTIONS | DISTRIBUTION | UPDATE | ILM | ALL }

  • 其中索引参数index_parameters为：

    1 2	[ WITH ( {storage_parameter = value} [, ... ] ) ] [ USING INDEX TABLESPACE tablespace_name ]

  • 其中距离参数distance_type为：

    1	{ L2 | COSINE | HAMMING }

  • 其中RANGE分布规则

    slice_less_than_item为：
    SLICE name VALUES LESS THAN ({ expression | MAXVALUE } [, ...]) [ DATANODE datanode_name | ( datanode_name_list [, ... ] ) ]
    slice_start_end_item为：
    SLICE name {
    { START ( expression ) END ( expression ) EVERY ( expression ) } |
    { START ( literal ) END ( { literal | MAXVALUE } ) } |
    { START ( literal ) } |
    { END ( { literal | MAXVALUE } ) }
    }

  • 其中LIST分布规则slice_values_item为：

    [ ( SLICE name VALUES (expression [, ... ]) [DATANODE datanode_name | ( datanode_name_list )]
     [, ... ] ) |
      ( SLICE name VALUES (DEFAULT) [DATANODE datanode_name] | ( datanode_name_list ) )
    ]

  • 其中update_expr为：

    { CURRENT_TIMESTAMP | LOCALTIMESTAMP | NOW() }