MAKIZOU.COM

経路列挙モデル 階層データの追加・更新・削除

階層データの追加・更新・削除は、一行のINSERT構文、一行のUPDATE構文では処理できない事が多々発生します。

そんな複数行に渡る一連の処理をストアドプロシージャ（stored procedure）という機能を使い効率的に実装します。

ストアドプロシージャとは、データベースに対する一連の処理をまとめた手続きにまとめて、リレーショナルデータベース管理システムに保存（永続化）したもので、MySQLでは、バージョン5.0以降に標準SQL準拠でサポートされています。
※似たよう機能として、ストアドファンクションがありますが、ストアドプロシージャとの違いは、戻り値があるということだけです。

ストアドプロシージャ作成にあたって、もし phpMyAdmin よりSQLを実行する場合は、１行目「delimiter //」と最終行「//」を除き、デミリタに「//」を入力して実行してください。

なお、作成したストアドプロシージャを実行するには、CALLステートメントによりストアドプロシージャを呼び出します。

おしながき

• ノードの追加： 指定ノード(親)の末子として追加
• 指定ノードのみを入れ替える
• 指定ノード以下を入れ替える
• ノード(1)以下をノード(2)配下に移動する
• 指定ノード以下の除去（子ノードは削除）
• 指定ノードのみ除去（子ノードは上階層へ）
• 関連記事

ノードの追加： 指定ノード(親)の末子として追加

まず、新たに追加されるノードのIDを取得します。
親ノードのパスに新ノードのIDを追加して、新ノードのパスとすることにより実現します。

delimiter //
CREATE PROCEDURE add_node_leaf(
IN target_node INT(10),
IN add_name VARCHAR(64)
)
BEGIN
DECLARE newId INT;
SELECT IFNULL(MAX(node_id)+1,1) INTO newId FROM node;
IF target_node IS NULL THEN
INSERT INTO node(node_id,name,path)
SELECT newId,add_name,CONCAT('.',newId,'.');
ELSE
INSERT INTO node(node_id,name,path)
SELECT newId,add_name,CONCAT((SELECT path FROM node WHERE node_id=target_node),newId,'.');
END IF;
END;
//


CALLステートメント構文
CALL add_node_leaf('指定ノードID(親)','登録表示名');

CALLステートメント実行例
CALL add_node_leaf(10,'OS');
CALL add_node_leaf(10,'アプリケーション');
CALL add_node_leaf(2,'サーバー');

+---------+----------------------------+-----------+
| node_id | name                       | path      |
+---------+----------------------------+-----------+
|       1 | Apple                      | .1.       |
|      10 |     ソフトウェア           | .1.10.    |
|      11 |         OS                 | .1.10.11. |
|      12 |         アプリケーション   | .1.10.12. |
|       2 |     コンピューター         | .1.2.     |
|      13 |         サーバー           | .1.2.13.  |
|       3 |         デスクトップPC     | .1.2.3.   |
|       4 |             タワー型       | .1.2.3.4. |
|       5 |             一体型         | .1.2.3.5. |
|       6 |         ラップトップPC     | .1.2.6.   |
|       7 |         ハンドヘルドPC     | .1.2.7.   |
|       8 |     デジタル音楽プレーヤー | .1.8.     |
|       9 |     携帯電話               | .1.9.     |
+---------+----------------------------+-----------+

指定ノードのみを入れ替える

delimiter //
CREATE PROCEDURE change_node(IN node1 INT(10),IN node2 INT(10))
BEGIN
DECLARE myPath1 VARCHAR(255);
DECLARE myPath2 VARCHAR(255);
IF node1 != node2 THEN
SELECT path INTO myPath1 FROM node WHERE node_id = node1;
SELECT path INTO myPath2 FROM node WHERE node_id = node2;
UPDATE node SET node_id = 0, path = '.0.' WHERE node_id = node1;
UPDATE node SET node_id = node1, path = myPath1 WHERE node_id = node2;
UPDATE node SET node_id = node2, path = myPath2 WHERE node_id = 0;
END IF;
END;
//


CALLステートメント構文
CALL change_node('指定ノードID(1)','指定ノードID(2)');

CALLステートメント実行例
CALL change_node(13,9);

