mysql 全文索引

mysql官方文档
其他参考：https://www.jianshu.com/p/c48106149b6a

存储引擎

旧版的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')

转载于:https://www.cnblogs.com/lanqie/p/10838773.html