begin
declare `@i` int(11);
declare `@sitecount` int(11);
declare `@sqlstr` varchar(2560);
declare `@sqlinsert` varchar(2560); //以上声明变量
select count(0) into `@sitecount` from tbl_base_site; //计算表tbl_base_site的记录总条数
set `@i`=1;
while (`@i`-1)*300<`@sitecount` do //while循环执行
set @sqlstr = concat('create table tbl_base_syslog',`@i`,'(syslog_id int(11) auto_increment primary key,
create_user varchar(32),
description text,
create_time datetime,
site_id int(11),
ip varchar(64),
version_id smallint(2),
module_identity varchar(64),
right_name varchar(64)
)');
prepare stmt from @sqlstr;
execute stmt;
//以上实现动态创建表
set @sqlinsert = concat('insert into tbl_base_syslog',`@i`,'(
syslog_id,
create_user,
description,
create_time,
site_id,
ip,
version_id,
module_identity,
right_name)
select syslog_id,
create_user,
description,
create_time,
site_id,
ip,
version_id,
module_identity,
right_name
from tbl_base_syslog
where site_id in (select tbs.site_id from (select site_id from tbl_base_site limit ',`@i`*300,',300) as tbs)
order by syslog_id');
prepare stmt from @sqlinsert;
execute stmt;
//以上实现从一张表查询记录插入到动态创建的新表中
set `@i`= `@i`+1;
end while;
end
如对本文有疑问,
点击进行留言回复!!
相关文章:
-
-
-
-
-
分布式事务
分布式事务文章目录分布式事务一、分布式场景下的数据一致性问题形成原因剖析1. 分布式事务背景背景知识什么情况下会...
[阅读全文]
-
-
-
-
-
-
网友评论