Mysql – Reinsert data in new table with new IDs

bulkinsertmyisammysql-5.5

I have the following two tables:

buynsell

CREATE TABLE `buynsell` (
    `id` VARCHAR(20) NULL DEFAULT NULL,
    `msg` VARCHAR(255) NULL DEFAULT NULL,
    `date` VARCHAR(25) NULL DEFAULT NULL,
    `fid` BIGINT(20) NOT NULL AUTO_INCREMENT,
    PRIMARY KEY (`fid`),
    INDEX `fid_index` (`fid`)
)
COLLATE='utf8_general_ci'
ENGINE=MyISAM
AUTO_INCREMENT=6523;

bsmsgs

CREATE TABLE `bsmsgs` (
    `id` VARCHAR(20) NULL DEFAULT NULL,
    `msg` MEDIUMTEXT NULL,
    `date` VARCHAR(25) NULL DEFAULT NULL,
    `subfid` BIGINT(20) NULL DEFAULT NULL,
    `fid` BIGINT(20) NOT NULL AUTO_INCREMENT,
    PRIMARY KEY (`fid`),
    INDEX `fid_subfid_index` (`fid`, `subfid`)
)
COLLATE='utf8_general_ci'
ENGINE=MyISAM
AUTO_INCREMENT=11408;

The field bsmsgs.subfid is referenced to bunsell.fid. I was fed up using these tables, and hence created the following two new ones(Engine is still MYISAM):

buynsell

CREATE TABLE `buynsell` (
    `id` INT(10) NOT NULL AUTO_INCREMENT,
    `type` ENUM('B','S','D','H','T') NOT NULL DEFAULT 'B',
    `msg` VARCHAR(250) NOT NULL,
    `nick` VARCHAR(32) NOT NULL,
    `dated` DATETIME NOT NULL,
    PRIMARY KEY (`id`),
    UNIQUE INDEX `msg_nick` (`msg`, `nick`),
    INDEX `type` (`type`),
    FULLTEXT INDEX `msg` (`msg`)
)
COMMENT='The table storing information about users selling/buying/hiring things etc.'
COLLATE='utf8_general_ci'
ENGINE=MyISAM;

replies

CREATE TABLE `replies` (
    `id` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
    `bns_id` INT(10) UNSIGNED NOT NULL,
    `msg` VARCHAR(250) NOT NULL,
    `nick` VARCHAR(32) NOT NULL,
    `dated` DATETIME NOT NULL,
    PRIMARY KEY (`id`),
    UNIQUE INDEX `bns_id_msg` (`bns_id`, `msg`),
    INDEX `bns_id` (`bns_id`),
    FULLTEXT INDEX `msg` (`msg`)
)
COMMENT='Replies to buynsell messages from interested users.'
COLLATE='utf8_general_ci'
ENGINE=MyISAM;

Now, my problem is even though the AUTO_INCREMENT values are so high, there are actually less than 2000 entries in buynsell table and around 8000 entries in bsmsgs table.

What I can do is use the same entries from previous tables in my new ones but I want an elegant design and hence, would like to insert from scratch.

I can reinsert data from older buynsell by skipping to AUTO_INCREMENT key, fid and generate new rows with ids starting from 1 to 2000.

What I am facing trouble with is referencing the subfid field in bsmsgs to new id values in buynsell while correctly referencing and storing them into bns_id of replies table.

NOTE

If it can not be achieved by purely MySQL, I can use LuaSQL for this task.

Best Answer

I guess you can do the following:
1. In your new table buynsell, add a column , old_id
2. Reinsert data from the old table as you said, skipping auto_increment field, and storing old_buynsell.id as old_id in your new table.
3. Import data to new replies table
4. Update bns_id column in replies :

UPDATE replies a
INNER JOIN (select id, old_id FROM buynsell) b ON (b.old_id = a.bns_id)
SET a.bns_id=b.id

5 Drop old_id column, you don't need it anymore.

Related Question