mysql 触发器的使用（慎用）

傷城~ 2022-07-14 00:29 167阅读 0赞

【前言】最近有一个需求，店铺积分统计，每生成一条积分消费（赠送）日志记录，对应的店铺增加对应的剩余总积分和消费总积分，想了想觉得 mysql 触发器不错，然后尝试写了一个。注意，触发器基于诸多原因，请慎用或不用。

至于相关原因，请自行百度 触发器的弊端。

## 1. 表结构准备 ##

<table> 
 <tbody> 
  <tr> 
   <td>表1 日志表</td> 
   <td>字段1 ID</td> 
   <td>字段2 使用类型（1增加2消费）</td> 
   <td>字段3 积分数</td> 
   <td>字段4 店铺ID</td> 
   <td>字段5 时间戳</td> 
  </tr> 
  <tr> 
   <td>score_log</td> 
   <td>id</td> 
   <td>score_type</td> 
   <td>score_num</td> 
   <td>shop_id</td> 
   <td>create_at</td> 
  </tr> 
 </tbody> 
</table>

<table> 
 <tbody> 
  <tr> 
   <td>表2 店铺积分总表<span style="white-space:pre"> </span></td> 
   <td>字段1 ID</td> 
   <td>字段2 店铺ID</td> 
   <td>字段3 店铺剩余可用积分</td> 
   <td>字段4 店铺已消费总积分</td> 
  </tr> 
  <tr> 
   <td>shop_score</td> 
   <td>id</td> 
   <td>shop_id</td> 
   <td>left_score</td> 
   <td>use_score</td> 
  </tr> 
 </tbody> 
</table>

## 2. 需求说明 ##

的每生成一条 score\_log 日志记录

1）如果 shop\_score 中存在shop\_id 为新score\_log 日志中的shop\_id  那接下来更新

如果不存在 则先插入一条 shop\_id  为新score\_log 日志中的shop\_id 其他值为0 的初始记录 然后再更新

2） 更新规则

1> score\_log 中 使用为消费类型 score\_type = 2

shop\_score 中 left\_score = left\_score - score\_log.score\_num

use\_score = use\_score +  score\_log.score\_num

2> score\_log 中 使用为赠送类型 score\_type = 1

shop\_score 中 left\_score = left\_score + score\_log.score\_num

## 3. 知识 储备 ##

1）触发器的使用

# 增加触发器
    DELIMITER $$
    
    CREATE
        TRIGGER `数据库名`.`触发器名` BEFORE | AFTER     INSERT/UPDATE/DELETE
        ON `dba`.`score_log`
        FOR EACH ROW BEGIN
    
       # 这里加入逻辑代码
        
        END$$
    
    DELIMITER ;

#删除触发器
    
    DROP TRIGGER `触发器名`;

【注意】mysql 触发器对应每张表 仅能添加一个触发器  且 INSERT 中仅能使用 NEW 关键字 UPDATE 可以使用 NEW|OLD  DELETE 可以使用 OLD 代替 表名 获取相应的数据

## 4. 实现上述需求 ##

DELIMITER $$
    
    CREATE
        TRIGGER `dba`.`triggerA` AFTER INSERT
        ON `dba`.`score_log`
        FOR EACH ROW BEGIN
    
        IF NOT EXISTS(SELECT id FROM shop_score WHERE shop_score.shop_id = new.shop_id) THEN
        INSERT INTO shop_score(shop_id) VALUES(NEW.shop_id);
        END IF;     
    
        IF new.score_type = 1 THEN
        UPDATE shop_score SET left_score = left_score + new.score_num WHERE shop_score.shop_id  = new.shop_id;
        END IF; 
    
        IF new.score_type = 2 THEN
        UPDATE shop_score SET left_score = left_score - new.score_num WHERE shop_score.shop_id  = new.shop_id;
        UPDATE shop_score SET use_score = use_score + new.score_num WHERE shop_score.shop_id  = new.shop_id;
        END IF;
        
        END$$
    
    DELIMITER ;
    # 删除触发器
    DROP TRIGGER `triggerA`;

## 5.  更多 ##

请参看[官方文档 ][Link 1]

[Link 1]: http://doc.mysql.cn/mysql5/refman-5.1-zh.html-chapter/triggers.html