mysql全文索引 or_mysql 全文索引

存储引擎

旧版的MySQL MyISAM引擎支持，MySQL5.6以后InnoDB引擎也加入了全文索引。

version 5.6

MySQL 5.6 Reference Manual / ... / InnoDB FULLTEXT Indexes

14.6.2.3 InnoDB FULLTEXT Indexes

FULLTEXT indexes are created on text-based columns (CHAR, VARCHAR, or TEXT columns) to help speed up queries and DML operations on data contained within those columns, omitting any words that are defined as stopwords.

A FULLTEXT index is defined as part of a CREATE TABLE statement or added to an existing table using ALTER TABLE or CREATE INDEX.

Full-text search is performed using MATCH() ... AGAINST syntax. For usage information, see Section 12.9, “Full-Text Search Functions”.

查看存储引擎信息

-- 查看mysql版本

select version();

-- 查看支持的存储引擎

show engines;

-- 查看某个表使用的存储引擎

show create table tb_name;

创建

ALTER TABLE tb_order_item ADD FULLTEXT INDEX ft_order_item_goods_sku (goods_name, sku_content);

or

CREATE FULLTEXT INDEX ft_order_item_goods_sku ON tb_order_item (goods_name, sku_content) WITH PARSER ngram;

删除

DROP INDEX ft_order_item_goods_sku ON tb_order_item;

使用

SELECT *

FROM tb_goods g

WHERE MATCH(name) AGAINST('一加3')