+---------+----------------------------+-----------+
| node_id | name                       | path      |
+---------+----------------------------+-----------+
|       1 | Apple                      | .1.       |
|      10 |     ソフトウェア           | .1.10.    |
|      11 |         OS                 | .1.10.11. |
|      12 |         アプリケーション   | .1.10.12. |
|       2 |     コンピューター         | .1.2.     |
|      13 |         携帯電話           | .1.2.13.  |
|       3 |         デスクトップPC     | .1.2.3.   |
|       4 |             タワー型       | .1.2.3.4. |
|       5 |             一体型         | .1.2.3.5. |
|       6 |         ラップトップPC     | .1.2.6.   |
|       7 |         ハンドヘルドPC     | .1.2.7.   |
|       8 |     デジタル音楽プレーヤー | .1.8.     |
|       9 |     サーバー               | .1.9.     |
+---------+----------------------------+-----------+

指定ノード以下を入れ替える

delimiter //
CREATE PROCEDURE change_subtree(IN node1 INT(10),IN node2 INT(10))
BEGIN
DECLARE myPath1 varchar(255);
DECLARE myPath2 varchar(255);
IF node1 != node2 THEN
SELECT path INTO myPath1 FROM node WHERE node_id = node1;
SELECT path INTO myPath2 FROM node WHERE node_id = node2;
UPDATE node SET
node_id = CASE WHEN node_id = node1 THEN 0 ELSE node_id END,
path = REPLACE(path,myPath1,'.0.')
WHERE path LIKE CONCAT(myPath1,'%');
UPDATE node SET
node_id = CASE WHEN node_id = node2 THEN node1 ELSE node_id END,
path = REPLACE(path,myPath2,myPath1)
WHERE path LIKE CONCAT(myPath2,'%');
UPDATE node SET
node_id = CASE WHEN node_id = 0 THEN node2 ELSE node_id END,
path = REPLACE(path,'.0.',myPath2)
WHERE path LIKE '.0.%';
END IF;
END;
//


CALLステートメント構文
CALL change_subtree('指定ノードID(1)','指定ノードID(2)');

CALLステートメント実行例
CALL change_subtree(10,6);

+---------+------------------------------+------------+
| node_id | name                         | path       |
+---------+------------------------------+------------+
|       1 | Apple                        | .1.        |
|      10 |     ラップトップPC           | .1.10.     |
|       2 |     コンピューター           | .1.2.      |
|      13 |         携帯電話             | .1.2.13.   |
|       3 |         デスクトップPC       | .1.2.3.    |
|       4 |             タワー型         | .1.2.3.4.  |
|       5 |             一体型           | .1.2.3.5.  |
|       6 |         ソフトウェア         | .1.2.6.    |
|      11 |             OS               | .1.2.6.11. |
|      12 |             アプリケーション | .1.2.6.12. |
|       7 |         ハンドヘルドPC       | .1.2.7.    |
|       8 |     デジタル音楽プレーヤー   | .1.8.      |
|       9 |     サーバー                 | .1.9.      |
+---------+------------------------------+------------+

ノード(1)以下をノード(2)配下に移動する
※ルート（ツリー構造で最上位のノード）は移動できません

delimiter //
CREATE PROCEDURE move_node(IN node1 INT(10),IN node2 INT(10))
BEGIN
DECLARE myId INT;
DECLARE myPath1 VARCHAR(255);
DECLARE myPath2 VARCHAR(255);
SELECT node_id INTO myId FROM node WHERE node_id = REPLACE(path,'.','');
IF node1 != myId AND node1 != node2 THEN
SELECT path INTO myPath1 FROM node WHERE node_id = node1;
SELECT path INTO myPath2 FROM node WHERE node_id = node2;
UPDATE node SET path = REPLACE(path, myPath1, CONCAT(myPath2,node1,'.'))
WHERE path LIKE CONCAT(myPath1,'%');
END IF;
END;
//


CALLステートメント構文
CALL move_node('指定ノードID(1)','指定ノードID(2)');

CALLステートメント実行例
CALL move_node(13,8);

