描述

该语句用来在数据库中创建一张新表。

格式

  1. CREATE [GLOBAL TEMPORARY] TABLE table_name
  2. (table_definition_list) [table_option_list] [partition_option] [on_commit_option]
  3. CREATE [GLOBAL TEMPORARY] TABLE table_name
  4. (table_definition_list) [table_option_list] [partition_option] [AS] select;
  5. table_definition_list:
  6. table_definition [, table_definition ...]
  7. table_definition:
  8. column_definition
  9. | INDEX [index_name] index_desc
  10. | [CONSTRAINT [constraint_name]] [PRIMARY KEY|UNIQUE] (column_desc_list) [USING INDEX index_option_list]
  11. | [CONSTRAINT [constraint_name]] FOREIGN KEY (column_name, column_name ...) references_clause constranit_state
  12. | [CONSTRAINT [constraint_name]] CHECK(expression) constranit_state
  13. column_definition_list:
  14. column_definition [, column_definition ...]
  15. column_definition:
  16. column_name data_type
  17. [VISIBLE|INVISIBLE]
  18. {
  19. [DEFAULT expression]
  20. [NULL | NOT NULL]
  21. [CONSTRAINT [constraint_name] [PRIMARY] KEY] [UNIQUE [KEY]]
  22. [CONSTRAINT [constraint_name] CHECK(expression) constranit_state]
  23. [CONSTRAINT [constraint_name] references_clause
  24. |
  25. [GENERATED ALWAYS] AS (expression) [VIRTUAL]
  26. [NULL | NOT NULL] [UNIQUE KEY] [[PRIMARY] KEY] [UNIQUE LOWER_KEY] [COMMENT string]
  27. }
  28. references_clause:
  29. REFERENCES table_name [ (column_name, column_name ...) ] [ON DELETE {CASCADE}]]
  30. constranit_state:
  31. [RELY|NORELY] [USING INDEX index_option_list] [ENABLE|DISABLE] [VALIDATE|NOVALIDATE]
  32. index_desc:
  33. (column_desc_list) [index_option_list]
  34. column_desc_list:
  35. column_desc [, column_desc ...]
  36. column_desc:
  37. column_name [ASC | DESC][NULL LAST|NULL FIRST]
  38. index_option_list:
  39. index_option [ index_option ...]
  40. index_option:
  41. [GLOBAL | LOCAL]
  42. | block_size
  43. | compression
  44. | STORING(column_name_list)
  45. | comment
  46. table_option_list:
  47. table_option [ table_option ...]
  48. table_option:
  49. primary_zone
  50. | replica_num
  51. | table_tablegroup
  52. | block_size
  53. | compression
  54. | comment
  55. | DUPLICATE_SCOPE [=] "none|zone|region|cluster"
  56. | LOCALITY [=] "locality description"
  57. | ENABLE ROW MOVEMENT
  58. | DISABLE ROW MOVEMENT
  59. | physical_attribute
  60. physical_attribute_list:
  61. physical_attribute [physical_attribute]
  62. physical_attribute:
  63. PCTFREE [=] num
  64. | PCTUSED num
  65. | INITRANS num
  66. | MAXTRANS num
  67. | STORAGE(storage_option [storage_option] ...)
  68. | TABLESPACE tablespace
  69. compression:
  70. NOCOMPRESS
  71. | COMPRESS { BASIC | FOR OLTP | FOR QUERY [LOW|HIGH] | FOR ARCHIVE [LOW|HIGH]}
  72. storage_option:
  73. INITIAL_ num [K|M|G|T|P|E]
  74. | NEXT num [K|M|G|T|P|E]
  75. | MINEXTENTS num [K|M|G|T|P|E]
  76. | MAXEXTENTS num [K|M|G|T|P|E]
  77. partition_option:
  78. PARTITION BY HASH(column_name_list)
  79. [subpartition_option] hash_partition_define
  80. | PARTITION BY RANGE (column_name_list)
  81. [subpartition_option] (range_partition_list)
  82. | PARTITION BY LIST (column_name_list)
  83. [subpartition_option] (list_partition_list)
  84. /*模板化二级分区*/
  85. subpartition_option:
  86. SUBPARTITION BY HASH (column_name_list) hash_subpartition_define
  87. | SUBPARTITION BY RANGE (column_name_list) SUBPARTITION TEMPLATE
  88. (range_subpartition_list)
  89. | SUBPARTITION BY LIST (column_name_list) SUBPARTITION TEMPLATE
  90. (list_subpartition_list)
  91. /*非模板化二级分区*/
  92. subpartition_option:
  93. SUBPARTITION BY HASH (column_name_list)
  94. | SUBPARTITION BY RANGE (column_name_list)
  95. | SUBPARTITION BY LIST (column_name_list)
  96. subpartition_list:
  97. (hash_subpartition_list)
  98. | (range_subpartition_list)
  99. | (list_subpartition_list)
  100. hash_partition_define:
  101. PARTITIONS partition_count [TABLESPACE tablespace] [compression]
  102. | (hash_partition_list)
  103. hash_partition_list:
  104. hash_partition [, hash_partition, ...]
  105. hash_partition:
  106. partition [partition_name] [subpartition_list/*仅非模板化二级分区可定义*/]
  107. hash_subpartition_define:
  108. SUBPARTITIONS subpartition_count
  109. | SUBPARTITION TEMPLATE (hash_subpartition_list)
  110. hash_subpartition_list:
  111. hash_subpartition [, hash_subpartition, ...]
  112. hash_subpartition:
  113. subpartition [subpartition_name]
  114. range_partition_list:
  115. range_partition [, range_partition ...]
  116. range_partition:
  117. PARTITION [partition_name]
  118. VALUES LESS THAN {(expression_list) | (MAXVALUE)}
  119. [subpartition_list/*仅非模板化二级分区可定义*/]
  120. [ID = num] [physical_attribute_list] [compression]
  121. range_subpartition_list:
  122. range_subpartition [, range_subpartition ...]
  123. range_subpartition:
  124. SUBPARTITION subpartition_name
  125. VALUES LESS THAN {(expression_list) | MAXVALUE} [physical_attribute_list]
  126. list_partition_list:
  127. list_partition [, list_partition] ...
  128. list_partition:
  129. PARTITION [partition_name]
  130. VALUES (DEFAULT|expression_list)
  131. [subpartition_list/*仅非模板化二级分区可定义*/]
  132. [ID num] [physical_attribute_list] [compression]
  133. list_subpartition_list:
  134. list_subpartition [, list_subpartition] ...
  135. list_subpartition:
  136. SUBPARTITION [partition_name] VALUES (DEFAULT|expression_list) [physical_attribute_list]
  137. expression_list:
  138. expression [, expression ...]
  139. column_name_list:
  140. column_name [, column_name ...]
  141. partition_name_list:
  142. partition_name [, partition_name ...]
  143. partition_count | subpartition_count:
  144. INT_VALUE
  145. on_commit_option:
  146. ON COMMIT DELETE ROWS
  147. | ON COMMIT PRESERVE ROWS

