文档首页/ 数据仓库服务 GaussDB(DWS)/ 工具指南/ DSC/ MySQL语法迁移/ 索引/ 删除索引
更新时间：2024-06-29 GMT+08:00
查看PDF
分享

删除索引

MySQL支持DROP INDEX和ALTER TABLE DROP INDEX两种删除索引的语句。DSC工具迁移时会根据GaussDB(DWS)的特性进行相应适配。

  1. DROP INDEX

    输入示例

     1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
    		 
    CREATE TABLE `test_create_table03` (
	`DEMAND_ID` INT(11) NOT NULL,
	`DEMAND_NAME` CHAR(100) NOT NULL,
	`THEME` VARCHAR(200) NULL DEFAULT NULL,
	`SEND_ID` INT(11) NULL DEFAULT NULL,
	`SEND_NAME` CHAR(20) NULL DEFAULT NULL,
	`SEND_TIME` DATETIME NULL DEFAULT NULL,
	`DEMAND_CONTENT` TEXT NOT NULL
)
COLLATE='utf8_general_ci'
ENGINE=InnoDB;

CREATE UNIQUE INDEX DEMAND_NAME_INDEX ON TEST_CREATE_TABLE03(DEMAND_NAME);
DROP INDEX DEMAND_NAME_INDEX ON TEST_CREATE_TABLE03;

CREATE INDEX SEND_ID_INDEX ON TEST_CREATE_TABLE03(SEND_ID);
DROP INDEX SEND_ID_INDEX ON TEST_CREATE_TABLE03;

    输出示例

     1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
    		 
    CREATE TABLE "public"."test_create_table03"
(
  "demand_id" INTEGER NOT NULL,
  "demand_name" CHAR(400) NOT NULL,
  "theme" VARCHAR(800) DEFAULT NULL,
  "send_id" INTEGER DEFAULT NULL,
  "send_name" CHAR(80) DEFAULT NULL,
  "send_time" TIMESTAMP WITHOUT TIME ZONE DEFAULT NULL,
  "demand_content" TEXT NOT NULL
)
  WITH ( ORIENTATION = ROW, COMPRESSION = NO )
  NOCOMPRESS
  DISTRIBUTE BY HASH ("demand_id");

CREATE INDEX "demand_name_index" ON "public"."test_create_table03" ("demand_name");
DROP INDEX "public"."demand_name_index" RESTRICT;

CREATE INDEX "send_id_index" ON "public"."test_create_table03" USING BTREE ("send_id");
DROP INDEX "public"."send_id_index" RESTRICT;
  2. ALTER TABLE DROP INDEX

    输入示例

     1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
    		 
    CREATE TABLE `test_create_table03` (
	`DEMAND_ID` INT(11) NOT NULL,
	`DEMAND_NAME` CHAR(100) NOT NULL,
	`THEME` VARCHAR(200) NULL DEFAULT NULL,
	`SEND_ID` INT(11) NULL DEFAULT NULL,
	`SEND_NAME` CHAR(20) NULL DEFAULT NULL,
	`SEND_TIME` DATETIME NULL DEFAULT NULL,
	`DEMAND_CONTENT` TEXT NOT NULL
)
COLLATE='utf8_general_ci'
ENGINE=InnoDB;

ALTER TABLE TEST_CREATE_TABLE03 ADD UNIQUE INDEX TEST_CREATE_TABLE03_NAME_INDEX(DEMAND_NAME(50));
ALTER TABLE TEST_CREATE_TABLE03 DROP INDEX TEST_CREATE_TABLE03_NAME_INDEX;

    输出示例

     1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
    		 
    CREATE TABLE "public"."test_create_table03"
(
  "demand_id" INTEGER NOT NULL,
  "demand_name" CHAR(400) NOT NULL,
  "theme" VARCHAR(800) DEFAULT NULL,
  "send_id" INTEGER DEFAULT NULL,
  "send_name" CHAR(80) DEFAULT NULL,
  "send_time" TIMESTAMP WITHOUT TIME ZONE DEFAULT NULL,
  "demand_content" TEXT NOT NULL
)
  WITH ( ORIENTATION = ROW, COMPRESSION = NO )
  NOCOMPRESS
  DISTRIBUTE BY HASH ("demand_id");

CREATE INDEX "test_create_table03_name_index" ON "public"."test_create_table03" ("demand_name");
DROP INDEX "public"."test_create_table03_name_index" RESTRICT;
父主题： 索引