MySQL中触发器的基础学习教程_MySQL-mysql チュートリアル-php.cn

ホームページ

データベース

mysql チュートリアル

MySQL中触发器的基础学习教程_MySQL

WBOYWBOYWBOYWBOYWBOYWBOYWBOYWBOYWBOYWBOYWBOYWBOYWB

May 27, 2016 pm 01:46 PM

mysqlチュートリアル引き金

0.触发器的基本概念
触发器是一种特殊的存储过程，它在插入，删除或修改特定表中的数据时触发执行，它比数据库本身标准的功能有更精细和更复杂的数据控制能力。

数据库触发器有以下的作用：

(1).安全性。可以基于数据库的值使用户具有操作数据库的某种权利。

# 可以基于时间限制用户的操作，例如不允许下班后和节假日修改数据库数据。

# 可以基于数据库中的数据限制用户的操作，例如不允许股票的价格的升幅一次超过10%。

(2).审计。可以跟踪用户对数据库的操作。

# 审计用户操作数据库的语句。

# 把用户对数据库的更新写入审计表。

(3).实现复杂的数据完整性规则

# 实现非标准的数据完整性检查和约束。触发器可产生比规则更为复杂的限制。与规则不同，触发器可以引用列或数据库对象。例如，触发器可回退任何企图吃进超过自己保证金的期货。

# 提供可变的缺省值。

(4).实现复杂的非标准的数据库相关完整性规则。触发器可以对数据库中相关的表进行连环更新。例如，在auths表author_code列上的删除触发器可导致相应删除在其它表中的与之匹配的行。

# 在修改或删除时级联修改或删除其它表中的与之匹配的行。

# 在修改或删除时把其它表中的与之匹配的行设成NULL值。

# 在修改或删除时把其它表中的与之匹配的行级联设成缺省值。

# 触发器能够拒绝或回退那些破坏相关完整性的变化，取消试图进行数据更新的事务。当插入一个与其主健不匹配的外部键时，这种触发器会起作用。例如，可以在books.author_code 列上生成一个插入触发器，如果新值与auths.author_code列中的某值不匹配时，插入被回退。

(5).同步实时地复制表中的数据。

(6).自动计算数据值，如果数据的值达到了一定的要求，则进行特定的处理。例如，如果公司的帐号上的资金低于5万元则立即给财务人员发送警告数据。

1. 创建触发器语法

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 }

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权限：

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

权限收回：

revoke create trigger on `database_naem`.`table_name` from `user_name`@`ip_address`;
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)
1.3 DEFINER子句
在激活触发器时，检查访问权限，确保触发器安全使用。
1.4 trigger_time
定义触发器触发时间。可以设置为在行记录更改之前或之后发生。
1.5 trigger_event
定义触发器触发事件。触发的事件有：
1.5.1
INSERT：当一个新行插入到表中时触发。如INSERT、LOAD DATA和REPLACE语句。
UPDATE：当一个行数据被更改时触发。如UPDATE语句。
DELETE：当一个行从表中删除时触发。如DELETE和REPLACE语句。注意：DROP TABLE和TRUNCATE TABLE语句不会触发该触发器，因为它们不是使用DELETE。同样删除一个分区表也不会触发。
有一个潜在的混乱情况，如INSERT INTO ... ON DUPLICATE KEY UPDATE ... 取决于是否有重复键行。
不能对一个表创建具有相同的触发事件和触发时间的多个触发器。如对于一个表不能创建两个BEFORE UPDATE触发器，但是，可以创建一个BEFORE UPDATE和一个BEFORE INSERT或一个BEFORE UPDATE和一个AFTER UPDATE触发器。
1.6 FOR EACH ROW子句
定义触发执行间隔。FOR EACH ROW子句定义触发器每隔一行执行一次动作，而不是对整个表执行一次。
1.7 trigger_body子句
包含要触发执行的SQL语句。可以是任何合法的语句，包括复合语句(需要使用BEGIN ... END结构)，流控制语句(if、case、while、loop、for、repeat、leave、iterate)，变量声明(declare)以及指派(set)，异常处理声明，允许条件声明，但是这里的语句受的限制和函数的一样。
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可以同时使用。
2. 实例
2.1 创建表
使用在《mysqludf_json将关系数据以JSON编码》一文中创建的表。后续会将用户表迁移到nosql数据库上的。

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`)
  -> );

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`)
  -> );

创建另外一张表来存放触发器动作数据。

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

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

2.2 创建触发器

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;
  -> //

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 测试

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\ - "http://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\ - "http://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)

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\ - "http://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\ - "http://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 列出触发器

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表

sql> 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)

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)

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 删除触发器

mysql> drop trigger trigger_name;
mysql> drop trigger trigger_name;

