Geekly Articles each Day

ネストされたセット+ MySQL TRIGGER

挑戦する


タスクは前の記事と同じで、MySQLにのみ適用できます。

すくい


良いニュースだ！ MySQLの再帰トリガーに問題はありません！ MySQL開発者は、トリガーレベルでも変更可能なテーブルを愚かにロックするだけです。これが大根です。 しかし、実際には、停電だけが私たちを止めることができます。
小さな抜け穴があり、結合されたテーブルがあります。 私はそれが非常に特別に考案されたことを文書で確認しませんでしたが、否定もありませんでした。 確かに、この抜け穴はカバーされる可能性がありますが、ポイントはわかりません。
悲しいかな、 MySQLのトリガーメカニズムは新しく、かなり粗雑なものであり、その使用にいくつかの制限を課していますが、それでも問題を解決するには十分です。
したがって、ソーステーブル：
SQLコード（1）
 CREATE TABLE `ns_tree`（
     `id` int（11）NOT NULL auto_increment、
     `left_key` int（11）NOT NULLデフォルト '0'、
     `right_key` int（11）NOT NULLデフォルト '0'、
     `level` int（11）NOT NULLデフォルト '0'、
     `parent_id` int（11）NOT NULLデフォルト '0'、
     `tree` int（11）NOT NULLデフォルト '1'、
     `field1`テキスト、
    主キー（ `id`）
 ）エンジン= MyISAM;

それに基づいて、まったく同じフィールドセットを持つまったく同じテーブルを作成します。
SQLコード（2）
 CREATE TABLE `_ns_tree`（
     `id` int（11）NOT NULL auto_increment、
     `left_key` int（11）NOT NULLデフォルト '0'、
     `right_key` int（11）NOT NULLデフォルト '0'、
     `level` int（11）NOT NULLデフォルト '0'、
     `parent_id` int（11）NOT NULLデフォルト '0'、
     `tree` int（11）NOT NULLデフォルト '1'、
     `field1`テキスト、
    主キー（ `id`）
 ）エンジン=挿入METHOD =最後の結合=（ `ns_tree`）;

キーワード-MERGEは基本的にテーブルのビューを作成し、それをテーブルとして使用できます。 一般に、MERGEメカニズムも少し湿っています。 結合されたテーブルの構造は、元のテーブルとまったく同じでなければなりません。
重要!!! 元のテーブルの構造を変更すると、テーブル間の接続は失われますが、すでに接続されている行の間では-NO！ 注意してください。 ソーステーブルの構造を変更するとき、同じ変更を結合されたものに適用する必要があります！
元のテーブルもMyISAMである必要があります。これは理解可能であり、この場合のトランザクションは、テーブルが相互にロックできず、テーブル内のデータが同じであるため、適用できません。 ただし、トリガーの制限内でソーステーブルがロックされ、結合されたテーブルのデータをトリガーから変更するため、これは怖いことではありません。
また、私が注意したいこと：テーブルが完全にブロックされ、キューが長くなりすぎる可能性があるため、同じツリー内でルートノードを使用しないことを強くお勧めします（以前の記事で述べたように）。

レコードを作成


MySQLトリガー方言は、 PostgreSQL方言とわずかに異なります 。
したがって、ロジックは多少変更されます。
SQLコード（3）
 CREATE DEFINER = 'user' @ 'localhost' TRIGGER `ns_tree_before_ins_tr` BEFORE INSERT ON` ns_tree`
    各行ごと
開始
     SET @left_key：= 0;
     SET @level：= 0;
 -親を指定した場合：
     NEW.parent_idがNULLではなく、NEW.parent_id> 0の場合
         SELECT right_key、 `level` + 1 INTO @left_key、@level
             ns_treeから
             WHERE id = NEW.parent_id AND tree = NEW.tree;
    終了IF;
 -左キーを指定した場合：
     NEW.left_keyがNULLではなく、NEW.left_key> 0かつ 
         （@left_keyはNULLまたは@left_key = 0）THEN
         SELECT id、left_key、right_key、 `level`、parent_id 
             INTO @ tmp_id、@ tmp_left_key、@ tmp_right_key、@ tmp_level、@ tmp_parent_id
             ns_treeから
             WHEREツリー= NEW.tree AND（left_key = NEW.left_key OR right_key = NEW.left_key）;
         IF @tmp_left_keyがNULLではなく、かつ@tmp_left_key> 0 AND NEW.left_key = @tmp_left_key THEN
             SET NEW.parent_id：= @tmp_parent_id;
             SET @left_key：= NEW.left_key;
             SET @level：= @tmp_level;
         ELSEIF @tmp_left_keyはNULLではなく、@ tmp_left_key> 0 AND NEW.left_key = @tmp_right_key THEN
             SET NEW.parent_id：= @tmp_id;
             SET @left_key：= NEW.left_key;
             SET @level：= @tmp_level + 1;
        終了IF;
    終了IF;
 -親キーまたは左キーが指定されていない場合、または何も見つからなかった場合
     @left_keyがNULLまたは@left_key = 0の場合
         SELECT MAX（right_key）+ 1 INTO @left_key
             ns_treeから
             WHEREツリー= NEW.tree;
         @left_keyがNULLまたは@left_key = 0の場合
             SET @left_key：= 1;
        終了IF;
         SET @level：= 0;
         SET NEW.parent_id：= 0; 
    終了IF;
 -新しいキー値を設定する
     SET NEW.left_key：= @left_key;
     SET NEW.right_key：= @left_key + 1;
     SET NEW.`level`：= @level;
 -ツリーにギャップを形成する
     UPDATE _ns_tree
         SET left_key = case when when left_key> = @left_key 
               THEN left_key + 2 
               ELSE left_key + 0 
             END、
             right_key = right_key + 2
         WHEREツリー= NEW.tree AND right_key> = @left_key;
