Mysql – Transfer rows into a new table, forcing to generate unique primary keys till its insertable

auto-incrementinnodbinsertMySQL

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.

Best Answer

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

SUGGESTION

If the table has barely 1000 rows, you could manually compress it and force to have MAX(id) each time. Suppose the table looks something like this:

USE mydb
CREATE TABLE x
(
    id INT NOT NULL AUTO_INCREMENT,
    ...
    PRIMARY KEY (id)
);

Try doing OPTIMIZE TABLE manually as follows:

InnoDB or MyISAM tables with no non-unique keys

USE mydb
CREATE TABLE x_new LIKE x;
INSERT INTO x_new SELECT * FROM x ORDER BY id;
ALTER TABLE x RENAME x_old;
ALTER TABLE x_new RENAME x;
DROP TABLE x_old;

MyISAM tables with non-unique keys

USE mydb
CREATE TABLE x_new LIKE x;
ALTER TABLE x_new DISABLE KEYS;
INSERT INTO x_new SELECT * FROM x ORDER BY id;
ALTER TABLE x_new ENABLE KEYS;
ALTER TABLE x RENAME x_old;
ALTER TABLE x_new RENAME x;
DROP TABLE x_old;

This should preserve all id's and assign AUTO_INCREMENT appropriately.

It should work just fine with MySQL Replication.

CAVEAT

If the ids are different for any reason between Master and Slave, blame MySQL (I mean blame Oracle) : http://dev.mysql.com/doc/refman/5.5/en/replication-features-auto-increment.html

If the aforementioned suggestion does not rectify this, there is only one thing left to do and it is guaranteed to work. What is it?

Run this on the Master Only:

mysqldump -uroot -p... --triggers mydb x > mydb_x.sql
mysql -uroot -p... -Dmydb < mydb_x.sql

With a table of 1000 rows, this should

go pretty quickly
replicate and produce an exact copy on the slave
restore auto_increment behavior to Master and Slave

Give it a Try !!!

UPDATE 2013-02-11 19:30 EDT

Let's assume that x and xhistory have identical layouts. Let's also add some columns:

CREATE TABLE x
(
    xid int(10) unsigned NOT NULL AUTO_INCREMENT,
    col1 ... ,
    col2 ... ,
    col3 ... ,
    PRIMARY KEY (xid)
) ENGINE=MyISAM AUTO_INCREMENT=124 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

CREATE TABLE xhistory
(
    xid int(10) unsigned NOT NULL DEFAULT '0',
    col1 ... ,
    col2 ... ,
    col3 ... ,
    PRIMARY KEY (xid)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

Perhaps you could just use the REPLACE command. It mechanically operates as either an INSERT or UPDATE via DELETE and INSERT.

REPLACE INTO xhistory SELECT * FROM x;
TRUNCATE TABLE x;

Previously existing rows get deleted from xhistory and then inserted from x into xhistory. New rows are simply inserted.

Best Answer

Related Solutions

Mysql – Getting duplicate primary key for auto increment column

MySQL auto increment problem with deleting rows / archive table

SUGGESTION

InnoDB or MyISAM tables with no non-unique keys

MyISAM tables with non-unique keys

CAVEAT

UPDATE 2013-02-11 19:30 EDT

Related Question