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;
