mysql从存储过程中检索最后一个\u insert \u id

我正在与nodejs和mysql合作，通过post\u tag保存post及其标记：

DROP TABLE IF EXISTS post_tag;
DROP TABLE IF EXISTS post;
DROP TABLE IF EXISTS tag;
DROP PROCEDURE IF EXISTS select_all_posts;
DROP PROCEDURE IF EXISTS insert_post;

CREATE TABLE post (
  id INT NOT NULL AUTO_INCREMENT,
  title VARCHAR(45) NULL,
  body TEXT NULL,
  PRIMARY KEY (id));

CREATE TABLE tag (
  id INT NOT NULL AUTO_INCREMENT,
  tag VARCHAR(45) NULL,
  PRIMARY KEY (id));

CREATE TABLE post_tag (
  post_id INT NOT NULL,
  tag_id INT NOT NULL,
  PRIMARY KEY (post_id, tag_id),
  INDEX fk_post_tag_tag1_idx (tag_id ASC),
  INDEX fk_post_tag_post_idx (post_id ASC),
  CONSTRAINT fk_post_tag_post
    FOREIGN KEY (post_id)
    REFERENCES post (id),
  CONSTRAINT fk_post_tag_tag1
    FOREIGN KEY (tag_id)
    REFERENCES tag (id));

INSERT INTO post (id, title, body) VALUES (1, 'post 1', "Post body");
INSERT INTO post (id, title, body) VALUES (2, 'post 2', "Post body");
INSERT INTO tag (id, tag) VALUES (1, 'tag 1');
INSERT INTO tag (id, tag) VALUES (2, 'tag 2');
INSERT INTO post_tag (post_id, tag_id) VALUES (1, 1);
INSERT INTO post_tag (post_id, tag_id) VALUES (1, 2);
INSERT INTO post_tag (post_id, tag_id) VALUES (2, 1);

我创建了一个存储过程来插入新帖子：

-- Stored procedure to insert post and tags
DELIMITER $$
CREATE PROCEDURE insert_post(
  IN title VARCHAR(45),
  IN body TEXT
)
BEGIN
  INSERT INTO post (title, body) VALUES (title, body);
END $$
DELIMITER ;

所以在node我可以做：

var mysql = require("mysql2");
var connection = mysql.createConnection({
  host: "127.0.0.1",
  user: "test_database",
  password: "test_database",
  database: "test_database",
});

connection.connect();

const postTitle = JSON.stringify("Post 3");
const postBody = JSON.stringify("This is post 3 body");
const insertPostQuery = "CALL insert_post(" + postTitle + "," + postBody + " )";

connection.query(insertPostQuery, (error, results) => {
  console.log(JSON.stringify(results, null, 4));
});

connection.end();

这将返回：

{
    "fieldCount": 0,
    "affectedRows": 1,
    "insertId": 0,
    "info": "",
    "serverStatus": 2,
    "warningStatus": 0
}

现在我想从存储过程中检索最后插入的id，所以我修改了存储过程以添加 OUT last_inserted_id INT param，然后在插入后赋值：

-- Stored procedure to insert post and tags
DELIMITER $$
CREATE PROCEDURE insert_post(
  IN title VARCHAR(45),
  IN body TEXT,
  OUT last_inserted_id INT
)
BEGIN
  INSERT INTO post (title, body) VALUES (title, body);
  SET last_inserted_id = LAST_INSERT_ID();
END $$
DELIMITER ;

但是当在节点中执行查询时，返回 undefined 而不是带有 last_inserted_id 价值观。
我做错了什么？

你能举个例子吗？

CREATE FUNCTION insert_post_fn(
  in_title VARCHAR(45),
  in_body TEXT
)
RETURNS BIGINT
MODIFIES SQL DATA
BEGIN
  INSERT INTO post (title, body) VALUES (in_title, in_body);
  RETURN LAST_INSERT_ID();
END

使用方法：

SELECT insert_post_fn('Post title', 'This is post body') AS `LAST_INSERT_ID`;

CREATE PROCEDURE insert_post_proc(
  IN in_title VARCHAR(45),
  IN in_body TEXT,
  OUT out_id BIGINT
)
BEGIN
  INSERT INTO post (title, body) VALUES (in_title, in_body);
  SET out_id = LAST_INSERT_ID();
END

使用方法：

CALL insert_post_proc('Post title', 'This is post body', @id);
SELECT @id AS `LAST_INSERT_ID`;
-- PS. Variable name used is random
-- PPS. Both statements must be executed 
--      in the same connection and without reconnect

CREATE PROCEDURE insert_post_proc(
  IN in_title VARCHAR(45),
  IN in_body TEXT
)
BEGIN
  INSERT INTO post (title, body) VALUES (in_title, in_body);
  SELECT LAST_INSERT_ID() AS `LAST_INSERT_ID`;
END

使用方法：

CALL insert_post_proc('Post title', 'This is post body');

小提琴

mysql从存储过程中检索最后一个\u insert \u id

1条答案

相关问题

热门标签

最新问答