mysql 全文索引

首先，创建一个支持全文索引的表：

sql

CREATE TABLE articles (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    title VARCHAR(200),
    body TEXT,
    FULLTEXT (title, body)  -- 在title和body上创建联合全文索引
    -- 也可以创建单独的索引：
    -- FULLTEXT (title)
    -- FULLTEXT (body)
) ENGINE=InnoDB;

-- 插入示例数据
INSERT INTO articles (title, body) VALUES
('MySQL Database Tutorial', 'Learn how to use MySQL database with comprehensive examples and best practices.'),
('Python Programming Guide', 'A complete guide to Python programming language for beginners and advanced users.'),
('Web Development with JavaScript', 'Building modern web applications using JavaScript, HTML, and CSS.'),
('Data Analysis with SQL', 'Using SQL queries to analyze and extract insights from your data.'),
('MySQL Performance Optimization', 'Tips and techniques for optimizing MySQL database performance.');

mysql> select * from articles ;
+----+---------------------------------+-----------------------------------------------------------------------------------+
| id | title                           | body                                                                              |
+----+---------------------------------+-----------------------------------------------------------------------------------+
|  1 | MySQL Database Tutorial         | Learn how to use MySQL database with comprehensive examples and best practices.   |
|  2 | Python Programming Guide        | A complete guide to Python programming language for beginners and advanced users. |
|  3 | Web Development with JavaScript | Building modern web applications using JavaScript, HTML, and CSS.                 |
|  4 | Data Analysis with SQL          | Using SQL queries to analyze and extract insights from your data.                 |
|  5 | MySQL Performance Optimization  | Tips and techniques for optimizing MySQL database performance.                    |
+----+---------------------------------+-----------------------------------------------------------------------------------+
5 rows in set (0.00 sec)

案例1：自然语言搜索（默认模式）

-- 搜索包含 "MySQL" 和 "database" 的文章
SELECT * FROM articles 
WHERE MATCH (title, body) AGAINST ('MySQL database');

mysql> SELECT * FROM articles 
    -> WHERE MATCH (title, body) AGAINST ('MySQL database');
+----+--------------------------------+---------------------------------------------------------------------------------+
| id | title                          | body                                                                            |
+----+--------------------------------+---------------------------------------------------------------------------------+
|  1 | MySQL Database Tutorial        | Learn how to use MySQL database with comprehensive examples and best practices. |
|  5 | MySQL Performance Optimization | Tips and techniques for optimizing MySQL database performance.                  |
+----+--------------------------------+---------------------------------------------------------------------------------+
2 rows in set (0.01 sec)

-- 搜索包含 "Python" 或 "programming" 的文章
SELECT * FROM articles 
WHERE MATCH (title, body) AGAINST ('Python programming');

mysql> SELECT * FROM articles 
    -> WHERE MATCH (title, body) AGAINST ('Python programming');
+----+--------------------------+-----------------------------------------------------------------------------------+
| id | title                    | body                                                                              |
+----+--------------------------+-----------------------------------------------------------------------------------+
|  2 | Python Programming Guide | A complete guide to Python programming language for beginners and advanced users. |
+----+--------------------------+-----------------------------------------------------------------------------------+
1 row in set (0.00 sec)

-- 搜索结果按相关性排序
SELECT id, title, 
       MATCH (title, body) AGAINST ('MySQL database') as relevance_score
FROM articles 
WHERE MATCH (title, body) AGAINST ('MySQL database')
ORDER BY relevance_score DESC;

mysql> SELECT id, title, 
    ->        MATCH (title, body) AGAINST ('MySQL database') as relevance_score
    -> FROM articles 
    -> WHERE MATCH (title, body) AGAINST ('MySQL database')
    -> ORDER BY relevance_score DESC;
+----+--------------------------------+---------------------+
| id | title                          | relevance_score     |
+----+--------------------------------+---------------------+
|  1 | MySQL Database Tutorial        |  0.6334249973297119 |
|  5 | MySQL Performance Optimization | 0.47506874799728394 |
+----+--------------------------------+---------------------+
2 rows in set (0.00 sec)

案例2：布尔模式搜索（更精确的控制）

 

 

-- 必须包含 "MySQL" 且必须包含 "database"
SELECT * FROM articles 
WHERE MATCH (title, body) AGAINST ('+MySQL +database' IN BOOLEAN MODE);

mysql> SELECT * FROM articles 
    -> WHERE MATCH (title, body) AGAINST ('+MySQL +database' IN BOOLEAN MODE);
