ユーザ用ツール

サイト用ツール

Writing /var/www/vhosts/w629.ws.domainking.cloud/enjoy-lei.com/lei_wiki/data/cache/4/4dcd9e4c605fa3ac3d683155b598a575.i failed
Unable to save cache file. Hint: disk full; file permissions; safe_mode setting.
Writing /var/www/vhosts/w629.ws.domainking.cloud/enjoy-lei.com/lei_wiki/data/cache/4/4dcd9e4c605fa3ac3d683155b598a575.metadata failed

mysqlで階層化データを使用
Writing /var/www/vhosts/w629.ws.domainking.cloud/enjoy-lei.com/lei_wiki/data/cache/4/4dcd9e4c605fa3ac3d683155b598a575.i failed
Unable to save cache file. Hint: disk full; file permissions; safe_mode setting.
Writing /var/www/vhosts/w629.ws.domainking.cloud/enjoy-lei.com/lei_wiki/data/cache/2/250c3f87f966d72324ccc8e3164a4358.i failed
Unable to save cache file. Hint: disk full; file permissions; safe_mode setting.
Writing /var/www/vhosts/w629.ws.domainking.cloud/enjoy-lei.com/lei_wiki/data/cache/2/250c3f87f966d72324ccc8e3164a4358.metadata failed
Writing /var/www/vhosts/w629.ws.domainking.cloud/enjoy-lei.com/lei_wiki/data/cache/4/4dcd9e4c605fa3ac3d683155b598a575.xhtml failed

MySQLで階層化データを使用

経路列挙モデル(準備編):MySQLで階層化データを使う

■テーブルの作成
CREATE TABLE node(
 node_id INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
 name VARCHAR(64) NOT NULL,
 path varchar(255) NOT NULL,
 PRIMARY KEY (node_id),
 UNIQUE KEY (path)
 );
