|
Задавайте вопросы, мы ответим
Вы не зашли.
Доброго времени суток!
Есть иерархическая таблица, `parent_id` - `item_id` родителя:
CREATE TABLE `tree` (
`item_id` int(11) unsigned NOT NULL auto_increment,
`parent_id` int(11) default NULL,
`descr` varchar(64) default NULL,
`some_data` tinytext,
PRIMARY KEY (`item_id`)
) ENGINE=MyISAMЗаполненая следующим образом
INSERT INTO `tree` (`item_id`,`parent_id`,`descr`,`some_data`) VALUES (1,'0','root','The start'), (2,'1','level 1','2'), (3,'1','level 1','3'), (4,'3','level 2','4'), (5,'4','level 3','5'), (6,'2','level 2','6'), (7,'3','level 2','7'), (8,'2','level 2','8'), (9,'8','level 3','9'), (10,'1','level 1','10'), (11,'10','level 2','11'), (12,'6','level 3','12'), (13,'11','level 3','13'), (14,'4','level 3','14'), (15,'7','level 3','15'), (16,'12','appendix','The end');
Есть следующая процедура. В неё передаётся item_id любого элемента и она выбирает клиенту все элементы от этого item_id до root.
DELIMITER $$ DROP PROCEDURE IF EXISTS `fromItemToRoot` $$ CREATE PROCEDURE `fromItemToRoot` (IN _item_id INT) BEGIN WHILE _item_id > 0 DO SELECT * FROM `tree` WHERE `item_id` = _item_id; SELECT parent_id INTO _item_id FROM `tree` WHERE `item_id` = _item_id; #SELECT _item_id; END WHILE; END $$ DELIMITER ;
Проблема в том что, передавая, например, _item_id = 16, WHILE делает только одну итерацию, хотя по окончанию первой итерации _item_id = 12. Почему так? Как заставить его делать все итерации?
Отредактированно Aco (30.04.2008 10:20:27)
Неактивен
А вообще возможно такие выборки составлять?
Неактивен
Спасибо за подробное описание. Я скопировал Ваш код в консоль mysql и вызвал процедуру, она сделала 5 итераций как положено. Какая у Вас версия MySQL?
mysql> CALL fromItemToRoot(16); +---------+-----------+----------+-----------+ | item_id | parent_id | descr | some_data | +---------+-----------+----------+-----------+ | 16 | 12 | appendix | The end | +---------+-----------+----------+-----------+ 1 row in set (0.00 sec) +---------+-----------+---------+-----------+ | item_id | parent_id | descr | some_data | +---------+-----------+---------+-----------+ | 12 | 6 | level 3 | 12 | +---------+-----------+---------+-----------+ 1 row in set (0.00 sec) +---------+-----------+---------+-----------+ | item_id | parent_id | descr | some_data | +---------+-----------+---------+-----------+ | 6 | 2 | level 2 | 6 | +---------+-----------+---------+-----------+ 1 row in set (0.00 sec) +---------+-----------+---------+-----------+ | item_id | parent_id | descr | some_data | +---------+-----------+---------+-----------+ | 2 | 1 | level 1 | 2 | +---------+-----------+---------+-----------+ 1 row in set (0.00 sec) +---------+-----------+-------+-----------+ | item_id | parent_id | descr | some_data | +---------+-----------+-------+-----------+ | 1 | 0 | root | The start | +---------+-----------+-------+-----------+ 1 row in set (0.01 sec) Query OK, 0 rows affected (0.01 sec) mysql> \s -------------- mysql Ver 14.12 Distrib 5.0.22, for redhat-linux-gnu (i686) using readline 5.0 Connection id: 2 Current database: test Current user: root@localhost SSL: Not in use Current pager: stdout Using outfile: '' Using delimiter: ; Server version: 5.0.22-log Protocol version: 10 Connection: Localhost via UNIX socket Server characterset: cp1251 Db characterset: latin1 Client characterset: cp1251 Conn. characterset: cp1251 UNIX socket: /var/lib/mysql/mysql.sock Uptime: 46 min 9 sec Threads: 1 Questions: 108 Slow queries: 0 Opens: 66 Flush tables: 1 Open tables: 64 Queries per second avg: 0.039 --------------
Неактивен
MySQL версии 5.0.43. Хмм.. в консоле именно так всё и обстоит именно так.
Но дело в том, что я использую GUI оболочку MySQLyog. Проблема, видимо, с её стороны. Она может обработать зараз только один SELECT, в то время как процедура за раз вызывает несколько "разных" SELECT'ов. Интересно, а как будет реагировать PHP? (сейчас нет возможности проверить)
Отредактированно Aco (30.04.2008 14:24:04)
Неактивен
Процедура выполняется на уровне MySQL-сервера. Оболочка передается один запрос CALL имя_процедуры(). MySQL в Вашем случае выдает несколько ответов, видимо оболочка обрабатывает только первый.
Рекомендую модифицировать процедуру. Добавить в начало CREATE TEMPORARY TABLE t LIKE tree;
Далее вместо SELECT, INSERT INTO t SELECT * FROM tree;
В конце SELECT * FROM t; DROP TEMPORARY TABLE t;
Это упрощенная схема, Вам возможно потребуется выводить записи в том же порядке, в котором они выбирались, тогда в цикле следует объявить переменную counter, вставлять ее в таблицу t и в конце сортировать по ней
Неактивен
PHP не может обработать запрос "CALL fromItemToRoot(16)". Возвращает ошибку "PROCEDURE prc.fromItemToRoot can't return a result set in the given context". Переделал процедуру через prepared statements:
DELIMITER $$
DROP PROCEDURE IF EXISTS `fromItemToRoot` $$
CREATE PROCEDURE `fromItemToRoot` (IN _item_id INT)
BEGIN
SET @items := _item_id;
WHILE _item_id > 0 DO
SELECT parent_id INTO _item_id FROM `tree` WHERE `item_id` = _item_id;
SET @items := CONCAT(@items,",",_item_id);
END WHILE;
SET @s := CONCAT("SELECT * FROM `tree` WHERE item_id IN (",@items,") ORDER BY item_id DESC");
PREPARE stp FROM @s;
EXECUTE stp;
END $$
DELIMITER ;Однако и это не помогло. Ошибка всё та же.
Попробую через временную таблицу, как Вы советуете.
Рас уж такое дело, есть вопрос по временным таблицам: если одновременно два клиента запустят процедуру, они конфликтовать не будут? Или каждый видит только свою временную таблицу?
Отредактированно Aco (30.04.2008 14:57:04)
Неактивен
Видимо PHP не умеет обрабатывать множественные resultset-ы. Временная таблица существует только в рамках подключения, после завершения сессии, временная таблица удаляется автоматически, так что конфликта не будет - каждый пользователь будет видеть свои временные таблицы (в связи с этим есть некоторые особенности репликации)
Неактивен
Переделал на временные таблицы:
DELIMITER $$
DROP PROCEDURE IF EXISTS `fromItemToRoot` $$
CREATE PROCEDURE `fromItemToRoot` (IN _item_id INT)
BEGIN
DROP TABLE IF EXISTS temp;
CREATE TEMPORARY TABLE temp
(
`item_id` int(11) unsigned NOT NULL,
`parent_id` int(11) default NULL,
`descr` varchar(64) default NULL,
`some_data` tinytext
) CHARSET=cp1251;
WHILE _item_id > 0 DO
INSERT INTO temp SELECT * FROM `tree` WHERE `item_id` = _item_id;
SELECT parent_id INTO _item_id FROM `tree` WHERE `item_id` = _item_id;
END WHILE;
SELECT * FROM temp ORDER BY item_id DESC;
END $$
DELIMITER ;И всё равно ошибка!
MySQL всё равно сообщает, что не может передать данные. Видимо взаимодействие PHP - MySQL через процедуры не такое лёгкое дело Пойду курить мануалы по PHP...
Неактивен
Стандартные php-функции mysql не позволяют процедуре вернуть значения. С процедурами корректо работают функции mysqli (доступные по умолчанию начиная с PHP 5). Вот пример кода, который работает у меня
<?php
$c = mysqli_connect('localhost','user','password','test') or die(mysql_error());
mysqli_select_db($c,'test') or die(mysql_error());
mysqli_query($c,"SET NAMES UTF8") or die(mysql_error());
$result = mysqli_query($c,"call fromItemToRoot(16);") or die(mysql_error());
while($aRow = mysqli_fetch_assoc($result)) {
print_r($aRow);
}
?>
Неактивен
Спасибо. Через mysqli всё работает. mysqli_connect и mysql_connect конфликтуют между собой, если не указывать коннекты в функциях?
Неактивен
В mysqli нельзя не указать соединение в функциях. mysql_ юзает соединение mysqli. По умолчанию mysql_, кажется, юзает последнее установленное соединение. Если Вы установите только соединение mysqli, то все должно работать правильно.
Неактивен
Кстати, вариант процедуры prepared statements работает быстрее чем через временные таблицы, эдак 2 раза. А помощь большое спасибо!
Неактивен
