CREATE TABLE · k8s原理与实践

[TOC] # CREATE TABLE ## 描述该语句用来在数据库中创建一张新表。 ## 格式 ~~~ CREATE [TEMPORARY] TABLE [IF NOT EXISTS] table_name (table_definition_list) [table_option_list] [partition_option] [AS] select; CREATE [TEMPORARY] 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 | {INDEX | KEY} [index_name] index_desc | FULLTEXT [INDEX | KEY] [index_name] fulltext_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 index_desc: (column_desc_list) [index_type] [index_option_list] fulltext_index_desc: (column_desc_list) CTXCAT(column_desc_list) [index_option_list] 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 ~~~ ## 参数说明 <table data-tag="table" id="table-rrd-ygw-7cn" class="table"><colgroup width="240" span="1" data-tag="col" id="col-t19-57u-vdh" colwidth="1*" colnum="1" colname="col1" style="width:50%" class="col"></colgroup><colgroup width="477" span="1" data-tag="col" id="col-a7i-e69-3dw" colwidth="1*" colnum="2" colname="col2" style="width:50%" class="col"></colgroup><thead id="thead-qqf-yy9-a84" class="thead"><tr id="tr-p45-xjs-x0x"><th id="td-979-5j6-hlm">参数</th><th id="td-9ff-z3a-dlr">描述</th></tr></thead><tbody data-tag="tbody" id="tbody-uvx-wcf-jli" class="tbody"><tr data-tag="tr" id="tr-l7m-rb6-z2c" class="tr"><td data-tag="td" id="td-pm9-si4-rog" class="td">DUPLICATE_SCOPE</td><td data-tag="td" id="td-s64-n3t-2b5" class="td">用来指定复制表属性，取值如下：<ul lake-indent="0" data-tag="ul" id="ul-f38-ctv-l8n" class="ul"><li data-tag="li" id="li-o75-k0w-ujf" class="li">none：表示该表是一个普通表</li><li data-tag="li" id="li-lq7-5x4-2o2" class="li">zone：表示该表是一个复制表，leader需要将事务复制到本zone的所有F副本及R副本</li><li data-tag="li" id="li-go0-5qf-q2i" class="li">region：表示该表是一个复制表，leader需要将事务复制到本region的所有F副本及R副本</li><li data-tag="li" id="li-dfc-ajd-v6l" class="li">cluster：表示该表是一个复制表，leader需要将事务复掉到cluster的所有F副本及R副本</li></ul>不指定DUPLICATE_SCOPE的情况下，默认值为none。</td></tr><tr data-tag="tr" id="tr-0qz-lfh-2ha" class="tr"><td data-tag="td" id="td-0te-5xa-akv" class="td">ROW_FORMAT</td><td data-tag="td" id="td-5s0-8p0-tk4" class="td">指定表是否开启encoding存储格式<ul lake-indent="0" data-tag="ul" id="ul-x0g-8sc-qjg" class="ul"><li data-tag="li" id="li-zhe-whr-rs5" class="li"> redundant</li></ul><ul lake-indent="1" data-tag="ul" id="ul-60j-9wi-yrg" class="ul"><ul lake-indent="0" data-tag="ul" id="ul-tds-6m0-uxu" class="ul"><li data-tag="li" id="li-vzn-r5j-moy" class="li">不开启encoding存储格式</li></ul></ul><ul lake-indent="0" data-tag="ul" id="ul-8u2-xqg-6up" class="ul"><li data-tag="li" id="li-6s2-7c0-r4a" class="li"> compact</li></ul><ul lake-indent="1" data-tag="ul" id="ul-2ii-zpg-6zv" class="ul"><ul lake-indent="0" data-tag="ul" id="ul-iiv-zgk-4nb" class="ul"><li data-tag="li" id="li-rur-7jw-gms" class="li">不开启encoding存储格式</li></ul></ul><ul lake-indent="0" data-tag="ul" id="ul-hz8-871-knn" class="ul"><li data-tag="li" id="li-yhh-asw-vgk" class="li"> dynamic</li></ul><ul lake-indent="1" data-tag="ul" id="ul-xes-a81-ras" class="ul"><ul lake-indent="0" data-tag="ul" id="ul-628-zoz-ysq" class="ul"><li data-tag="li" id="li-ogt-oop-cyn" class="li">encoding存储格式</li></ul></ul><ul lake-indent="0" data-tag="ul" id="ul-42q-3ww-gai" class="ul"><li data-tag="li" id="li-uhq-mbo-tyw" class="li">compressed</li></ul><ul lake-indent="1" data-tag="ul" id="ul-xqp-uti-n3q" class="ul"><ul lake-indent="0" data-tag="ul" id="ul-3eq-jio-1cp" class="ul"><li data-tag="li" id="li-mzm-oq5-cem" class="li">encoding存储格式</li></ul></ul><ul lake-indent="0" data-tag="ul" id="ul-o9j-o65-y03" class="ul"><li data-tag="li" id="li-sf4-n5g-xm9" class="li"> default</li></ul><ul lake-indent="1" data-tag="ul" id="ul-sec-2ak-s3m" class="ul"><ul lake-indent="0" data-tag="ul" id="ul-jvg-4lz-vvm" class="ul"><li data-tag="li" id="li-q49-n1w-ovu" class="li">等价dynamic模式</li></ul></ul></td></tr><tr data-tag="tr" id="tr-xen-12q-txd" class="tr"><td data-tag="td" id="td-gd3-bzo-siq" class="td">BLOCK_SIZE</td><td data-tag="td" id="td-ljp-8vj-h4t" class="td">指定表的微块大小</td></tr><tr data-tag="tr" id="tr-sfx-3lt-yx9" class="tr"><td data-tag="td" id="td-sx3-ljv-01o" class="td">COMPRESSION</td><td data-tag="td" id="td-udo-pqy-czw" class="td">指定表的压缩算法，取值如下：<ol start="1" lake-indent="0" data-tag="ol" id="ol-lu3-o4s-gco" class="ol"><li data-tag="li" id="li-es2-nqk-r1r" class="li">none：不使用压缩算法</li><li data-tag="li" id="li-jeq-qhg-j7z" class="li">lz4_1.0: 使用lz4压缩算法</li><li data-tag="li" id="li-k6m-cnn-3lu" class="li">zstd_1.0: 使用zstd压缩算法</li><li data-tag="li" id="li-bqy-ko3-ku7" class="li">snappy_1.0: 使用snappy压缩算法</li></ol></td></tr><tr data-tag="tr" id="tr-of7-6lr-p6a" class="tr"><td data-tag="td" id="td-zlt-uii-hac" class="td">CHARSET | CHARACTER SET</td><td data-tag="td" id="td-jad-avj-3ak" class="td">指定表中列的默认字符集，可使用：utf8, utf8mb4, gbk, utf16, gb18030</td></tr><tr data-tag="tr" id="tr-wra-wgi-k5a" class="tr"><td data-tag="td" id="td-bxj-3oh-jxn" class="td">COLLATE</td><td data-tag="td" id="td-r53-f12-3tq" class="td">指定表中列的默认比较规则，可使用：utf8_bin, utf8_general_ci, utf8_unicode_ci, gbk_bin, gbk_chinese_ci, utf8mb4_general_ci, utf8mb4__general_cs, utf8mb4_bin, utf8mb4_unicode_ci, utf16_general_ci, utf16_bin, utf16_unicode_ci, gb18030_chinese_ci, gb18030_bin</td></tr><tr data-tag="tr" id="tr-oww-qa5-vfp" class="tr"><td data-tag="td" id="td-hx8-g3n-uq2" class="td">primary_zone</td><td data-tag="td" id="td-6j2-m11-hbd" class="td">指定主Zone（副本Leader所在 Zone）。</td></tr><tr data-tag="tr" id="tr-qg7-rys-doq" class="tr"><td data-tag="td" id="td-w3z-wui-fn8" class="td">replica_num</td><td data-tag="td" id="td-4cz-dke-yy9" class="td">指定副本数。</td></tr><tr data-tag="tr" id="tr-tqj-t0e-87i" class="tr"><td data-tag="td" id="td-nbe-iib-n7q" class="td">table_tablegroup</td><td data-tag="td" id="td-hb6-ypj-vrb" class="td">指定表所属的talegroup。</td></tr><tr data-tag="tr" id="tr-is4-qaz-lkc" class="tr"><td data-tag="td" id="td-cll-nwb-taz" class="td">AUTO_INCREMENT</td><td data-tag="td" id="td-cfw-m2o-u0f" class="td">指定表中自增列的初始值。</td></tr><tr data-tag="tr" id="tr-mko-o1z-l19" class="tr"><td data-tag="td" id="td-78b-0km-r6h" class="td">comment</td><td data-tag="td" id="td-rh3-toi-nqn" class="td">注释。</td></tr><tr data-tag="tr" id="tr-a7c-xzn-vhn" class="tr"><td data-tag="td" id="td-dkb-k14-j3a" class="td">LOCALITY</td><td data-tag="td" id="td-vn0-j6d-hh6" class="td">描述副本在Zone间的分布情况，如：F@z1,F@z2,F@z3,R@z4 表示z1, z2, z3为全功能副本，z4为只读副本。</td></tr><tr data-tag="tr" id="tr-qox-sqq-0gz" class="tr"><td data-tag="td" id="td-iqr-1ow-dyf" class="td">PCTFREE</td><td data-tag="td" id="td-ebh-l20-gkj" class="td">指定宏块保留空间百分比。</td></tr><tr data-tag="tr" id="tr-x65-dzv-3z2" class="tr"><td id="td-hzs-nyc-x1k">parallel_clause</td><td id="td-uru-2en-9kl">指定表级别的并行度：<ul id="ul-7br-akd-ily"><li id="li-r7c-dyt-0d4">NOPARALLEL：并行度为1，默认配置</li><li id="li-mnp-v04-mnk">PARALLEL integer：指定并行度，integer 取值大于等于 1。</li></ul></td></tr></tbody></table> ## 举例 * 创建数据库表。 ~~~ CREATE TABLE test (c1 int primary key, c2 VARCHAR(50)) REPLICA_NUM = 3, PRIMARY_ZONE = 'zone1'; ~~~ * 创建表，并指定垂直分区。其中第一个分区只有列 c3，第二个分区包含列 c1、c2，没有列出的 c4、c5 列为第三个分区。 ~~~ CREATE TABLE t1(c1 int, c2 int, c3 int, c4 int, c5 int) PARTITION BY CLOUMN ( c3, (c1, c2)); ~~~ * 创建一个复制表。 ~~~ CREATE TABLE item() locality = 'F,R{all_server}@hz1, F,R{all_server}@hz2, F,R{all_server}@hz3' DUPLICATE_SCOPE="cluster" ~~~ * 创建带索引的表。 ~~~ create table t1 (c1 int primary key, c2 int, c3 int, index i1 (c2)); ~~~ * 创建 hash 分区，分区数为 8 的表。 ~~~ create table t1 (c1 int primary key, c2 int) partition by hash(c1) partitions 8; ~~~ * 创建一级分区为 range 分区，二级分区为 key 分区的表。 ~~~ create table t1 (c1 int, c2 int, c3 int) partition by range(c1) subpartition by key(c2, c3) subpartitions 5 (partition p0 values less than(0), partition p1 values less than(100)); ~~~ * 创建一列为 gbk, 一列为 utf8 的表。 ~~~ create table t1 (c1 varchar(10), c2 varchar(10) charset gbk collate gbk_bin) default charset utf8 collate utf8mb4_general_ci; ~~~ * 开启 encoding 并使用 zstd 压缩，宏块保留空间为 5%。 ~~~ create table t1 (c1 int, c2 int, c3 varchar(64)) compression 'zstd_1.0' ROW_FORMAT dynamic pctfree 5; ~~~ * 创建表 t1，并设置并行度为 3。 ~~~ create table t1(c1 int primary key, c2 int) parallel 3; ~~~