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

普通索引和前缀索引

GaussDB(DWS)不支持前缀索引，也不支持内联普通索引。DSC工具迁移时会根据GaussDB(DWS)的特性将其迁移为普通索引。

  1. 内联普通(前缀)索引。

    输入示例

    1
2
3
4
5
6
    		 
    CREATE TABLE IF NOT EXISTS `public`.`runoob_dataType_test`
(
  `id` INT PRIMARY KEY AUTO_INCREMENT,
  `name` VARCHAR(128) NOT NULL,
  INDEX index_single(name(10))
);

    输出示例

    1
2
3
4
5
6
7
8
9
    		 
    CREATE TABLE IF NOT EXISTS "public"."runoob_datatype_test"
(
  "id" SERIAL PRIMARY KEY,
  "name" VARCHAR(512) NOT NULL
)
  WITH ( ORIENTATION = ROW, COMPRESSION = NO )
  NOCOMPRESS
  DISTRIBUTE BY HASH ("id");
CREATE INDEX "index_single" ON "public"."runoob_datatype_test" USING BTREE ("name");
  2. ALTER TABLE创建普通(前缀)索引。

    输入示例

    1
2
3
4
5
6
7
8
9
    		 
    CREATE TABLE `public`.`test_create_table05` (
 `ID` INT(11) NOT NULL AUTO_INCREMENT,
 `USER_ID` INT(20) NOT NULL,
 `USER_NAME` CHAR(20) NULL DEFAULT NULL,
 `DETAIL` VARCHAR(100) NULL DEFAULT NULL,
 PRIMARY KEY (`ID`)
);

ALTER TABLE TEST_CREATE_TABLE05 ADD INDEX USER_NAME_INDEX_02(USER_NAME(10));

    输出示例

     1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
    		 
    CREATE TABLE "public"."test_create_table05"
(
  "id" SERIAL NOT NULL,
  "user_id" INTEGER NOT NULL,
  "user_name" CHAR(80) DEFAULT NULL,
  "detail" VARCHAR(400) DEFAULT NULL,
  PRIMARY KEY ("id")
)
  WITH ( ORIENTATION = ROW, COMPRESSION = NO )
  NOCOMPRESS
  DISTRIBUTE BY HASH ("id");

CREATE INDEX "user_name_index_02" ON "public"."test_create_table05" ("user_name");
  3. CREATE INDEX创建普通(前缀)索引。

    输入示例

    1
2
3
4
5
6
7
    		 
    CREATE TABLE IF NOT EXISTS `public`.`customer`(
    `name` varchar(64) primary key,
	id integer,
	id2 integer
);

CREATE INDEX part_of_name ON customer (name(10));

    输出示例

     1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
    		 
    CREATE TABLE IF NOT EXISTS "public"."customer"
(
  "name" VARCHAR(256) PRIMARY KEY,
  "id" INTEGER,
  "id2" INTEGER
)
  WITH ( ORIENTATION = ROW, COMPRESSION = NO )
  NOCOMPRESS
  DISTRIBUTE BY HASH ("name");

CREATE INDEX "part_of_name" ON "public"."customer" USING BTREE ("name");
父主题： 索引