「Mysql/日本語全文検索」の版間の差分
提供: 初心者エンジニアの簡易メモ
(→mecabを使う) |
(→mecab検索) |
||
| (同じ利用者による、間の4版が非表示) | |||
| 行13: | 行13: | ||
content VARCHAR(255), | content VARCHAR(255), | ||
FULLTEXT INDEX ftx_fulltext(content) /*!50100 WITH PARSER `ngram` */ | FULLTEXT INDEX ftx_fulltext(content) /*!50100 WITH PARSER `ngram` */ | ||
| − | ) | + | ) ENGINE=InnoDB CHARACTER SET utf8mb4; |
insert into diaries (content) values ("明日は晴れです"); | insert into diaries (content) values ("明日は晴れです"); | ||
insert into diaries (content) values ("明日は曇りです"); | insert into diaries (content) values ("明日は曇りです"); | ||
| 行25: | 行25: | ||
fulltext_column TEXT AS (CONCAT(title, ' ', content)) STORED, | fulltext_column TEXT AS (CONCAT(title, ' ', content)) STORED, | ||
FULLTEXT INDEX ftx_fulltext (fulltext_column) /*!50100 WITH PARSER `ngram` */ | FULLTEXT INDEX ftx_fulltext (fulltext_column) /*!50100 WITH PARSER `ngram` */ | ||
| − | ) | + | ) ENGINE=InnoDB CHARACTER SET utf8mb4; |
insert into diaries (title,content) values ("晴！", "明日は晴れです"); | insert into diaries (title,content) values ("晴！", "明日は晴れです"); | ||
insert into diaries (title,content) values ("曇！", "明日は曇りです"); | insert into diaries (title,content) values ("曇！", "明日は曇りです"); | ||
| 行43: | 行43: | ||
==mecab検索== | ==mecab検索== | ||
| − | CREATE TABLE | + | CREATE TABLE diaries_mecab |
( | ( | ||
id INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY, | id INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY, | ||
content VARCHAR(255), | content VARCHAR(255), | ||
FULLTEXT INDEX mecab_idx (content) WITH PARSER mecab | FULLTEXT INDEX mecab_idx (content) WITH PARSER mecab | ||
| − | )ENGINE=InnoDB CHARACTER SET utf8mb4; | + | ) ENGINE=InnoDB CHARACTER SET utf8mb4; |
| − | insert into | + | insert into diaries_mecab (content) values ("明日は晴れです"); |
| − | insert into | + | insert into diaries_mecab (content) values ("明日は曇りです"); |
| − | select * from | + | select * from diaries_mecab where match(content) against('晴れ'); |
| + | |||
| + | ==参考== | ||
| + | http://mysqlserverteam.com/innodb-%E5%85%A8%E6%96%87%E6%A4%9C%E7%B4%A2-mecab-parser/ | ||
2017年7月5日 (水) 02:36時点における最新版
目次
mysql5.7から日本語全文検索にも対応された
初期設定なしで、ngram2であれば使える。 mecabを入れれば分かち書きが使えるようになる。
mysql5.6までは
Mroonga [ｼｮｰﾄｶｯﾄ]
それまでは以下mroongaでしか使えなかった。
ngram2での検索サンプル
CREATE TABLE diaries (
id INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY,
content VARCHAR(255),
FULLTEXT INDEX ftx_fulltext(content) /*!50100 WITH PARSER `ngram` */
) ENGINE=InnoDB CHARACTER SET utf8mb4;
insert into diaries (content) values ("明日は晴れです");
insert into diaries (content) values ("明日は曇りです");
select * from diaries where match(content) against('晴れ');
ngram2での検索サンプル(複数カラムの検索の場合)
CREATE TABLE diaries (
id INT PRIMARY KEY AUTO_INCREMENT,
title VARCHAR(64) NULL DEFAULT NULL,
content TEXT NULL,
fulltext_column TEXT AS (CONCAT(title, ' ', content)) STORED,
FULLTEXT INDEX ftx_fulltext (fulltext_column) /*!50100 WITH PARSER `ngram` */
) ENGINE=InnoDB CHARACTER SET utf8mb4;
insert into diaries (title,content) values ("晴！", "明日は晴れです");
insert into diaries (title,content) values ("曇！", "明日は曇りです");
select * from diaries where match(fulltext_column) against('晴れ');
select * from diaries where match(fulltext_column) against('曇！');
mecabを使う
$ vi /etc/my.cnf [mysqld] loose-mecab-rc-file=/etc/mecabrc innodb_ft_min_token_size=2 $ sudo /etc/rc.d/init.d/mysqld restart mysql> INSTALL PLUGIN mecab SONAME 'libpluginmecab.so'; mysql> SHOW PLUGINS; | Name | Status | Type | Library | License | mecab | ACTIVE | FTPARSER | libpluginmecab.so | GPL
mecab検索
CREATE TABLE diaries_mecab
(
id INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY,
content VARCHAR(255),
FULLTEXT INDEX mecab_idx (content) WITH PARSER mecab
) ENGINE=InnoDB CHARACTER SET utf8mb4;
insert into diaries_mecab (content) values ("明日は晴れです");
insert into diaries_mecab (content) values ("明日は曇りです");
select * from diaries_mecab where match(content) against('晴れ');
参考
http://mysqlserverteam.com/innodb-%E5%85%A8%E6%96%87%E6%A4%9C%E7%B4%A2-mecab-parser/
