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

kaurovis · 27.01.2011 08:24:53

Здравствуйте, есть таблица типов товаров:

CREATE TABLE `goods_types` (

  `id_type` int(11) unsigned NOT NULL,

  `name` varchar(255) default NULL,

  `chpu` varchar(255) default NULL,

  `image` varchar(255) default NULL,

  `level` int(11) default '0',

  `parent_id` int(11) default NULL,

  `left_key` int(11) default NULL,

  `right_key` int(11) default NULL,

  `visible` int(11) NOT NULL default '1',

  PRIMARY KEY  (`id_type`),

  UNIQUE KEY `id_type` (`id_type`),

  KEY `left_key` (`left_key`),

  KEY `right_key` (`right_key`),

  KEY `parent_id` (`parent_id`)

) ENGINE=MyISAM AUTO_INCREMENT=111 DEFAULT CHARSET=cp1251;

Поля left_key и right_key - для реализации модели Nested Sets. Есть временная таблица goods_types_tmp, с точно такой же структурой в которой храниться дерево со сбитыми ключами. Надо перенести данные, восстановив целостность ключей. Получается, что в качестве исходных данных только связка id_type-parent_id. Написал рекурсивную процедуру, проходящую от родителей к детям:

DROP PROCEDURE IF EXISTS transfer_types;

CREATE PROCEDURE transfer_types(IN parent_id_param INT(11))

BEGIN

    DECLARE _name, _chpu VARCHAR(255);

    DECLARE _id_type, _parent_id INT(11);

    DECLARE x, _count INT(11);

    DECLARE _current_cursor CURSOR FOR SELECT `id_type`, `name`, `chpu`, `parent_id` FROM `goods_types_tmp` WHERE `parent_id`=parent_id_param ORDER BY `name`;

    SELECT COUNT(*) INTO _count FROM `goods_types_tmp` WHERE `parent_id`=parent_id_param;

    IF _count>0 THEN

        OPEN _current_cursor;

        SET x = 0;

           qwe : WHILE x < _count DO

            FETCH _current_cursor INTO _id_type, _name, _chpu, _parent_id;

            SET x = x + 1;

             CALL `insert_type`(_id_type, _name, _chpu, _parent_id);

            CALL transfer_types(_id_type);

        END WHILE qwe;

        CLOSE _current_cursor;

    END IF;

END;

Вызываю просто:

CALL transfer_types(0);

Функция insert_type реализует вставку используя алгоритм NestedSet.
Проблема в том, что происходит обработка и вставка только первой записи, т.е. процедура выполняется только один раз и не выполняется рекурсивно. Рекурсию на сервере пишу впервые, не могу понять в чем может быть проблема. Сначала думал, что не правильно организую проход по курсору, но если убрать вызов процедуры то по первому курсору обрабатываются все записи.

kaurovis · 27.01.2011 09:45:53

Или хотя бы подскажите где почитать про рекурсию в хранимых процедурах. Может есть какие-нибудь особенности при объявлении или еще что-нибудь... Перерыл все что можно, ничего полезного.

kaurovis · 27.01.2011 10:14:15

Делаю вызов вот так:

set max_sp_recursion_depth=3000;

CALL transfer_types(0);

результат тот же.

kaurovis · 27.01.2011 10:17:34

Параллельное обсуждение ведется здесь

paulus · 28.01.2011 01:33:25

Насколько я вижу, здесь обсуждение пока еще не ведется

А ошибок / предупреждений не выдает? Я тупо сделал так же, как у Вас — работает.
Единственное «но» — я правильно выставил DELIMITER. Тупой вопрос — у Вас не видно,
чтобы DELIMITER менялись, вы же их меняете, да?

kaurovis · 28.01.2011 07:23:39

Спасибо, paulus, увидел ТУПЕЙШУЮ ошибку, когда пересматривал этот пост. Здесь я написал, что вызов делаю так:

set max_sp_recursion_depth=3000;

CALL transfer_types(0);

А у самого, так:

set @max_sp_recursion_depth=3000;

CALL transfer_types(0);

