MySQL 全文检索的使用示例

-- 查看mysql的版本
mysql> select version();
+-----------+
| version() |
+-----------+
| 5.7.33    |
+-----------+
1 row in set (0.02 sec)

n=1: '你', '好', '世', '界' 
n=2: '你好', '好世', '世界' 
n=3: '你好世', '好世界' 
n=4: '你好世界'

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

ft_min_word_len = 2
ngram_token_size = 2


# 如果没有则新增配置
echo 'ft_min_word_len = 2
ngram_token_size = 2' >> mysqld.cnf

# 重启服务
/etc/init.d/mysql restart

-- 查看配置
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)

-- mysql 于全文检索的demo

mysql> CREATE TABLE `articles` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `title` varchar(50) DEFAULT NULL COMMENT '主题',
  `content` longtext NOT NULL COMMENT '内容',
  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
        ('如果','今生今世 永不再将你想起 
除了
除了在有些个
因落泪而湿润的夜里 如果
如果你愿意'),
        ('爱情','有一天路标迁了希望你能从容
有一天桥墩断了希望你能渡越
有一天栋梁倒了希望你能坚强
有一天期待蔫了希望你能理解'),
        ('远和近','你 一会看我
一会看云
我觉得
你看我时很远
你看云时很近'),
        ('断章','你站在桥上看风景，
看风景人在楼上看你。
明月装饰了你的窗子，
你装饰了别人的梦。'),
        ('独语','我向你倾吐思念
你如石像
沉默不应
如果沉默是你的悲抑
你知道这悲抑
最伤我心');

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 | 断章 |  你站在桥上看风景，
看风景人在楼上看你。
明月装饰了你的窗子，
你装饰了别人的梦。 |
+----+--------+--------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.02 sec)

-- 自然语言模式（NATURAL LANGUAGE MODE）查询并得到评分

mysql> SELECT id, title, MATCH ( content, title ) against ( '风景' IN NATURAL LANGUAGE MODE ) AS score  FROM articles;
+----+-----------+--------------------+
| id | title     | score              |
+----+-----------+--------------------+
|  7 | 如果    |                  0 |
|  8 | 爱情    |                  0 |
|  9 | 远和近 |                  0 |
| 10 | 断章    | 0.9771181344985962 |
| 11 | 独语    |                  0 |
+----+-----------+--------------------+
5 rows in set (0.02 sec)

-- 布隆模式(BOOLEAN MODE) 可以组合查询

mysql> SELECT id, title  FROM articles where MATCH ( content, title ) against ( '+风景 -爱情' IN BOOLEAN MODE );
+----+--------+
| id | title  |
+----+--------+
| 10 | 断章 |
+----+--------+
1 row in set (0.01 sec)

-- 查询扩展(QUERY EXPANSION) 可以联想出其他结果 
mysql> SELECT id, title  FROM articles where MATCH ( content, title ) against ( '风景' WITH QUERY EXPANSION );
+----+--------+
| id | title  |
+----+--------+
| 10 | 断章 |
| 11 | 独语 |
+----+--------+
2 rows in set (0.02 sec)
 

In [1]: import jieba

In [2]: jieba.lcut("你好世界")
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]: ['你好', '世界']

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