前置き
最近、データベースの再帰クエリを含むビジネス・シナリオがあります。この会社ではOracleを使用していますが、ご存知のようにOracleには再帰クエリ機能が付いているので、実装は特に簡単です。
しかし、MySQLには再帰クエリがないように記憶しています。
それで、こんな記事もあります。
記事の重要な知識ポイント
- Oracle 再帰クエリ、connect by prior Usage で開始します。
- find_in_set
- concat，concat_ws，group_concat
- MySQL カスタム関数
- MySQL再帰クエリの手動実装
Oracle 再帰クエリ
Oracle では、再帰クエリは connect by prior 構文を使用して実装されています。
先行キーワードが子ノード側か親ノード側か、そして現在照会されているノードが含まれているかどうかによって、4つのケースがあります。
prior 子ノード側で
select * from dept start with id='1001' connet by prior id=pid;
ここでは、現在のノードとその子ノードが、id='1001' という条件に従って再帰的にクエリされます。クエリ結果には、それ自身とすべての子ノードが含まれます。
select * from dept start with pid='1001' connect by prior id=pid;
ここでは、pid='1001' という条件に従って、現在のノードのすべての子が再帰的にクエリされます。クエリー結果には、自分自身ではなく、すべての子ノードのみが含まれます。
考えてみればその通りです。開始条件は親ノードをルートとして下方向に再帰するので、当然現在のノードは除外されます。
prior 親ノードで
select * from dept start with id='1001' connect by prior pid=id;
ここでは、現在のノードとその親が id='1001' という条件に従って再帰的にクエリされます。クエリの結果には、それ自身とすべての親が含まれます。
select * from dept start with pid='1001' connect by prior pid=id;
ここでは、pid='1001'という条件に従って、現在のノードの第一世代の子とその親が再帰的にクエリされます。クエリの結果には、自身の第一世代の子だけでなく、すべての親も含まれます。(自分自身を含む)
なぜなら、クエリの開始条件はルートノードとしての親ノードであり、上方再帰では当然、最初のレベルの子ノードの現在の親ノードを含める必要があるからです。
上記の4つは、最初は混乱して覚えやすいと思われるかもしれませんが、そうではありません。
priorの位置が子ノードの端であれば下へ、親ノードの端であれば上へ再帰することを覚えておいてください 。
- 開始条件は、子ノードであれば当然そのノードを含みます。
- 開始条件が親ノードの場合、下向きの再帰では当然、現在のノードは除外されます。上向きの再帰では、現在のノードとその最初の子ノードを含める必要があります。
MySQL 再帰クエリ
このように、Oracleでは再帰クエリを非常に簡単に実装できます。しかし、MySQL ではこのようなことはできませんので、手動で再帰クエリを実装する必要があります。
便宜上、部署テーブルを作成し、再帰リレーションシップを形成できるいくつかのデータを挿入します。
DROP TABLE IF EXISTS `dept`;
CREATE TABLE `dept` (
`id` varchar(10) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL,
`name` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL,
`pid` varchar(10) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL,
PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci ROW_FORMAT = Dynamic;
INSERT INTO `dept`(`id`, `name`, `pid`) VALUES ('1000', 'head_company', NULL);
INSERT INTO `dept`(`id`, `name`, `pid`) VALUES ('1001', '東京ブランチ', '1000');
INSERT INTO `dept`(`id`, `name`, `pid`) VALUES ('1002', '東京ブランチ', '1000');
INSERT INTO `dept`(`id`, `name`, `pid`) VALUES ('1003', '東京R&Dの, '1001');
INSERT INTO `dept`(`id`, `name`, `pid`) VALUES ('1004', '東京財務部', '1001');
INSERT INTO `dept`(`id`, `name`, `pid`) VALUES ('1005', '東京マーケティング部', '1001');
INSERT INTO `dept`(`id`, `name`, `pid`) VALUES ('1006', '東京R&D部 ', '1003');
INSERT INTO `dept`(`id`, `name`, `pid`) VALUES ('1007', '東京R&D第2部', '1003');
INSERT INTO `dept`(`id`, `name`, `pid`) VALUES ('1008', '東京R&D部, '1006');
INSERT INTO `dept`(`id`, `name`, `pid`) VALUES ('1009', '東京R&D部, '1006');
INSERT INTO `dept`(`id`, `name`, `pid`) VALUES ('1010', '東京R&D第2部、チーム1', '1007');
INSERT INTO `dept`(`id`, `name`, `pid`) VALUES ('1011', '東京R&D第2部、第2チーム', '1007');
INSERT INTO `dept`(`id`, `name`, `pid`) VALUES ('1012', '東京マーケティング部', '1005');
INSERT INTO `dept`(`id`, `name`, `pid`) VALUES ('1013', '東京R&Dの, '1002');
INSERT INTO `dept`(`id`, `name`, `pid`) VALUES ('1014', '東京R&D部 ', '1013');
INSERT INTO `dept`(`id`, `name`, `pid`) VALUES ('1015', '東京R&D第2部', '1013');
そうです、オラクルの再帰、それが先ほど使ったテーブルです。
また、ここで、この後に使用するMYSQLの関数をいくつか復習しておく必要があります。
find_in_set
関数のシンタックス： find_in_set(str,strlist)
strはクエリーされる文字列を表し、strlistはカンマで区切られた文字列です。
この関数は、文字列strlist内のstr文字列の位置を求め、1〜nを返します。見つからなければ0を返します。
栗をひとつ：
select FIND_IN_SET('b','a,b,c,d');
結果は2を返します。なぜなら b の位置は 2 番目の部分文字列の位置だからです。
さらに、テーブル・データを照会する場合にも、次のような使い方があります：
select * from dept where FIND_IN_SET(id,'1000,1001,1002');
結果はstrlist内のidを持つすべてのレコード、すなわちid = '1000'、id = '1001'、id = '1002'を返します。
これを見て、再帰的なクエリを解くヒントがあるのかどうかわかりません。
すべての子ノードに対する下向きの再帰的クエリの例を見てみましょう。現在のノードとそのすべての子ノードを含むカンマで連結された文字列strlistを見つけ、それをfind_in_set関数に渡すことができないかと考えました。そうすれば、必要なすべての再帰的データを照会ることができるでしょう。
そこで、このような文字列strlistをどのように構築するかが問題となります。
これには、以下の文字列スプライシング機能が必要です。
concat，concat_ws，group_concat
まず、文字列を連結する関数ですが、最も基本的なものは concat です。これはN個の文字列を連結するのに使います。
select CONCAT('M','Y','S','Q','L') from dual;
結果は'MYSQL'文字列です。
次に、concatはデフォルトのセパレーターとしてカンマを使用しますが、concat_wsはセパレーターを指定することができ、最初のパラメーターにはアンダースコアなどのセパレーターが渡されます。
第三に、group_concat関数は、より強力な、同時にグループ化することができ、特定の区切り文字を持つフィールドは、文字列にスプライスされます。
スプライスするフィールドの値を強調しないようにしたり、ソートしたり、セパレータを指定したりするオプションのパラメータがあることがわかります。指定しない場合、デフォルトはカンマ区切りです。
deptテーブルでは、テーブル内のすべてのIDをカンマでつなげることができます。
再帰クエリ用のMySQLカスタム関数
以上で文字列の継ぎ合わせの問題も解決したことがお分かりいただけると思います。次に、再帰的な関係を持つ文字列をどのように構成するかが問題となります。
関数は、ルート・ノードIDを渡すことによって、そのすべての子を見つけるようにカスタマイズすることができます。
下向きの再帰を例にとってみましょう。
delimiter $$
drop function if exists get_child_list$$
create function get_child_list(in_id varchar(10)) returns varchar(1000)
begin
declare ids varchar(1000) default '';
declare tempids varchar(1000);
set tempids = in_id;
while tempids is not null do
set ids = CONCAT_WS(',',ids,tempids);
select GROUP_CONCAT(id) into tempids from dept where FIND_IN_SET(pid,tempids)>0;
end while;
return ids;
end
$$
delimiter ;
デリミタ $$ はターミネータの定義に使用されます。MySQL のデフォルトのターミネータがセミコロンであることを知っている場合、これは命令が終了し、実行されることを示します。しかし、関数本体では、セミコロンが終端でなくとも遭遇させたいことがあります。そこで、デリミタを一時的に任意の他の値に変更する必要があります。これは$$に出会うまで終了せず、現在のステートメントを実行することを意味します。
get_child_list$$ が存在すれば、関数を削除します。関数get_child_listが既に存在する場合は、まずそれを削除します。現在のカスタム終端記号$$は、文の終了と実行に使用されていることに注意してください。 これは、以下の関数本体から分離する必要があるためです。
関数本体はbeginとendで囲まれます。特定のロジックを記述するために使用します。
declareは変数の宣言に使われ、defaultを使ってデフォルト値に設定することができます。
ここで定義されたidは、関数全体の戻り値として使用され、最終的に必要となるカンマ区切りの再帰文字列をつなぎ合わせるために使用されます。
tempidは、以下のwhileループで一時的に生成される、すべての子ノードのカンマをつなげた文字列です。
セットは、変数に値を割り当てるために使用されます。ここでは、入力されたルート・ノードが tempids に割り当てられます。
while do ... end while; ループ・ロジックを含むループ文。end whileの最後にはセミコロンが必要です。
ループの内部では、CONCAT_WS関数が、最終的なidと一時的なtempidをカンマで連結するために使用されます。
そして、FIND_IN_SET(pid,tempids)>0の場合、tempidsのすべてのpidを繰り返し、この親のすべての子idを見つけ、tempidsにGROUP_CONCAT(id)を入れて、これらの子idをカンマで連結し、更新されたtempidsを上書きします。
次のループが来ると、再びidをスプライスし、すべての子のすべての子を探します。このループを繰り返し、子ノードを再帰的に1層ずつ走査します。tempidsが空になり、すべての子ノードを走査し終わったことを示すまで、ループは終了します。
ここでは、『1000』を例にとります：
最初のループ
tempids=1000 ids=1000 tempids=1001,1002 (1000group_concat関数のすべての子ノードの)
番目のループで
tempids=1001,1002 ids=1000,1001,1002 tempids=1003,1004,1005,1013 (1001と1002のすべての子ノード)
3回目のループ
tempids=1003,1004,1005,1013
ids=1000,1001,1002,1003,1004,1005,1013
tempids=1003と、1004と1005と1013のすべての子ノード
...
最後のループで、子ノードが見つからなかったため，tempids=null，でループを終了させることができる。
関数の戻り値としてidsを返すために使用します。
関数本体が終了したら、ターミネーター$$でロジック全体を終了させ、実行することを忘れないでください。
最後に、ターミネーターをデフォルトのターミネーター・セミコロンにリセットすることを忘れないでください。
カスタム関数が完成したら、それを使って必要なデータを再帰的に照会ることができます。例えば、東京R&Dのすべての子ノードを照会します。
上記はすべての子ノードに対する下向きの再帰的なクエリーで、現在のノードも含まれます。現在のノードを含まないようにロジックを変更することもできますが、ここでは説明しません。
再帰クエリの手動実装
上向きの再帰は下向きの再帰に比べて単純です。
これは、下方向に再帰する場合、親ノードへの再帰の各レベルが複数の子ノードに対応するためです。
上方再帰の場合、各レベルの子ノードは1つの親ノードのみに対応し、関係はより均質になります。
同様に、get_parent_list 関数を定義して、ルート・ノードのすべての親を取得することができます。
delimiter $$
drop function if exists get_parent_list$$
create function get_parent_list(in_id varchar(10)) returns varchar(1000)
begin
declare ids varchar(1000);
declare tempid varchar(10);
set tempid = in_id;
while tempid is not null do
set ids = CONCAT_WS(',',ids,tempid);
select pid into tempid from dept where id=tempid;
end while;
return ids;
end
$$
delimiter ;
東京R&D II、チーム1、およびその再帰的親ノードを以下のように検索します：
注意事項
group_concat関数は文字列の連結に使用します。しかし、この関数には長さの制限があり、デフォルトは1024バイトです。 show variables like "group_concat_max_len"; これは.
単位は文字ではなくバイトであることに注意してください。MySQL では 1 文字は 1 バイトですが、通常の utf-8 では漢字は 3 バイトになります。
これは再帰クエリにとっては非常に致命的です。一般的に再帰はリレーションシップのレベルが深くなるため、最大長を超える可能性が高くなります。
ですから、この問題を解決するには2つの方法があります：
以下のいずれかの文を実行します。
SET GLOBAL group_concat_max_len=102400;またはSET SESSION group_concat_max_len=102400;両者の違いは、globalはグローバルで、新しいセッションが開かれるたびに有効になりますが、すでに開かれている現在のセッションには有効にならないことに注意してください。一方、Sessionは現在のセッションにのみ適用され、他のセッションには適用されません。
共通する点は、コンフィギュレーションファイルの設定に従って、MySQL の再起動後にすべての有効期限が切れるということです。したがって、設定ファイルを直接変更することをお勧めします。ID の長さを 10 バイトと仮定すると、10,000 の ID をまとめることができます。
group_concat関数を使うもう一つの制限は、同時にlimitを使えないことです。例えば
スプライスするデータは5つだけチェックするつもりでしたが、今はうまくいきません。
しかし、必要であれば、次のようなサブクエリで実現できます。
