程序師世界是廣大編程愛好者互助、分享、學習的平台,程序師世界有你更精彩!
首頁
編程語言
C語言|JAVA編程
Python編程
網頁編程
ASP編程|PHP編程
JSP編程
數據庫知識
MYSQL數據庫|SqlServer數據庫
Oracle數據庫|DB2數據庫
 程式師世界 >> 數據庫知識 >> MYSQL數據庫 >> MySQL綜合教程 >> mysql實現插入數據到分表,mysql實現插入數據

mysql實現插入數據到分表,mysql實現插入數據

編輯:MySQL綜合教程

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;

  1. 上一頁:
  2. 下一頁:
Copyright © 程式師世界 All Rights Reserved