参数说明

参数

描述

DUPLICATE_SCOPE

用来指定复制表属性,取值如下:

  • none:表示该表是一个普通表

  • zone:表示该表是一个复制表,leader 需要将事务复制到本 zone 的所有 F 副本及 R 副本

  • region:表示该表是一个复制表,leader 需要将事务复制到本 region 的所有 F 副本及 R 副本

  • cluster:表示该表是一个复制表,leader 需要将事务复掉到 cluster 的所有 F 副本及 R 副本

不指定 DUPLICATE_SCOPE 的情况下,默认值为 none。

BLOCK_SIZE

指定表的微块大小

COMPRESSION

指定存储格式 flat/encoding 以及压缩方法,对应如下:

  • nocompress:flat 格式, none 压缩

  • compress [basic]:flat 格式, lz4_1.0 压缩

  • compress for oltp:flat 格式, zstd_1.0 压缩

  • query [low|high]:encoding 格式, lz4_1.0 压缩

  • archive [low|high]:encoding 格式, zstd_1.0 压缩

primary_zone

指定主 Zone(副本 Leader 所在 Zone)。

replica_num

指定副本数。

table_tablegroup

指定表所属的 talegroup。

comment

注释。

LOCALITY

描述副本在 Zone 间的分布情况,如:F@z1,F@z2,F@z3,R@z4 表示 z1, z2, z3 为全功能副本,z4 为只读副本。

