MySQL过程保持返回0

Question

不知道为什么，但我一直把0作为输出参数.我运行了以下命令：

INSERT INTO users (first_name, last_name, email, image, date_of_registration)
 VALUES ("xx", "xx", "xxx", null, sysdate());SELECT LAST_INSERT_ID();

而且我得到了比我假设的PersistentConnections设置为true的好结果(!= 0)。还有其他可能导致这件事的秘密吗？

操作步骤

DROP PROCEDURE IF EXISTS partners.create_user;
CREATE DEFINER=`partnersmaster`@`%` PROCEDURE `create_user`(
  IN p_first_name NVARCHAR(30),
  IN p_last_name NVARCHAR(30),
  IN p_email NVARCHAR(100),
  IN p_image NVARCHAR(200),
  IN p_date_of_registration TIMESTAMP,
  OUT p_user_id INT
  )
BEGIN
    INSERT INTO users (first_name, last_name, email, image, date_of_registration)
    VALUES (p_first_name, p_last_name, p_email, p_image, p_date_of_registration);
    SET @p_user_id = LAST_INSERT_ID();
END;

Java

conn = engine.getDal().getConnection();
st = conn.prepareCall("{call partners.create_user(?,?,?,?,?,?)}");
st.setString("p_first_name", user.getFirstName());
st.setString("p_last_name", user.getLastName());
st.setString("p_email", user.getEmail());
if(user.getImage() != null)
    st.setString("p_image", user.getImage());
else
    st.setNull("p_image", Types.NVARCHAR);
st.setTimestamp("p_date_of_registration", new Timestamp(System.currentTimeMillis()));
st.registerOutParameter("p_user_id", Types.INTEGER);
st.execute();
userId = st.getInt("p_user_id");

Answer 1

过程参数和会话变量- p_user_id与@p_user_id之间存在一个问题.在您的示例中，没有设置p_user_id。

用这种方式改变代码-

BEGIN
  ...
  ...
  ...
  SET p_user_id = LAST_INSERT_ID();
END;