使用 LAST_INSERT_ID 插入 n 行

Question

我有 3 个表，分别名为 POSTS、HASHTAGS 和 POSTS_HASHTAGS_RELATION，如下所示。

CREATE TABLE POSTS(
  post_id int unsigned NOT NULL AUTO_INCREMENT,
  content varchar(200) NOT NULL,
  PRIMARY KEY (post_id)
);

CREATE TABLE HASHTAGS(
  hashtag_id int unsigned NOT NULL AUTO_INCREMENT,
  hashtag varchar(40) NOT NULL,
  PRIMARY KEY (hashtag_id)
);

CREATE TABLE POSTS_HASHTAGS_RELATION(
  post_id int unsigned NOT NULL,
  hashtag_id int unsigned NOT NULL,
  PRIMARY KEY (post_id, hashtag_id)
);

用户发帖时，会从 HASHTAGS 中保存的主题标签中选择最多 20 个主题标签。我将这些主题标签的 hashtag_id 作为逗号分隔字符串从前端发送到后端，在后端将其转换为 Nodejs 中的列表。

首先，是否有更好的方法来构建这个？

其次，如何在单个查询中向 POSTS_HASHTAGS_RELATION 插入可变数量的行？

INSERT INTO POSTS (content) VALUES ('bla bla bla bla');
SET @post_id = LAST_INSERT_ID();
INSERT INTO POSTS_HASHTAGS_RELATION (post_id, hashtag_id) VALUES (@post_id, 19), (@post_id, 41) ...;
// Something like this but the number of values can be between 1 and 20

如果之前已回答过这个问题，只需引导我找到这个答案。我找不到任何相关的东西。我认为这不是一个非常独特的问题。

Answer 1

您显示的是我这样做的方式。您可以使用显示的方式插入多行，通过在值关键字之后编写多个行构造函数。如果这样做，则必须在每个行构造器中插入语句中指定的所有列中包含一个值。因此，您必须在每个行构造函数中引用@post_id变量。

如果您真的不喜欢多次编写@post_id，那么您可以做这样的事情：

INSERT INTO POSTS_HASHTAGS_RELATION (post_id, hashtag_id) 
 SELECT @post_id, hashtag_id FROM (
  SELECT 19 AS hashtag_id UNION SELECT 41 UNION SELECT 42 UNION ...
 ) AS t;

但是，看起来 都比您这样做的方式更清晰且可读。

---

您的评论：

我不是Node.js程序员，但是我使用其他语言的技术来构建基于输入列表的许多行构造函数的SQL语句。适当的查询参数只能代替标量值，而不是列表，表达式或标识符或SQL关键字等。但是我理解Node.js可以做一些额外的字符串订阅魔法，因此他们并没有真正执行查询参数。

假设您刚刚将插入插入到帖子中，您可以捕获最后的插入ID：

var postId = result.insertId;

然后为下一个插入创建部分插入语句：

insert = 'INSERT INTO POSTS_HASHTAGS_RELATION (post_id, hashtag_id) VALUES';

您将需要一个行构造函数的数组，而对于参数，则需要一个数组：

let rowConstructors = [];
let parameters = [];

hashtags.forEach(function (hashtag) {
  rowConstructors.push('(?, ?)');
  parameters.concat(postId, hashtag);
});

现在您有一个数组行构造函数，toString（）将变成逗号分隔的字符串。而且您有一系列值以作为参数传递。

connection.query(insert + rowConstructors.toString(), 
  parameters, function (error, results, fields) {
  if (error) throw error;
  // ...
});

我猜.toString（）是可选的，因为该数组应通过将其连接到insert字符串来自动胁迫到字符串。

同样，我不是一个节点。但这应该给您这个技术的想法。

Answer 2

将数据以 SP 格式保存到表中：

CREATE PROCEDURE save_post_and_tags (post_content VARCHAR(200), hashtag_ids TEXT)
BEGIN
    DECLARE post_id INT UNSIGNED;
    DECLARE tag_id INT UNSIGNED;
    INSERT INTO posts VALUES (DEFAULT, post_content);
    SET post_id := LAST_INSERT_ID();
    SET hashtag_ids := concat(hashtag_ids, ',');
    REPEAT
        SET tag_id := 0 + SUBSTRING_INDEX(hashtag_ids, ',', 1);
        SET hashtag_ids := TRIM(SUBSTRING(hashtag_ids FROM 1 + LOCATE(',', hashtag_ids)));
        INSERT INTO posts_hashtags_relation VALUES (post_id, tag_id);
    UNTIL hashtag_ids = '' END REPEAT;
END

https://dbfiddle.uk/?rdbms=mysql_8.0&fiddle=a9c622ea7ad2dd48dba18312c7a33487（故意使用草率的 CSV）。