0

Lets say we have the following scenario...

+-----------------+-------------+---------------+
| Table           | PK          | Additional    |
+-----------------+-------------+---------------+
| playerinventory | id (int)    | amount, level |
+-----------------+-------------+---------------+
| newinventory    | id (BIGINT) | amount, level |
+-----------------+-------------+---------------+

We are in the middle of a architectural change of our game-database.

"amount, level" should get extracted from "playerinventory" and get inserted into "newinventory".

Limitations

  • "newinventory" has no auto-increment pk, therefore we need to generate a unique long for each... we also can not add "auto-increment" to the table
  • "newinventory" could possible already contain those identities, so we need to check if the generated id already exists and if so repeat the process for the affected set of extracted columns till it was inserted successfully.
  • We CANT simply insert each row from "playerinventory" into "newinventory" with its current id... that's simply not possible (if I would explain why this post would be much, much longer), that's why we NEED to generate a new unique ID

In nearly any programming language this process would look like this

for row in playerInventory
   id = generate new ID
   while(id in newInventory) id = generate new ID
   insert into newInventory( id, row.amount, row.level )
      

How can we achieve this in MySQL/SQL ?

The highest ID in "newinventory" is already 9223372036854775806 and therefore we cant increase anymore... we need to generate and test till it fits.

1
  • Are you saying that newinventory is already populated, and you won't want to re-do that? How many rows in the long run? (I ask because RAND() repeats after 1 billion.)
    – Rick James
    Aug 9, 2020 at 22:50

2 Answers 2

1

use a BEFORE INSERTTRIGGGER to generate UNIQUE numbers.

With SET @NEW_ID = 1; you define the first new value

Schema (MySQL v8.0)

CREATE table playerinventory ( id int PRIMARY KEY ,    amount INT , level int);
CREATE table newinventory ( id bigint PRIMARY KEY ,    amount INT , level int);

INSERT INTO playerinventory VALUEs (5,1,1),(6,2,2),(7,3,3),(8,4,4);

DELIMITER $$
CREATE TRIGGER before_newinventory_insert
BEFORE INSERT
ON newinventory FOR EACH ROW
BEGIN
    
    SELECT MAX(id) + 1
    INTO @NEW_ID
    FROM newinventory;
    IF @NEW_ID IS NULL THEN
        SET @NEW_ID = 1;
    END IF;
    set NEW.id = @NEw_ID;
END $$

DELIMITER ;

INSERT INTO newinventory SELECT * FROM playerinventory;

Query #1

SELECT * FROM newinventory;

| id  | amount | level |
| --- | ------ | ----- |
| 1   | 1      | 1     |
| 2   | 2      | 2     |
| 3   | 3      | 3     |
| 4   | 4      | 4     |

View on DB Fiddle

0
1

Assuming no one else is inserting into the table during this process, you can get a batch of available id:s like:

SELECT id+1
FROM newinventory x
WHERE NOT EXISTS (
    SELECT 1 
    FROM newinventory y
    WHERE y.id = x.id + 1
)
ORDER BY id+1
LIMIT 1000; -- batch size

Edit: The above will only get you next available id after each existing. If you have large gaps, you can use a recursive CTE to find all available id below a certain id:

with recursive T (n) as ( select 0 union all select n+1 from T where n+1 < 1000 )
select n from T
where not exists (
    select 1 from newinventory 
    where id = n
)
order by n
limit 100;

When you run out of available id:s, repeat the above. In the latter case you can initialize your base case with last found id:

with recursive T (n) as ( 
    select 1+@last_found 
    union all 
    select n+1 from T 
    where n+1 < 1000 + @last_found
)
select n from T
where not exists (
    select 1 from newinventory 
    where id = n
)
order by n
limit 100;

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.