mysql使用存储过程进行简单数据迁移

1. 单库主键范围分表-数据分片

新建存储过程

delimiter #

create procedure burstData(in i INT, in tableName varchar(100)) #tableName是要导入的表名

begin

  DECLARE n INT DEFAULT 0;

  while n < 1000 do # 循环1000次，根据实际情况定，这里一个分表的数据是1kw

      SET @sqlcmd = CONCAT('INSERT INTO ', tableName, ' SELECT * FROM sourceTableName a WHERE a.id > (', i * 10000 ,') AND a.id <= ((', i + 1, ') * 10000)');    #每次导1w条，防止长时间锁行影响数据update

      PREPARE stmt FROM @sqlcmd; 

  EXECUTE stmt;

      set i = i + 1;

      set n = n + 1;

  DEALLOCATE PREPARE stmt;

  end while;

end #

执行存储过程

delimiter ;

call burstData(0, 'tableName_0');

call burstData(1000, 'tableName_1');

call burstData(2000, 'tableName_2');

call burstData(3000, 'tableName_3');

call burstData(4000, 'tableName_4');

...

删除存储过程

drop procedure if exists burstData;

2. 单库分表建表

新建存储过程

delimiter #

create procedure createXXXTable()

begin

  DECLARE n INT DEFAULT 0;

  while n < 300 do

      SET @sqlcmd = CONCAT('CREATE TABLE `tableName_', n,'` (`id` int(4) unsigned NOT NULL AUTO_INCREMENT,`userid` int(4) unsigned NOT NULL DEFAULT 0,`disuserid` int(4) unsigned NOT NULL DEFAULT 0,`addtime` int(4) unsigned NOT NULL DEFAULT 0,PRIMARY KEY (`id`),KEY `userid` (`userid`),KEY `disuserid` (`disuserid`) USING BTREE) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8');

      PREPARE stmt FROM @sqlcmd;

  EXECUTE stmt;

      set n = n + 1;

      DEALLOCATE PREPARE stmt;

  end while;

end #

执行存储过程

delimiter ;

call createXXXTable();

...

删除存储过程

drop procedure if exists createXXXTable;

3. 跨主机备份数据库或表

mysqldump -h192.168.1.199 -uroot -p123456 --opt --single-transaction db1Name tableName | mysql -hlocalhost -uroot -proot -C db2Name tableName// need (at least one of) the SUPER privilege(s)， --single-transaction使用事务不锁表，mysqldump默认锁表

4. 单库取模分表-数据分片

新建存储过程

delimiter #

create procedure burstData()

begin

  DECLARE n INT DEFAULT 0;

  while n < 300 do

      SET @sqlcmd = CONCAT('INSERT INTO tableName_', n, ' SELECT * FROM tableName a WHERE a.userid % 300 = ', n);

      PREPARE stmt FROM @sqlcmd; 

      EXECUTE stmt;

      set n = n + 1;

      DEALLOCATE PREPARE stmt;

  end while;

end #

执行存储过程

delimiter ;

call burstData();

...

删除存储过程

drop procedure if exists burstData;