[Mysql] oracle의 START WITH CONNECT BY 구문을 mysql로 구현하기

일을 하다보니 십년전에 손놓은 mysql을 핸들링해야 하는 일이 생겼다.

뭐 다른것들이야 대충 뚜드려 돌리면 된다지만

mysql에서 제일 아쉬운게 oracle의 START WITH CONNECT BY 구문이었다.

구글 검색 돌리니 바로 나오네.

하여튼 구글도 대단하고 ㅎㅎㅎ



그냥 내가 필요한것만 적을까 하다가...

이거 필요로 하는 사람이 많을거 같아서 좀 더 상세하게 적을까 한다.


오라클에서는

self_id, parent_id, self_name 만 있으면 

START WITH CONNECT BY  구문으로

계층형 구조가 바로 처리가 된다.


근데 mysql에서는 이게 안된다 이게..ㅎㅎ


근데 구글로 찾아보니 해결이 있긴한데....

left와 right 칼럼을 추가해서 이걸 해결해 나가네

테이블 구조는 

CREATE TABLE nested_category (
 category_id INT AUTO_INCREMENT PRIMARY KEY,
 name VARCHAR(20) NOT NULL,
 parent INT DEFAULT NULL,
 lft INT NOT NULL,
 rgt INT NOT NULL
);

이게 되시겠단다.

여기서 lft와 rgt 가 어떤 일을 하는지는 여기(이 글의 원본이 된곳) 에서 찾아 보면 될거다.

자 그럼 이게 어케 되는지 테스트를 해 봐야겠지롱?

INSERT INTO nested_category
VALUES
(1,'ELECTRONICS',NULL,1,20),
(2,'TELEVISIONS',1,2,9),
(3,'TUBE',2,3,4),
(4,'LCD',2,5,6),
(5,'PLASMA',2,7,8),
(6,'PORTABLE ELECTRONICS',1,10,19),
(7,'MP3 PLAYERS',6,11,14),
(8,'FLASH',7,12,13),
(9,'CD PLAYERS',6,15,16),
(10,'2 WAY RADIOS',6,17,18);

이거 뚜드려 넣자.

뭐 대충 구조가 저렇다

근데 지금은 억지로 뚜드려 넣어서 그런데...

저걸 어떤식으로든 필드에 적용할려면 강제로 뚜드려 넣으면 안되자나...

그래서 테이블 생성후 최초로 입력될때는

INSERT INTO nested_category
VALUES
(아이템코드,'아이템명',NULL,1,2);

이 코드로 입력해야 한당. 


자 그럼 저걸 어케 조회하냐고?

SELECT node.category_id  as category_id,  node.parent as parent, 
CONCAT( REPEAT('     ', COUNT(parent.name) - 1), node.name) AS name
FROM nested_category AS node,
nested_category AS parent
WHERE node.lft BETWEEN parent.lft AND parent.rgt
GROUP BY node.name
ORDER BY node.lft;

이걸로 조회해보자

그럼 계층형으로 지가 알아서 나온다.

근데 이거 가만히 보면....

시바 테이블 전부다 뒤져서 가져오는거자나..

후억

이걸로는 답없음이다.ㅋ

SELECT node.category_id, node.parent,node.name
FROM nested_category AS node,
nested_category AS parent
WHERE node.lft BETWEEN parent.lft AND parent.rgt
AND parent.name = 'PORTABLE ELECTRONICS'
ORDER BY node.lft;

자 이걸로 함 조회해보자.

그럼 특정 아이템의 하위노드만 조회가 된다.

이거 잘 활용하면 필요한 쿼리문 만들수 있겠지


인제 조회해봤으니 삽입도 해봐야지?

LOCK TABLE nested_category WRITE;
SELECT @myLeft := lft FROM nested_category
WHERE name = 'ELECTRONICS';

UPDATE nested_category SET rgt = rgt + 2 WHERE rgt > @myLeft;
UPDATE nested_category SET lft = lft + 2 WHERE lft > @myLeft;

INSERT INTO nested_category(parent,name, lft, rgt) 
VALUES(1,'GAME CONSOLES', @myLeft + 1, @myLeft + 2);

UNLOCK TABLES;

일단 들어갔단다.

들어갔는지 조회해 보자.


SELECT node.category_id  as category_id,  node.parent as parent, 
CONCAT( REPEAT('     ', COUNT(parent.name) - 1), node.name) AS name
FROM nested_category AS node,
nested_category AS parent
WHERE node.lft BETWEEN parent.lft AND parent.rgt
GROUP BY node.name
ORDER BY node.lft;