■テーブルにデータINSERT
INSERT INTO node VALUES
 ( 1, 'Apple', '.1.'),
 ( 2, 'コンピューター', '.1.2.'),
 ( 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.'),
 (10, 'ソフトウェア', '.1.10.');

経路列挙モデル(検索編):MySQLで階層化データを使う

SELECT * FROM node ORDER BY node_id;
■ツリー構造よりルート(ツリー構造で最上位のノード)を取得する
SELECT * FROM node WHERE node_id = REPLACE(path, '.', '');
+---------+-------+------+
| node_id | name  | path |
+---------+-------+------+
|       1 | Apple | .1.  |
+---------+-------+------+
■ツリー構造よりリーフノード(子供がいないノード)を取得する
SELECT * FROM node AS parent WHERE NOT EXISTS
 (SELECT * FROM node AS child WHERE child.path LIKE CONCAT(parent.path,'_%'));
+---------+------------------------+-----------+
| node_id | name                   | path      |
+---------+------------------------+-----------+
|       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.     |
|      10 | ソフトウェア           | .1.10.    |
+---------+------------------------+-----------+
■ノードの深さを測る
SELECT *, LENGTH(path) - LENGTH(REPLACE(path,'.','')) -2 AS depth FROM node ORDER BY path;
+---------+------------------------+-----------+-------+
| node_id | name                   | path      | depth |
+---------+------------------------+-----------+-------+
|       1 | Apple                  | .1.       |     0 |
|      10 | ソフトウェア           | .1.10.    |     1 |
|       2 | コンピューター         | .1.2.     |     1 |
|       3 | デスクトップPC         | .1.2.3.   |     2 |
|       4 | タワー型               | .1.2.3.4. |     3 |
|       5 | 一体型                 | .1.2.3.5. |     3 |
|       6 | ラップトップPC         | .1.2.6.   |     2 |
|       7 | ハンドヘルドPC         | .1.2.7.   |     2 |
|       8 | デジタル音楽プレーヤー | .1.8.     |     1 |
|       9 | 携帯電話               | .1.9.     |     1 |
+---------+------------------------+-----------+-------+
■ツリー構造の深さを測る
SELECT MAX(LENGTH(path) - LENGTH(REPLACE(path,'.','')) -1) AS depth FROM node;
+-------+
| depth |
+-------+
|     4 |
+-------+
■ノードをインデントにより階層表示
SELECT node_id,CONCAT(REPEAT("\t",LENGTH(path) - LENGTH(REPLACE(path,'.',''))-2),name) as name,path
 FROM node ORDER BY path;
+---------+------------------------------+-----------+
| node_id | name                         | path      |
+---------+------------------------------+-----------+
|       1 | Apple                        | .1.       |
|      10 |     ソフトウェア             | .1.10.    |
|       2 |     コンピューター           | .1.2.     |
|       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.     |
+---------+------------------------------+-----------+
■親ノードから見た場合の子ノードを取得する
SELECT
 parent.node_id AS parent, parent.name parent_name,
 child.node_id AS child, child.name AS child_name
 FROM node AS parent LEFT JOIN node AS child
 ON parent.path = (SELECT MAX(path) FROM node WHERE child.path LIKE CONCAT(path,'_%'));
+--------+------------------------+-------+------------------------+
| parent | parent_name            | child | child_name             |
+--------+------------------------+-------+------------------------+
|      1 | Apple                  |     2 | コンピューター         |
|      1 | Apple                  |     8 | デジタル音楽プレーヤー |
|      1 | Apple                  |     9 | 携帯電話               |
|      1 | Apple                  |    10 | ソフトウェア           |
|      2 | コンピューター         |     3 | デスクトップPC         |
|      2 | コンピューター         |     6 | ラップトップPC         |
|      2 | コンピューター         |     7 | ハンドヘルドPC         |
|      3 | デスクトップPC         |     4 | タワー型               |
|      3 | デスクトップPC         |     5 | 一体型                 |
|      4 | タワー型               |  NULL | NULL                   |
|      5 | 一体型                 |  NULL | NULL                   |
|      6 | ラップトップPC         |  NULL | NULL                   |
|      7 | ハンドヘルドPC         |  NULL | NULL                   |
|      8 | デジタル音楽プレーヤー |  NULL | NULL                   |
|      9 | 携帯電話               |  NULL | NULL                   |
|     10 | ソフトウェア           |  NULL | NULL                   |
+--------+------------------------+-------+------------------------+
■親ノード配下の子ノードの数
SELECT parent.node_id AS parent, parent.name AS parent_name, COUNT(child.node_id) AS child_count
 FROM node AS parent LEFT JOIN node AS child
 ON parent.path = (SELECT MAX(path) FROM node WHERE child.path LIKE CONCAT(path,'_%'))
 GROUP BY parent.node_id;
+--------+------------------------+-------------+
| parent | parent_name            | child_count |
+--------+------------------------+-------------+
|      1 | Apple                  |           4 |
|      2 | コンピューター         |           3 |
|      3 | デスクトップPC         |           2 |
|      4 | タワー型               |           0 |
|      5 | 一体型                 |           0 |
|      6 | ラップトップPC         |           0 |
|      7 | ハンドヘルドPC         |           0 |
|      8 | デジタル音楽プレーヤー |           0 |
|      9 | 携帯電話               |           0 |
|     10 | ソフトウェア           |           0 |
+--------+------------------------+-------------+
■子から見た場合の親
SELECT
 child.node_id AS child, child.name AS child_name,
 parent.node_id AS parent, parent.name parent_name
 FROM node AS child LEFT JOIN node AS parent
 ON parent.path = (SELECT MAX(path) FROM node WHERE child.path LIKE CONCAT(path,'_%'));
+-------+------------------------+--------+----------------+
| child | child_name             | parent | parent_name    |
+-------+------------------------+--------+----------------+
|     1 | Apple                  |   NULL | NULL           |
|     2 | コンピューター         |      1 | Apple          |
|     3 | デスクトップPC         |      2 | コンピューター |
|     4 | タワー型               |      3 | デスクトップPC |
|     5 | 一体型                 |      3 | デスクトップPC |
|     6 | ラップトップPC         |      2 | コンピューター |
|     7 | ハンドヘルドPC         |      2 | コンピューター |
|     8 | デジタル音楽プレーヤー |      1 | Apple          |
|     9 | 携帯電話               |      1 | Apple          |
|    10 | ソフトウェア           |      1 | Apple          |
+-------+------------------------+--------+----------------+
■ツリー構造より一部分を取得する
SELECT child.* FROM node AS parent, node AS child
 WHERE child.path LIKE CONCAT(parent.path,'%') AND parent.name = 'デスクトップPC'
 ORDER BY child.path;
+---------+----------------+-----------+
| node_id | name           | path      |
+---------+----------------+-----------+
|       3 | デスクトップPC | .1.2.3.   |
|       4 | タワー型       | .1.2.3.4. |
|       5 | 一体型         | .1.2.3.5. |
+---------+----------------+-----------+

SELECT child.* FROM node AS parent, node AS child
 WHERE child.path LIKE CONCAT(parent.path,'_%') AND parent.name = 'デスクトップPC'
 ORDER BY child.path;
+---------+----------+-----------+
| node_id | name     | path      |
+---------+----------+-----------+
|       4 | タワー型 | .1.2.3.4. |
|       5 | 一体型   | .1.2.3.5. |
+---------+----------+-----------+
■ノード間のパスを取得する
[:start_node] = 3;
[:end_node] = 4;

SELECT parent.* FROM node AS parent, node AS child
 WHERE
 parent.path >= (SELECT path FROM node WHERE node_id = 3) AND
 child.node_id = 4 AND
 child.path LIKE CONCAT(parent.path,'%')
 ORDER BY parent.path;
+---------+----------------+-----------+
| node_id | name           | path      |
+---------+----------------+-----------+
|       3 | デスクトップPC | .1.2.3.   |
|       4 | タワー型       | .1.2.3.4. |
+---------+----------------+-----------+

SELECT parent.* FROM node AS parent, node AS child
 WHERE
 parent.path >= (SELECT path FROM node WHERE node_id = 1) AND
 child.node_id = 7 AND
 child.path LIKE CONCAT(parent.path,'%')
 ORDER BY parent.path;
+---------+----------------+---------+
| node_id | name           | path    |
+---------+----------------+---------+
|       1 | Apple          | .1.     |
|       2 | コンピューター | .1.2.   |
|       7 | ハンドヘルドPC | .1.2.7. |
+---------+----------------+---------+

経路列挙モデル(更新編):MySQLで階層化データを使う

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

まず、新たに追加されるノードのIDを取得します。
親ノードのパスに新ノードのIDを追加して、新ノードのパスとすることにより実現します。
■ストアドプロシージャ確認
SHOW CREATE PROCEDURE add_node_leaf;
■ストアドプロシージャ削除
DROP PROCEDURE add_node_leaf;
■SQL文字コード変更
SET add_name cp932;
■ストアドプロシージャ(stored procedure)という機能を使用
■ストアドプロシージャの(末子として追加)
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,'サーバー');


