描述

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

格式

  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|SET NULL}]]
  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] PARTITIONS partition_count [TABLESPACE tablespace] [compression]
  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. subpartition_option:
  85. SUBPARTITION BY HASH (column_name_list) SUBPARTITIONS subpartition_count
  86. | SUBPARTITION BY RANGE (column_name_list) SUBPARTITION TEMPLATE
  87. (range_subpartition_list)
  88. | SUBPARTITION BY LIST (column_name_list) SUBPARTITION TEMPLATE (list_subpartition_list)
  89. range_partition_list:
  90. range_partition [, range_partition ...]
  91. range_partition:
  92. PARTITION [partition_name]
  93. VALUES LESS THAN {(expression_list) | MAXVALUE} [ID = num] [physical_attribute_list] [compression]
  94. range_subpartition_list:
  95. range_subpartition [, range_subpartition ...]
  96. range_subpartition:
  97. SUBPARTITION subpartition_name
  98. VALUES LESS THAN {(expression_list) | MAXVALUE} [physical_attribute_list]
  99. list_partition_list:
  100. list_partition [, list_partition] ...
  101. list_partition:
  102. PARTITION [partition_name] VALUES (DEFAULT|expression_list) [ID num] [physical_attribute_list] [compression]
  103. list_subpartition_list:
  104. list_subpartition [, list_subpartition] ...
  105. list_subpartition:
  106. SUBPARTITION [partition_name] VALUES (DEFAULT|expression_list) [physical_attribute_list]
  107. expression_list:
  108. expression [, expression ...]
  109. column_name_list:
  110. column_name [, column_name ...]
  111. partition_name_list:
  112. partition_name [, partition_name ...]
  113. partition_count | subpartition_count:
  114. INT_VALUE
  115. on_commit_option:
  116. ON COMMIT DELETE ROWS
  117. | 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() locality = 'F,R{all_server}@hz1, F,R{all_server}@hz2,
  2. F,R{all_server}@hz3' DUPLICATE_SCOPE="cluster"
  • 创建带索引的表。
  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);