MySQL full-text search usage examples

-- Check the version of MySQL mysql> select version();
+-----------+
| version() |
+-----------+
| 5.7.33 |
+-----------+
1 row in set (0.02 sec)

n=1: 'you', 'good', 'world', 'world' 
n=2: 'Hello', 'Good World', 'World' 
n=3: 'Hello world', 'Good world' 
n=4: 'Hello World'

# /etc/mysql/mysql.conf.d/mysqld.cnf

ft_min_word_len = 2
ngram_token_size = 2


# If not, add configuration echo 'ft_min_word_len = 2
ngram_token_size = 2' >> mysqld.cnf

# Restart service /etc/init.d/mysql restart

-- View the configuration of mysql> 
SHOW VARIABLES LIKE 'ft_min_word_len';
SHOW VARIABLES LIKE 'ngram_token_size';
+-----------------+-------+
| Variable_name | Value |
+-----------------+-------+
| ft_min_word_len | 2 |
+-----------------+-------+
1 row in set (0.02 sec)
 
+------------------+-------+
| Variable_name | Value |
+------------------+-------+
| ngram_token_size | 2 |
+------------------+-------+
1 row in set (0.03 sec)

-- Demo of mysql for full-text search

mysql> CREATE TABLE `articles` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `title` varchar(50) DEFAULT NULL COMMENT 'Subject',
  `content` longtext NOT NULL COMMENT 'content',
  PRIMARY KEY (`id`),
  FULLTEXT KEY `title_content_index` (`content`,`title`) /*!50100 WITH PARSER `ngram` */ 
) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=utf8;
Query OK, 0 rows affected (0.20 sec)
 

mysql> INSERT INTO articles (`title`, `content`) VALUES
        ('If', 'I will never think of you again in this life, except on some nights wet with tears, if you want'),
        ('Love', 'One day the road sign is moved, I hope you can be calm. One day the bridge pier is broken, I hope you can cross. One day the pillar falls, I hope you can be strong. One day the expectation fades, I hope you can understand'),
        ('Far and near', 'You look at me for a while and then at the clouds. I feel that when you look at me, I am very far away, and when you look at the clouds, you are very close'),
        ('Fragment', 'You stand on the bridge and look at the scenery,
The people who are watching the scenery are watching you from upstairs.
The bright moon adorns your window,
You decorate other people's dreams. '),
        ('Solo', 'I tell you I miss you like a stone statue, silence is not right. If silence is your sadness, you know this sadness hurts me the most');

Query OK, 5 rows affected (0.08 sec)
Records: 5 Duplicates: 0 Warnings: 0
 
mysql> SELECT * from articles where match(content, title) against('风景' in NATURAL LANGUAGE MODE) LIMIT 10;
+----+--------+--------------------------------------------------------------------------------------------------------------------------+
| id | title | content |
+----+--------+--------------------------------------------------------------------------------------------------------------------------+
| 10 | Fragment | You stand on the bridge and look at the scenery.
The people who are watching the scenery are watching you from upstairs.
The bright moon adorns your window,
You decorate other people's dreams. |
+----+--------+--------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.02 sec)

-- Natural language mode (NATURAL LANGUAGE MODE) query and get the score mysql> SELECT id, title, MATCH ( content, title ) against ( '风景' IN NATURAL LANGUAGE MODE ) AS score FROM articles;
+----+-----------+--------------------+
| id | title | score |
+----+-----------+--------------------+
| 7 | if | 0 |
| 8 | Love | 0 |
| 9 | Far and Near | 0 |
| 10 | Fragment | 0.9771181344985962 |
| 11 | Soliloquy | 0 |
+----+-----------+--------------------+
5 rows in set (0.02 sec)

-- BOOLEAN MODE can be used to combine queries mysql> SELECT id, title FROM articles where MATCH ( content, title ) against ( '+风景-爱情' IN BOOLEAN MODE );
+----+--------+
| id | title |
+----+--------+
| 10 | Fragment |
+----+--------+
1 row in set (0.01 sec)

-- Query expansion (QUERY EXPANSION) can associate other results mysql> SELECT id, title FROM articles where MATCH ( content, title ) against ( '风景' WITH QUERY EXPANSION );
+----+--------+
| id | title |
+----+--------+
| 10 | Fragment |
| 11 | Soliloquy|
+----+--------+
2 rows in set (0.02 sec)
 

In [1]: import jieba

In [2]: jieba.lcut("Hello World")
Building prefix dict from the default dictionary ...
Dumping model to file cache /var/folders/st/b16fyn3s57x_5vszjl599njw0000gn/T/jieba.cache
Loading model cost 0.937 seconds.
Prefix dict has been built successfully.
Out[2]: ['Hello', 'World']

In [3]: jieba.lcut("hello world")
Out[3]: ['hello', ' ', 'world']