文档首页 > > 工具指南> DSC SQL语法迁移工具> SQL语法迁移参考> MySQL语法迁移> FULLTEXT全文索引

FULLTEXT全文索引

分享
更新时间: 2020/08/05 GMT+08:00

GaussDB A 不支持 FULLTEXT全文索引。DSC工具迁移时会根据GaussDB的特性进行相应适配。

  1. 内联FULLTEXT全文索引

    输入示例

    ## A.
    CREATE TABLE `public`.`test_create_table02` (
    	`ID` INT(11) NOT NULL PRIMARY KEY,
    	`TITLE` CHAR(255) NOT NULL,
    	`CONTENT` TEXT NULL,
    	`CREATE_TIME` DATETIME NULL DEFAULT NULL,
    	 FULLTEXT (`CONTENT`)
    );
    
    ## B. 
    CREATE TABLE IF NOT EXISTS `public`.`runoob_dataType_test`
    (
      `id` INT PRIMARY KEY AUTO_INCREMENT,
      `name` VARCHAR(128) NOT NULL,
      FULLTEXT INDEX (name)
    );
    
    ## C.
    CREATE TABLE IF NOT EXISTS `public`.`runoob_dataType_test`
    (
      `id` INT PRIMARY KEY AUTO_INCREMENT,
      `name` VARCHAR(128) NOT NULL,
      FULLTEXT INDEX (name ASC)
    );

    输出示例

    -- A.
    CREATE TABLE "public"."test_create_table02"
    (
      "id" INTEGER(11) NOT NULL PRIMARY KEY,
      "title" CHAR(255) NOT NULL,
      "content" TEXT,
      "create_time" TIMESTAMP WITHOUT TIME ZONE DEFAULT NULL
    )
      WITH ( ORIENTATION = ROW, COMPRESSION = NO )
      NOCOMPRESS
      DISTRIBUTE BY HASH ("id");
    CREATE INDEX "idx_test_create_table02_content" ON "public"."test_create_table02" USING GIN(to_tsvector(coalesce("content",'')));
    
    -- B. 
    CREATE TABLE "public"."runoob_datatype_test"
    (
      "id" SERIAL PRIMARY KEY,
      "name" VARCHAR(128) NOT NULL
    )
      WITH ( ORIENTATION = ROW, COMPRESSION = NO )
      NOCOMPRESS
      DISTRIBUTE BY HASH ("id");
    CREATE INDEX "idx_runoob_datatype_test_name" ON "public"."runoob_datatype_test" USING GIN(to_tsvector(coalesce("name",'')));
    
    -- C.
    CREATE TABLE "public"."runoob_datatype_test"
    (
      "id" SERIAL PRIMARY KEY,
      "name" VARCHAR(128) NOT NULL
    )
      WITH ( ORIENTATION = ROW, COMPRESSION = NO )
      NOCOMPRESS
      DISTRIBUTE BY HASH ("id");
    CREATE INDEX "idx_runoob_datatype_test_name" ON "public"."runoob_datatype_test" USING GIN(to_tsvector(coalesce("name",'')));
  2. ALTER TABLE创建全文索引

    输入示例

    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 FULLTEXT INDEX USER_ID_INDEX_02(USER_ID);
    ALTER TABLE TEST_CREATE_TABLE05 ADD FULLTEXT USER_NAME_INDEX_02(USER_NAME);

    输出示例

    CREATE TABLE "public"."test_create_table05"
    (
      "id" SERIAL NOT NULL,
      "user_id" INTEGER(20) NOT NULL,
      "user_name" CHAR(20) DEFAULT NULL,
      "detail" VARCHAR(100) DEFAULT NULL,
      PRIMARY KEY ("id")
    )
      WITH ( ORIENTATION = ROW, COMPRESSION = NO )
      NOCOMPRESS
      DISTRIBUTE BY HASH ("id");
    CREATE INDEX "user_id_index_02" ON "public"."test_create_table05" USING GIN(to_tsvector(coalesce("user_id",'')));
    CREATE INDEX "user_name_index_02" ON "public"."test_create_table05" USING GIN(to_tsvector(coalesce("user_name",'')));
  3. CREATE INDEX创建全文索引

    输入示例

    CREATE TABLE `public`.`test_index_table02` (
    	`ID` INT(11) NOT NULL PRIMARY KEY,
    	`TITLE` CHAR(255) NOT NULL,
    	`CONTENT` TEXT NULL,
    	`CREATE_TIME` INT(10) NULL DEFAULT NULL
    );
    CREATE FULLTEXT INDEX CON_INDEX ON TEST_INDEX_TABLE02(CONTENT);

    输出示例

    CREATE TABLE "public"."test_index_table02"
    (
      "id" INTEGER(11) NOT NULL PRIMARY KEY,
      "title" CHAR(255) NOT NULL,
      "content" TEXT,
      "create_time" INTEGER(10) DEFAULT NULL
    )
      WITH ( ORIENTATION = ROW, COMPRESSION = NO )
      NOCOMPRESS
      DISTRIBUTE BY HASH ("id");
    CREATE INDEX "con_index" ON "public"."test_index_table02" USING GIN(to_tsvector(coalesce("content",'')));
分享:

    相关文档

    相关产品

文档是否有解决您的问题?

提交成功!

非常感谢您的反馈,我们会继续努力做到更好!

反馈提交失败,请稍后再试!

*必选

请至少选择或填写一项反馈信息

字符长度不能超过200

提交反馈 取消

如您有其它疑问,您也可以通过华为云社区问答频道来与我们联系探讨

智能客服提问云社区提问