MySQLでの全文検索(N-gram編)

本記事は東京学芸大学 櫨山研究室 Advent Calendar 2020の九日目の記事になります．

はじめに

今回はMySQLを使った全文検索について扱いたいと思います．

MySQLでは5.7よりInnoDBを使って簡単に全文検索を実現することができます．
本記事はMySQLのN-gramパーサーを使って日本語の全文検索を実現する方法を取り扱います．

N-gramとは？

まずN-gramについて簡単におさらいをしておきます．

N-gramとは任意の文字列をN文字で区切った形で表す方法です．

例えば自然言語処理という文字列をN=2のN-gramで表現する場合

'自然', '然言', '言語', '語処', '処理'

という形になります．

N=1の場合をunigram，N=2の場合をbigram，N=3の場合をtrigramと呼びます．

なおMySQLのN-gramパーサーはデフォルトでbigramを使用します．

MySQLの準備

MySQLサーバを用意します．
今回はちょっと試すだけなのでdockerとdocker-composeで用意します．

バージョンはMySQL 8.0を使います．
日本語を使いたいので文字コードはUTF-8とします．

Dockerfile

FROM mysql:8

RUN apt-get update \
    && apt-get install -y locales \
    && locale-gen ja_JP.UTF-8
ENV LANG ja_JP.UTF-8
ENV LANGUAGE ja_JP:ja
ENV LC_ALL=ja_JP.UTF-8
RUN localedef -f UTF-8 -i ja_JP ja_JP.utf8

my.cnfは以下の通りです．
my.cnfは./conf/my.cnfに配置します．

my.cnf

[mysqld]
character-set-server = utf8mb4
collation-server = utf8mb4_general_ci
innodb-use-native-aio = 0

docker-compose.ymlを用意します．

docker-compose.yml

version: '3.7'

services:
  db:
    container_name: db
    build:
      context: .
      dockerfile: ./Dockerfile
    volumes:
      - ./conf/my.cnf:/etc/mysql/conf.d/my.cnf
    tty: true
    environment:
      TZ: 'Asia/Tokyo'
      MYSQL_USER: 'user'
      MYSQL_PASSWORD: 'password'
      MYSQL_ROOT_PASSWORD: 'root'

docker-compose upコマンドで立ち上げてdocker execコマンドでMySQLの中に入ります．

docker-compose up
docker exec -it db bin/bash -c "mysql -u root -p"

はじめにデータベースを作りましょう．
データベース名は適当にsampleとします．

MySQL_Client

CREATE DATABASE sample;
use sample;

ここまででMySQLの準備は終了です．

全文検索に使用するデータ

次に全文検索を試すデータです．

今回は小倉百人一首のデータを使います．
データ構造は以下のようになっています．

項目	内容
id	主キー
poet	歌人
above	上の句
below	下の句
above_kana	上の句（かな）
below_kana	下の句（かな）

先ほど作成したsampleデータベースの中に，この構造を持ったテーブルを作成します．
テーブル名はoguraとします．

MySQL_Client