У меня стояла СОБАКА! Скопировал с какого-то сайта когда искал решение проблемы, а сюда уже руками писал... Никогда еще не терял столько времени на подобное. Так мелочи могут "съесть" у человека и половину жизни.
DELIMITER меняю, как во всех примерах и указано, но если честно не понимаю для чего это делается. Попробовал убрать смену DELIMITER, все работает точно так же. Не могли бы вы объяснить зачем это нужно? Где-то прочел: "Для отделения внутреннего запроса от внешнего". Но мне это объяснение не понятно, потому что DELIMITER меняется, например, на '/', но в процедуре используется все равно ';', потом опять возвращается ';', а '/' так нигде и не используется...
Так же не совсем понятно и значение собаки. Она говорит о том, что переменная является глобальной в пределах одного запроса или сессии?

Отредактированно kaurovis (28.01.2011 11:16:05)

paulus · 28.01.2011 14:20:21

У Вас DELIMITER нигде не стоят, меня это и смущает. Смысл очень простой —
они ограничивают запросы. Если у Вас есть составной запрос, который нужно
отправить на сервер одним куском, то нужно клиенту сказать «те точки с запя-
той, которые я пишу, запрос не ограничивают, жди, когда наберу вот этот
ограничитель».

Наверное, примером проще:

Код:

[silentia] root test > CREATE PROCEDURE t () BEGIN SELECT 1; SELECT 2; END;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '' at line 1
+---+
| 2 |
+---+
| 2 |
+---+
1 row in set (0.00 sec)

ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'END' at line 1
[silentia] root test > \d ;;
[silentia] root test > CREATE PROCEDURE t () BEGIN SELECT 1; SELECT 2; END;;
Query OK, 0 rows affected (0.00 sec)

Тут видно, что клиент начал отправлять запросы, начиная с первой
точки с запятой, что приводит к ошибкам.

Что касается собаки — нет, она не означает глобальную переменную.
Вы можете использовать @любое_слово в качестве пользовательской
переменной, которая хранит произвольное скалярное значение. С сис-
темными переменными они никак не связаны. Есть, конечно, оговорка —
значения глобальных переменных можно читать, если поставить перед
названием переменной @@ (но при этом переменная всё равно без @@).

kaurovis · 28.01.2011 16:04:29

Спасибо, paulus.
1. Про DELIMITER по вашему примеру вроде понял, но тогда получается что мой код, где я создаю процедуру transfer_types не должен сработать? Он, как я понял, должен попытаться завершить запрос при первой встрече ';' (или после встречи), т.е. ругнуться на этой (или после) строчке:

DECLARE _name, _chpu VARCHAR(255);

Но у меня этот код срабатывает, а у вас? Или может разработчики научили сервер обходить это при создании процедур (у меня версия '5.0.45-community-nt')? Или может мэнеджер разруливает (у меня EMS MySQLManager версии 4.5.0.9)? Другие процедуры я также писал.

2. Проблема была все-таки в обращении к системной переменной. Обращаясь к ней так:

set @max_sp_recursion_depth=3000;

т.е. я к ней не обращался

, а просто объявлял переменную и задавал ей значение, а системная переменная так и оставалась равной нулю, поэтому рекурсия и не работала (правильно?).

3. А чем отличаются переменные var1 и var2 внутри процедуры:

DECLARE var1 INT(11);

set var1=100;

set @var2=var1;

И еще здесь же: чем отличаются операторы = и := ?

Отредактированно kaurovis (28.01.2011 17:24:14)

paulus · 28.01.2011 17:49:53

1. Да, наверное, Ваш клиент умный. На мой взгляд, это страшно, я бы ему не доверял
Но у Вас может быть другой опыт.

2. Да, а вот если бы у Вас был менее умный клиент, он бы показал текст ошибки (кстати,
там же и нормальное название переменной указано)

Код:

[silentia] root test > call p(1);
+------+------+
| a    | t    |
+------+------+
|    1 |    1 |
+------+------+
1 row in set (0.00 sec)

ERROR 1456 (HY000): Recursive limit 0 (as set by the max_sp_recursion_depth variable) was exceeded for routine p

3. Они отличаются тем, что var1 живет внутри процедуры и имеет тип INT,
а @var2 глобальная (т.е. после выхода из процедуры она будет жить до
конца сессии) и не типизированная.

4. Внутри SET операторы совпадают. Их различие проявляется внутри SELECT.

