MySQL触发器速度的优化

实际开发发现 @变量是不支持 作为比较的

BEGIN
declare localid int;
set localid=new.id;
if localid>0 then

insert b(a,b,c) values(localid,localid,localid);

end if;
END

其他
mysql> delimiter //
mysql> create trigger mytrigger after update on bill  
for each row
begin
      DECLARE billId,reivewID,shopID,productID int ;
      if(bill.status == 2 && bill.ctobreview_id == NULL && datediff(bill.nowdate,now())>15)
      begin
          set billId = bill.id;
          --select id into billId from bill where status = 2 and ctobreview = null;
          SELECT product_id into productID from billitem where bill_id = billId;
          select shop_id into shopID from product where shop_id = productID;
          insert into ctobReview values(2,null,null,10,10,10,10,shopID);
          -- select @reviewID from inserted;
          select last_insert_id();
          update bill set ctobreview_id = reviewID where id = billId;  
      end
end ;
这次任务加深了对 MySQL trigger & procedure的了解。拷贝代码做一个记录~~~
其中一张表的触发器代码：
create trigger SetPiUnderId BEFORE INSERT on t_play_item  
for each ROW
    begin
         DECLARE uid int;
         declare usedNum int;
         declare retry int;
         declare maxRetry int;
         SELECT max(pi_under_id)+1 into uid from t_play_item;
         IF uid = 0  
         then  
              set uid = 1;
         end if;
         IF uid < 65535 THEN  
            -- UPDATE t_play_item set pi_under_id = pi_id where pi_id = new.pi_id;
            set NEW.pi_under_id = uid;
         else
             begin
    -- 否则开始生成随机数查询
    set maxRetry = 65530;
    set retry = 0;
    REPEAT
        set uid = RAND() * 65530; [Page]
        set usedNum = 0;
        -- 有几条记录使用这个编号
        select count(*) into usedNum from t_play_item where pi_under_id = uid;
        set retry = retry + 1;
    until usedNum = 0 or retry >= maxRetry
end repeat;
    IF  usedNum = 0  
    then
        -- UPDATE t_play_item set pi_under_id = uid where pi_id = new.pi_id;
        set NEW.pi_under_id = uid;
    else
        -- 强制设置成id， 没有找到抛出错误的办法，
        set NEW.pi_under_id = uid;
        -- UPDATE t_play_item set pi_under_id = pi_id where pi_id = new.pi_id;
        -- UPDATE t_play_item set pi_under_id = 65535 where pi_id = new.pi_id;
    end if;
             end;           end if; [Page]
    end
用来测试的存储过程：
CREATE PROCEDURE TestSetPiUnderId(in times int, in showMsg bool)
    MODIFIES SQL DATA
begin
     declare t int;
     set t = 0;
     REPEAT
           set t = t + 1;
           insert into t_play_item(pi_style, pi_rect_no, pi_sent, pi_des) values(t, 1, 0, ’’);
           IF showMsg then  
   select CONCAT(’插入成功第’, CAST(t as char));
           end if;
     until t> times
     end repeat;

mysql触发器问题
我需要把一些数据直接导入dedecms里
但他的文章是分两个表存放的
麻烦帮写个触发器当
insert into dede_archives(typeid,description)values(1,内容数据)
时同步的
insert into dede_addonarticle(id,typeid,body)values(值1,值2,值3)

其中值1也就是id是表dede_archives的id(这个是自增的),值2等于dede_archives中的typeid,值3等于dede_archives中的description

因为我的采集器只支持一条语句,所以只能通过这个方法来实现~
麻烦给出该触发器的语法

这是一篇来自百度知道的问题

一捅姜姗

乞丐

UID 328066
精华 0
积分 -74
帖子 0
威望 -37
ZD币 6 元
阅读权限 0
注册 2008-5-20
状态 离线   沙发 使用商品  发表于 2008-7-25 01:01  资料  个人空间  短消息  加为好友  
mysql触发器问题
[此问题的推荐答案]
楼上的写的都SQL Server支持的语法
MySQL中不是这样写滴
如下：
CREATE TRIGGER trg_Table1
AFTER insert ON `Table1`
FOR EACH ROW
BEGIN
INSERT INTO dede_addonarticle(id,typeid,body)
SELECT id,typeid,description
FROM dede_archives
WHERE id=LAST_INSERT_ID()
END；

