mysql:通过node.js中的存储过程保存json数据

yqkkidmi  于 2021-08-09  发布在  Java
关注(0)|答案(1)|浏览(437)

我试图用mysql和node中的存储过程保存一些json数据。
我有 post , post_tag 以及 tag table。

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,
  my_data TEXT NULL,
  PRIMARY KEY (id));

CREATE TABLE tag (
  id INT NOT NULL AUTO_INCREMENT,
  tag VARCHAR(45) NULL UNIQUE,
  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) VISIBLE,
  INDEX fk_post_tag_post_idx (post_id ASC) VISIBLE,
  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
DROP PROCEDURE IF EXISTS insert_post;
DELIMITER $$
CREATE PROCEDURE insert_post(
  IN my_data JSON
)
BEGIN
  -- Declare iterator variable to use it later on in the loop
  DECLARE i INT DEFAULT 0;

  -- Retrieve values from JSON
  SET @title = JSON_UNQUOTE(JSON_EXTRACT(my_data, '$.title'));
  SET @body = JSON_UNQUOTE(JSON_EXTRACT(my_data, '$.body'));
  SET @tags = JSON_EXTRACT(my_data, '$.tags');
  SET @json = JSON_UNQUOTE(my_data);
  -- Insert post
  INSERT INTO post (title, body, my_data) VALUES (
    @title,
    @body,
    @json);
  -- Retrieve inserted id to reuse it in post_tag
  SET @last_post = LAST_INSERT_ID();

  -- Get tags length for the loop
  SET @tags_length = JSON_LENGTH(@tags);
  -- Execute loop over tags length
  WHILE i < @tags_length DO
    -- Retrieve current tag from tags array
    SET @tag = JSON_UNQUOTE(JSON_EXTRACT(my_data, CONCAT('$.tags[',i,'].tag')));

    -- Insert tag
    INSERT INTO tag (tag) VALUES (
    @tag
    ) ON DUPLICATE KEY UPDATE tag = @tag;
    -- -- Retrieve inserted tag to reuse it on post_tag
    SET @last_tag = LAST_INSERT_ID();
    -- Insert retrieved post_id and tag_id into post_tag
    INSERT INTO post_tag (post_id, tag_id) VALUES (
      @last_post,
      @last_tag
    ) ON DUPLICATE KEY UPDATE post_id = @last_post, tag_id = @last_tag;
    -- -- Add step to iterator
    SELECT i + 1 INTO i;
  END WHILE;
END $$
DELIMITER ;

我可以直接用sql测试:

CALL insert_post('{"title":"My post","body":"postbody","tags":[{"tag":"tag 3"}]}');

这将成功,因为没有重复的标记。

CALL insert_post('{"title":"My post","body":"postbody","tags":[{"tag":"tag 1"}]}');

这将失败,因为已经有一个 tag 1 .
我该怎么解决这个问题?

5cnsuln7

5cnsuln71#

CREATE PROCEDURE insert_post(
  IN my_data JSON
)
BEGIN
  -- Declare iterator variable to use it later on in the loop
  DECLARE i INT DEFAULT 0;

  -- Retrieve values from JSON
  SET @title = JSON_EXTRACT(my_data, '$.title');
  SET @body = JSON_EXTRACT(my_data, '$.body');
  SET @tags = JSON_EXTRACT(my_data, '$.tags');
  SET @json = JSON_UNQUOTE(my_data);
  -- Insert post
  INSERT INTO post (title, body, my_data) VALUES (
--****  UNQUOTE values  ****
    JSON_UNQUOTE(@title),
    JSON_UNQUOTE(@body),
    @json);
  -- Retrieve inserted id to reuse it in post_tag
  SET @last_post = LAST_INSERT_ID();

  -- Get tags length for the loop
  SET @tags_length = JSON_LENGTH(@tags);
  -- Execute loop over tags length
  WHILE i < @tags_length DO
    -- Retrieve current tag from tags array
    SET @tag = JSON_EXTRACT(my_data, CONCAT('$.tags[',i,'].tag'));

    -- Insert tag
    INSERT INTO tag (tag) VALUES (
--****  UNQUOTE value  ****
      JSON_UNQUOTE(@tag)
    ) ON DUPLICATE KEY UPDATE tag = JSON_UNQUOTE(@tag);
    -- Retrieve inserted tag to reuse it on post_tag
--****  ODKU and LAST_INSERT_ID are not relative!!! removed.  ****
--    SET @last_tag = LAST_INSERT_ID();
--****  Retrieve according `id`  ****
    SELECT id INTO @last_tag
    FROM tag
    WHERE tag = JSON_UNQUOTE(@tag);
    -- Insert retrieved post_id and tag_id into post_tag
    INSERT INTO post_tag (post_id, tag_id) VALUES (
      @last_post,
      @last_tag
    ) ON DUPLICATE KEY UPDATE post_id = @last_post, tag_id = @last_tag;
    -- Add step to iterator
    SELECT i + 1 INTO i;
  END WHILE;
END

小提琴
另外,你可以用 ON DUPLICATE KEY UPDATE tag = VALUES(tag); 而不是 ON DUPLICATE KEY UPDATE tag = JSON_UNQUOTE(@tag); . 对我来说也是一样 post_tag . 小提琴。

相关问题