达梦数据库对现有表创建分区

1.思路

达梦数据库不支持在现有表的基础上创建分区，只能通过创建临时表来转换解决。创建方式如下（数据库名为:LYLP）

2.数据库现有表

例如现在数据库中有一个YP_USER表，DDL如下

CREATE TABLE "LYLP"."YP_USER"
(
	"ID" VARCHAR(32) NOT NULL,
	"USER_NAME" VARCHAR(32),
	"SEX" VARCHAR(10),
	"AGE" INT,
	"CREATE_TIME" TIMESTAMP(0) NOT NULL,
	"LAST_MODIFY_TIME" TIMESTAMP(0),
	"IS_DELETE" BIT NOT NULL,
	NOT CLUSTER PRIMARY KEY("ID")
);

CREATE UNIQUE  INDEX "INDEX5546873216276677" ON "LYLP"."YP_USER"("ID" ASC);

插入数据

INSERT INTO "LYLP"."YP_USER"(ID,USER_NAME,SEX,AGE,CREATE_TIME,LAST_MODIFY_TIME,IS_DELETE) VALUES 
	('1', '张三1','男', 25, '2018-05-05 00:00:00', '2018-05-05 00:00:00', 0),
	('2', '张三2','男', 25, '2019-05-05 00:00:00', '2019-05-05 00:00:00', 0),
	('3', '张三3','男', 25, '2020-05-05 00:00:00', '2020-05-05 00:00:00', 0),
	('4', '张三4','男', 25, '2021-05-05 00:00:00', '2021-05-05 00:00:00', 0),
	('5', '张三5','男', 25, '2022-05-05 00:00:00', '2022-05-05 00:00:00', 0),
	('6', '张三6','男', 25, '2023-05-05 00:00:00', '2023-05-05 00:00:00', 0),
	('7', '张三7','男', 25, '2024-05-05 00:00:00', '2024-05-05 00:00:00', 0),
	('8', '张三8','男', 25, '2025-05-05 00:00:00', '2025-05-05 00:00:00', 0),
	('9', '张三9','男', 25, '2026-05-05 00:00:00', '2026-05-05 00:00:00', 0),
	('10', '张三10','男', 25, '2027-05-05 00:00:00', '2027-05-05 00:00:00', 0),
	('11', '张三11','男', 25, '2028-05-05 00:00:00', '2028-05-05 00:00:00', 0),
	('12', '张三12','男', 25, '2029-05-05 00:00:00', '2029-05-05 00:00:00', 0),
	('13', '张三13','男', 25, '2030-05-05 00:00:00', '2030-05-05 00:00:00', 0),
	('14', '张三14','男', 25, '2031-05-05 00:00:00', '2031-05-05 00:00:00', 0);

commit;

3.创建YP_USER临时表

第一步就是创建一个YP_USER零食表，表明为"YP_USER_TMP"，临时表需要创建分区，DDL如下

	CREATE TABLE "LYLP"."YP_USER_TMP"
(
	"ID" VARCHAR(32) NOT NULL,
	"USER_NAME" VARCHAR(32),
	"SEX" VARCHAR(10),
	"AGE" INT,
	"CREATE_TIME" TIMESTAMP(0) NOT NULL,
	"LAST_MODIFY_TIME" TIMESTAMP(0),
	"IS_DELETE" BIT NOT NULL,
	NOT CLUSTER PRIMARY KEY("ID", "CREATE_TIME")
)
PARTITION BY RANGE (CREATE_TIME)
(
	PARTITION LESSTHAN2019 VALUES LESS THAN (to_date('2019-01-01')),
	PARTITION PAR2019 VALUES LESS THAN (to_date('2020-01-01')),
	PARTITION PAR2020 VALUES LESS THAN (to_date('2021-01-01')),
	PARTITION PAR2021 VALUES LESS THAN (to_date('2022-01-01')),
	PARTITION PAR2022 VALUES LESS THAN (to_date('2023-01-01')),
	PARTITION PAR2023 VALUES LESS THAN (to_date('2024-01-01')),
	PARTITION PAR2024 VALUES LESS THAN (to_date('2025-01-01')),
	PARTITION PAR2025 VALUES LESS THAN (to_date('2026-01-01')),
	PARTITION PAR2026 VALUES LESS THAN (to_date('2027-01-01')),
	PARTITION PAR2027 VALUES LESS THAN (to_date('2028-01-01')),
	PARTITION PAR2028 VALUES LESS THAN (to_date('2029-01-01')),
	PARTITION PAR2029 VALUES LESS THAN (to_date('2030-01-01')),
	PARTITION PAR2030 VALUES LESS THAN (to_date('2031-01-01')),
	PARTITION MORETHAN2030 VALUES EQU OR LESS THAN (MAXVALUE)
);

CREATE UNIQUE  INDEX "INDEX5546873216673678" ON "LYLP"."YP_USER_TMP"("ID" ASC,"CREATE_TIME" ASC);

4.拷贝数据

将"YP_USER"表中的数据拷贝到"YP_USER_TMP"表中，SQL如下

	INSERT INTO "LYLP"."YP_USER_TMP"(ID,USER_NAME,SEX,AGE,CREATE_TIME,LAST_MODIFY_TIME,IS_DELETE) SELECT ID,USER_NAME,SEX,AGE,CREATE_TIME,LAST_MODIFY_TIME,IS_DELETE FROM "LYLP"."YP_USER";

5.修改表名

第一步：将"YP_USER"表名改为"YP_USER_OLD"（或者删除YP_USER表）,SQL如下

ALTER TABLE "LYLP"."YP_USER" RENAME TO YP_USER_OLD;

第二步：将"YP_USER_TMP"表名改为"YP_USER"

ALTER TABLE "LYLP"."YP_USER_TMP" RENAME TO YP_USER;

5.分区验证

SELECT * FROM "LYLP"."YP_USER" PARTITION (LESSTHAN2019);