mysql 全文索引(一）

建表语句：

CREATE TABLE `test` (
  `id` bigint(20) NOT NULL,
  `name` varchar(20) NOT NULL DEFAULT 'name',
  `is_auto` tinyint(1) NOT NULL DEFAULT '1',
  `title` text,
  PRIMARY KEY (`id`),
  KEY `index_name` (`name`),
  FULLTEXT KEY `name` (`name`,`title`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8

　　

数据格式：

mysql> select * from test;
+----+-------+---------+-----------------------------------------+
| id | name  | is_auto | title                                   |
+----+-------+---------+-----------------------------------------+
|  1 | name2 |       1 | nihao wa liyinhe                        |
|  2 | name3 |       1 | goodmorning wangxiaobo                  |
|  3 | name  |       1 | nihao wa liyinhe                        |
|  4 | name  |       1 | nihao wa liyinhe                        |
|  5 | name  |       1 | goodmorning wangxiaobo                  |
|  9 | name  |       1 | goodmorning wangxiaobo                  |
| 10 | santa |       0 | santa is good,satan is evil             |
| 11 | santa |       0 | santa is good,satan is evil,all is well |
+----+-------+---------+-----------------------------------------+
8 rows in set (0.00 sec)

全文索引查询：

mysql> select * from test where  match(name,title) against ("wangxiaobo ");
+----+-------+---------+------------------------+
| id | name  | is_auto | title                  |
+----+-------+---------+------------------------+
|  2 | name3 |       1 | goodmorning wangxiaobo |
|  5 | name  |       1 | goodmorning wangxiaobo |
|  9 | name  |       1 | goodmorning wangxiaobo |
+----+-------+---------+------------------------+
3 rows in set (0.00 sec)

 

参考资料：

https://dev.mysql.com/doc/refman/5.7/en/fulltext-natural-language.html