56,679
社区成员
发帖
与我相关
我的任务
分享
mysql> call MsgContent_pro//
ERROR 1206 (HY000): The total number of locks exceeds the lock table size
mysql>
mysql> delimiter //
mysql>
mysql> create procedure MsgContent_pro ()
-> begin
-> repeat
-> update tbContent
-> set msgID=( select mi from (select msgContent,max(msgID) as ma,min(msgID) as mi
-> from tbMsgContent
-> group by msgContent
-> having count(msgContent)>=2
-> ) c where ma=tbContent.msgID)
-> where msgID in (select ma from (select msgContent,max(msgID) as ma,min(msgID) as mi
-> from tbMsgContent
-> group by msgContent
-> having count(msgContent)>=2
-> ) c )
-> order by tbContent.msgID
-> limit 100;
-> until row_count()=0 end repeat;
-> delete a from tbMsgContent a inner join tbMsgContent b
-> on a.msgContent=b.msgContent and a.msgID>b.msgID;
-> end;
->
-> //
Query OK, 0 rows affected (0.01 sec)
mysql>
mysql> delimiter ;
mysql> call MsgContent_pro();
Query OK, 0 rows affected (0.06 sec)
mysql>
SQL codeselect msgContent,min(msgID)
from tbMsgContent
group by msgContent
having count(*)>1
select msgContent,min(msgID)
from tbMsgContent
group by msgContent
having count(*)>1
select msgContent,min(msgID)
from tbMsgContent
group by msgContent
having count(*)>1
select * from tbMsgContent t
where exists (select 1 from tbMsgContent where msgContent=t.msgContent)
order by msgContent,msgID;
222603 rows in set (2 min 5.20 sec)
20 rows in set (6 min 8.23 sec)
select * from tbMsgContent t
where exists (select 1 from tbMsgContent where msgContent=a.msgContent)
order by msgContent,msgID;
create table tbMsgContent
(
msgID bigint not null,
msgContent tinytext not null,
primary key (msgID)
);
create table tbContent
(
reID bigint not null,
msgTypeID integer not null,
isRequest boolean not null,
msgID bigint not null,
textID bigint,
fileID bigint
);
delimiter //
create procedure MsgContent_pro ()
begin
repeat
update tbContent
set msgID=( select mi from (select msgContent,max(msgID) as ma,min(msgID) as mi
from tbMsgContent
group by msgContent
having count(msgContent)>=2
) c where ma=tbContent.msgID)
where msgID in (select ma from (select msgContent,max(msgID) as ma,min(msgID) as mi
from tbMsgContent
group by msgContent
having count(msgContent)>=2
) c )
order by tbContent.msgID
limit 100;
until row_count()=0 end repeat;
delete a from tbMsgContent a inner join tbMsgContent b
on a.msgContent=b.msgContent and a.msgID>b.msgID;
end;
//
delimiter ;