mysql 分表 插入_mysql实现插入数据到分表

use mysql;

/*创建原始数据表*/

DROP TABLE IF EXISTS `articleinfo`;

CREATE TABLE `articleinfo`(

`id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,

`title` VARCHAR(20) DEFAULT NULL,

`content` VARCHAR(20) DEFAULT NULL,

`comment_time` VARCHAR(20) DEFAULT NULL

) ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=1;

/*创建存储过程来新增部分数据到原始数据表*/

delimiter $$

drop procedure if exists proc_articleinfo;

create procedure proc_articleinfo()

begin

declare i int;

set i=1;

while(i<=32) do

insert into articleinfo(title,content,comment_time)

values (concat(‘数据库基础‘,i),concat(‘努力学习基础知识‘,i%4),DATE_FORMAT(NOW(),‘%Y-%m-%d %H:%i:%S‘));

set i=i+1;

end while;

end;

$$

delimiter ;

/*调用存储过程*/

call proc_articleinfo();

/*创建分表 */

DROP TABLE IF EXISTS `tb_articleinfo_0`;

CREATE TABLE `tb_articleinfo_0`(

`id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,

`title` VARCHAR(20) DEFAULT NULL,

`content` VARCHAR(20) DEFAULT NULL,

`comment_time` VARCHAR(20) DEFAULT NULL

) ENGINE=MyISAM DEFAULT CHARSET=utf8 AUTO_INCREMENT=1;

CREATE TABLE `tb_articleinfo_1` LIKE `tb_articleinfo_0`;

CREATE TABLE `tb_articleinfo_2` LIKE `tb_articleinfo_0`;

CREATE TABLE `tb_articleinfo_3` LIKE `tb_articleinfo_0`;

/*创建主表*/

DROP TABLE IF EXISTS `tb_articleinfo`;

CREATE TABLE `tb_articleinfo`(

`id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,

`title` VARCHAR(20) DEFAULT NULL,

`content` VARCHAR(20) DEFAULT NULL,

`comment_time` VARCHAR(20) DEFAULT NULL,

index(`id`)

) ENGINE=MRG_MYISAM UNION=(`tb_articleinfo_0`,`tb_articleinfo_1`,`tb_articleinfo_2`,`tb_articleinfo_3`) INSERT_METHOD=LAST DEFAULT CHARSET=utf8 AUTO_INCREMENT=1;

/*插入数据到分表*/

insert into tb_articleinfo_0(title,content,comment_time)

select title,content,comment_time from articleinfo where id%4=0;

insert into tb_articleinfo_1(title,content,comment_time)

select title,content,comment_time from articleinfo where id%4=1;

insert into tb_articleinfo_2(title,content,comment_time)

select title,content,comment_time from articleinfo where id%4=2;

insert into tb_articleinfo_3(title,content,comment_time)

select title,content,comment_time from articleinfo where id%4=3;

commit;

/*查询数据*/

select * from articleinfo;

select * from tb_articleinfo_0;

select * from tb_articleinfo_1;

select * from tb_articleinfo_2;

select * from tb_articleinfo_3;

原文：http://www.cnblogs.com/NiceTime/p/6664516.html