mysql存储过程返回为null,MySQL-存储过程OUT变量返回null

My Table Structure is:

DROP TABLE IF EXISTS `child`;

CREATE TABLE `child` (

`id` int(11) NOT NULL AUTO_INCREMENT,

`name` varchar(255) NOT NULL,

PRIMARY KEY (`id`)

) ENGINE=InnoDB DEFAULT CHARSET=latin1;

DROP TABLE IF EXISTS `map_parent_child`;

CREATE TABLE `map_parent_child` (

`id` int(11) NOT NULL AUTO_INCREMENT,

`parent_id` int(11) NOT NULL,

`child_id` int(11) NOT NULL,

PRIMARY KEY (`id`),

UNIQUE KEY `unique_parent_child` (`parent_id`,`child_id`)

) ENGINE=InnoDB DEFAULT CHARSET=latin1;

DROP TABLE IF EXISTS `parent`;

CREATE TABLE `parent` (

`id` int(11) NOT NULL AUTO_INCREMENT,

`name` varchar(255) NOT NULL,

PRIMARY KEY (`id`)

) ENGINE=InnoDB DEFAULT CHARSET=latin1;

I have create a stored procedure like

DELIMITER $$

DROP PROCEDURE IF EXISTS `test`.`sp_parent`$$

CREATE DEFINER=`root`@`localhost` PROCEDURE `sp_parent`(

IN parent_name VARCHAR(255),

IN child_name VARCHAR(255),

OUT parent_id INT(11))

BEGIN

DECLARE parent_id INT DEFAULT 0;

DECLARE child_id INT DEFAULT 0;

START TRANSACTION;

INSERT INTO `parent` (`name`) VALUES(parent_name);

SET parent_id = LAST_INSERT_ID();

INSERT INTO `child` (`name`) VALUES(child_name);

SET child_id = LAST_INSERT_ID();

INSERT INTO `map_parent_child` (`parent_id`,`child_id`) VALUES(parent_id,child_id);

commit;

END$$

DELIMITER ;

CALL sp_parent("test", "test", @parentid);

But when i try to fetch output variable using select then i get NULL however all INSERT statement work fine and adding record into the database table.

SELECT @parentid;

What i am missing here?

解决方案

You might have already solved this by now, but the first thing I noticed about your stored procedure is that you have a local variable with the same name as the output variable (parent_id). It looks to me that you're setting the value of the local variable rather than the return variable, so the caller never sees the correct value.

Perhaps removing the local parent_id variable declaration will solve your problem.