Форум пользователей MySQL

Qtx · 16.09.2008 19:47:22

Допустим есть таблица

ITEMS(ID, PARENT_ID,NAME)

нужно вывести список в виде

item 1
item 1.1
item 1.2
item 2
item 2.1
item 2.2
item 2.3

В Oracle есть sys_coonect_by_path, в MS SQL я это делаю рекурсивным вызовом процедуры. В MySQL рекурсивные вызовы запрещены или выключены по умолчанию. Есть ли какое то оптимальное решение для MySQL

paulus · 16.09.2008 19:59:36

SET max_sp_recursion_depth = 10;

Оптимальное решение - отдельная табличка с родственниками или хранение индекса
обхода, а вовсе не хранимые процедуры

Qtx · 16.09.2008 21:24:58

SET max_sp_recursion_depth = 10;

MySQL 5.0.22
Unknown system variable 'max_sp_recursion_depth'

О чем и речь.

А хранение и поддержка индекса обхода как то гемморно.

Планирую так. Курсором пробежаться и накапливать данные во временную таблицу с хитрым SORT_ID, только вот пока тямы не хватает как его его сделать

paulus · 16.09.2008 22:47:18

эээ... вот тут говорится, что с 5.0.17 работает. В 5.0.51 у меня работает точно. Проверяйте еще раз.

Qtx · 16.09.2008 23:16:15

Когда я гуглил по данной теме то натыкался на баг репорт на форуме MySQL, там говорилось что в ранних версиях 5.0.x они это вводили, потмо отменяли, потом опять вводили.

PS
Это должно работать у любых провайдеров с MySQL 5.0.x их веть не заставишь проапгрейдить их MySQL. Так что буду искать другое решение. А с рекурсией было бы красиво конечно.

paulus · 16.09.2008 23:53:49

Хранимые процедуры не работают вообще в 5.0.0 - тем не менее хостинг может заявлять,
что на нем стоит "пятый" MySQL... Нужно подходить разумно к проблеме, 5.0.22 - это старый
сервер, двухлетней давности, его даже на зеркалах нету уже

Qtx · 19.09.2008 12:06:29

Не захотелось ограничивать уровень рекурсии, всё таки придумал решение на временных таблицах. Столкнулся с ограничением что на временную таблицу нельзя ссылаться дважды в одном запросе, пришлось вводить дополнительные. Такой вот монстр получился

drop table if exists items;

create table items

(

id int not null,

parent_id int,

name varchar(200),

primary key(id)

);

/*

inserting test values

*/

insert into items values (1, null, 'Item 1');

insert into items values (2, 1, 'Item 1.1');

insert into items values (3, 1, 'Item 1.2');

insert into items values (4, null, 'Item 2');

insert into items values (5, 4, 'Item 2.1');

insert into items values (6, 4, 'Item 2.2');

insert into items values (7, 4, 'Item 2.3');

insert into items values (8, null, 'Item 3');

insert into items values (9, 8, 'Item 3.1');

insert into items values (10, 8, 'Item 3.2');

insert into items values (11, 10, 'Item 3.2.1');

insert into items values (12, 10, 'Item 3.2.2');

drop procedure if exists get_tree;

create procedure get_tree()

    DETERMINISTIC

    SQL SECURITY INVOKER

