mysql触发器_MySQL触发器

mysql触发器

Now that even my favourite $50/year web hosts (example) are offering MySQL 5 and even recommending it over 4, why not take advantage of MySQL's "enterprise" features. Like triggers. OK, what's a trigger? It's something that happens as a result of something else. Clear? Yeah, maybe an example.

现在，即使我最喜欢的$ 50 / year网络主机(例如)都提供MySQL 5，甚至推荐它超过4，为什么不利用MySQL的“企业”功能。 像触发器。 好的，这是什么触发条件？ 这是由于其他原因而发生的事情。 明确？ 是的，也许是一个例子。

Say, as it often happens in life, you insert a row into a table. If there is a trigger that "listens" to inserts on this table, it will be, well... triggered.

假设您在生活中经常发生这种情况，请在表格中插入一行。 如果有一个触发器“监听”到此表上，它将被触发。

Quick example. You have a blog app and a `blog` database that has a table called `posts`. You want to show some stats, like the total number of posts. Instead of using some (potentially heavy) queries that include some COUNT()-ing and SUM()-ing, you can "cache" the stats for faster display. So you create a table called `stats`. Let's see.

快速示例。 您有一个博客应用程序和一个“博客”数据库，其中包含一个名为“ posts”的表。 您想显示一些统计信息，例如帖子总数。 可以不使用某些包含一些COUNT()和SUM()的查询(可能很繁琐)，而可以“缓存”统计信息以便更快地显示。 因此，您创建了一个名为“ stats”的表。 让我们来看看。

C:\\Development\\Apache\\MySQL\\bin>mysql -u root -p
Enter password: ****
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 240 to server version: 5.0.27-community-nt

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> \u blog
Database changed
mysql> SHOW TABLES;
+----------------+
| Tables_in_blog |
+----------------+
| posts          |
| stats          |
+----------------+
2 rows in set (0.00 sec)

mysql> SHOW COLUMNS FROM posts;
+-------+------------------+------+-----+---------+----------------+
| Field | Type             | Null | Key | Default | Extra          |
+-------+------------------+------+-----+---------+----------------+
| id    | int(10) unsigned | NO   | PRI | NULL    | auto_increment |
| title | varchar(255)     | NO   |     |         |                |
| body  | text             | NO   |     |         |                |
+-------+------------------+------+-----+---------+----------------+
3 rows in set (0.00 sec)

mysql> SHOW COLUMNS FROM stats;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| key   | varchar(50) | NO   | PRI |         |       |
| value | int(11)     | NO   |     |         |       |
+-------+-------------+------+-----+---------+-------+
2 rows in set (0.00 sec)

mysql>

So far, so good. Initializing the stats...

到现在为止还挺好。 初始化统计信息...

mysql> INSERT INTO stats VALUES ('posts', 0);
Query OK, 1 row affected (0.02 sec)

Let's create the trigger!

让我们创建触发器！

mysql> CREATE TRIGGER mytrigger
    -> AFTER INSERT ON posts
    -> FOR EACH ROW
    -> UPDATE stats SET `value` = `value` + 1 WHERE `key` = 'posts';
Query OK, 0 rows affected (0.01 sec)

What does it mean? Well, after each row INSERT-ed in `posts`, it will update the `stats` table, incrementing the total number of posts. Does it work? But of course!

这是什么意思？ 好吧，在“ posts”中插入每一行后，它将更新“ stats”表，从而增加总帖子数。 它行得通吗？ 但是当然！

mysql> INSERT INTO posts VALUES ('', 'title1', 'body1');
Query OK, 1 row affected, 1 warning (0.04 sec)

mysql> INSERT INTO posts VALUES ('', 'title2', 'body2');
Query OK, 1 row affected, 1 warning (0.02 sec)

mysql> SELECT * FROM stats;
+-------+-------+
| key   | value |
+-------+-------+
| posts |     2 |
+-------+-------+
1 row in set (0.00 sec)

mysql> ha-haaaa!

To display the triggers you've created, use the SHOW TRIGGERS statement, as described in the MySQL manual. More on the triggers - using triggers, CREATE TRIGGER syntax

要显示您创建的触发器，请按照MySQL手册中的说明使用SHOW TRIGGERS语句。 有关触发器的更多信息-使用触发器， CREATE TRIGGER语法

Tell your friends about this post on Facebook and Twitter

在Facebook和Twitter上告诉您的朋友有关此帖子的信息

翻译自: https://www.phpied.com/mysql-triggers/

mysql触发器