MySQL Mulitple Row Insert-Select Statement with Last_Insert_Id()

MySQL LAST_INSERT_ID() used with multiple records INSERT statement

Yes. This behavior of last_insert_id() is documented in the MySQL docs:

Important

If you insert multiple rows using a single INSERT statement, LAST_INSERT_ID() returns the value generated for the first inserted row only. The reason for this is to make it possible to reproduce easily the same INSERT statement against some other server.

Insert n rows with LAST_INSERT_ID

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.

PDO - lastInsertId() for insert query with multiple rows

It's not possible. If you need generated ids for both rows - you need to perform 2 separated INSERT

Important If you insert multiple rows using a single INSERT statement,
LAST_INSERT_ID() returns the value generated for the first inserted
row only. The reason for this is to make it possible to reproduce
easily the same INSERT statement against some other server.

http://dev.mysql.com/doc/refman/5.5/en/information-functions.html#function_last-insert-id

Mysql mulitple row insert-select statement with last_insert_id()

For the last query, use this

INSERT INTO dbNEW.`user` (userID, entityID, other)  
SELECT user_id, entityID, other
FROM
(
    SELECT user_id, @key + @rn entityID, other, @rn := @rn + 1
    FROM (select @rn:=0) x, dbOLD.`user`
    order by user_id
) y;

The LAST_INSERT_ID() in MySQL is the FIRST id created in a batch, unlike SCOPE_IDENTITY() in SQL Server which is the LAST id. Since it is the first, we increment each row using the variable @rn, starting at addition=0 for the first row.

insert many rows, LAST_INSERT_ID return 1

The MySQL documentation clearly explains this behavior:

With no argument, LAST_INSERT_ID() returns a BIGINT UNSIGNED (64-bit) value representing the first (emphasis mine) automatically generated value successfully inserted for an AUTO_INCREMENT column as a result of the most recently executed INSERT statement. The value of LAST_INSERT_ID() remains unchanged if no rows are successfully inserted.

The first value generated by the auto increment sequence in the insert was 1, not 2 or 3, so the value 1 gets returned.

I think the confusion you have is around the name LAST_INSERT_ID. The "last" part refers to the most recent insert statement, not the most recent id value within that insert.

LAST_INSERT_ID( ) returning multiple rows of 0?

LAST_INSERT_ID() returns the id of the last inserted row and is not bound to any table. So if you create a new row:

INSERT INTO table VALUES('a', 'b', 'c');

It will return the last id (whatever value the new primary key has).

SELECT LAST_INSERT_ID();
=> 123

For details, please take a look at the manual:

LAST_INSERT_ID() (with no argument) returns a BIGINT (64-bit) value representing the first automatically generated value that was set for an AUTO_INCREMENT column by the most recently executed INSERT statement to affect such a column. For example, after inserting a row that generates an AUTO_INCREMENT value, you can get the value like this:

If you just want to get last ID in a table, you can do it like this:

SELECT id FROM table ORDER BY id DESC LIMIT 1;

Insert multiple rows from a select and put the generated IDs into other table

You can assume that a multi-row INSERT generates consecutive auto-increment id's, unless innodb_auto_inc_lock_mode=2. See https://dev.mysql.com/doc/refman/8.0/en/innodb-auto-increment-handling.html

LAST_INSERT_ID() will tell you the first id generated by the insert, and the others will each be 1 greater, as many as the number of rows inserted.

Then you can UPDATE alpha to set these, but be sure to use ORDER BY to specify both the order of copying and the order of UPDATE so they remain the same.

INSERT INTO beta (type_info)
SELECT type_info FROM alpha
WHERE beta_id IS NULL
ORDER BY id;

SET @id = LAST_INSERT_ID()-1;

UPDATE alpha
SET beta_id=(@id := @id + 1)
WHERE beta_id IS NULL
ORDER BY id;