Insert n rows with LAST_INSERT

I have 3 tables called POSTS, HASHTAGS and POSTS_HASHTAGS_RELATION, as below.

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)
);

When user posts, they select upto 20 hashtags from those saved in HASHTAGS. I send the hashtag_id(s) of these hashtags from front end as comma separated string to backend where it is converted to list in nodejs.

Firstly, is there a better approach to struture this?

Secondly, how do I insert variable number of rows to POSTS_HASHTAGS_RELATION in a single query?

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

If this has been answered befored, just guide me to that answer. I am unable to find anything relevant. I assume this is not a very unique problem.

CodePudding user response：

What you show is the way I would do it. You can insert multiple rows using INSERT in the manner you show, by writing multiple row constructors after the VALUES keyword. If you do, you must include a value for all the columns named in your INSERT statement in every row constructor. Therefore you must reference the @post_id variable in each row constructor.

If you really don't like to write @post_id more than once, you could do something like this:

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;

But that seems less clear and readable than the way you were doing it.

Re your comment:

I'm not a node.js programmer, but I've used the technique in other languages to build an SQL statement with a number of row constructors based on the input list. Proper query parameters can only be used in place of scalar values, not lists or expressions or identifiers or SQL keywords, etc. But I understand node.js does some extra string-substitution magic, so they're not really doing query parameters.

Suppose you had just done your INSERT into POSTS, you could capture the last insert id:

var postId = result.insertId;

Then create a partial INSERT statement for your next insert:

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

You will need an array for the row constructors and an array for the parameters:

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

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

Now you have an array of row constructors, which toString() will turn into a comma-separated string. And you have an array of values to pass as parameters.

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

I guess the .toString() is optional, because the array should be coerced to a string automatically by concatenating it to the insert string.

Again, I'm not a node.js programmer, so forgive me if there are errors or style problems. But that should give you the idea of this technique.

CodePudding user response：

Saving the data into the tables in SP format:

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 (sloppy CSV is used on purpose).