MySQL触发器的应用

14/08/2013 17:43:05MySQL226,54110字数 7316阅读24分23秒阅读模式

MySQL触发器是在5.0版本引入的。针对需要对数据库做级联更改是很适宜使用的，如需要实时监控某张表中的某个字段的更改而需要做出相应的处理。触发器虽然功能强大，能轻松可靠的实现许多复杂的功能，《使用MySQL UDFs来调用gearman分布式任务分发系统》、《sphinx应用(三)—sphinx分布式, XML数据源, Gearman, UpdateAttributes》以及后续会将用户表导入到NOSQL数据库中，都使用到触发器的。不过，要慎用触发器，滥用触发器会造成数据库以及应用程序维护困难。

1. 创建触发器语法文章源自运维生存时间-https://www.ttlsa.com/mysql/application-of-mysql-triggers/

CREATE
	[DEFINER = { user | CURRENT_USER }]
	TRIGGER trigger_name
	trigger_time trigger_event
	ON tbl_name FOR EACH ROW
	trigger_body

trigger_time: { BEFORE | AFTER }
trigger_event: { INSERT | UPDATE | DELETE }

语法相关部分说明：
1.1 授权与回收
创建触发器需要有CREATE TRIGGER权限：文章源自运维生存时间-https://www.ttlsa.com/mysql/application-of-mysql-triggers/

grant create trigger on `database_naem`.`table_name` to `user_name`@`ip_address`;

权限收回：文章源自运维生存时间-https://www.ttlsa.com/mysql/application-of-mysql-triggers/

revoke create trigger on `database_naem`.`table_name` from `user_name`@`ip_address`;

1.2 trigger_name
必须给触发器命令，最多64个字符，建议用表的名字_触发器类型的缩写方法命名。如ttlsa_posts_bi(表ttlsa_posts，触发器发生在insert之前before)文章源自运维生存时间-https://www.ttlsa.com/mysql/application-of-mysql-triggers/

1.3 DEFINER子句
在激活触发器时，检查访问权限，确保触发器安全使用。文章源自运维生存时间-https://www.ttlsa.com/mysql/application-of-mysql-triggers/

1.4 trigger_time
定义触发器触发时间。可以设置为在行记录更改之前或之后发生。文章源自运维生存时间-https://www.ttlsa.com/mysql/application-of-mysql-triggers/

1.5 trigger_event
定义触发器触发事件。触发的事件有：
1.5.1
INSERT：当一个新行插入到表中时触发。如INSERT、LOAD DATA和REPLACE语句。
UPDATE：当一个行数据被更改时触发。如UPDATE语句。
DELETE：当一个行从表中删除时触发。如DELETE和REPLACE语句。注意：DROP TABLE和TRUNCATE TABLE语句不会触发该触发器，因为它们不是使用DELETE。同样删除一个分区表也不会触发。文章源自运维生存时间-https://www.ttlsa.com/mysql/application-of-mysql-triggers/

有一个潜在的混乱情况，如INSERT INTO ... ON DUPLICATE KEY UPDATE ... 取决于是否有重复键行。文章源自运维生存时间-https://www.ttlsa.com/mysql/application-of-mysql-triggers/

不能对一个表创建具有相同的触发事件和触发时间的多个触发器。如对于一个表不能创建两个BEFORE UPDATE触发器，但是，可以创建一个BEFORE UPDATE和一个BEFORE INSERT或一个BEFORE UPDATE和一个AFTER UPDATE触发器。文章源自运维生存时间-https://www.ttlsa.com/mysql/application-of-mysql-triggers/

1.6 FOR EACH ROW子句
定义触发执行间隔。FOR EACH ROW子句定义触发器每隔一行执行一次动作，而不是对整个表执行一次。文章源自运维生存时间-https://www.ttlsa.com/mysql/application-of-mysql-triggers/

1.7 trigger_body子句
包含要触发执行的SQL语句。可以是任何合法的语句，包括复合语句(需要使用BEGIN ... END结构)，流控制语句(if、case、while、loop、for、repeat、leave、iterate)，变量声明(declare)以及指派(set)，异常处理声明，允许条件声明，但是这里的语句受的限制和函数的一样。文章源自运维生存时间-https://www.ttlsa.com/mysql/application-of-mysql-triggers/