終了


レコードを編集


原則は、方言の使用と同じです。
SQLコード（4）
 CREATE DEFINER = 'user' @ 'localhost' TRIGGER `ns_tree_before_upd_tr` BEFORE UPDATE ON` ns_tree`
    各行ごと
開始
 -フィールドの変更、または不快なものの送信を禁止します
     SET NEW.tree：= OLD.tree;
     SET NEW.right_key：= OLD.right_key;
     SET NEW.`level`：= OLD.`level`;
     SET @return_flag：= 0;
     NEW.parent_idがNULLの場合、NEW.parent_idを設定します：= 0; 終了IF;
 -ツリーの構造に関連する変更があるかどうかを確認します
     NEW.parent_id <> OLD.parent_idまたはNEW.left_key <> OLD.left_keyの場合
 -ツリーを再構築しています。先に進みましょう。
         SET @left_key：= 0;
         SET @level：= 0;
         SET @skew_tree：= OLD.right_key-OLD.left_key + 1;
 -移動先を決定します。
 -parent_idが変更された場合：
         NEW.parent_id <> OLD.parent_idの場合
 -別の悪にさらされている場合：
             NEW.parent_id> 0の場合
                 SELECT right_key、レベル+ 1
                     INTO @ left_key、@ level
                     ns_treeから
                     WHERE id = NEW.parent_id AND tree = NEW.tree;
 -それ以外の場合、ツリーのルートに転送します。
            その他
                 SELECT MAX（right_key）+ 1 
                     INTO @left_key
                     ns_treeから
                     WHEREツリー= NEW.tree;
                 SET @level：= 0;
            終了IF;
 -突然親が移動したノードの範囲内にある場合は、以下を確認してください。
             @left_keyがNULLではない場合 
                @left_key> 0 AND
                @left_key> OLD.left_key AND
                @left_key <= OLD.right_key THEN
                    SET NEW.parent_id：= OLD.parent_id;
                    SET NEW.left_key：= OLD.left_key;
                    SET @return_flag：= 1;
            終了IF;
        終了IF;
 -parent_idではない場合、left_keyが変更されるか、parent_idを変更しても何も生じない場合
         @left_keyがNULLまたは@left_key = 0の場合
             SELECT id、left_key、right_key、 `level`、parent_id 
                 INTO @ tmp_id、@ tmp_left_key、@ tmp_right_key、@ tmp_level、@ tmp_parent_id
                 ns_treeから
                 WHEREツリー= NEW.tree AND（right_key = NEW.left_key OR right_key = NEW.left_key-1）
                 LIMIT 1;
             @tmp_left_keyがNULLではない場合 
                @tmp_left_key> 0 AND 
                NEW.left_key-1 = @tmp_right_key THEN
                 SET NEW.parent_id：= @tmp_parent_id;
                 SET @left_key：= NEW.left_key;
                 SET @level：= @tmp_level;
             ELSEIF @tmp_left_keyはNULLではない 
                    @tmp_left_key> 0 AND 
                    NEW.left_key = @tmp_right_key THEN
                 SET NEW.parent_id：= @tmp_id;
                 SET @left_key：= NEW.left_key;
                 SET @level：= @tmp_level + 1;
             ELSEIF NEW.left_key = 1 THEN
                 SET NEW.parent_id：= 0;
                 SET @left_key：= NEW.left_key;
                 SET @level：= 0;
            その他
                 SET NEW.parent_id：= OLD.parent_id;
                 SET NEW.left_key：= OLD.left_key;
                 SET @return_flag = 1;
            終了IF;
        終了IF;
 -これで、ツリーの移動先がわかりました
 -やりがいがあるかどうかを確認します
         @return_flagがNULLまたは@return_flag = 0の場合
             SET @skew_level：= @level-OLD.`level`;
             IF @left_key> OLD.left_key THEN
 -ツリーを上に移動する
                 SET @skew_edit：= @left_key-OLD.left_key-@skew_tree;
                 UPDATE _ns_tree
                     SET left_key = right_key <= OLD.right_keyの場合
                                          THEN left_key + @skew_edit
                                         その他の場合left_key> OLD.right_keyの場合
                                                    THEN left_key-@skew_tree
                                                    ELSE left_key
                                              終了
                                    END、
                         `level` = right_key <= OLD.right_keyの場合 
                                         THEN `レベル` + @skew_level
                                         ELSE `レベル`
                                    END、
                         right_key = right_key <= OLD.right_keyの場合 
                                          THEN right_key + @skew_edit
                                         その他のケースright_key <@left_keyの場合
                                                    THEN right_key-@skew_tree
                                                    ELSE right_key
                                              終了
                                    終了
                     WHEREツリー= OLD.tree AND
                           right_key> OLD.left_key AND
                           left_key <@left_key AND
                           id <> OLD.id;
                 SET @left_key：= @left_key-@skew_tree;
            その他
 -ツリーを下に移動する：
                 SET @skew_edit：= @left_key-OLD.left_key;
                 UPDATE _ns_tree 
                    セット
                         right_key = left_key> = OLD.left_keyの場合
                                          THEN right_key + @skew_edit
                                         その他の場合right_key <OLD.left_keyの場合
                                                    THEN right_key + @skew_tree
                                                    ELSE right_key
                                              終了
                                     END、
                         `level` = left_key> = OLD.left_keyの場合
                                          THEN `レベル` + @skew_level
                                          ELSE `レベル`
                                     END、
                         left_key =ケースleft_key> = OLD.left_keyの場合
                                          THEN left_key + @skew_edit
                                         その他の場合left_key> = @left_key
                                                    THEN left_key + @skew_tree
                                                    ELSE left_key
                                              終了
                                    終了
                     WHEREツリー= OLD.tree AND
                           right_key> = @left_key AND
                           left_key <OLD.right_key AND
                           id <> OLD.id;
            終了IF;
 -ツリーが再構築され、現在のノードのみが残りました
             SET NEW.left_key：= @left_key;
             SET NEW.`level`：= @level;
             SET NEW.right_key：= @left_key + @skew_tree-1;
        終了IF;
    終了IF;