begin

   DECLARE V_INDENT INTEGER;

   DECLARE AFFECTED_CNT INTEGER;

   CREATE TEMPORARY TABLE IF NOT EXISTS TMP_ITEMS(ID INTEGER, NAME VARCHAR(200), PARENT_ID INTEGER, INDENT INTEGER, SORT_ID text);

   CREATE TEMPORARY TABLE IF NOT EXISTS TMP_ITEMS2(ID INTEGER, NAME VARCHAR(200), PARENT_ID INTEGER, INDENT INTEGER, SORT_ID text);

   CREATE TEMPORARY TABLE IF NOT EXISTS TMP_ITEMS3(ID INTEGER, NAME VARCHAR(200), PARENT_ID INTEGER, INDENT INTEGER, SORT_ID text);

   CREATE TEMPORARY TABLE IF NOT EXISTS TMP_ITEMS4(ID INTEGER, NAME VARCHAR(200), PARENT_ID INTEGER, INDENT INTEGER, SORT_ID text);

   DELETE FROM TMP_ITEMS;

   DELETE FROM TMP_ITEMS2;

   DELETE FROM TMP_ITEMS3;

   DELETE FROM TMP_ITEMS4;

   INSERT INTO TMP_ITEMS (ID, NAME, PARENT_ID, INDENT, SORT_ID) 

   SELECT ID, NAME, PARENT_ID, 0, NAME FROM ITEMS WHERE PARENT_ID IS NULL;

   INSERT INTO TMP_ITEMS2 (ID, NAME, PARENT_ID, INDENT, SORT_ID) SELECT ID, NAME, PARENT_ID, INDENT, SORT_ID FROM TMP_ITEMS;

   INSERT INTO TMP_ITEMS3 (ID, NAME, PARENT_ID, INDENT, SORT_ID) SELECT ID, NAME, PARENT_ID, INDENT, SORT_ID FROM TMP_ITEMS;

   INSERT INTO TMP_ITEMS4 (ID, NAME, PARENT_ID, INDENT, SORT_ID) SELECT ID, NAME, PARENT_ID, INDENT, SORT_ID FROM TMP_ITEMS;

   SET @AFFECTED_CNT = 0;

   SET @V_INDENT = 1;

   REPEAT

     INSERT INTO TMP_ITEMS (ID, NAME, PARENT_ID, INDENT, SORT_ID)

     SELECT ITEMS.ID, ITEMS.NAME, ITEMS.PARENT_ID, @V_INDENT, CONCAT(TMP_ITEMS4.SORT_ID, ITEMS.NAME)

     FROM ITEMS

     LEFT JOIN TMP_ITEMS4 ON (ITEMS.PARENT_ID = TMP_ITEMS4.ID)

     WHERE ITEMS.PARENT_ID IN (SELECT ID FROM TMP_ITEMS2)

     AND ITEMS.ID NOT IN (SELECT ID FROM TMP_ITEMS3);

     SET @AFFECTED_CNT = ROW_COUNT();

     DELETE FROM TMP_ITEMS2;

     INSERT INTO TMP_ITEMS2 (ID, NAME, PARENT_ID, INDENT, SORT_ID)

     SELECT ID, NAME, PARENT_ID, INDENT, SORT_ID FROM TMP_ITEMS;

     DELETE FROM TMP_ITEMS3;

     INSERT INTO TMP_ITEMS3 (ID, NAME, PARENT_ID, INDENT, SORT_ID)

     SELECT ID, NAME, PARENT_ID, INDENT, SORT_ID FROM TMP_ITEMS;

     DELETE FROM TMP_ITEMS4;

     INSERT INTO TMP_ITEMS4 (ID, NAME, PARENT_ID, INDENT, SORT_ID)

     SELECT ID, NAME, PARENT_ID, INDENT, SORT_ID FROM TMP_ITEMS;

     SET @V_INDENT = @V_INDENT + 1;

   UNTIL @AFFECTED_CNT = 0

   END REPEAT;

   SELECT * FROM TMP_ITEMS ORDER BY SORT_ID;

end;

call get_tree();

indent name

0    Item 1

1    Item 1.1

1    Item 1.2

0    Item 2

1    Item 2.1

1    Item 2.2

1    Item 2.3

0    Item 3

1    Item 3.1

1    Item 3.2

2    Item 3.2.1

2    Item 3.2.2

paulus · 21.09.2008 14:40:37

Почему нельзя ссылаться дважды? Можно так же, как и всегда, через алиасы:

SELECT * FROM tablename a, tablename b;

Qtx · 22.09.2008 00:14:56

В документации по 5.0 было сказано

You cannot refer to a TEMPORARY table more than once in the same query. For example, the following does not work:

mysql> SELECT * FROM temp_table, temp_table AS t2;
ERROR 1137: Can't reopen table: 'temp_table'

На 5.0.22 не работало, на 5.0.67 работает. Спасибо за информацию.

PS
Да, MySQL разработчики странные ребята. В вресии меняется только цифра третьего звена, а изменений как в major update.

Форум пользователей MySQL

#1 16.09.2008 19:47:22

Вывод древовидного списка

#2 16.09.2008 19:59:36

Re: Вывод древовидного списка

#3 16.09.2008 21:24:58

Re: Вывод древовидного списка

#4 16.09.2008 22:47:18

Re: Вывод древовидного списка

#5 16.09.2008 23:16:15

Re: Вывод древовидного списка

#6 16.09.2008 23:53:49

Re: Вывод древовидного списка

#7 19.09.2008 12:06:29

Re: Вывод древовидного списка

#8 21.09.2008 14:40:37

Re: Вывод древовидного списка

#9 22.09.2008 00:14:56

Re: Вывод древовидного списка

Board footer