Insert n rows with LAST_INSERT_ID

Question

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.

?? Why you skip FOREIGN KEY definitions from tables structures? — Akina, Apr 10 '22 at 17:13
Just to keep the question shorter. Foreign keys and indexes are all there. — longhorn, Apr 10 '22 at 17:15
*Just to keep the question shorter.* .. and less relevant. *Assume hashtag_id(s) are available as a list in nodejs.* You need SQL side solution, is it? but SQL knows nothing about nodejs lists... you must tell in what format the SQL server will receive this data. CSV? JSON? something else? — Akina, Apr 10 '22 at 17:29
The hashtags are already stored in mysql table. When a user posts they just select from the existing hashtags. They can select between 1 and 20 hashtags. I send the hashtag_id(s) of these hashtags from my front end as comma separated string to backend where I convert the string to a list in nodejs. Now my problem is that since the number of rows to be entered to POSTS_HASHTAGS_RELATION is not fixed, how do I write it in query. — longhorn, Apr 10 '22 at 17:45
*I send the hashtag_id(s) of these hashtags from my front end as comma separated string to backend where I convert the string to a list in nodejs.* Do not convert CSV, provide it as-is, parse and save on the MySQL side. Recommended saving format - stored procedure. — Akina, Apr 10 '22 at 18:15

Bill Karwin · Accepted Answer · 2022-04-10T17:58:36.737

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.

Thanks for the reply Bill. I am okay with referencing @post_id multiple times but the number of values to be inserted into POSTS_HASHTAGS_RELATION is not fixed. The user might select only 1 hashtag or might select 20. How do I account for that in the query? — longhorn, Apr 10 '22 at 17:01
I am using nodejs by the way. I tried using VALUES ? and constructing the string using a for loop but mysql gives error. — longhorn, Apr 10 '22 at 17:05
Yeah learnt that after trial. The use case I have mentioned should not be that uncommon yet I am unable to find anything helpful. — longhorn, Apr 10 '22 at 17:37
I think this will work but in doing so we split the query into 2 parts right. Our actual query has few more steps in it and is being run as a transaction. Any way this can be done in a single query? Either way appreciate your detailed response. — longhorn, Apr 10 '22 at 18:17
You can't insert to two tables in a single SQL statement, but you can insert to the first table and then to the subsequent table as two separate insert statements within the same transaction. — Bill Karwin, Apr 10 '22 at 18:20
I found an example of two inserts in the same transaction: https://github.com/mysqljs/mysql#transactions — Bill Karwin, Apr 10 '22 at 18:28

score 0 · Answer 2 · answered Apr 10 '22 at 18:30

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

Insert n rows with LAST_INSERT_ID

2 Answers2