声明

この記事の内容はネチズンが自主的に寄稿したものであり、著作権は原著者に帰属します。このサイトは、それに相当する法的責任を負いません。盗作または侵害の疑いのあるコンテンツを見つけた場合は、admin@php.cn までご連絡ください。

MySQLのライセンスは、他のデータベースシステムと比較してどうですか？Apr 25, 2025 am 12:26 AM

MySQLはGPLライセンスを使用します。 1）GPLライセンスにより、MySQLの無料使用、変更、分布が可能になりますが、変更された分布はGPLに準拠する必要があります。 2）商業ライセンスは、公的な変更を回避でき、機密性を必要とする商用アプリケーションに適しています。

MyisamよりもInnodbを選びますか？Apr 25, 2025 am 12:22 AM

Myisamの代わりにInnoDBを選択する場合の状況には、次のものが含まれます。1）トランザクションサポート、2）高い並行性環境、3）高いデータの一貫性。逆に、Myisamを選択する際の状況には、1）主に操作を読む、2）トランザクションサポートは必要ありません。 INNODBは、eコマースプラットフォームなどの高いデータの一貫性とトランザクション処理を必要とするアプリケーションに適していますが、Myisamはブログシステムなどの読み取り集約型およびトランザクションのないアプリケーションに適しています。

MySQLの外国キーの目的を説明してください。Apr 25, 2025 am 12:17 AM

MySQLでは、外部キーの機能は、テーブル間の関係を確立し、データの一貫性と整合性を確保することです。外部キーは、参照整合性チェックとカスケード操作を通じてデータの有効性を維持します。パフォーマンスの最適化に注意し、それらを使用するときに一般的なエラーを避けてください。

MySQLのインデックスのさまざまなタイプは何ですか？Apr 25, 2025 am 12:12 AM

MySQLには、B-Treeインデックス、ハッシュインデックス、フルテキストインデックス、空間インデックスの4つのメインインデックスタイプがあります。 1.B-Treeインデックスは、範囲クエリ、ソート、グループ化に適しており、従業員テーブルの名前列の作成に適しています。 2。HASHインデックスは、同等のクエリに適しており、メモリストレージエンジンのHASH_TABLEテーブルのID列の作成に適しています。 3。フルテキストインデックスは、記事テーブルのコンテンツ列の作成に適したテキスト検索に使用されます。 4.空間インデックスは、地理空間クエリに使用され、場所テーブルのGEOM列での作成に適しています。

MySQLでインデックスをどのように作成しますか？Apr 25, 2025 am 12:06 AM

tocreateanindexinmysql、usethecreateindexstatement.1）forasinglecolumn、 "createdexidx_lastnameonemployees（lastname）;" 2）foracompositeindexを使用して、 "createindexidx_nameonemployees（lastname、firstname）;" 3）; "3）、" 3）を使用します

MySQLはSQLiteとどのように違いますか？Apr 24, 2025 am 12:12 AM

MySQLとSQLiteの主な違いは、設計コンセプトと使用法のシナリオです。1。MySQLは、大規模なアプリケーションとエンタープライズレベルのソリューションに適しており、高性能と高い並行性をサポートしています。 2。SQLiteは、モバイルアプリケーションとデスクトップソフトウェアに適しており、軽量で埋め込みやすいです。

MySQLのインデックスとは何ですか？また、パフォーマンスをどのように改善しますか？Apr 24, 2025 am 12:09 AM

MySQLのインデックスは、データの取得をスピードアップするために使用されるデータベーステーブル内の1つ以上の列の順序付けられた構造です。 1）インデックスは、スキャンされたデータの量を減らすことにより、クエリ速度を改善します。 2）B-Tree Indexは、バランスの取れたツリー構造を使用します。これは、範囲クエリとソートに適しています。 3）CreateIndexステートメントを使用して、createIndexidx_customer_idonorders（customer_id）などのインデックスを作成します。 4）Composite Indexesは、createIndexIDX_CUSTOMER_ORDERONORDERS（Customer_Id、Order_date）などのマルチコラムクエリを最適化できます。 5）説明を使用してクエリ計画を分析し、回避します

データの一貫性を確保するために、MySQLでトランザクションを使用する方法を説明します。Apr 24, 2025 am 12:09 AM

MySQLでトランザクションを使用すると、データの一貫性が保証されます。 1）StartTransactionを介してトランザクションを開始し、SQL操作を実行して、コミットまたはロールバックで送信します。 2）SavePointを使用してSave Pointを設定して、部分的なロールバックを許可します。 3）パフォーマンスの最適化の提案には、トランザクション時間の短縮、大規模なクエリの回避、分離レベルの使用が合理的に含まれます。

See all articles