Insert array to mysql, but add other mysql-based value to each new row before insert

Question

I have a table 'images' like this:

ProductId	SortOrder	Source
1	1	source.com/234/
1	2	source.com/675/
2	1	source.com/7698/
2	2	source.com/678/
2	3	source.com/7456/

In one mysql query, I want to insert multiple rows. But, most importantly, I want to populate the next SortOrder for each row(starting at the last one for that ProductId).

For example, I would love to be able to do:

const values = [
    [2,'source.com/456546', @max_sort_order := @max_sort_order + 1],
    [2,'source.com/237675', @max_sort_order := @max_sort_order + 1]
]

const query = "SET @max_sort_order := (SELECT COALESCE(MAX( SortOrder ), 1) FROM images i WHERE ProductId = 2);INSERT INTO images (ProductId, Source, SortOrder) VALUES ?"

connection.query({sql: query, timeout: 40000, values: [values]...

Which would ideally result in the following new rows:

ProductId	SortOrder	Source
2	4	source.com/456/
2	5	source.com/275/

But I can't put that @max_sort_order variable in the values, due to it being prepared values(I think).

Any idea on how to be go about this?

Answer 1

The simplest solution is secondary autoincremented column in MyISAM table:

CREATE TABLE images (
    ProductId INT,
    SortOrder INT AUTO_INCREMENT,
    Source VARCHAR(160),
    PRIMARY KEY (ProductId, SortOrder)
    ) ENGINE = MyISAM; 
INSERT INTO images VALUES 
(1, 1,  'source.com/234/'),
(1, 2,  'source.com/675/'),
(2, 1,  'source.com/7698/'),
(2, 2,  'source.com/678/'),
(2, 3,  'source.com/7456/');
SELECT * FROM images;

ProductId	SortOrder	Source
1	1	source.com/234/
1	2	source.com/675/
2	1	source.com/7698/
2	2	source.com/678/
2	3	source.com/7456/

INSERT INTO images (ProductId, Source) VALUES
(2,'source.com/456546'),
(2,'source.com/237675');
SELECT * FROM images;

ProductId	SortOrder	Source
1	1	source.com/234/
1	2	source.com/675/
2	1	source.com/7698/
2	2	source.com/678/
2	3	source.com/7456/
2	4	source.com/456546
2	5	source.com/237675

fiddle