+---------+------------------------------+------------+
| node_id | name                         | path       |
+---------+------------------------------+------------+
|       1 | Apple                        | .1.        |
|      10 |     ラップトップPC           | .1.10.     |
|       2 |     コンピューター           | .1.2.      |
|       3 |         デスクトップPC       | .1.2.3.    |
|       4 |             タワー型         | .1.2.3.4.  |
|       5 |             一体型           | .1.2.3.5.  |
|       6 |         ソフトウェア         | .1.2.6.    |
|      11 |             OS               | .1.2.6.11. |
|      12 |             アプリケーション | .1.2.6.12. |
|       7 |         ハンドヘルドPC       | .1.2.7.    |
|       8 |     デジタル音楽プレーヤー   | .1.8.      |
|      13 |         携帯電話             | .1.8.13.   |
|       9 |     サーバー                 | .1.9.      |
+---------+------------------------------+------------+

指定ノード以下の除去（子ノードは削除）

delimiter //
CREATE PROCEDURE del_node_joint(IN del INT(10))
BEGIN
DECLARE myPath varchar(255);
SELECT CONCAT(path,'%') INTO myPath FROM node WHERE node_id = del;
DELETE FROM node WHERE path LIKE myPath;
END;
//


CALLステートメント構文
CALL del_node_joint('指定ノードID');

CALLステートメント実行例
CALL del_node_joint(6);
CALL del_node_joint(10);

+---------+----------------------------+-----------+
| node_id | name                       | path      |
+---------+----------------------------+-----------+
|       1 | Apple                      | .1.       |
|       2 |     コンピューター         | .1.2.     |
|       3 |         デスクトップPC     | .1.2.3.   |
|       4 |             タワー型       | .1.2.3.4. |
|       5 |             一体型         | .1.2.3.5. |
|       7 |         ハンドヘルドPC     | .1.2.7.   |
|       8 |     デジタル音楽プレーヤー | .1.8.     |
|      13 |         携帯電話           | .1.8.13.  |
|       9 |     サーバー               | .1.9.     |
+---------+----------------------------+-----------+

指定ノードのみ除去（子ノードは上階層へ）
※ルート（ツリー構造で最上位のノード）は移動できません

delimiter //
CREATE PROCEDURE del_node_self(IN del INT(10))
BEGIN
DECLARE myId INT;
SELECT node_id INTO myId FROM node WHERE node_id = REPLACE(path, '.', '');
IF del != myId THEN
DELETE FROM node WHERE node_id = del;
UPDATE node SET path = REPLACE(path,CONCAT('.',del,'.'),'.')
WHERE path LIKE CONCAT('%.',del,'.%');
END IF;
END;
//


CALLステートメント構文
CALL del_node_self('指定ノードID');

CALLステートメント実行例
CALL del_node_self(2);
CALL del_node_self(9);

+---------+----------------------------+-----------+
| node_id | name                       | path      |
+---------+----------------------------+-----------+
|       1 | Apple                      | .1.       |
|       3 |     デスクトップPC         | .1.2.3.   |
|       4 |         タワー型           | .1.2.3.4. |
|       5 |         一体型             | .1.2.3.5. |
|       7 |     ハンドヘルドPC         | .1.2.7.   |
|       8 |     デジタル音楽プレーヤー | .1.8.     |
|      13 |         携帯電話           | .1.8.13.  |
+---------+----------------------------+-----------+

関連記事

• MySQLで階層化データを使うには
• 入れ子集合モデル（準備編）：MySQLで階層化データを使う
• 入れ子集合モデル（検索編）：MySQLで階層化データを使う
• 入れ子集合モデル（更新編）：MySQLで階層化データを使う
• 経路列挙モデル（準備編）：MySQLで階層化データを使う
• 経路列挙モデル（検索編）：MySQLで階層化データを使う
• 経路列挙モデル（更新編）：MySQLで階層化データを使う

トラックバック用URL

コメント

• アクセス解析の達人
• 初心者でもできるプロが使うLinuxWebサーバー構築講座

カテゴリ

• Linuxコマンド・リファレンス
• webシステム開発・運用・保守
• WEB集客技術（SEO）
• 今週のモチベーション
• 当サイトについて
• 徒然なるままに

人気のある記事

アーカイブ

タグクラウド

日本語マニュアル

ブログロール

• アドレスマップ
• グッズアーカイブ
• 専用サーバー設定
• 成功ノウハウ情報
• 鬱病の保健師（看護師）