AnalyticDB for MySQL allows you to execute SQL statements to define the data type of a column as MULTIVALUE for implementing full-text search. The tokenizer type is nlp_tokenizer.

Note Full-text search in SQL works by using the tokenizer, whereas the LIKE predicate is used to perform fuzzy matches by using indexes created for string columns.

Create a table

Execute the following SQL statement to create a table named TEST3, define the data type of the text column as MULTIVALUE, and set the tokenizer type to nlp_tokenizer. 

CREATE TABLE test3 (
    user_id BIGINT,
    city VARCHAR,
    text MULTIVALUE nlp_tokenizer 'ik' value_type 'varchar'
)
DISTRIBUTE BY HASH (user_id)

Write data to the table

Execute the following SQL statements to write data to the TEST3 table.

insert into test3 values(1, 'HZ', 'People's Republic of China');
insert into test3 values(2, 'BJ', 'AnalyticDB for MySQL V3.0 is a next-generation online analytical processing (OLAP) database.');
insert into test3 values(3, 'SH', 'hello, world');
select * from test3 order by user_id;
+---------+------+--------------------------------+
| user_id | city | text                           |
+---------+------+------------------------------- +
|       1 | HZ   |People's Republic of China                   |
|       2 | BJ   |AnalyticDB for MySQL V3.0 is a next-generation OLAP database.|
|       3 | SH   | hello, world                   |

Perform a search

Note By default, all uppercase letters are converted to lowercase letters when a full-text search is executed. 
select * from test3 where text in ('China', 'database');
+---------+------+--------------------------------+
| user_id | city | text                           |
+---------+------+--------------------------------+
|       1 | HZ   | People's Republic of China                   |
|       2 | BJ   |AnalyticDB for MySQL V3.0 is a next-generation OLAP database.|
select * from test3 where text in ('hello') and city ! = 'HZ';
+---------+------+--------------+
| user_id | city | text         |
+---------+------+--------------+
|       3 | SH   | hello, world |
select * from test3 where ref(text,0) in ('analyticdb');
+---------+------+-----------------------------------+
| user_id | city | text                              |
+---------+------+-----------------------------------+
|       2 | BJ   |AnalyticDB for MySQL V3.0 is a next-generation OLAP database.   |
# You can also use the LIKE predicate to query common string columns.
mysql> select * from test3 where city like '%J%';
+---------+------+-----------------------------------+
| user_id | city | text                              |
+---------+------+-----------------------------------+
|       2 | BJ   |AnalyticDB for MySQL V3.0 is a next-generation OLAP database.   |