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. |