mysql自增长id数据表迁移到其他服务器

背景

         由于业务调整，需要将某个服务机器上的几个表原样迁移到其他服务节点，不能改变这些表的结构，数据和功能特性。其中一个表为自增长id的数据表，无法像其他表那样直接原样导入导出。此时，需要采取一些措施，间接地达到我们的目标。

导出难点

       由于数据表是自增长的索引id, 经过长时间的使用，必然有一系列的插入和删除，其id早已经不是严格连续的，会存在很多相邻id，他们是断裂衔接的，

       比如一张表，先插入了6条数据，mysql为数据表生成的id是1,2,3,4,5,6，然后又 删除了id为2和6的数据行。再插入一条数据，此时，数据表的id序列是这样的：1,3,4,5,7. 这样，id为1的下一条数据id为3，id为5的下一条id为7，他们已经不连续了。

      如果我们在目标机器数据库创建同样的自增长id数据表，然后直接将原表数据导入，势必会改变他们的id序列，生成的自增长id是连续的，这就改变了原表的数据，和我们的需求不符。

方案思路

      先在目标机器数据库创建一个表，除自增长字段外，该表的其它字段和原表完全一致，而自增长字段对应的字段，只把它创建成普通的索引字段。然后导出原表所有字段数据。对于自增长id字段，把它当作一个普通字段，和其他字段一起，导入到目标表中。导完之后，再将目标表的id改为自增长类型的，并为其设置自增长开始位置。这样目标表的字段，数据和功能特性，就和原表完全一样了！

操作过程

        假设我们现在有一个表words, 里面存放了某个业务的重点词汇。我们创建一个简单表操作一下，模拟自增长id索引表的原样导出导入迁移。

0 前置处理

# 创建表
CREATE TABLE `words` (
	`id` INT (11) AUTO_INCREMENT PRIMARY KEY,
	`word` VARCHAR(30) UNIQUE
); 

# 插入几条数据
INSERT INTO `words`(word) VALUES ('a'),('b'),('c'),('d'),('e'),('f');

# 中途删除2条数据
DELETE FROM `words` WHERE id=2;
DELETE FROM `words` WHERE id=6;

# 最后又插入1条
INSERT INTO `words` (word) VALUES ('h');

此时，查询出的数据是这样的

id	word
1	a
3	c
4	d
5	e
7	h

可以看到有些相邻id之间是断裂的。

 

1 创建目标表

创建一个结构和原表一致的数据表，但id是普通索引

CREATE TABLE `words_cp` (
	`id` INT (11) PRIMARY KEY, # 索引id没有设置为自增长
	`word` VARCHAR(30) UNIQUE
); 

2 导入数据到目标表

将原表数据（包括id）导入,完成后，查询目标表words_cp数据

SELECT id,word FROM words_cp;

      

id	word
1	a
3	c
4	d
5	e
7	h

数据和原表一致，但我们的目标还没有达成，因为这个表还不是自增长id。

3 修改索引为自增长

将目标表words_cp改成自增长id，并设置自增长起始位置。

# 将索引id改成自增长类型
ALTER TABLE words_cp MODIFY COLUMN id INT AUTO_INCREMENT;

# 设置id自增长起始位置
ALTER TABLE `words_cp` AUTO_INCREMENT=10;

 

4 验证结果

# 插入1条数据进行验证
INSERT INTO words_cp (word) VALUES ('i');
SELECT id,word FROM words_cp;

id	word
1	a
3	c
4	d
5	e
7	h
10	i

 

显然，目标表words_cp已经是一个自增长索引id的表了,并且可以任意设置增长起始位置。

至此，大功告成！