[Mysql] places의 전체 레코드의 category (부모 category 포함) 조회방법

gistfile1.md

category

id	name	parent	index
1	맛집	0	0
3	로컬정보	0	2
4	치킨	3	0
5	중식	3	1
6	피자/스파게티	3	2
10	전체	3	0
11	마트	3	1
12	병원	3	2
13	성형외과	12	0

places

id	name	category
1	TEST 1	4
2	TEST 2	13
3	TEST 3	13

필요한 결과

place_id	category_id
1	4
1	1
2	13
2	12
2	3
3	13
3	12
3	3

결국 임시 테이블을 생성하여 사용 (어차피 검색엔진쪽으로 인덱싱용이니...)

DELIMITER $$

drop function if exists insertCategoryData$$
create function insertCategoryData(placeid int, categoryid int) RETURNS INT
begin
    INSERT INTO `place_category_list` (`place_id`, `category_id`, `parent`)
    SELECT placeid, @r AS _id, ( SELECT @r := parent FROM category WHERE id = _id ) AS parent
    FROM ( SELECT @r :=categoryid ) vars, category h
    WHERE @r <> 0;
    return 1;
end $$

drop procedure if exists createCategoryList$$
create procedure createCategoryList()
begin
    DROP TABLE if exists `place_category_list`;
    CREATE TABLE `place_category_list` (
      `place_id` INT NOT NULL,
      `category_id` INT NULL,
      `parent` INT NULL);
    SELECT insertCategoryData(`places`.`id`, `places`.`category`) FROM `places`;
end $$

drop procedure if exists getCategoryList$$
create procedure getCategoryList()
begin
    CALL createCategoryList();
    SELECT `place_id`, `category_id` FROM `place_category_list`;
    DROP TABLE `place_category_list`;
end $$

DELIMITER ;

Call getCategoryList();