physical_attribute

PCTFREE: 指定宏块保留空间百分比

其它属性:STORAGE, TABLESPACE 等仅为了语法兼容方便迁移,不生效

ENABLE/DISABLE ROW MOVEMENT

是否允许更会致行在不同分区间移动的分区键更新

ON COMMIT DELETE ROWS

事务级临时表:提交时删除数据

ON COMMIT PRESERVE ROWS

会话级临时表:会话结束时删除数据

示例

  • 创建数据库表。
  1. CREATE TABLE test (c1 INT PRIMARY KEY, c2 VARCHAR(50)) REPLICA_NUM = 3, PRIMARY_ZONE = 'zone1';
  • 创建一个复制表。
  1. CREATE TABLE item (i_id INT
  2. , i_name VARCHAR(24)
  3. , i_price DECIMAL(5,2)
  4. , i_data VARCHAR(50)
  5. , i_im_id INT
  6. , PRIMARY KEY(i_id)) COMPRESS FOR QUERY pctfree=0 BLOCK_SIZE=16384
  7. DUPLICATE_SCOPE='cluster' LOCALITY='F@zone1, F@zone2,R{all_server}@zone3' primary_zone='zone1';
  • 创建带索引的表。
  1. create table t1 (c1 int primary key, c2 int, c3 int, index i1 (c2));
  • 创建 hash 分区,分区数为 8 的表。
  1. create table t1 (c1 int primary key, c2 int) partition by hash(c1) partitions 8;
  • 创建一级分区为 range 分区,二级分区为 hash 分区的表。
  1. create table t1 (c1 int, c2 int, c3 int)
  2. partition by range(c1) subpartition by hash(c2) subpartitions 5
  3. (partition p0 values less than(0), partition p1 values less than(100));
  • 开启 encoding 并使用 zstd 压缩,宏块保留空间为 5%。
  1. create table t1 (c1 int, c2 int, c3 varchar(64))
  2. COMPRESS FOR ARCHIVE
  3. PCTFREE 5;
  • 创建一个事务级临时表。
  1. create global temporary table t1 (c1 int) on commit delete rows ;
  • 创建一个带约束的表。
  1. create table t1 (c1 int, c2 int, c3 int, CONSTRAINT equal_check CHECK(c2 = c3 * 2) ENABLE VALIDATE);
  • 创建非模板化的二级分区表。
  1. create table t_range_range1 (c1 int, c2 int, c3 int) partition by range(c1)
  2. subpartition by range (c2)
  3. (
  4. partition p0 values less than (100)
  5. (
  6. subpartition p0_r1 values less than (100),
  7. subpartition p0_r2 values less than (200),
  8. subpartition p0_r3 values less than (300)
  9. ),
  10. partition p1 values less than (200)
  11. (
  12. subpartition p1_r1 values less than (100),
  13. subpartition p1_r2 values less than (200),
  14. subpartition p1_r3 values less than (300)
  15. ),
  16. partition p2 values less than (300)
  17. (
  18. subpartition p2_r1 values less than (100),
  19. subpartition p2_r2 values less than (200),
  20. subpartition p2_r3 values less than (300)
  21. )
  22. );