mysql全文索引含义_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