+----+--------------------------------+---------------------------------------------------------------------------------+
| id | title                          | body                                                                            |
+----+--------------------------------+---------------------------------------------------------------------------------+
|  1 | MySQL Database Tutorial        | Learn how to use MySQL database with comprehensive examples and best practices. |
|  5 | MySQL Performance Optimization | Tips and techniques for optimizing MySQL database performance.                  |
+----+--------------------------------+---------------------------------------------------------------------------------+
2 rows in set (0.00 sec)

 

-- 包含 "MySQL" 但不能包含 "Python"
SELECT * FROM articles 
WHERE MATCH (title, body) AGAINST ('+MySQL -Python' IN BOOLEAN MODE);

mysql> SELECT * FROM articles 
    -> WHERE MATCH (title, body) AGAINST ('+MySQL -Python' IN BOOLEAN MODE);
+----+--------------------------------+---------------------------------------------------------------------------------+
| id | title                          | body                                                                            |
+----+--------------------------------+---------------------------------------------------------------------------------+
|  1 | MySQL Database Tutorial        | Learn how to use MySQL database with comprehensive examples and best practices. |
|  5 | MySQL Performance Optimization | Tips and techniques for optimizing MySQL database performance.                  |
+----+--------------------------------+---------------------------------------------------------------------------------+
2 rows in set (0.00 sec)

 

-- 包含 "web" 或 "development"，但 "web" 的权重更高
SELECT * FROM articles 
WHERE MATCH (title, body) AGAINST ('>web development' IN BOOLEAN MODE);

mysql> SELECT * FROM articles 
    -> WHERE MATCH (title, body) AGAINST ('>web development' IN BOOLEAN MODE);
+----+---------------------------------+-------------------------------------------------------------------+
| id | title                           | body                                                              |
+----+---------------------------------+-------------------------------------------------------------------+
|  3 | Web Development with JavaScript | Building modern web applications using JavaScript, HTML, and CSS. |
+----+---------------------------------+-------------------------------------------------------------------+
1 row in set (0.00 sec)

 

-- 包含 "data" 开头的词（通配符）
SELECT * FROM articles 
WHERE MATCH (title, body) AGAINST ('data*' IN BOOLEAN MODE);

mysql> SELECT * FROM articles 
    -> WHERE MATCH (title, body) AGAINST ('data*' IN BOOLEAN MODE);
+----+--------------------------------+---------------------------------------------------------------------------------+
| id | title                          | body                                                                            |
+----+--------------------------------+---------------------------------------------------------------------------------+
|  1 | MySQL Database Tutorial        | Learn how to use MySQL database with comprehensive examples and best practices. |
|  4 | Data Analysis with SQL         | Using SQL queries to analyze and extract insights from your data.               |
|  5 | MySQL Performance Optimization | Tips and techniques for optimizing MySQL database performance.                  |
+----+--------------------------------+---------------------------------------------------------------------------------+
3 rows in set (0.01 sec)

 

-- 短语搜索（必须完整匹配）
SELECT * FROM articles 
WHERE MATCH (title, body) AGAINST ('"MySQL database"' IN BOOLEAN MODE);

mysql> SELECT * FROM articles 
    -> WHERE MATCH (title, body) AGAINST ('"MySQL database"' IN BOOLEAN MODE);
+----+--------------------------------+---------------------------------------------------------------------------------+
| id | title                          | body                                                                            |
+----+--------------------------------+---------------------------------------------------------------------------------+
|  1 | MySQL Database Tutorial        | Learn how to use MySQL database with comprehensive examples and best practices. |
|  5 | MySQL Performance Optimization | Tips and techniques for optimizing MySQL database performance.                  |
+----+--------------------------------+---------------------------------------------------------------------------------+
2 rows in set (0.00 sec)

 

案例3：查询扩展模式（扩大搜索结果）

sql

 

-- 先进行常规搜索，然后基于结果中的相关词再次搜索
SELECT * FROM articles 
WHERE MATCH (title, body) AGAINST ('database' WITH QUERY EXPANSION);

mysql> SELECT * FROM articles 
    -> WHERE MATCH (title, body) AGAINST ('database' WITH QUERY EXPANSION);