Код:

[silentia] root test > SET @a = 1;
Query OK, 0 rows affected (0.00 sec)

[silentia] root test > SELECT @a = 2, @a := 2;
+--------+---------+
| @a = 2 | @a := 2 |
+--------+---------+
|      0 |       2 |
+--------+---------+
1 row in set (0.00 sec)

Подсказка: первый нолик — это «ложь»

kaurovis · 29.01.2011 06:10:02

Спасибо, paulus.

Попробовал выполнить через phpMyAdmin, действительно ругается И про переменную тоже говорит
Вообще, это даже возмутительно, почему клиент не показывает ошибки, которые выдает ему сервер, особенно в случае с переменной. В случае с процедурами еще можно понять "сами поменяем разделитель, чтоб вам не париться".

Люди, не пользуйтесь "умными" клиентами, иначе придется выглядеть глупо!

А про собаку я все-таки прав был? "Она говорит о том, что переменная является глобальной в пределах одного запроса или сессии?"

И еще вопрос: если установить значение системной переменной, например

set max_sp_recursion_depth=255;

, то какое время оно будет оставаться таким? На время сессии или навсегда. Есть смысл каждый раз устанавливать это значение?

paulus · 29.01.2011 23:20:20

Ммм, да, наверное, я неправильно Вас понял. Это действительно пользовательская пере-
менная, действующая только на текущую сессию, и живущая до конца этой сессии.

Для меня же глобальная переменная — это переменная, которая действует на сервер в
целом. Попробую объяснить примером, заодно и на следующий вопрос отвечу

Код:

[silentia] root (none) > SELECT @@max_sp_recursion_depth; -- проверим текущее значение
+--------------------------+
| @@max_sp_recursion_depth |
+--------------------------+
|                        0 |
+--------------------------+
1 row in set (0.00 sec)

[silentia] root (none) > SET max_sp_recursion_depth = 255;  -- установим локальное значение
Query OK, 0 rows affected (0.00 sec)

[silentia] root (none) > SELECT @@max_sp_recursion_depth;   -- и проверим
+--------------------------+
| @@max_sp_recursion_depth |
+--------------------------+
|                      255 |
+--------------------------+
1 row in set (0.00 sec)

[silentia] root (none) > SET GLOBAL max_sp_recursion_depth = 20;  -- а вот тут установим глобальную переменную
Query OK, 0 rows affected (0.00 sec)

[silentia] root (none) > SELECT @@max_sp_recursion_depth;   -- локальное значение не изменилось
+--------------------------+
| @@max_sp_recursion_depth |
+--------------------------+
|                      255 |
+--------------------------+
1 row in set (0.00 sec)

[silentia] root (none) > \r -- пересоединимся с сервером
Connection id:    46
Current database: *** NONE ***

[silentia] root (none) > SELECT @@max_sp_recursion_depth;  -- теперь локальное значение взято из глобальной переменной
+--------------------------+
| @@max_sp_recursion_depth |
+--------------------------+
|                       20 |
+--------------------------+
1 row in set (0.00 sec)

То есть глобальное значение имеет смысл один раз прописать в конфигурации
сервера, и все локальные переменные будут инициализироваться глобальным
значением.

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

#1 27.01.2011 08:24:53

Выполнение рекурсивной процедуры

#2 27.01.2011 09:45:53

Re: Выполнение рекурсивной процедуры

#3 27.01.2011 10:14:15

Re: Выполнение рекурсивной процедуры

#4 27.01.2011 10:17:34

Re: Выполнение рекурсивной процедуры

#5 28.01.2011 01:33:25

Re: Выполнение рекурсивной процедуры

#6 28.01.2011 07:23:39

Re: Выполнение рекурсивной процедуры

#7 28.01.2011 14:20:21

Re: Выполнение рекурсивной процедуры

Код:

#8 28.01.2011 16:04:29

Re: Выполнение рекурсивной процедуры

#9 28.01.2011 17:49:53

Re: Выполнение рекурсивной процедуры

Код:

Код:

#10 29.01.2011 06:10:02

Re: Выполнение рекурсивной процедуры

#11 29.01.2011 23:20:20

Re: Выполнение рекурсивной процедуры

Код:

Board footer