Full-text index

In the MySQL mode of OceanBase Database, you can create full-text indexes on columns of the CHAR, VARCHAR, and TEXT types. In addition, you can create multiple full-text indexes in the primary table and on the same column.

Full-text indexes can be created on both partitioned and non-partitioned tables, regardless of whether they have a primary key. The limitations for creating full-text indexes are as follows:

• You can create full-text indexes only on columns of the CHAR, VARCHAR, and TEXT types.
• In the current version of OceanBase Database, you can create only local full-text indexes.
• When you create a full-text index, you cannot specify the UNIQUE keyword.
• To create a full-text index across multiple columns, you must ensure that these columns use the same character set.

Based on these syntax and rules, you can use the full-text index feature of OceanBase Database to efficiently search and retrieve text data.

DML operations

You can perform complex DML operations such as INSERT INTO ON DUPLICATE KEY, REPLACE INTO, updates and deletions on multiple tables, and updates on updatable views on tables that contain full-text indexes.

Here are some examples:

• INSERT INTO ON DUPLICATE KEY:

  INSERT INTO articles VALUES ('OceanBase', 'Fulltext search index support insert into on duplicate key')
      ON DUPLICATE KEY UPDATE title = 'OceanBase 4.3.3';
  

• REPLACE INTO:

  REPLACE INTO articles(title, context) VALUES ('Oceanbase 4.3.3', 'Fulltext search index support replace');
  

• Updates and deletions on multiple tables.

  1. Create a table named tbl1.

     CREATE TABLE tbl1 (a int PRIMARY KEY, b text, FULLTEXT INDEX(b));
     

  2. Create a table named tbl2.

     CREATE TABLE tbl2 (a int PRIMARY KEY, b text);
     

  3. Execute an UPDATE statement on multiple tables.

     UPDATE tbl1 JOIN tbl2 ON tbl1.a = tbl2.a 
         SET tbl1.b = 'dddd', tbl2.b = 'eeee';
     

     UPDATE  tbl1 JOIN tbl2 ON tbl1.a = tbl2.a SET tbl1.b = 'dddd';
     

     UPDATE tbl1 JOIN tbl2 ON tbl1.a = tbl2.a SET tbl2.b = tbl1.b;
     

  4. Execute a DELETE statement on multiple tables.

     DELETE tbl1, tbl2 FROM tbl1 JOIN tbl2 ON tbl1.a = tbl2.a;
     

     DELETE tbl1 FROM tbl1 JOIN tbl2 ON tbl1.a = tbl2.a;
     

     DELETE tbl1 FROM tbl1 JOIN tbl2 ON tbl1.a = tbl2.a;
     

• DML operations on updatable views.

  1. Create a view named fts_view.

     CREATE VIEW fts_view AS SELECT * FROM tbl1;
     

  2. Execute an INSERT statement on the updatable view.

     INSERT INTO fts_view VALUES(3, 'cccc'), (4, 'dddd');
     

  3. Execute an UPDATE statement on the updatable view.

     UPDATE fts_view SET b = 'dddd';
     

     UPDATE fts_view JOIN normal ON fts_view.a = tbl2.a
        SET fts_view.b = 'dddd', tbl2.b = 'eeee';
     

  4. Execute a DELETE statement on the updatable view.

     DELETE FROM fts_view WHERE b = 'dddd';
     

     DELETE tbl1 FROM fts_view JOIN tbl1 ON fts_view.a = tbl1.a AND 1 = 0;
     

References

For more information about how to create a full-text index, see the Create a full-text index section in Create an index.