mysql full-text search: fulltext index

http://www.mysqltutorial.org/mysql-full-text-search.aspx
https://dev.mysql.com/doc/refman/5.6/en/innodb-fulltext-index.html

In MySQL version 5.6 or later, only MyISAM and InnoDB storage engines support full-text search.

Defining FULLTEXT Indexes for MySQL Full-Text Searching

CREATE TABLE opening_lines (
id INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY,
opening_line TEXT(500),
author VARCHAR(200),
title VARCHAR(200),
FULLTEXT idx (opening_line)
) ENGINE=InnoDB;

like operation, regular expression

http://www.mysqltutorial.org/introduction-to-mysql-full-text-search.aspx

MySQL supports text searching by using the LIKE operator and regular expression.

当被搜索的字段其content很大，并且随着行数的增加，麻烦会越来越突出：
性能不高
灵活性不高
匹配度打分不高

解决方案：
FULLTEXT index可以解决搜索问题。

based on pattern: like operation, regular expression

http://www.mysqltutorial.org/mysql-regular-expression-regexp.aspx

http://www.mysqltutorial.org/mysql-like/

based on keyword: FULLTEXT index