How to generate a reference key between rows I would like to group rows under a reference number.

My current approach is this:

CREATE TABLE `txn` (
    `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
    `reference` BIGINT UNSIGNED NOT NULL,
    PRIMARY KEY (`id`)
) ENGINE = InnoDB;

SET @ref := (SELECT FLOOR(RAND() * 9999999999) AS ref FROM txn WHERE "ref" NOT IN (SELECT reference FROM txn) LIMIT 1);

-- They belong together 
INSERT INTO txn (reference) VALUES (@ref), (@ref);


This seems to work fine. The only concern is that I have to dump CSV files with around 50 to 100 thousand entries and I do worry about the performance. On every new reference it needs to SELECT to check if the reference already exists.

I would really appreciate other approches or hints how to improve the query.

EDIT: If this can be solved with a sequence, this would be also quite nice.
As I had some time to think about it, I came up with this solution. I would appriciate a comment on this.

SET @ref = LAST_INSERT_ID() + 1;

INSERT INTO txn (reference) VALUES (@ref), (@ref);


I'm a bit worried about concurrent connections, which could result in identical reference numbers for unrelated transactions.

EDIT:

The upper solution will NOT work, since LAST_INSERT_ID() returns the last inserted ID of the current connection, which in this case will always be 0.

So another idea would be this:

-- This is the parent transaction
INSERT INTO txn (reference) VALUES (0);
-- Get ID of parent
SET @ref = LAST_INSERT_ID();
-- Insert related/child transactions
INSERT INTO txn (reference) VALUES (@ref), (@ref);
-- Update parent with its own ID
UPDATE txn SET reference = @ref WHERE id = @ref;