在这里最麻烦的是取得自增量最后插入的值，根据MySQL的官方说明，是使用LAST_INSERT_ID()这个函数，LAST_INSERT_ID 是与table无关的，如果向表a插入数据后，再向表b插入数据，LAST_INSERT_ID会改变。

一般情况下获取刚插入的数据的id，使用select max(id) from table 是可以的。

但在多线程情况下，就不行了。在多用户交替插入数据的情况下max(id)显然不能用。

这就该使用LAST_INSERT_ID了，因为LAST_INSERT_ID是基于Connection的，只要每个线程都使用独立的Connection对象，LAST_INSERT_ID函数将返回该Connection对AUTO_INCREMENT列最新的insert or update操作生成的第一个record的ID。这个值不能被其它客户端（Connection）影响，保证了你能够找回自己的 ID 而不用担心其它客户端的活动，而且不需要加锁。

用MySQL命令行测试，结果是正确的，但是使用MySQL Query Browser测试时，LAST_INSERT_ID返回了全部的id值。可能是软件的问题，跟MySQL无关。

另外，如果你有一次有多行记录插入时，LAST_INSERT_ID只返回第1个，
例如：
INSERT INTO dede_archives(typeid,description)
VALUES (222, 'Mary') , (333, 'Jane') , (444, 'Lisa');
一次插入3行记录，假设此时生成的id是4,5,6三个，但是用LAST_INSERT_ID只能返回4

因此，对于批量插入时的处理，你要结合使用ROW_COUNT()来判断插入的记录数
INSERT INTO dede_addonarticle(id,typeid,body)
SELECT id,typeid,description
FROM dede_archives
WHERE id>=LAST_INSERT_ID()
AND id <=LAST_INSERT_ID()+ROW_COUNT()

我没有测试过，你自己测试一下再说，基本上应该没问题

这是一篇来自百度知道的问题

咬不动的狗不理

乞丐

UID 328071
精华 1
积分 -123
帖子 0
威望 -64
ZD币 4 元
阅读权限 0
注册 2008-5-20
状态 离线   板凳 使用商品  发表于 2008-7-25 01:01  资料  个人空间  短消息  加为好友  
mysql触发器问题
s="ianswer"> 其他回答    共 5 条
create trigger tr_table
on dede_archives
for insert
as
insert into dede_archives(id,typeid,body) select id,typeid,description from inserted
回答者：wangjie_02 - 江湖新秀 四级 7-23 19:36

这是一篇来自百度知道的问题

hata_nannan

乞丐

UID 328083
精华 2
积分 -114
帖子 0
威望 -62
ZD币 7 元
阅读权限 0
注册 2008-5-20
状态 离线   地毯 使用商品  发表于 2008-7-25 01:01  资料  个人空间  短消息  加为好友  
mysql触发器问题
a>
CREATE trigger dede_archives_add on dede_archives
after insert
as
declare @typeid int
declare @count
set @typeid = (select bh from inserted)
select @count =count(*) from dede_addonarticle where typeid = @typeid
if @count = 0
insert into dede_addonaritcle(id,typeid,description) select id,typeid,body from inserted
回答者：feng5354 - 江湖新秀 五级 7-23 19:50

这是一篇来自百度知道的问题

cayenne0627

乞丐

UID 328438
精华 0
积分 -128
帖子 0
威望 -64
ZD币 2 元
阅读权限 0
注册 2008-5-21
状态 离线   第5楼 使用商品  发表于 2008-7-25 01:01  资料  个人空间  短消息  加为好友  
mysql触发器问题
a>
declare @typeid int
declare @count
set @typeid = (select bh from inserted)
select @count =count(*) from dede_addonarticle where typeid = @typeid
if @count = 0
insert into dede_addonaritcle(id,typeid,description) select id,typeid,body from inserted
回答者：剑张 - 秀才 二级 7-23 20:52

这是一篇来自百度知道的问题

我爱夏天添

乞丐

UID 328167
精华 0
积分 -104
帖子 0
威望 -52
ZD币 5 元
阅读权限 0
注册 2008-5-20
状态 离线   第6楼 使用商品  发表于 2008-7-25 01:01  资料  个人空间  短消息  加为好友  
mysql触发器问题
a>
create trigger tr_table
on dede_archives
for insert
as
insert into dede_addonarticle(id,typeid,body) select id,typeid,description from inserted

