文档首页/ 数据仓库服务 GaussDB(DWS)/ 工具指南/ DSC/ MySQL语法迁移/ 表（可选参数、操作）/ PARTITION BY
更新时间：2024-06-29 GMT+08:00
查看PDF
分享

PARTITION BY

在MySQL中，PARTITION BY用于创建分区表。GaussDB(DWS)目前仅对MySQL中的RANGE，LIST分区进行支持。

对于PARTITION BY的HASH分区，DSC暂不支持该特性的完整迁移，将其移除。对于表的当前功能暂时没有影响，性能方面可能存在些许差异。

PARTITION BY RANGE

输入示例

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
 
CREATE TABLE IF NOT EXISTS `runoob_tbl_part_test`(
  `runoob_id` INT NOT NULL,
  `runoob_title` VARCHAR(100) NOT NULL,
  `runoob_author` VARCHAR(40) NOT NULL,
  `submission_date` VARCHAR(30)
)ENGINE=InnoDB DEFAULT CHARSET=utf8
PARTITION BY RANGE (`runoob_id`)(
    PARTITION p0 VALUES LESS THAN(100),
    PARTITION p1 VALUES LESS THAN(200),
    PARTITION p2 VALUES LESS THAN(300),
    PARTITION p3 VALUES LESS THAN(400),
    PARTITION p4 VALUES LESS THAN(500),
    PARTITION p5 VALUES LESS THAN (MAXVALUE)
);

ALTER TABLE `runoob_tbl_part_test` ADD PARTITION (PARTITION p6 VALUES LESS THAN (600));

ALTER TABLE `runoob_tbl_part_test` ADD PARTITION (PARTITION p7 VALUES LESS THAN (700),PARTITION p8 VALUES LESS THAN (800));

输出示例

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
 
CREATE TABLE IF NOT EXISTS "public"."runoob_tbl_part_test" (
  "runoob_id" INTEGER NOT NULL,
  "runoob_title" VARCHAR(400) NOT NULL,
  "runoob_author" VARCHAR(160) NOT NULL,
  "submission_date" VARCHAR(120)
) WITH (ORIENTATION = ROW, COMPRESSION = NO) NOCOMPRESS DISTRIBUTE BY HASH ("runoob_id") PARTITION BY RANGE ("runoob_id") (
  PARTITION p0
  VALUES
    LESS THAN (100),
    PARTITION p1
  VALUES
    LESS THAN (200),
    PARTITION p2
  VALUES
    LESS THAN (300),
    PARTITION p3
  VALUES
    LESS THAN (400),
    PARTITION p4
  VALUES
    LESS THAN (500),
    PARTITION p5
  VALUES
    LESS THAN (MAXVALUE)
);
ALTER TABLE "public"."runoob_tbl_part_test" ADD PARTITION p6 VALUES LESS THAN (600);
ALTER TABLE "public"."runoob_tbl_part_test" ADD PARTITION p7 VALUES LESS THAN (700), ADD PARTITION p8 VALUES LESS THAN (800);

PARTITION BY LIST

输入示例

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
 
CREATE TABLE IF NOT EXISTS `runoob_tbl_part_test`(
    `runoob_id` INT NOT NULL,
    `runoob_title` VARCHAR(100) NOT NULL,
    `runoob_author` VARCHAR(40) NOT NULL,
    `submission_date` VARCHAR(30),
    PRIMARY KEY (`runoob_id`)
    )ENGINE=InnoDB DEFAULT CHARSET=utf8
    PARTITION BY LIST (runoob_id)(
    PARTITION r0 VALUES IN (1, 5, 9, 13, 17, 21),
    PARTITION r1 VALUES IN (2, 6, 10, 14, 18, 22),
    PARTITION r2 VALUES IN (3, 7, 11, 15, 19, 23),
    PARTITION r3 VALUES IN (4, 8, 12, 16, 20, 24)
    );
ALTER TABLE `runoob_tbl_part_test` ADD PARTITION (PARTITION p5 VALUES IN(30, 40, 50, 60, 70, 80));

输出示例

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
 
CREATE TABLE IF NOT EXISTS "public"."runoob_tbl_part_test" (
  "runoob_id" INTEGER NOT NULL,
  "runoob_title" VARCHAR(400) NOT NULL,
  "runoob_author" VARCHAR(160) NOT NULL,
  "submission_date" VARCHAR(120),
  PRIMARY KEY ("runoob_id")
) WITH (ORIENTATION = ROW, COMPRESSION = NO) NOCOMPRESS DISTRIBUTE BY HASH ("runoob_id") PARTITION BY LIST (runoob_id) (
  PARTITION r0
  VALUES
    (1, 5, 9, 13, 17, 21),
    PARTITION r1
  VALUES
    (2, 6, 10, 14, 18, 22),
    PARTITION r2
  VALUES
    (3, 7, 11, 15, 19, 23),
    PARTITION r3
  VALUES
    (4, 8, 12, 16, 20, 24)
);
ALTER TABLE "public"."runoob_tbl_part_test" ADD PARTITION p5 VALUES (30, 40, 50, 60, 70, 80);

相关文档