1.7.1 OLD与NEW
在触发器的SQL语句中，可以关联表中的任何列，通过使用OLD和NEW列名来标识，如OLD.col_name、NEW.col_name。OLD.col_name关联现有的行的一列在被更新或删除前的值。NEW.col_name关联一个新行的插入或更新现有的行的一列的值。
对于INSERT语句，只有NEW是合法的。否则会报错：ERROR 1363 (HY000): There is no OLD row in on INSERT trigger
对于DELETE语句，只有OLD是合法的。否则会报错：ERROR 1363 (HY000): There is no NEW row in on DELETE trigger
对于UPDATE语句，NEW和OLD可以同时使用。文章源自运维生存时间-https://www.ttlsa.com/mysql/application-of-mysql-triggers/

2. 实例
2.1 创建表
使用在《mysqludf_json将关系数据以JSON编码》一文中创建的表。后续会将用户表迁移到nosql数据库上的。文章源自运维生存时间-https://www.ttlsa.com/mysql/application-of-mysql-triggers/

mysql> create table `ttlsa_users` (
    -> `uid` int(11) unsigned,
    -> `username` varchar(40) NOT NULL,
    -> `password` varchar(40) NOT NULL,
    -> `createtime` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    -> PRIMARY KEY (`uid`)
    -> );

创建另外一张表来存放触发器动作数据。文章源自运维生存时间-https://www.ttlsa.com/mysql/application-of-mysql-triggers/

mysql> create table `ttlsa_users3` (
    -> `uid` int(11) unsigned,
    -> `userinfo` varchar(200),
    -> );

2.2 创建触发器文章源自运维生存时间-https://www.ttlsa.com/mysql/application-of-mysql-triggers/

mysql> delimiter //
mysql> create trigger ttlsa_users_ai
    -> after insert on ttlsa_users
    -> for each row
    -> insert into ttlsa_users3 (uid, userinfo) values(uid, json_object(NEW.uid, NEW.username, NEW.password));
    -> //

mysql> create trigger ttlsa_users_au
    -> after update on ttlsa_users
    -> for each row
    -> update ttlsa_users3 set userinfo=json_object(NEW.uid, NEW.username, NEW.password) where uid=OLD.uid;
    -> //

2.3 测试文章源自运维生存时间-https://www.ttlsa.com/mysql/application-of-mysql-triggers/

mysql> insert into ttlsa_users values (890,'xuhh',md5('abc'),NULL,'test trigger')//
Query OK, 1 row affected (0.01 sec)

mysql> select * from ttlsa_users//
+-----+-------------+----------------------------------+---------------------+------------------------------------+
| uid | username    | password                         | createtime          | json_data                          |
+-----+-------------+----------------------------------+---------------------+------------------------------------+
| 888 | ttlsa_admin | 6a6e41c9b741f740cfa5f266b249d452 | 2013-08-10 11:27:01 | \website\ - "https://www.ttlsa.com" |
| 889 | ttlsa_admin | 6a6e41c9b741f740cfa5f266b249d452 | 2013-08-10 14:08:44 | xuhh                               |
| 890 | xuhh        | 900150983cd24fb0d6963f7d28e17f72 | 2013-08-14 16:40:49 | test trigger                       |
+-----+-------------+----------------------------------+---------------------+------------------------------------+
3 rows in set (0.00 sec)

mysql> select * from ttlsa_users3//
+-----------------------------------------------------------------------------+------+
| userinfo                                                                    | uid  |
+-----------------------------------------------------------------------------+------+
| {"uid":890,"username":"xuhh","password":"900150983cd24fb0d6963f7d28e17f72"} |  890 |
+-----------------------------------------------------------------------------+------+
2 rows in set (0.00 sec)

mysql> update ttlsa_users set password='test_update' where uid=890//
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> select * from ttlsa_users//
+-----+-------------+----------------------------------+---------------------+------------------------------------+
| uid | username    | password                         | createtime          | json_data                          |
+-----+-------------+----------------------------------+---------------------+------------------------------------+
| 888 | ttlsa_admin | 6a6e41c9b741f740cfa5f266b249d452 | 2013-08-10 11:27:01 | \website\ - "https://www.ttlsa.com" |
| 889 | ttlsa_admin | 6a6e41c9b741f740cfa5f266b249d452 | 2013-08-10 14:08:44 | xuhh                               |
| 890 | xuhh        | test_update                      | 2013-08-14 16:41:33 | test trigger                       |
+-----+-------------+----------------------------------+---------------------+------------------------------------+
3 rows in set (0.00 sec)

mysql> select * from ttlsa_users3//
+-----------------------------------------------------------------------------+------+
| userinfo                                                                    | uid  |
+-----------------------------------------------------------------------------+------+
| {"uid":890,"username":"xuhh","password":"test_update"}                      |  890 |
+-----------------------------------------------------------------------------+------+
2 rows in set (0.00 sec)

