CODE VS 2.1 をSQLでやるとどうなる6
前回の続きです。今回は消すブロックを選択するところまで作ります。
作るのは http://codevswc.jp/jpn/rule.html これです。
是非、ルールをよく読んで、「自分なら SQL でこういう方針で作る」
「Javaなら、.Netなら、Rubyなら…… こういう方針で作る」
と想像しながら読んでください。
多分、多くの人が、「『消すブロックを選択するところ』が一番、SQLでできない」と考えるところじゃないかと思っています。が、この辺の処理は極めてSQL的です。
最初の方で作った4方向、8方向へオフセットするテーブルを使いますから、勘のいい人はアレを観た時点で分かったと思いますが……。
現状のデータでは分かりにくいので新たなデータでやります
このSQL http://www.g1sys.co.jp/TestB.txt を流して確認すると、
-- 確認 SELECT * FROM DispBoard10(-1, 0, 0, 0) ORDER BY Row DESC;
図の様に返ってきます。
以降、これをベースに考えます。
色が変わっているブロックを選択出来れば成功です。
4方向か8方向か
まずは消すブロックをカウントするのですけれど、今、落としたブロックは分かるわけですから、落としたブロックだけを8方向に合算して行き、合計が条件と一致する(現在のゲームでは10)になれば消すブロックと考えれば良いことです。
(1, 0)の位置にある[5]から右下方向に合算して行って(0, 1)の[5]を見つけ、「消すブロック」と決定することができます。
さらに、(0, 0)の位置の[1]、(1, 1)の位置の[11]はどの方向合算していっても消せませんが、(0, 1)の[5]を左上に合算をして行ったとき、既に選択済みの(1, 0)の[5]が二重に選択されます。単純な個数なら重複して抽出し、後から重複を消せばすむ話ですが、ルールからオレンジの(0, 1)の[5]は二重計上しなければ行けません。
この複雑さを回避するために、全部の数字を4方向にチェックしていくのが一般的と思います。(ちなみに8方向に合算していくと、必ず二重計上され無駄です)
いくつかフラグを足せばSQLでも、落としたブロックだけをチェックするだけでできなくはないのですが、解説が大変なので4方向に合算していくやり方にしました(苦笑)
恐らく、パフォーマンス的にはそんなに変わらないと思います。
再帰SQLとCROSS JOIN
WITH cb (GameID, SimulationID, Turn, Row, Col , ERow, ECol, Num, Amount, Cnt, Angle, Memo) -- 4方向に合算された結果 AS( SELECT GameID , SimulationID , Turn , Row , Col , Row AS ERow , Col AS ECol , Num , Num AS amount , 1 AS cnt , 0 --調べている位置 , CAST(N'調査' AS nvarchar(8)) AS Memo FROM Board WHERE GameID =-1 AND Turn = 0 AND Num < 10 UNION ALL SELECT cb.GameID , cb.SimulationID , cb.Turn , cb.Row -- 調査元の行 , cb.Col -- 調査元の列 , bd.Row -- 調査先の行 , bd.Col -- 調査先の列 , bd.Num , cb.Amount + bd.Num , Cnt + 1 , dr.Angle , dr.Memo FROM Board AS bd INNER JOIN (cb CROSS JOIN Direction4 AS dr) ON bd.Row = cb.Row + (dr.RowOffset * Cnt) AND bd.Col = cb.Col + (dr.ColOffset * Cnt) AND bd.GameID = cb.GameID AND bd.SimulationID = cb.SimulationID AND bd.turn = cb.turn WHERE cb.amount + bd.num <= 10 ) -- SELECT * FROM cb; -- として、以下をコメントアウトすれば4方向に合算した結果が確認できる , eb (GameID, SimulationID, Turn, Row, Col , ERow, ECol, Num, Amount, Cnt, Angle, Memo) -- 消すブロック AS( SELECT * FROM cb AS bd WHERE EXISTS ( SELECT * FROM cb WHERE bd.row = cb.row AND bd.col = cb.col AND (bd.angle = 0 OR bd.angle = cb.angle) AND cb.amount = 10 ) ) -- WITHの終わり -- SELECT * FROM eb; -- でより詳しい情報が表示されます。 SELECT ERow, ECol, Num FROM eb;
実行すれば、(0, 1)の[5]が重複して、都合5つのブロックが選択されるはずです。
つづく
CODE VS 2.1 をSQLでやるとどうなる5
前回の続きです。今回は、パックを投下するところまでを作ります。
作るのは http://codevswc.jp/jpn/rule.html これです。データは、与えられるInputのサンプルとします。
是非、ルールをよく読んで、「自分なら SQL でこういう方針で作る」
「Javaなら、.Netなら、Rubyなら…… こういう方針で作る」
と想像しながら読んでください。
プログラムを作るよりも解説の方がはるかに難しい……。だんだん手抜きになってきていますがご容赦。
Turnテーブル(シミュレーション用)から全通りのパックを投下する
前回作ったTurnテーブル(シミュレーション用)から、1つ目のパックが取り得る全通りのパターンで投下します。
まず、現在、盤面に残っているブロック(現状は空)に、新しい SimulationID 次のターンにインサートします。
INSERT INTO Board (GameID, SimulationID, Turn, Row, Col, Num) SELECT t.GameID , t.SimulationID , t.Turn , b.Row , b.Col , b.Num FROM Turn t LEFT JOIN board b ON t.GameID = b.GameID AND 0 = b.SimulationID -- 採用したパターン AND t.Turn = b.Turn -1 WHERE t.GameID = 0 AND t.Turn = 1;
続いて、パックデータを投下します
尚、上のSQLのSELECT部分と、下のSQLのSELECT部分を、UNION ALLで繋いで1回にしても同じです。
INSERT INTO Board (GameID, SimulationID, Turn, Row, Col, Num) SELECT tp.GameID , tp.SimulationID , tp.Turn , ISNULL(b.MaxRow, -1) -- ゼロオリジンなのでブロックがないときは-1 + RANK() OVER(PARTITION BY tp.SimulationID, tp.tCol ORDER BY tp.tRow) , tp.tCol , tp.Num FROM (SELECT t.GameID, t.Turn, t.SimulationID, t.Rot , dbo.ChangeRow(t.Rot, p.Row, p.Col, 4) AS tRow -- 回転後のRow , dbo.ChangeCol(t.Rot, p.Row, p.Col, 4) + t.XPos AS tCol -- 回転後落とす位置のCol , p.Num FROM Turn t INNER JOIN Pack p --参照されるPackテーブル ON t.GameID = p.GameID AND t.Turn = p.Turn + 1 -- Turnテーブルのターンはカウントアップ済み WHERE t.Turn = 1 ) AS tp LEFT JOIN ( SELECT MAX(Row) As MaxRow, Col FROM Board WHERE Turn = 0 -- コピー元を観た方が効率的 GROUP BY Col) As b ON tp.tCol = b.Col ; -- 確認 xx に1〜52までの数字を入れて、パックを回転させて -- 狙った位置に投下できているか確認しましょう。 SELECT * FROM DispBoard10(0, xx, 1, 0) ORDER BY Row DESC;
CODE VS 2.1 をSQLでやるとどうなる4
前回の続きです。今回はパックを回転するするところまでを作ります。
作るのは http://codevswc.jp/jpn/rule.html これです。データは、与えられるInputのサンプルとします。
是非、ルールをよく読んで、「自分なら SQL でこういう方針で作る」
「Javaなら、.Netなら、Rubyなら…… こういう方針で作る」
と想像しながら読んでください。
パックを回転します
回転するには図の様に、Row、Colを変更する必要があります。これは他の言語でもほぼ同じでしょう。もちろん、左上を (0, 0) としたときは変更の必要があるので、お気を付けください。
具体的には図の通りになります。
これは他の言語でもされるように関数にしましょう。
……ですが、SQLでは一つずつ作ることになります。
CREATE FUNCTION ChangeRow (@Rot int, @Row int, @Col int, @Size int) RETURNS int BEGIN DECLARE @Ret int; SET @Ret = CASE @Rot WHEN 1 THEN (@Size - 1) - @Col WHEN 2 THEN (@Size - 1) - @Row WHEN 3 THEN @Col ELSE @Row END; RETURN @Ret; END; CREATE FUNCTION ChangeCol (@Rot int, @Row int, @Col int, @Size int) RETURNS int BEGIN DECLARE @Ret int; SET @Ret = CASE @Rot WHEN 1 THEN @Row WHEN 2 THEN (@Size - 1) - @Col WHEN 3 THEN (@Size - 1) - @Row WHEN @Col END; RETURN @Ret; END;
パックを投下するリストを作る
ルールhttp://codevswc.jp/jpn/rule.html を読むと、パックは横方向(X軸)はマイナスの位置にも落とせます。
ゼロオリジンになっていますので、取り得るパックを落とす位置の一番右端は、
0 - パックのサイズ - 1
となります。
左端は、
盤の幅 - 1
となります。
パックは90°ずつ回した4パターンがあります。
今回のゲーム
パックのサイズ = 4
盤の幅 = 10
ですから、パックを投下するパターンは、
-3 〜 9 = 13通り × 4つの角度 = 52通り
になります。
SELECT xp.Seq AS XPos , rt.Seq AS Rot FROM tf_SequentialNums(-3, 10) AS xp CROSS JOIN tf_SequentialNums(0, 3) AS rt
1つめのパックを落とせる全てのパターンを作ることができます。
SimulationID は 1から採番して 0を実際に採用したアウトプットとします。
投下した時点でTurnはカウントアップされます。
INSERT INTO Turn (GameID, SimulationID, Turn, XPos, Rot) SELECT 0, Rank() OVER(ORDER BY xp.Seq, rt.Seq) , 0 + 1, xp.Seq, rt.Seq FROM tf_SequentialNums(-3, 9) AS xp CROSS JOIN tf_SequentialNums(0, 3) AS rt;
回転したリスト
作成したTurn(シミュレーション用)のデータとパックを結合して、回転後のRow、Colの一覧を見る。
SELECT t.SimulationID, t.Rot , p.Row, p.Col, p.Num , dbo.ChangeRow(t.Rot, p.Row, p.Col, 4) AS tRow -- 回転後のRow , dbo.ChangeCol(t.Rot, p.Row, p.Col, 4) AS tCol -- 回転後のCol FROM Turn t INNER JOIN Pack p --参照されるPackテーブル ON t.GameID = p.GameID -- TurnテーブルのキーのSimulationIDと結合して AND t.Turn = p.Turn -- ないのでSimulationIDの種類数倍になる。
つづく
CODE VS 2.1 をSQLでやるとどうなる3
前回の続きです。
作るのは http://codevswc.jp/jpn/rule.html これです。所謂、「落ちゲー」もある程度、SQLで作れます。
是非、ルールをよく読んで、「自分なら SQL でこういう方針で作る」
「Javaなら、.Netなら、Rubyなら…… こういう方針で作る」
と想像しながら読んでください。
パックを登録
前回説明したとおり、左下を(0, 0) として、(行, 列)の組合せで登録してきます。
例えば、Sampleにあるように、Javaでは以下の様に登録することが多いと思います。
int[][][] pack = new int[step][size][size]; String endstr = null; for(int i=0;i<step;i++){ for(int j=0;j<size;j++){ for(int k=0;k<size;k++){ pack[i][j][k] = scan.nextInt(); -- 0以外の数字のとき、INSERTする。 } } endstr = scan.next(); }
この pack[i][j][k] = scan.nextInt(); の j が Row にあたるのですが、左下を(0, 0)としているため、Row = size - j - 1 となります。好みで変えてください。
0は不要ですので、0のときは INSERT しません。
連番テーブルファンクションを作る
よく使うので、共通関数としてDBに一つ作っておいても良いかも知れません。
CREATE FUNCTION tf_SequentialNums (@From int, @To int) RETURNS TABLE AS RETURN ( WITH SequentialNums (Seq) AS ( SELECT @From AS Seq UNION ALL SELECT Seq + 1 FROM SequentialNums WHERE Seq + 1 <= @To ) SELECT Seq FROM SequentialNums );
使い方
SELECT * FROM tf_SequentialNums(-3, 10);
とすれば、-3から10までの連番を作ることができます。
盤面を確認する
盤面(Boardテーブル)にパック(Packテーブル)の内容を落としていくゲームですが、Boardテーブルも数値の羅列で何のことか分かりません。しかし、以下のSQLで確認することが可能です。
残念なことに、SQLでは列を動的に指定することはできません。そのため、盤面の種類毎(今回の大会では、盤の幅が10、15、20)に作るか、一番大きな盤面で代替するかになります。
よく使うことになるので、これもテーブルファンクションにしておくと良いでしょう。
個人的に PIVOT は嫌いで、CASEで書くことが多いのですが、今回はPIVOTにしておきました。
DispBoard15、DispBoard20 は列数が多いだけなので、ご自身で作ってください。
CREATE FUNCTION DispBoard10 (@GameID int, @SimulationID money, @Turn int, @Chain int) RETURNS TABLE AS RETURN ( SELECT pvt.Seq AS Row , [00], [01], [02], [03], [04], [05], [06], [07], [08], [09] FROM tf_SequentialNums(0, 19) AS l -- 盤の高さ+Packサイズで良いと思う LEFT JOIN Board AS b ON l.Seq = b.Row AND @GameID = b.GameID -- WHERE句ではなくFROM句に書くのがミソ AND @SimulationID = b.SimulationID -- WHERE句ではなくFROM句に書くのがミソ AND @Turn = b.Turn -- WHERE句ではなくFROM句に書くのがミソ AND @Chain = b.Chain -- WHERE句ではなくFROM句に書くのがミソ PIVOT ( MAX(b.Num) FOR b.Col IN([00], [01], [02], [03], [04], [05], [06], [07], [08], [09]) ) AS pvt ) go
使い方
SELECT * FROM DispBoard10(0, 1, 1, 0) ORDER BY Row DESC
現状は空なので、NULLが並んだ盤面が再現されるはずです。
与えられるInputのサンプル。
Packテーブルにデータを入れるInsert文のサンプル。
(SQLServer2005向けで、2008なら列リストは要らないのですが……)
(つづく)
CODE VS 2.1 をSQLでやるとどうなる2
作るのは http://codevswc.jp/jpn/rule.html これです。
所謂、「落ちゲー」をSQLで作るにはどうしたらよいか?ということになります。
かなり複雑でルールを良く読み込まないの理解できないでしょう。
是非、よく読んで、「自分なら SQL でこういう方針で作る」
「Javaなら、.Netなら、Rubyなら…… こういう方針で作る」
と想像しながら読んでください。
悩んでいます(苦笑)
Javaでやったとき、パックや盤の左上を原点(0, 0)として、それぞれRow, Col(行, 列)としてコーディングしたのですが、一番底の位置がゼロでないのが、どうもしっくりこなかった。
で、SQLでするのに左下を原点(0, 0)としようと思ったのですが、左下だとこれまたRow, Col(行, 列)と呼んではしっくりこない。
左下を原点とすると(X, Y)としないとしっくりこないが、そうすると元のRow, Col(行, 列)で言うとCol, Row(列, 行)と逆になることになって、これまたしっくりこない。
どうするのが一般的なのでしょうか?
私はゲームをほとんど作ったことがないのでよく分かりませんので、おかしいと思った人はコメントや、Twitterで絡んでください。
とりあえず、左下を原点、Row, Col(行, 列)としますので、しっくりこない人は適当に読み替えてください。
テーブル
欲張って、SimulationID というのを付けています。実際に問題を解くにはSimulationIDだけでは足りないと思うけれど……。
データは 0 以外の数字をPackテーブルに突っ込みます(ここのコードは単純なINSERTなので解説しません)。
それを、1ターン毎にBoardテーブルに追加していくことでゲームを再現します。
CREATE TABLE Pack ( GameID int DEFAULT 0 NOT NULL, Turn int DEFAULT 0 NOT NULL, Row int DEFAULT 0 NOT NULL, Col int DEFAULT 0 NOT NULL, Num int DEFAULT 0 NOT NULL , PRIMARY KEY (GameID, Turn, Row, Col) ); CREATE TABLE Board -- Chain単位で保存する ( GameID int DEFAULT 0 NOT NULL, SimulationID money DEFAULT 0 NOT NULL, Turn int DEFAULT 0 NOT NULL, Chain int DEFAULT 0 NOT NULL, Row int DEFAULT 0 NOT NULL, Col int DEFAULT 0 NOT NULL, Num int DEFAULT 0 NOT NULL , PRIMARY KEY (GameID, SimulationID, Turn, Chain, Row, Col) ); CREATE TABLE Deleted -- なくても良い。ステップで考えるときに必要 ( GameID int NOT NULL, SimulationID money NOT NULL, Turn int NOT NULL, Chains int NOT NULL, Row int NOT NULL, Col int NOT NULL , PRIMARY KEY (GameID, SimulationID, Turn, Chains, Row, Col) ); CREATE TABLE Turn -- 記録する ( GameID int DEFAULT 0 NOT NULL, SimulationID money DEFAULT 0 NOT NULL, Turn int DEFAULT 0 NOT NULL, XPos int DEFAULT 0 NOT NULL, Rot int DEFAULT 0 NOT NULL, Ereases int DEFAULT 0 NULL, Chains int DEFAULT 0 NULL, FireCounts int DEFAULT 1 NULL, CurScore money DEFAULT 0 NULL, TotalScore money DEFAULT 0 NULL , PRIMARY KEY (GameID, SimulationID, Turn) ); CREATE TABLE Game -- パラメータで渡しても良い気もする ( GameID int DEFAULT 0 NOT NULL, Width int NULL, Height int NULL, MaxTurn int NULL, PackSize int NULL, Sums int NULL, P_Point int NULL, A_Point int NULL, B_obs int NULL, Th int NULL, Memo text NULL , PRIMARY KEY (GameID) );
そのほかに必要なテーブル。
CREATE TABLE Direction4 -- 4方向にチェックするときように4レコード保存 ( Angle int NOT NULL, RowOffset int NULL, ColOffset int NULL, Memo nvarchar(8) NULL , PRIMARY KEY (Angle) ); CREATE TABLE Direction8 -- 8方向にチェックするときように8レコード保存 ( Angle int NOT NULL, RowOffset int NULL, ColOffset int NULL, Memo nvarchar(8) NULL , PRIMARY KEY (Angle) );
Direction4 には、上、右上、右、右下、つまり、RowOffset, ColOffset をそれぞれ、(1, 0)(1, 1)(0, 1)(-1, 1) を登録する。
Direction8 には、上、右上、右、右下、下、左下、左、左上、つまり、RowOffset, ColOffset をそれぞれ、(1, 0)(1, 1)(0, 1)(-1, 1)(-1, 0)(-1, -1)(0, -1)(1, -1) を登録する。
(つづく)
CODE VS 2.1 をSQLでやるとどうなる1
CODE VS というコーディングコンテストに参加しました。
CODE VS 2.1 で勝てなかったからネタに
結果は惨憺たるもので、私は1手読みまでで、Largeの条件で9億ぐらいのスコアー。時間とメモリーさえあれば無限に先読み(総当たりで)できるけれど、数手先まで伸ばしても同じスコアーでした。
終わりの方に気づいたのですが、何手先まで読むかというのは間違いでした(当たり前)。1手読みでもう少しスコアーを伸ばせるエレガンスなロジックにして、それで違った条件の手が出現したとき(ブロックを消すべきか、積むべきか迷う局面)をターニングポイントとして記録。何らかの条件を満たしたときチェックポイントとして決め(例えば、ブロックを消す条件になったとき)そのときのスコアーなどが、目標を満たしてないとき、ターニングポイントまで戻る。
というようなやり方ならもっと伸ばせたと思う。
多分、上位の人は、もっとエレガンスなやり方をしていると思うけれど、私が思いついたのはその程度。
何にしても、私はゲームをほとんどしたことがないためか、プログラム以前に、私自身が1ターン毎の局面で「何をもって良い手とするか」という条件を見いだすことができなかった。つまり、ターニングポイントも、チェックポイントも、私には良いものは選べなかったと思うので、スコアーを伸ばせるはずがない……。
ぶっちゃけ、年齢的な衰えも相当に感じたorz
思考力の瞬発力は確実に衰えているな〜、それにしても、後、何日かあれば……。
正直、子供っぽい性格なので本当に悔しい……。
それはともかく、スコアーが出なかったのでじゃあ、ネタにしよう。ということにしました。
私は Java で参戦したのですが、画面イメージは必要ありませんが、データ的には問題のゲームを完全に再現しなければ解けません。そこまでをSQLでやったらどうなるか?ということを書いてみたいと思います。
※ SQLServer2005以上を想定しています。
SQLでやる部分(基本設計)予定
確認用(ある時点のボードの状態を確認する)
SELECT文(PIVOT/クロス集計)
数値羅列テーブル(再帰SQL・テーブルファンクション)
Input情報をDBに入れる
INSERT文(単純に入力する)
以下を繰り返す
Boardにパックを落とす
INSERT SELECT文(一括挿入・分析関数)
※ パックの角度(4通り)× パックが置ける位置の通り
ブロックを消せなくなるまで繰り返す。
消せるブロックを探す
INSERT SELECT文(一括挿入・再帰SQL・CROSS JOIN)
消すお邪魔ブロックを探す
INSERT SELECT文(一括挿入・再帰SQL・CROSS JOIN)
パックを落とす
INSERT SELECT文(一括挿入・分析関数)
繰り返し終わり
スコアーを計算する
UPDATE SELECT文(一括更新・集計関数)
良い手を探す(ここが私には分からない所)
SELECT文(集計関数イロイロ)
不要情報を消す ※特に必要ないけど、場合によってはエライ量になるので
DELETE(一括削除)
繰り返し終わり
最終的にいろんなシミュレーションをしようと思えば、もっと必要になりますが、最低限であれば、SQL文が10個ぐらいあれば事足りる。
ターン毎に区切るのはゲームの仕様上同じですが、Javaなどと違う点は、一気に全通りのパックを落としてしまうところ。この辺が集合理論の SQL の特徴です。
業務SE・PGなら、問題を見た瞬間にこのぐらいまで行って欲しい。
もちろん、上の様なロジックは、私は Javaなどでやる場合と、SQLでやる場合と、ほぼ同時に頭の中にあって、
SQLは一回でできるし、状況も確認しやすいから楽。
メモリーで完結するスピード勝負のコンテストでは SQL は不利。
と判断しました。
具体的な SQL は次回以降ボチボチ書いていきます。
このぐらいが「SQL でできるよね」って判断している人は、実務で設計を考えるとき、全く違う景色で観ています。
違う景色から観れば、RDBMSを使うなら SQL を使えば、工数もパフォーマンスも、本当に劇的に違うものになるのですけどね……。
MySQLで全文検索2
前回(MySQLで全文検索1)の続き。
my.cnf(my.ini)の調整
FullTextIndex でインデックスされる最小の文字数を確認する。
MySQL5.5以下
mysql> SHOW VARIABLES like 'ft_min_word_len'; +-----------------+-------+ | Variable_name | Value | +-----------------+-------+ | ft_min_word_len | 4 | +-----------------+-------+
MySQL5.6 では innodb_ft_min_token_size に変更されている様子(デフォルトは3)。
mysql> SHOW VARIABLES like 'innodb_ft_min_token_size'; +-----------------+-------+ | Variable_name | Value | +-----------------+-------+ | innodb_ft_min_token_size | 3 | +-----------------+-------+
それぞれが、2以上のとき、my.cnf(my.ini)で ft_min_word_len を追加(変更)し、MySQLを再起動する。
[mysqld] #・・・に下記を追加追加(変更)。 ft_min_word_len=2 innodb_ft_min_token_size=2
再起動後、変わっているか確認。
mysql> SHOW VARIABLES like 'ft_min_word_len'; +-----------------+-------+ | Variable_name | Value | +-----------------+-------+ | ft_min_word_len | 2 | +-----------------+-------+ mysql> SHOW VARIABLES like 'innodb_ft_min_token_size'; +-----------------+-------+ | Variable_name | Value | +-----------------+-------+ | innodb_ft_min_token_size | 2 | +-----------------+-------+
検索用のカラムを作成しインデックスを作る
検索用のカラムを追加する。
例えば、以下の様なテーブルに
CREATE TABLE address ( address_id smallint(5) unsigned NOT NULL AUTO_INCREMENT, address varchar(50) NOT NULL, address2 varchar(50) DEFAULT NULL, district varchar(20) NOT NULL, city_id smallint(5) unsigned NOT NULL, postal_code varchar(10) DEFAULT NULL, phone varchar(20) NOT NULL, last_update timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, PRIMARY KEY (address_id), KEY idx_fk_city_id (city_id), ) ;
searchというカラムを追加する。
ALTER TABLE address ADD COLUMN search TEXT NULL AFTER last_update;
追加したカラムに全文検索したいカラムの内容を繋いで、nGram でカットして更新する。
(あまり一度に更新されないように、WHERE句で絞りながら行ってください)
UPDATE address SET search = nGram( CONCAT(IFNULL(address), ' ', IFNULL(address2)) ) WHERE -- 絞りながら行ってください。 ;
search カラムに FullTextIndex を作成する。
ALTER TABLE address ADD FULLTEXT(search);
全文検索を行う
ヒット率順に出力。
※ ただし、全体の50%を超えたら出力されない。
SELECT * FROM address WHERE MATCH(search) against(ngram('住之江区 HOGEマンション'));
ヒット率を無視して出力
SELECT * FROM address WHERE MATCH(search) against(ngram('住之江区 HOGEマンション') in boolean mode );
というような形で、Like検索よりは高速に検索することができます。
感想
本当にビッグデータのときは、groonga などを利用する方が良いと思いますけれど……。
アプリケーション側で2文字ずつにカットするよりは、DBで行った方がデータの正規化が容易で、トリガーを入れておけばメンテフリーになります。
異字体は名前、住所をユーザが入れるシステムでは、正規化しておいた方が良いと思います。
今回、初めて MySQL のストアドプロシージャ(ファンクション)を書きましたが、シンプルである程度は使えると思いました。ちなみに、半角カナを変換しているのは utf8_unicode_ci にしていれば不要とも言えるのですが、全角に直さないと2文字にカットできないためで……。
半角カナはアプリ側で禁止(変換)しても良いかと思うのですが、携帯から入力する人は気軽に使うので微妙ですね。