終了

注意!!! MySQLでは、同じクエリのクエリ中に変更されたフィールドが値を新しいものに変更するため、 UPDATEクエリにフィールドがリストされる順序が重要です。条件の下でクエリでこれらのフィールドをさらに使用すると、結果が不十分になります。

レコードを削除


トリガーの再帰には問題がないため、一般に削除は簡単な作業になります。
オプションのトリガー：「ブランチ全体を削除」：
SQLコード（5）
 CREATE DEFINER = 'user' @ 'localhost' TRIGGER `ns_tree_before_del_tr` AFTER DELETE ON` ns_tree`
    各行ごと
開始
 -子ノードを削除します。
     _ns_treeから削除
        どこ 
            ツリー= OLD.tree AND
             left_key> OLD.left_key AND
             right_key <OLD.right_key;
 -キーのギャップを削除します。
     SET @skew_tree：= OLD.right_key-OLD.left_key + 1;
     UPDATE _ns_tree
         SET left_key = left_key> OLD.left_keyの場合
                             THEN left_key-@skew_tree
                             ELSE left_key
                        END、
             right_key = right_key-@skew_tree
         WHERE right_key> OLD.right_key AND
            ツリー= OLD.tree AND
             id <> OLD.id;
終了

オプションのトリガー：「子ノードを上に移動してノードを削除する」：
SQLコード（6）
 CREATE DEFINER = 'user' @ 'localhost' TRIGGER `ns_tree_before_del_tr` AFTER DELETE ON` ns_tree`
    各行ごと
開始
 -キーのギャップを削除します。
    UPDATE _ns_tree
         SET left_key = left_key <OLD.left_keyの場合
                             THEN left_key
                            その他のケース（right_key <OLD.right_keyの場合）
                                       THEN left_key-1 
                                       ELSE left_key-2
                                 終了
                        END、
             parent_id = right_key <OLD.right_key AND `level` = OLD.level + 1の場合
                            OLD.parent_id
                            ELSE parent_id
                         END、
             `level` = right_key <OLD.right_keyの場合
                            THEN `レベル`-1 
                            ELSE `レベル`
                       END、
             right_key = right_key <OLD.right_keyの場合
                              THEN right_key-1 
                              ELSE right_key-2
                        終了
         WHERE（right_key> OLD.right_keyまたは
             （left_key> OLD.left_key AND right_key <OLD.right_key））AND
            ツリー= OLD.tree;
終了

ツリーの構造に影響を与える変更はバッチで行う必要はなく、各ノードで順番に行う必要があるため、その整合性が維持されることに注意してください。
実際にはすべて。 インデックスを置くことだけが残っています（ここでも、SQLコマンドを書くのが面倒なので、声を出して説明します）。
お楽しみください;-)
セルゲイ・トムレヴィッチ（別名フェニックス）（07/08/2009）

Source: https://habr.com/ru/post/J63883/

More articles:


All Articles