3. 管理
3.1 列出触发器文章源自运维生存时间-https://www.ttlsa.com/mysql/application-of-mysql-triggers/

mysql> SHOW TRIGGERS  like '%ttlsa%';  触发器名称匹配%ttlsa%
*************************** 1. row ***************************
             Trigger: ttlsa_users_ai
               Event: INSERT
               Table: ttlsa_users
           Statement: insert into ttlsa_users3 (uid,userinfo) values(NEW.uid,json_object(NEW.uid, NEW.username, NEW.password))
              Timing: AFTER
             Created: NULL
            sql_mode: NO_ENGINE_SUBSTITUTION
             Definer: root@127.0.0.1
character_set_client: utf8
collation_connection: utf8_general_ci
  Database Collation: latin1_swedish_ci
*************************** 2. row ***************************
             Trigger: ttlsa_users_au
               Event: UPDATE
               Table: ttlsa_users
           Statement: update ttlsa_users3 set userinfo=json_object(NEW.uid, NEW.username, NEW.password) where uid=OLD.uid
              Timing: AFTER
             Created: NULL
            sql_mode: NO_ENGINE_SUBSTITUTION
             Definer: root@127.0.0.1
character_set_client: utf8
collation_connection: utf8_general_ci
  Database Collation: latin1_swedish_ci
2 rows in set (0.00 sec)
mysql> SHOW TRIGGERS; 列出所有
mysql> SHOW TRIGGERS  from database_name;  列出数据库的触发器
mysql> SHOW CREATE TRIGGER trigger_name;    查看创建触发器
*************************** 1. row ***************************
               Trigger: ttlsa_users_ai
              sql_mode: NO_ENGINE_SUBSTITUTION
SQL Original Statement: CREATE DEFINER=`root`@`127.0.0.1` trigger ttlsa_users_ai after insert on ttlsa_users for each row insert into ttlsa_users3 (uid,userinfo) values(NEW.uid,json_object(NEW.uid, NEW.username, NEW.password))
  character_set_client: utf8
  collation_connection: utf8_general_ci
    Database Collation: latin1_swedish_ci
1 row in set (0.01 sec)

3.2 INFORMATION_SCHEMA.TRIGGERS表文章源自运维生存时间-https://www.ttlsa.com/mysql/application-of-mysql-triggers/

mysql> SELECT * FROM INFORMATION_SCHEMA.TRIGGERS  WHERE TRIGGER_SCHEMA='test' AND TRIGGER_NAME='ttlsa_users_au'\G
*************************** 1. row ***************************
           TRIGGER_CATALOG: def
            TRIGGER_SCHEMA: test
              TRIGGER_NAME: ttlsa_users_au
        EVENT_MANIPULATION: UPDATE
      EVENT_OBJECT_CATALOG: def
       EVENT_OBJECT_SCHEMA: test
        EVENT_OBJECT_TABLE: ttlsa_users
              ACTION_ORDER: 0
          ACTION_CONDITION: NULL
          ACTION_STATEMENT: update ttlsa_users3 set userinfo=json_object(NEW.uid, NEW.username, NEW.password) where uid=OLD.uid
        ACTION_ORIENTATION: ROW
             ACTION_TIMING: AFTER
ACTION_REFERENCE_OLD_TABLE: NULL
ACTION_REFERENCE_NEW_TABLE: NULL
  ACTION_REFERENCE_OLD_ROW: OLD
  ACTION_REFERENCE_NEW_ROW: NEW
                   CREATED: NULL
                  SQL_MODE: NO_ENGINE_SUBSTITUTION
                   DEFINER: root@127.0.0.1
      CHARACTER_SET_CLIENT: utf8
      COLLATION_CONNECTION: utf8_general_ci
        DATABASE_COLLATION: latin1_swedish_ci
1 row in set (0.00 sec)

3.3 删除触发器文章源自运维生存时间-https://www.ttlsa.com/mysql/application-of-mysql-triggers/

mysql> drop trigger trigger_name;

如需转载请注明出处：MySQL触发器的应用 https://www.ttlsa.com/html/2335.html文章源自运维生存时间-https://www.ttlsa.com/mysql/application-of-mysql-triggers/

我的微信

微信公众号

扫一扫关注运维生存时间公众号，获取最新技术文章~

MySQL触发器的应用

Inception 对OSC的支持（13）

Inception 对线上配置需求（11）

DBA蹩脚编程能力-0007-通过拓扑批量创建主从(视频)

如何添加和调整mysql innodb log文件