MYSQL触发器的使用浅谈

梦里梦外; 2024-03-22 21:37 82阅读 0赞

![49545c384ee3e2e4b54369f6f44bcc0f.jpeg][]

触发器用于在 MySQL 执行插入、更新或删除语句时，自动触发执行其他SQL代码。本文讲解触发器的正确使用方式

触发器可以在执行语句前或执行后触发其他 SQL 代码运行。触发器可以读取触发语句改变了哪些数据，但是没有返回值。因此可以使用触发器加强业务逻辑的约束而不需要在应用程序写对应的代码。

从上述描述可以看到，触发器可以简化应用程序的逻辑并且可以提升性能，这是因为使用触发器减少了应用程序和服务端的交互次数。同时，触发器有助于完成自动更新归一化和统计数据。例如，我们可以使用触发器自动统计交易订单总金额，订单数及平均客单价。 然而，MySQL 的触发器的应用场合也十分有限，如果你使用过其他数据库产品的触发器，不要以为 MySQL 也能实现相同的功能，例如：

*  每个数据表的单一事件只能有一个触发器，也就是说对于 AFTER INSERT 这样的事件来说，不能同时有超过1个的触发器。
 *  MySQL 只支持行级别的触发器，也就是只能按 FOR EACH ROW 这种方式使用触发而不是整个 SQL 语句，这对于大量数据的操作而言会比较低效。MySQL 的触发器只能按下面的形式编写：

<table> 
 <tbody> 
 <tr> 
 <td> 1 2 3 4 5 </td> 
 <td> <code>CREATE</code> <code>TRIGGER</code> <code>触发器名 BEFORE|</code><code>AFTER</code> <code>触发事件</code> <code>ON</code> <code>表名 </code><code>FOR</code> <code>EACH ROW</code> <code>BEGIN</code> <code>&nbsp;&nbsp;&nbsp;&nbsp;</code><code>执行语句列表;</code> <code>END</code> </td> 
 </tr> 
 </tbody> 
</table>

执行语句列表支持单条或多条语句，下面是一个多条语句的示例：

<table> 
 <tbody> 
 <tr> 
 <td> 1 2 3 4 5 6 7 8 9 10 11 </td> 
 <td> <code>DELIMITER $$</code> <code>CREATE</code> <code>TRIGGER</code> <code>user_create_log </code><code>AFTER</code> <code>INSERT</code> <code>ON</code> <code>t_users </code><code>FOR</code> <code>EACH ROW</code> <code>BEGIN</code> <code>DECLARE</code> <code>log_info </code><code>VARCHAR</code><code>(40)</code><code>character</code> <code>set</code> <code>utf8;</code> <code>DECLARE</code> <code>description </code><code>VARCHAR</code><code>(20) </code><code>character</code> <code>set</code> <code>utf8;#后面发现中文字符编码出现乱码，这里设置字符集</code> <code>SET</code> <code>description = </code><code>" is created"</code><code>;</code> <code>SET</code> <code>log_info = CONCAT(NEW.user_name, description);&nbsp;&nbsp;&nbsp;&nbsp; #函数CONCAT可以将字符串连接</code> <code>INSERT</code> <code>INTO</code> <code>logs(log) </code><code>values</code><code>(log_info);</code> <code>END</code> <code>$$</code> <code>DELIMITER ;</code> </td> 
 </tr> 
 </tbody> 
</table>

*  触发器可能导致服务端实际执行的工作不可预测，一个简单的语句可能导致服务端做大量不可见的工作。例如，如果一个触发器更新了 一个相关的表，可能导致受影响的行数加倍。
 *  触发器难以调试，并且一旦引入了触发器，很难分析性能瓶颈。
 *  触发器会导致潜在的锁等待和死锁。如果触发器失败了，源查询也会失败。如果没有意识到触发器的存在，这类玩呢提很难发现。

大多数限制中，最大的限制是 FOR EACH ROW 的设计，这有时候导致触发器没法用于维护统计和缓存表，这是因为这可能很慢。使用触发器的主要理由是相比定时同步更新，触发器可以一致保持数据的一致性。 触发器也没法保证原子性。例如，更新 MyISAM 数据表的触发器在源 SQL 语句出错后，无法回滚。而且，触发器自身也可能都只错误。如果我们使用了 AFTER UPDATE 基于 MyISAM 数据表去更新另一个表。如果触发器有个导致第二张表操作失败的错误，那对于第一张表的操作不会回滚。

InnoDB 的触发器相关的操作，包括源语句都在同一个事务中，因此是满足原子性的。然而，如果使用InnoDB 的触发器去与另一张表校验数据一致性的时候，这个时候如果不小心的话可能导致不正确的结果。例如，假设需要使用触发器模拟外键，可以使用 BEFORE INSERT触发器验证另一张表是否存在对应的记录，但是如果在触发器读取另一张表数据的时候不使用 SELECT FOR UPDATE的话，则由于并发性性问题可能导致错误的结果。 虽然触发器有些缺陷，但是这并不意味着不能用。相反，触发器本身也是有用的，尤其是对于约束，系统维护任务和保持统计数据保持最新。

也可以使用触发器记录数据行的变化。这样即便是离线手动操作数据库的记录（如修复错误数据）也能够被记录下来。但是，需要注意的是对于往其他自增主键表插入数据时要小心，这对于复制性的语句表现会有问题，因为自增值对于两个相同的副本值并不同。

结语：

触发器在有限的场合能够发挥其优势，比如统计数据、数据表变更日志等。但是也会有一些缺陷，比如大数据量的更新由于逐行触发，会降低效率。还有就是，MyISAM 引擎无法保障原子性。因此，要根据应用场景是否要是有触发器。

以上就是MySQL触发器的使用的详细内容，希望可以帮到你

转自：[微点阅读][Link 1] [https://www.weidianyuedu.com][https_www.weidianyuedu.com]

[49545c384ee3e2e4b54369f6f44bcc0f.jpeg]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/14/629930e3fb674416814eed42721a1ce1.png
[Link 1]: https://m.weidianyuedu.com/
[https_www.weidianyuedu.com]: https://www.weidianyuedu.com