■■テーブルの中身確認
SELECT * FROM node;
■■ここまでのWindowsの使用流だと
・テーブル作成して文字データを登録してから「ストアドプロシージャ」をすると
デーブルデータ全部文字化けになる。
なので、
「ストアドプロシージャ」ある状態で
1.テーブルを削除して、
2.「SET NAMES cp932;」日本語登録できるように設定して
3.最初のデータ登録する
4.CALLの登録する。
これで文字化けなく表示できるようになりました。
■■ストアドプロシージャ処理の流れだと

サンプル:下記のを追加するなら

●CALL add_node_leaf(13,'レンタルサーバー');
●CALL add_node_leaf(13,'専用サーバー');

・INSERT INTO node(node_id,name,path)
	まず新データ登録
・SELECT IFNULL(MAX(node_id)+1,1) INTO newId FROM node;
	SELECT IFNULL(MAX(node_id)+1,1) AS newId FROM node;
	最大ID取得する
・select node_id, name ,CONCAT('.',newId,'.');
	関連すべてのデータ取得
・SELECT newId,add_name,CONCAT((SELECT path FROM node WHERE node_id=target_node),newId,'.');
	SELECT path FROM node WHERE node_id=13;
	+----------+
	| path     |
	+----------+
	| .1.2.13. |
	+----------+
	:newId = 14;
	:add_name = 'レンタルサーバー';
	SELECT newId,add_name ,CONCAT(' .1.2.13.',14,'.') FROM node WHERE node_id=13;
■■登録階層表示確認
mysql> SELECT node_id,CONCAT(REPEAT("\t",LENGTH(path) - LENGTH(REPLACE(path,'.
',''))-2),name) as name,path
    ->  FROM node ORDER BY path;
+---------+-------------------------------+-----------+
| 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.     |
+---------+-------------------------------+-----------+

参考サイト

SQLで木と階層構造のデータを扱う~ http://www.geocities.jp/mickindex/database/db_tree_pe.html

MySQLで階層化データを使う~ http://makizou.com/1662/

http://makizou.com/1621/

mysqlで階層化データを使用.txt · 最終更新: 2013/12/02 11:05 by luis_lee