+----+---------------------------------+-----------------------------------------------------------------------------------+
| id | title                           | body                                                                              |
+----+---------------------------------+-----------------------------------------------------------------------------------+
|  1 | MySQL Database Tutorial         | Learn how to use MySQL database with comprehensive examples and best practices.   |
|  5 | MySQL Performance Optimization  | Tips and techniques for optimizing MySQL database performance.                    |
|  2 | Python Programming Guide        | A complete guide to Python programming language for beginners and advanced users. |
|  3 | Web Development with JavaScript | Building modern web applications using JavaScript, HTML, and CSS.                 |
|  4 | Data Analysis with SQL          | Using SQL queries to analyze and extract insights from your data.                 |
+----+---------------------------------+-----------------------------------------------------------------------------------+

 

案例4：在SELECT列表中显示相关性分数

sql

 

SELECT 
    id,
    title,
    body,
    MATCH (title, body) AGAINST ('MySQL optimization') as relevance,
    ROUND(MATCH (title, body) AGAINST ('MySQL optimization') * 100, 2) as relevance_percentage
FROM articles 
WHERE MATCH (title, body) AGAINST ('MySQL optimization')
ORDER BY relevance DESC;

mysql> SELECT 
    ->     id,
    ->     title,
    ->     body,
    ->     MATCH (title, body) AGAINST ('MySQL optimization') as relevance,
    ->     ROUND(MATCH (title, body) AGAINST ('MySQL optimization') * 100, 2) as relevance_percentage
    -> FROM articles 
    -> WHERE MATCH (title, body) AGAINST ('MySQL optimization')
    -> ORDER BY relevance DESC;
+----+--------------------------------+---------------------------------------------------------------------------------+---------------------+----------------------+
| id | title                          | body                                                                            | relevance           | relevance_percentage |
+----+--------------------------------+---------------------------------------------------------------------------------+---------------------+----------------------+
|  5 | MySQL Performance Optimization | Tips and techniques for optimizing MySQL database performance.                  |   0.805271565914154 |                80.53 |
|  1 | MySQL Database Tutorial        | Learn how to use MySQL database with comprehensive examples and best practices. | 0.31671249866485596 |                31.67 |
+----+--------------------------------+---------------------------------------------------------------------------------+---------------------+----------------------+
2 rows in set (0.00 sec)

mysql

 

案例5：多字段搜索与权重调整

sql

 

-- 创建带有不同权重的全文索引
ALTER TABLE articles ADD FULLTEXT idx_weighted (title, body) WITH PARSER ngram;

-- 在查询时，标题的匹配比正文的匹配更重要
SELECT 
    id,
    title,
    MATCH (title) AGAINST ('MySQL') * 2 +  -- 标题权重为2
    MATCH (body) AGAINST ('MySQL') * 1     -- 正文权重为1
    as weighted_score
FROM articles 
WHERE MATCH (title, body) AGAINST ('MySQL')
ORDER BY weighted_score DESC;

案例6：结合其他查询条件

sql

 

-- 全文搜索与其他条件结合使用
SELECT * FROM articles 
WHERE MATCH (title, body) AGAINST ('MySQL database')
AND id > 2
ORDER BY id DESC;

-- 使用全文搜索和LIKE结合（但不推荐，性能较差）
SELECT * FROM articles 
WHERE MATCH (title, body) AGAINST ('MySQL')
AND body LIKE '%tutorial%';

案例7：处理停用词和最小词长

sql

 

-- 查看当前的全文索引配置
SHOW VARIABLES LIKE 'ft_%';

-- 修改停用词列表（需要修改配置文件后重启）
-- ft_stopword_file = ''  -- 空字符串表示禁用停用词
-- ft_min_word_len = 2    -- 修改最小词长为2（默认通常是4）

-- 重建全文索引
REPAIR TABLE articles QUICK;

注意事项

1. 引擎要求：全文索引需要使用 InnoDB 或 MyISAM 存储引擎

2. 最小词长：默认通常为4个字符，小于此长度的词不会被索引

3. 停用词：常见词（如"the", "and", "is"等）不会被索引

4. 性能：对于大型数据集，全文搜索比 LIKE 查询快得多

5. 索引维护：数据变更后，全文索引会自动更新，但大量数据插入后可能需要优化

查看全文索引信息

sql

 

-- 查看表的索引信息
SHOW INDEX FROM articles;

-- 查看全文索引的详细信息
SELECT * FROM INFORMATION_SCHEMA.INNODB_FT_CONFIG;
SELECT * FROM INFORMATION_SCHEMA.INNODB_FT_INDEX_TABLE;

这些案例涵盖了 MATCH ... AGAINST 的主要用法，您可以根据具体需求选择适合的模式。