我试图用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
.
我该怎么解决这个问题?
1条答案
按热度按时间5cnsuln71#
小提琴
另外,你可以用
ON DUPLICATE KEY UPDATE tag = VALUES(tag);
而不是ON DUPLICATE KEY UPDATE tag = JSON_UNQUOTE(@tag);
. 对我来说也是一样post_tag
. 小提琴。