'ELECTRONICS' 의 하위노드로 'GAME CONSOLES'가 들어가 있을거다.



 자 집어넣고 조회도 해봤으니 삭제도 함 해보자.

LOCK TABLE nested_category WRITE;

SELECT @myLeft := lft, @myRight := rgt, @myWidth := rgt - lft + 1
FROM nested_category
WHERE name = 'GAME CONSOLES';

DELETE FROM nested_category WHERE lft BETWEEN @myLeft AND @myRight;

UPDATE nested_category SET rgt = rgt - @myWidth WHERE rgt > @myRight;
UPDATE nested_category SET lft = lft - @myWidth WHERE lft > @myRight;

UNLOCK TABLES;


이러면 아까 들어갔던 'GAME CONSOLES'이 삭제가 되었다.

물론 하위노드가 있다면 하위노드까지 같이 지워진다.


근데 지금까지 잘 따라 왔다면 한가지 졸라 의문이 생길꺼다.

생겨야 된다.

안생긴다고?

그럼 내가 알켜주께

지금까지 한거 보면....

분명히 입력 삭제 조회가 다 됐다.

근데도 아쉽다...뭐가 아쉬울까?

그렇지...아까 입력할때는 특정노드의 하위노드로 입력을 한거지.

입력해야할 노드가 최상위노드이면?

못넣나?

못넣으면 말이 안되지.

LOCK TABLE nested_category WRITE;
SELECT @myRight := rgt FROM nested_category
WHERE category_id=( select max(category_id) from nested_category where parent is null);

UPDATE nested_category SET rgt = rgt + 2 WHERE rgt > @myRight;
UPDATE nested_category SET lft = lft + 2 WHERE lft > @myRight;
INSERT INTO nested_category(parent,name, lft, rgt) 
VALUES(null,'GAME CONSOLES', @myRight + 1, @myRight + 2);

UNLOCK TABLES; 

이건 최상위노드를 생성할때 써야 한다.

뭐 대충 쿼리보면 알겟지.

테이블 정의할때 최상위 노드의 parent 는 null로 하기로 했으니 그거가지고 한거다.


다 끝났어.

지금까지 따라 오니라 고생했다.

근데 끝났다고 다 끝이냐? 보나스가 없으면 심심하자나.


자...인제 리뷰 한번해보자.


조회야 그렇다 치고....

문제는 입력과 삭제인데 말이지..

신규로 입력되거나 삭제될때 남은놈들의 left와 right를 모두 업데이트 한단말이지.

이거 졸라 웃기지도 않고 말도 안되는거자나?

맞아....말도 안되는거야.

설마 이걸 계층형게시판에 가져다 쓸라고 하는거 아니지?

진짜지?

이거 절대 게시판같은거에 쓰면 절대!!절대!!! 안돼

데이터용량이 많은 상태에서는 절대 이거 쓰면 안돼.

데이터 많은데 쓸라면 다른 알고리즘 찾아봐 인터넷에 많이 널려 있어

카테고리 정의할때, BOM 정의할때, 뭐 이정도로만 쓰지 다른데 쓰면 킬나...



아까 출처 밝혔고. 지금 또 출처 밝히고

출처 그대로 배낀건 아니고 수정좀 했고.

이글로 만족못하면 출처에 가서 다시한번 확인해 보고...

궁금한거 있으면 묻지마...ㅡㅡ 

나 처음에 이야기 했자나 mysql 10년만에 다시 만져본다고...

아...그리고 이건 내가 궁금한건데...

아까 입력이나 삭제를 위해서 쿼리를 던지면 (물론 eclipse 상에서지만.)

에러가 뜬다?

Access denied for user '유저명'@'%' to database 데이터베이스명

이런 에러가 뜨고(물론 유저명이랑 데이터베이스명은 여기꺼지.)

데이터를 조회해보면 데이터가 들어가있거나 삭제가 되어있네?

이거 아시는분 제발 좀 알켜 주세효    *@@*

뭐 남들 다 아는건데 졸라 길게 떠들었다고 뭐라그럼 할말없고.

필요한 사람 분명히 있을거야!!!

장담한다 내가!!!

하여튼....저 에러 뜨는거 아시는분 좀 알켜 주세효 ^^;



출처 : http://drugholic.tistory.com/18

[출처] [MYSQL] oracle의 START WITH CONNECT BY 구문을 mysql로 구현하기|작성자 백전백승