一楼的insert表的时候错了

create trigger tr_table --建立一个触发器，并命名
on dede_archives --哪个表
for insert --执行什么操作的时候
as
insert into dede_addonarticle(id,typeid,body) --插入到哪个表
select id,typeid,description --插入什么数据
from inserted --这个其实是sql的临时表的机制

另外id是自增的就有问题了
dede_addonarticle可以不让这个表自增
不然你逻辑上都说不过去，就别提程序上了
回答者：山顶与山脚 - 经理 五级 7-23 23:53

这是一篇来自百度知道的问题

我喷火烧死你

乞丐

UID 328080
精华 0
积分 -120
帖子 0
威望 -60
ZD币 3 元
阅读权限 0
注册 2008-5-20
状态 离线   第7楼 使用商品  发表于 2008-7-25 01:01  资料  个人空间  短消息  加为好友  
mysql触发器问题
a>
create trigger tr_table
on dede_archives
for insert
as
if not exists(select 1 from dede_addonarticle where id in (select id from inserted ))
begin
insert into dede_addonarticle(id,typeid,body)
select id,typeid,description from inserted
end
本贴来自ZDNetChina中文社区 http://bbs.zdnet.com.cn ,本贴地址：http://bbs.zdnet.com.cn/viewthread.php?tid=547063

[ 本帖最后由 liyihongcug 于 2009-5-15 10:23 编辑 ]

昵称: liyihongcug  时间: 2009-05-15 10:21:00

DELIMITER $$   
DROP TRIGGER `itfStatusTrigerDel`$$   
CREATE  
    /*!50017 DEFINER = 'root'@'localhost' */      TRIGGER `itfStatusTrigerDel` AFTER DELETE ON `itfitem`   
    FOR EACH ROW  BEGIN  
    Declare var_serialNum varchar(255) default '212';   
    Declare var_qty bigint(20) default -1;      Declare var_oid bigint(20) default 13;      Declare stopFlag int;   
    Declare triggerFlag int(4) default 1;      Declare existFlag int(4) default 0;   
    Declare cursor_count int(4) default 0;      DECLARE cur1 CURSOR FOR SELECT qty,serialNumber FROM itfitem where itfOid=old.itfOid;   
    DECLARE CONTINUE HANDLER FOR NOT FOUND set stopFlag=1;   
    OPEN cur1;   
        REPEAT   
        FETCH cur1 INTO var_qty, var_serialNum;   
           if (stopFlag is null) then begin  
            Declare var_count bigint(20) default 0;   
            Declare var_tempIndex int(4) default 0;   
            set existFlag=1;   
            set cursor_count=cursor_count+1;   
            insert into temp_variable(temp_value, variable) values (cursor_count, 'cursor_count del');   
insert into temp_variable(temp_value, variable) values (var_qty, 'var_qty del');   
insert into temp_variable(temp_value, variable) values (var_serialNum, 'var_serialNum del');   
            if var_serialNum!='' then begin  
/* at least have one serialNum */   
set var_count=var_count + 1;   
label:loop   
      set var_tempIndex = locate(',', var_serialNum, var_tempIndex+1);   
      if var_tempIndex!=0 then set var_count=var_count + 1;   
      insert into temp_variable(temp_value, variable) values (var_count, 'inside loop var_count del');   
      else leave label;   
      end if;   
end loop label;   
insert into temp_variable(temp_value, variable) values (var_qty, 'var_qty del');   
insert into temp_variable(temp_value, variable) values (var_count, 'var_count del');   
if var_qty!=var_count then set triggerFlag=0;   
end if;   
            end;              else set triggerFlag=0;   
            end if;              end;end if;   
        /*set var_count=var_count+1;*/   
        UNTIL stopFlag END REPEAT;   
    CLOSE cur1;   
    insert into temp_variable(temp_value, variable) values (triggerFlag, 'triggerFlag del');   
    insert into temp_variable(temp_value, variable) values (existFlag, 'existFlag del');   
    if (existFlag=1 && triggerFlag = 1) then update itf set itfStatus='shipped' where oid=old.itfOid;      end if;      
END$$   
DELIMITER ;