create table `ogura` (
  `id` INT unsigned NOT NULL AUTO_INCREMENT,
  `poet` text,
  `above` text,
  `below` text,
  `above_kana` text,
  `below_kana` text,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

次にデータを投入します．
なおデータのcsvファイルは以下のリンクを元に作成しました．
このようなデータを用意していただき感謝しています．

nyoronjp/Ogura_Hyakunin_Isshu.csv, https://github.com/nyoronjp/Ogura_Hyakunin_Isshu.csv

MySQL_Client

INSERT INTO `ogura` (`poet`,`above`,`below`,`above_kana`,`below_kana`)
VALUES
  ('天智天皇','秋の田のかりほの庵の苫をあらみ','わが衣手は露にぬれつつ','あきのたのかりほのいほのとまをあらみ','わがころもではつゆにぬれつつ'),
  ('持統天皇','春過ぎて夏来にけらし白妙の','衣干すてふ天の香具山','はるすぎてなつきにけらししろたへの','ころもほすてふあまのかぐやま'),
  ('柿本人麻呂','あしびきの山鳥の尾のしだり尾の','ながながし夜をひとりかも寝む','あしびきのやまどりのをのしだりをの','ながながしよをひとりかもねむ'),
  ('山辺赤人','田子の浦にうち出でてみれば白妙の','富士の高嶺に雪は降りつつ','たごのうらにうちいでてみればしろたへの','ふじのたかねにゆきはふりつつ'),
  ('猿丸大夫','奥山に紅葉踏み分け鳴く鹿の','声聞く時ぞ秋は悲しき','おくやまにもみぢふみわけなくしかの','こゑきくときぞあきはかなしき'),
  ('中納言家持','かささぎの渡せる橋に置く霜の','白きを見れば夜ぞ更けにける','かささぎのわたせるはしにおくしもの','しろきをみればよぞふけにける'),
  ('安倍仲麻呂','天の原ふりさけみれば春日なる','三笠の山に出でし月かも','あまのはらふりさけみればかすがなる','みかさのやまにいでしつきかも'),
  ('喜撰法師','わが庵は都の辰巳しかぞ住む','世をうぢ山と人はいふなり','わがいほはみやこのたつみしかぞすむ','よをうぢやまとひとはいふなり'),
  ('小野小町','花の色は移りにけりないたづらに','わが身世にふるながめせしまに','はなのいろはうつりにけりないたづらに','わがみよにふるながめせしまに'),
  ('蝉丸','これやこの行くも帰るも別れては','知るも知らぬも逢坂の関','これやこのゆくもかへるもわかれては','しるもしらぬもあふさかのせき'),
  ('参議篁','わたの原八十島かけて漕ぎ出でぬと','人にはつげよ海人の釣船','わたのはらやそしまかけてこぎいでぬと','ひとにはつげよあまのつりぶね'),
  ('僧正遍昭','天つ風雲の通ひ路吹きとぢよ','乙女の姿しばしとどめむ','あまつかぜくものかよひぢふきとぢよ','をとめのすがたしばしとどめむ'),
  ('陽成院','筑波嶺の峰より落つるみなの川','恋ぞつもりて淵となりぬる','つくばねのみねよりおつるみなのがは','こひぞつもりてふちとなりぬる'),
  ('河原左大臣','陸奥のしのぶもぢずりたれゆえに','乱れそめにしわれならなくに','みちのくのしのぶもぢずりたれゆゑに','みだれそめにしわれならなくに'),
  ('光孝天皇','君がため春の野に出でて若菜摘む','わが衣手に雪は降りつつ','きみがためはるののにいでてわかなつむ','わがころもでにゆきはふりつつ'),
  ('中納言行平','立ち別れいなばの山の峰に生ふる','まつとし聞かば今帰り来む','たちわかれいなばのやまのみねにおふる','まつとしきかばいまかへりこむ'),
  ('在原業平朝臣','ちはやぶる神代も聞かず竜田川','から紅に水くくるとは','ちはやぶるかみよもきかずたつたがは','からくれなゐにみづくくるとは'),
  ('藤原敏行朝臣','住の江の岸に寄る波よるさへや','夢の通ひ路人目よくらむ','すみのえのきしによるなみよるさへや','ゆめのかよひぢひとめよくらむ'),
  ('伊勢','難波潟短き葦のふしの間も','逢はでこの世を過ぐしてよとや','なにはがたみじかきあしのふしのまも','あはでこのよをすぐしてよとや'),
  ('元良親王','わびぬれば今はた同じ難波なる','身をつくしても逢はむとぞ思ふ','わびぬればいまはたおなじなにはなる','みをつくしてもあはむとぞおもふ'),
  ('素性法師','今来むといひしばかりに長月の','有明の月を待ち出でつるかな','いまこむといひしばかりにながつきの','ありあけのつきをまちいでつるかな'),
  ('文屋康秀','吹くからに秋の草木のしをるれば','むべ山風を嵐といふらむ','ふくからにあきのくさきのしをるれば','むべやまかぜをあらしといふらむ'),
  ('大江千里','月見れば千々にものこそ悲しけれ','わが身ひとつの秋にはあらねど','つきみればちぢにものこそかなしけれ','わがみひとつのあきにはあらねど'),
  ('菅家','このたびは幣もとりあへず手向山','紅葉の錦神のまにまに','このたびはぬさもとりあへずたむけやま','もみぢのにしきかみのまにまに'),
  ('三条右大臣','名にし負はば逢坂山のさねかずら','人に知られでくるよしもがな','なにしおはばあふさかやまのさねかづら','ひとにしられでくるよしもがな'),
  ('貞信公','小倉山峰のもみぢ葉心あらば','今ひとたびのみゆき待たなむ','をぐらやまみねのもみぢばこころあらば','いまひとたびのみゆきまたなむ'),
  ('中納言兼輔','みかの原わきて流るる泉川','いつ見きとてか恋しかるらむ','みかのはらわきてながるるいづみがは','いつみきとてかこひしかるらむ'),
  ('源宗于朝臣','山里は冬ぞ寂しさまさりける','人目も草もかれぬと思へば','やまざとはふゆぞさびしさまさりける','ひとめもくさもかれぬとおもへば'),
  ('凡河内躬恒','心あてに折らばや折らむ初霜の','置きまどはせる白菊の花','こころあてにをらばやをらむはつしもの','おきまどはせるしらぎくのはな'),
  ('壬生忠岑','有明のつれなく見えし別れより','暁ばかり憂きものはなし','ありあけのつれなくみえしわかれより','あかつきばかりうきものはなし'),
  ('坂上是則','朝ぼらけ有明の月と見るまでに','吉野の里に降れる白雪','あさぼらけありあけのつきとみるまでに','よしののさとに ふれるしらゆき'),
  ('春道列樹','山川に風のかけたるしがらみは','流れもあへぬ紅葉なりけり','やまがはにかぜのかけたるしがらみは','ながれもあへぬもみぢなりけり'),
  ('紀友則','ひさかたの光のどけき春の日に','しづ心なく花の散るらむ','ひさかたのひかりのどけきはるのひに','しづこころなくはなのちるらむ'),
  ('藤原興風','誰をかも知る人にせむ高砂の','松も昔の友ならなくに','たれをかもしるひとにせむたかさごの','まつもむかしのともならなくに'),
  ('紀貫之','人はいさ心も知らずふるさとは','花ぞ昔の香に匂ひける','ひとはいさこころもしらずふるさとは','はなぞむかしのかににほひける'),
  ('清原深養父','夏の夜はまだ宵ながら明けぬるを','雲のいずこに月宿るらむ','なつのよはまだよひながらあけぬるを','くものいづこにつきやどるらむ'),
  ('文屋朝康','白露に風の吹きしく秋の野は','つらぬきとめぬ玉ぞ散りける','しらつゆにかぜのふきしくあきののは','つらぬきとめぬたまぞちりける'),
  ('右近','忘らるる身をば思はず誓ひてし','人の命の惜しくもあるかな','わすらるるみをばおもはずちかひてし','ひとのいのちのをしくもあるかな'),
  ('参議等','浅茅生の小野の篠原忍ぶれど','あまりてなどか人の恋しき','あさぢふのをののしのはらしのぶれど','あまりてなどかひとのこひしき'),
  ('平兼盛','忍ぶれど色に出でにけりわが恋は','ものや思ふと人の問ふまで','しのぶれどいろにいでにけりわがこひは','ものやおもふとひとのとふまで'),
  ('壬生忠見','恋すてふわが名はまだき立ちにけり','人知れずこそ思ひそめしか','こひすてふわがなはまだきたちにけり','ひとしれずこそおもひそめしか'),
  ('清原元輔','契りきなかたみに袖をしぼりつつ','末の松山波越さじとは','ちぎりきなかたみにそでをしぼりつつ','すゑのまつやまなみこさじとは'),
  ('権中納言敦忠','逢ひ見ての後の心にくらぶれば','昔はものを思はざりけり','あひみてののちのこころにくらぶれば','むかしはものをおもはざりけり'),
  ('中納言朝忠','逢ふことの絶えてしなくはなかなかに','人をも身をも恨みざらまし','あふことのたえてしなくはなかなかに','ひとをもみをもうらみざらまし'),
  ('謙徳公','あはれともいふべき人は思ほえで','身のいたずらになりぬべきかな','あはれともいふべきひとはおもほえで','みのいたづらになりぬべきかな'),
  ('曾禰好忠','由良の戸を渡る舟人かぢをたえ','ゆくへも知らぬ恋の道かな','ゆらのとをわたるふなびとかぢをたえ','ゆくへもしらぬこひのみちかな'),
  ('恵慶法師','八重むぐら茂れる宿のさびしきに','人こそ見えね秋は来にけり','やへむぐらしげれるやどのさびしきに','ひとこそみえねあきはきにけり'),
  ('源重之','風をいたみ岩うつ波のおのれのみ','くだけてものを思ふころかな','かぜをいたみいはうつなみのおのれのみ','くだけてものをおもふころかな'),
  ('大中臣能宣','御垣守衛士のたく火の夜は燃え','昼は消えつつものをこそ思へ','みかきもりゑじのたくひのよるはもえ','ひるはきえつつものをこそおもへ'),
  ('藤原義孝','君がため惜しからざりし命さへ','長くもがなと思ひけるかな','きみがためをしからざりしいのちさへ','ながくもがなとおもひけるかな'),
  ('藤原実方朝臣','かくとだにえやはいぶきのさしも草','さしも知らじな燃ゆる思ひを','かくとだにえやはいぶきのさしもぐさ','さしもしらじなもゆるおもひを'),
  ('藤原道信朝臣','明けぬれば暮るるものとは知りながら','なほ恨めしき朝ぼらけかな','あけぬればくるるものとはしりながら','なほうらめしきあさぼらけかな'),
  ('右大将道綱母','嘆きつつひとり寝る夜の明くる間は','いかに久しきものとかは知る','なげきつつひとりぬるよのあくるまは','いかにひさしきものとかはしる'),
  ('儀同三司母','忘れじの行く末まではかたければ','今日をかぎりの命ともがな','わすれじのゆくすゑまではかたければ','けふをかぎりのいのちともがな'),
  ('大納言公任','滝の音は絶えて久しくなりぬれど','名こそ流れてなほ聞こえけれ','たきのおとはたえてひさしくなりぬれど','なこそながれてなほきこえけれ'),
  ('和泉式部','あらざらむこの世のほかの思ひ出に','今ひとたびの逢ふこともがな','あらざらむこのよのほかのおもひでに','いまひとたびのあふこともがな'),
  ('紫式部','めぐり逢ひて見しやそれとも分かぬ間に','雲隠れにし夜半の月かな','めぐりあひてみしやそれともわかぬまに','くもがくれにしよはのつきかな'),
  ('大弐三位','有馬山猪名の笹原風吹けば','いでそよ人を忘れやはする','ありまやまゐなのささはらかぜふけば','いでそよひとをわすれやはする'),
  ('赤染衛門','やすらはで寝なましものを小夜更けて','かたぶくまでの月を見しかな','やすらはでねなましものをさよふけて','かたぶくまでのつきをみしかな'),
  ('小式部内侍','大江山いく野の道の遠ければ','まだふみも見ず天の橋立','おほえやまいくののみちのとほければ','まだふみもみずあまのはしだて'),
  ('伊勢大輔','いにしへの奈良の都の八重桜','けふ九重に匂ひぬるかな','いにしへのならのみやこのやへざくら','けふここのへににほひぬるかな'),
  ('清少納言','夜をこめて鳥のそら音ははかるとも','よに逢坂の関はゆるさじ','よをこめてとりのそらねははかるとも','よにあふさかのせきはゆるさじ'),
  ('左京大夫道雅','今はただ思ひ絶えなむとばかりを','人づてならでいふよしもがな','いまはただおもひたえなむとばかりを','ひとづてならでいふよしもがな'),
  ('権中納言定頼','朝ぼらけ宇治の川霧たえだえに','あらはれわたる瀬々の網代木','あさぼらけうぢのかはぎりたえだえに','あらはれわたるせぜのあじろぎ'),
  ('相模','恨みわび干さぬ袖だにあるものを','恋に朽ちなむ名こそ惜しけれ','うらみわびほさぬそでだにあるものを','こひにくちなむなこそをしけれ'),
  ('前大僧正行尊','もろともにあはれと思え山桜','花よりほかに知る人もなし','もろともにあはれとおもへやまざくら','はなよりほかにしるひともなし'),
  ('周防内侍','春の夜の夢ばかりなる手枕に','かひなく立たむ名こそ惜しけれ','はるのよのゆめばかりなるたまくらに','かひなくたたむなこそをしけれ'),
  ('三条院','心にもあらで憂き夜にながらへば','恋しかるべき夜半の月かな','こころにもあらでうきよにながらへば','こひしかるべきよはのつきかな'),
  ('能因法師','嵐ふく三室の山のもみぢ葉は','竜田の川の錦なりけり','あらしふくみむろのやまのもみぢばは','たつたのかはのにしきなりけり'),
  ('良暹法師','寂しさに宿を立ち出でてながむれば','いづこも同じ秋の夕暮れ','さびしさにやどをたちいでてながむれば','いづこもおなじあきのゆふぐれ'),
  ('大納言経信','夕されば門田の稲葉おとづれて','葦のまろやに秋風ぞ吹く','ゆふさればかどたのいなばおとづれて','あしのまろやにあきかぜぞふく'),
  ('祐子内親王家紀伊','音に聞く高師の浜のあだ波は','かけじや袖のぬれもこそすれ','おとにきくたかしのはまのあだなみは','かけじやそでのぬれもこそすれ'),
  ('権中納言匡房','高砂の尾の上の桜咲きにけり','外山の霞立たずもあらなむ','たかさごのをのへのさくらさきにけり','とやまのかすみたたずもあらなむ'),
  ('源俊頼朝臣','憂かりける人を初瀬の山おろしよ','はげしかれとは祈らぬものを','うかりけるひとをはつせのやまおろしよ','はげしかれとはいのらぬものを'),
  ('藤原基俊','契りおきしさせもが露を命にて','あはれ今年の秋もいぬめり','ちぎりおきしさせもがつゆをいのちにて','あはれことしのあきもいぬめり'),
  ('法性寺入道前関白太政大臣','わたの原漕ぎ出でて見ればひさかたの','雲居にまがふ沖つ白波','わたのはらこぎいでてみればひさかたの','くもゐにまがふおきつしらなみ'),
  ('崇徳院','瀬をはやみ岩にせかるる滝川の','われても末に逢はむとぞ思ふ','せをはやみいはにせかるるたきがはの','われてもすゑにあはむとぞおもふ'),
  ('源兼昌','淡路島通ふ千鳥の鳴く声に','いく夜寝覚めぬ須磨の関守','あはぢしまかよふちどりのなくこゑに','いくよねざめぬすまのせきもり'),
  ('左京大夫顕輔','秋風にたなびく雲のたえ間より','もれ出づる月の影のさやけさ','あきかぜにたなびくくものたえまより','もれいづるつきのかげのさやけさ'),
  ('待賢門院堀河','ながからむ心も知らず黒髪の','乱れてけさはものをこそ思へ','ながからむこころもしらずくろかみの','みだれてけさはものをこそおもへ'),
  ('後徳大寺左大臣','ほととぎす鳴きつる方をながむれば','ただ有明の月ぞ残れる','ほととぎすなきつるかたをながむれば','ただありあけのつきぞのこれる'),
  ('道因法師','思ひわびさても命はあるものを','憂きに堪へぬは涙なりけり','おもひわびさてもいのちはあるものを','うきにたへぬはなみだなりけり'),
  ('皇太后宮大夫俊成','世の中よ道こそなけれ思ひ入る','山の奥にも鹿ぞ鳴くなる','よのなかよみちこそなけれおもひいる','やまのおくにもしかぞなくなる'),
  ('藤原清輔朝臣','ながらへばまたこのごろやしのばれむ','憂しと見し世ぞ今は恋しき','ながらへばまたこのごろやしのばれむ','うしとみしよぞいまはこひしき'),
  ('俊恵法師','夜もすがらもの思ふころは明けやらで','ねやのひまさへつれなかりけり','よもすがらものおもふころはあけやらで','ねやのひまさへつれなかりけり'),
  ('西行法師','嘆けとて月やはものを思はする','かこち顔なるわが涙かな','なげけとてつきやはものをおもはする','かこちがほなるわがなみだかな'),
  ('寂蓮法師','村雨の露もまだ干ぬまきの葉に','霧立ちのぼる秋の夕暮','むらさめのつゆもまだひぬまきのはに','きりたちのぼるあきのゆふぐれ'),
  ('皇嘉門院別当','難波江の葦のかりねのひとよゆゑ','身をつくしてや恋ひわたるべき','なにはえのあしのかりねのひとよゆゑ','みをつくしてやこひわたるべき'),
  ('式子内親王','玉の緒よ絶えなば絶えねながらへば','忍ぶることの弱りもぞする','たまのをよたえなばたえねながらへば','しのぶることのよわりもぞする'),
  ('殷富門院大輔','見せばやな雄島の海人の袖だにも','濡れにぞ濡れし色は変はらず','みせばやなをじまのあまのそでだにも','ぬれにぞぬれしいろはかはらず'),
  ('後京極摂政前太政大臣','きりぎりす鳴くや霜夜のさむしろに','衣かたしきひとりかも寝む','きりぎりすなくやしもよのさむしろに','ころもかたしきひとりかもねむ'),
  ('二条院讃岐','わが袖は潮干に見えぬ沖の石の','人こそ知らねかわく間もなし','わがそではしほひにみえぬおきのいしの','ひとこそしらねかわくまもなし'),
  ('鎌倉右大臣','世の中は常にもがもな渚漕ぐ','海人の小舟の綱手かなしも','よのなかはつねにもがもななぎさこぐ','あまのをぶねのつなでかなしも'),
  ('参議雅経','み吉野の山の秋風小夜更けて','ふるさと寒く衣うつなり','みよしののやまのあきかぜさよふけて','ふるさとさむくころもうつなり'),
  ('前大僧正慈円','おほけなく憂き世の民におほふかな','わが立つ杣にすみ染の袖','おほけなくうきよのたみにおほふかな','わがたつそまにすみぞめのそで'),
  ('入道前太政大臣','花さそふ嵐の庭の雪ならで','ふりゆくものはわが身なりけり','はなさそふあらしのにはのゆきならで','ふりゆくものはわがみなりけり'),
  ('権中納言定家','来ぬ人を松帆の浦の夕なぎに','焼くや藻塩の身もこがれつつ','こぬひとをまつほのうらのゆふなぎに','やくやもしほのみもこがれつつ'),
  ('従二位家隆','風そよぐ楢の小川の夕暮は','みそぎぞ夏のしるしなりける','かぜそよぐならのをがはのゆふぐれは','みそぎぞなつのしるしなりける'),
  ('後鳥羽院','人もをし人もうらめしあじきなく','世を思ふゆゑにもの思ふ身は','ひともをしひともうらめしあぢきなく','よをおもふゆゑにものおもふみは'),
  ('順徳院','百敷や古き軒端のしのぶにも','なほあまりある昔なりけり','ももしきやふるきのきばのしのぶにも','なほあまりあるむかしなりけり');

全文検索を試す

全文検索を行うためには全文検索の対象のカラムにfulltext indexを貼ります．

手始めにpoetカラムの内容に作成しましょう．

MySQL_Client

ALTER TABLE ogura ADD FULLTEXT (poet) WITH PARSER ngram;

indexが貼れたら全文検索のクエリを入力します．
ここでは歌人(poet)に"天皇"を含んでいる句を検索してみます．

MySQL_Client

SELECT * FROM ogura WHERE MATCH (poet) AGAINST ('天皇')\G;

*************************** 1. row ***************************
        id: 1
      poet: 天智天皇
     above: 秋の田のかりほの庵の苫をあらみ
     below: わが衣手は露にぬれつつ
above_kana: あきのたのかりほのいほのとまをあらみ
below_kana: わがころもではつゆにぬれつつ
*************************** 2. row ***************************
        id: 2
      poet: 持統天皇
     above: 春過ぎて夏来にけらし白妙の
     below: 衣干すてふ天の香具山
above_kana: はるすぎてなつきにけらししろたへの
below_kana: ころもほすてふあまのかぐやま
*************************** 3. row ***************************
        id: 15
      poet: 光孝天皇
     above: 君がため春の野に出でて若菜摘む
     below: わが衣手に雪は降りつつ
above_kana: きみがためはるののにいでてわかなつむ
below_kana: わがころもでにゆきはふりつつ
3 rows in set (0.00 sec)

おお，それっぽい結果が出力されています👏

この調子で全てのカラムにとりあえずfulltext indexを貼ります．

MySQL_Client

ALTER TABLE ogura ADD FULLTEXT (above) WITH PARSER ngram;
ALTER TABLE ogura ADD FULLTEXT (below) WITH PARSER ngram;
ALTER TABLE ogura ADD FULLTEXT (above_kana) WITH PARSER ngram;
ALTER TABLE ogura ADD FULLTEXT (below_kana) WITH PARSER ngram;

これで，例えば上の句に"君がため"という言葉を含んだ句を探したければ以下のようなクエリを入力します．

MySQL_Client

SELECT * FROM ogura WHERE MATCH (above) AGAINST ('君がため')\G;

*************************** 1. row ***************************
        id: 15
      poet: 光孝天皇
     above: 君がため春の野に出でて若菜摘む
     below: わが衣手に雪は降りつつ
above_kana: きみがためはるののにいでてわかなつむ
below_kana: わがころもでにゆきはふりつつ
*************************** 2. row ***************************
        id: 50
      poet: 藤原義孝
     above: 君がため惜しからざりし命さへ
     below: 長くもがなと思ひけるかな
above_kana: きみがためをしからざりしいのちさへ
below_kana: ながくもがなとおもひけるかな
*************************** 3. row ***************************
        id: 33
      poet: 紀友則
     above: ひさかたの光のどけき春の日に
     below: しづ心なく花の散るらむ
above_kana: ひさかたのひかりのどけきはるのひに
below_kana: しづこころなくはなのちるらむ
*************************** 4. row ***************************
        id: 42
      poet: 清原元輔
     above: 契りきなかたみに袖をしぼりつつ
     below: 末の松山波越さじとは
above_kana: ちぎりきなかたみにそでをしぼりつつ
below_kana: すゑのまつやまなみこさじとは
*************************** 5. row ***************************
        id: 54
      poet: 儀同三司母
     above: 忘れじの行く末まではかたければ
     below: 今日をかぎりの命ともがな
above_kana: わすれじのゆくすゑまではかたければ
below_kana: けふをかぎりのいのちともがな
*************************** 6. row ***************************
        id: 76
      poet: 法性寺入道前関白太政大臣
     above: わたの原漕ぎ出でて見ればひさかたの
     below: 雲居にまがふ沖つ白波
above_kana: わたのはらこぎいでてみればひさかたの
below_kana: くもゐにまがふおきつしらなみ
6 rows in set (0.00 sec)

6件ヒットしました．
しかし実際に君がためというフレーズを含んでいる句は上位二首だけですね🤔

そこでMODEを選択して検索をしてみましょう．
試しにBOOLEAN MODEを使用します．

MySQL_Client

SELECT * FROM ogura WHERE MATCH (above) AGAINST ('君がため' IN BOOLEAN MODE)\G;

*************************** 1. row ***************************
        id: 15
      poet: 光孝天皇
     above: 君がため春の野に出でて若菜摘む
     below: わが衣手に雪は降りつつ
above_kana: きみがためはるののにいでてわかなつむ
below_kana: わがころもでにゆきはふりつつ
*************************** 2. row ***************************
        id: 50
      poet: 藤原義孝
     above: 君がため惜しからざりし命さへ
     below: 長くもがなと思ひけるかな
above_kana: きみがためをしからざりしいのちさへ
below_kana: ながくもがなとおもひけるかな
2 rows in set (0.01 sec)

先ほどと検索結果が変わり上位二首だけになりました．

N-gramによる全文検索の弱点

ここまでだと全文検索できて便利そうな印象を受けますがngramでの全文検索には弱点があります．
例えば上の句に"春"を含んだ句を検索したいと考えましょう．

MySQL_Client

SELECT * FROM ogura WHERE MATCH (above) AGAINST ('春')\G;
Empty set (0.00 sec)

このように検索結果が得られません．
これはbigramでindexが作成されているため春というワードを探すことができていないためです．
unigramでindexを貼り直すなどが考えられますが，それが必ずしも賢いアプローチだとは限らないでしょう．

indexの中身をみる

実際にindexの中身をみてみましょう．
とりあえず20件ほど表示してみました．

MySQL_Client

SET GLOBAL innodb_ft_aux_table="sample/ogura";

SELECT * FROM INFORMATION_SCHEMA.INNODB_FT_INDEX_TABLE ORDER BY doc_id LIMIT 20;
+--------+--------------+-------------+-----------+--------+----------+
| WORD   | FIRST_DOC_ID | LAST_DOC_ID | DOC_COUNT | DOC_ID | POSITION |
+--------+--------------+-------------+-----------+--------+----------+
| つつ   |            3 |          99 |         5 |      3 |       27 |
| らみ   |            3 |          34 |         2 |      3 |       39 |
| 天皇   |            3 |          17 |         3 |      3 |        6 |
| の苫   |            3 |           3 |         1 |      3 |       27 |
| をあ   |            3 |           3 |         1 |      3 |       33 |
| が衣   |            3 |          17 |         2 |      3 |        3 |
| の庵   |            3 |           3 |         1 |      3 |       21 |
| ほの   |            3 |           3 |         1 |      3 |       18 |
| 秋の   |            3 |          39 |         3 |      3 |        0 |
| 田の   |            3 |          73 |         2 |      3 |        6 |
| りほ   |            3 |           3 |         1 |      3 |       15 |
| 苫を   |            3 |           3 |         1 |      3 |       30 |
| 智天   |            3 |           3 |         1 |      3 |        3 |
| 庵の   |            3 |           3 |         1 |      3 |       24 |
| あら   |            3 |          70 |         4 |      3 |       36 |
| にぬ   |            3 |           3 |         1 |      3 |       18 |
| かり   |            3 |          90 |         6 |      3 |       12 |
| のか   |            3 |          90 |         3 |      3 |        9 |
| 天智   |            3 |           3 |         1 |      3 |        0 |
| の田   |            3 |           3 |         1 |      3 |        3 |
+--------+--------------+-------------+-----------+--------+----------+
20 rows in set (0.01 sec)

bigramでwordが登録されていることがわかるかと思います．

おわりに

MySQLを使った全文検索について取り扱いました．
Elasticsearchを使った全文検索の方がよく見ますがもともとデータベースにMySQLを使用していて少ない工数で実現する場合などは結構便利なのではないでしょうか．

また今回はデフォルトのbigramによるN-gramパーサーを取り扱いましたがngramのカスタマイズはもちろん可能ですし，形態素解析のMeCabパーサーを利用することも可能です．

参考

• MySQL InnoDB の全文検索機能をサクッと試してみました, https://techblog.gmo-ap.jp/2020/01/06/mysql-innodb-fulltext-search-tuto/