I have a simple MySQL stored procedure designed to return all child records for a given node. My problem is when I type this manually it returns the proper results - but when I put the same code into a Stored Procedure it returns only the parent id.
我有一个简单的MySQL存储过程,用于返回给定节点的所有子记录。我的问题是当我手动输入时,它会返回正确的结果——但是当我将相同的代码放入一个存储过程中时,它只返回父id。
I'd really appreciate some guidance!
我真的很感激你的指导!
For example - when I call my procedure (code is below) I get:
例如,当我调用我的过程(代码如下)时,我得到:
call find_child(1006);
+--------+
| nodeid |
+--------+
| 1006 |
| 1006 |
| 1006 |
| 1006 |
+--------+
4 rows in set (0.01 sec)
BUT - When I cut and paste the command I get the proper resultset:
但是——当我剪切和粘贴命令时,我得到了正确的结果集:
mysql> create temporary table KID_TABLE (nodeid INT);
Query OK, 0 rows affected (0.00 sec)
mysql> insert ignore into KID_TABLE (nodeid) select nodeid from CORPORATENODE
where parentid in (1006);
Query OK, 4 rows affected (0.00 sec)
Records: 4 Duplicates: 0 Warnings: 0
mysql> select * from KID_TABLE;
+--------+
| nodeid |
+--------+
| 1007 |
| 1008 |
| 1031 |
| 1038 |
+--------+
4 rows in set (0.00 sec)
Here's the code:
这是代码:
DELIMITER $$
DROP PROCEDURE IF EXISTS `find_child`$$
CREATE PROCEDURE `find_child`( IN NodeID INT)
DETERMINISTIC
BEGIN
declare nid INT;
set nid= NodeID;
create temporary table KID_TABLE (nodeid INT);
insert ignore into KID_TABLE (nodeid) select nodeid
from CORPORATENODE where parentid in (1006);
select * from KID_TABLE;
drop table KID_TABLE;
END $$
DELIMITER ;
Here's the DDL for PARENT table
这是父表的DDL
CREATE TABLE `PARENT` (
`NODEID` int(11) NOT NULL AUTO_INCREMENT,
`PARENTID` int(11) NOT NULL DEFAULT '0' COMMENT '0 value means top node',
`NAME` varchar(50) NOT NULL,
PRIMARY KEY (`NODEID`) USING BTREE
) ENGINE=InnoDB;
2 个解决方案
#1
2
Mysql will return your in variable NodeId in your select nodeid from CORPORATENODE where parentid in (1006);
Mysql将从CORPORATENODE中返回您的变量NodeId,其中parentid在(1006);
Change the variable to in_NodeID instead.
将变量改为in_NodeID。
DELIMITER $$
DROP PROCEDURE IF EXISTS `find_child`$$
CREATE PROCEDURE `find_child`( IN in_NodeID INT)
BEGIN
create temporary table KID_TABLE (nodeid INT);
insert ignore into KID_TABLE (nodeid) select nodeid
from CORPORATENODE where parentid in (in_NodeID);
select * from KID_TABLE;
drop table KID_TABLE;
END $$
DELIMITER ;
But then of course, why use a temporary table?
但是当然,为什么要使用临时表呢?
DELIMITER $$
DROP PROCEDURE IF EXISTS `find_child`$$
CREATE PROCEDURE `find_child`( IN in_NodeID INT)
BEGIN
select nodeid from CORPORATENODE where parentid in (in_NodeID);
END $$
DELIMITER ;
#2
1
Thanks Andreas et al,
谢谢安德烈亚斯等,
I found a workaround to this just after posting - typical. But couldn't post my solution till now. I use Linux but have lower_case_table_names=1 set so the case is irrelevant. (I think?)
我在发布后发现了一个变通方法。但我现在还不能发布我的解决方案。我使用的是Linux,但是有一个小写的_case_table_names=1,所以这个大小写是不相关的。(我认为)
Andreas, I needed the temporary table as once I had selected back all the child records for a Node I then needed to update all the child records in the table NODEVERSION. I was running this via a trigger on the NODEVERSION table and was running into problems as I was updating the calling table etc. That's why I created a new CHILD_TABLE.
Andreas,我需要临时表,因为一旦我为一个节点选择了所有子记录,我就需要更新表NODEVERSION中的所有子记录。我通过NODEVERSION表上的触发器运行它,在更新调用表等时遇到了问题。这就是为什么我创建了一个新的CHILD_TABLE。
So I found that (even leaving DETERMINISTIC in... sorry pilcrow!) The following worked as expected - all I did was added aliases ... (The code below is a little different from above as I simplified the above code for my question)
所以我发现(即使是在…对不起pilcrow !)下面的操作如预期的那样-我所做的只是添加别名……(下面的代码与上面的稍有不同,因为我简化了上面的代码)
Don't fully understand why the aliases fixed the initial problem - but it worked. Perhaps you experts know why?
不完全理解为什么别名解决了最初的问题——但它确实有效。也许你们这些专家知道为什么?
CREATE PROCEDURE `find_child`( IN NodeID int)
DETERMINISTIC
BEGIN
declare nid INT;
declare rows_before INT DEFAULT 0;
declare rows_after INT DEFAULT 0;
set nid= NodeID;
delete from CHILD_TABLE;
insert into CHILD_TABLE values (nid);
set rows_before = (select count(*) from CHILD_TABLE);
if rows_before !=rows_after then
increment: repeat
set rows_before = (select count(*) from CHILD_TABLE);
insert ignore into CHILD_TABLE (nodeid) select b.nodeid from CORPORATENODE b
where b.parentid in (select c.nodeid from CHILD_TABLE c);
set rows_after= (select count(*) from CHILD_TABLE);
until rows_before =rows_after
end repeat increment;
end if;
update NODEVERSION n set STATUS=1 where n.nodeid in
(select c.nodeid from CHILD_TABLE c);
END
#1
2
Mysql will return your in variable NodeId in your select nodeid from CORPORATENODE where parentid in (1006);
Mysql将从CORPORATENODE中返回您的变量NodeId,其中parentid在(1006);
Change the variable to in_NodeID instead.
将变量改为in_NodeID。
DELIMITER $$
DROP PROCEDURE IF EXISTS `find_child`$$
CREATE PROCEDURE `find_child`( IN in_NodeID INT)
BEGIN
create temporary table KID_TABLE (nodeid INT);
insert ignore into KID_TABLE (nodeid) select nodeid
from CORPORATENODE where parentid in (in_NodeID);
select * from KID_TABLE;
drop table KID_TABLE;
END $$
DELIMITER ;
But then of course, why use a temporary table?
但是当然,为什么要使用临时表呢?
DELIMITER $$
DROP PROCEDURE IF EXISTS `find_child`$$
CREATE PROCEDURE `find_child`( IN in_NodeID INT)
BEGIN
select nodeid from CORPORATENODE where parentid in (in_NodeID);
END $$
DELIMITER ;
#2
1
Thanks Andreas et al,
谢谢安德烈亚斯等,
I found a workaround to this just after posting - typical. But couldn't post my solution till now. I use Linux but have lower_case_table_names=1 set so the case is irrelevant. (I think?)
我在发布后发现了一个变通方法。但我现在还不能发布我的解决方案。我使用的是Linux,但是有一个小写的_case_table_names=1,所以这个大小写是不相关的。(我认为)
Andreas, I needed the temporary table as once I had selected back all the child records for a Node I then needed to update all the child records in the table NODEVERSION. I was running this via a trigger on the NODEVERSION table and was running into problems as I was updating the calling table etc. That's why I created a new CHILD_TABLE.
Andreas,我需要临时表,因为一旦我为一个节点选择了所有子记录,我就需要更新表NODEVERSION中的所有子记录。我通过NODEVERSION表上的触发器运行它,在更新调用表等时遇到了问题。这就是为什么我创建了一个新的CHILD_TABLE。
So I found that (even leaving DETERMINISTIC in... sorry pilcrow!) The following worked as expected - all I did was added aliases ... (The code below is a little different from above as I simplified the above code for my question)
所以我发现(即使是在…对不起pilcrow !)下面的操作如预期的那样-我所做的只是添加别名……(下面的代码与上面的稍有不同,因为我简化了上面的代码)
Don't fully understand why the aliases fixed the initial problem - but it worked. Perhaps you experts know why?
不完全理解为什么别名解决了最初的问题——但它确实有效。也许你们这些专家知道为什么?
CREATE PROCEDURE `find_child`( IN NodeID int)
DETERMINISTIC
BEGIN
declare nid INT;
declare rows_before INT DEFAULT 0;
declare rows_after INT DEFAULT 0;
set nid= NodeID;
delete from CHILD_TABLE;
insert into CHILD_TABLE values (nid);
set rows_before = (select count(*) from CHILD_TABLE);
if rows_before !=rows_after then
increment: repeat
set rows_before = (select count(*) from CHILD_TABLE);
insert ignore into CHILD_TABLE (nodeid) select b.nodeid from CORPORATENODE b
where b.parentid in (select c.nodeid from CHILD_TABLE c);
set rows_after= (select count(*) from CHILD_TABLE);
until rows_before =rows_after
end repeat increment;
end if;
update NODEVERSION n set STATUS=1 where n.nodeid in
(select c.nodeid from CHILD_TABLE c);
END