MySQL事件场景（以及时髦的子查询）

Coming back to those MySQL events with a sort of more practical example. In case you've missed the previous post, events in brief: turns out that in MySQL starting from 5.1.6. you can setup and schedule cronjobs in the DB server itself. These are called events. An intro article and MySQL manual entry.

回到这些MySQL事件，并提供一些更实际的示例。 如果您错过了上一篇文章，请简要介绍事件：在MySQL中，从5.1.6开始。 您可以在数据库服务器本身中设置和计划cronjobs。 这些称为事件。 介绍性文章和MySQL手册条目。

What these can be used for? OK, so you have a blog, which gets dugg and slashdotted often and is in the top 10 Google results for "Paris Hilton", in other words you get tons of visitors. You want it to load fast and you want visitor stats as real-time as possible, so that you can display "This posts was viewed 12345678 times". It would be best if you can get all data about a post with a simple super fast SELECT ... WHERE id=123.

这些可以用来做什么？ 是的，所以您有一个博客，该博客经常被打乱和斜线，在“巴黎希尔顿”的Google搜索结果中排名前十，换句话说，您吸引了大量的访问者。 您希望它快速加载，并且希望访问者的统计数据尽可能实时，以便您可以显示“此帖子已被查看12345678次”。 最好使用简单的超快速SELECT ... WHERE id = 123获取有关帖子的所有数据。

To keep track of the stats you have a table `post_stats` with two fields - post ID and timestamp of the visit. This way later you can run all sorts of reports by date, grouped my hour, week, etc. For performance reasons every visit to a post results in one quick SELECT from the `posts` table and one INSERT into the stats, that's it. Doing a JOIN and COUNT()-ing the huuuge `post_stats` table when selecting from `posts` is unthinkable for your busy blog. So you add a field to the `posts` table called `hits` that has the count, this way a simple select is enough. OK, but how do you update the `hits`. Doing an UPDATE posts SET hits = hits + 1 is not a good idea, because you lock the table to do the update and doing it for every hit will put the other visitors on hold.

为了跟踪统计信息，您有一个表post_stats，其中包含两个字段-帖子ID和访问的时间戳。 这样以后您就可以按日期运行各种报告，将我的小时，周等分组。出于性能原因，每次访问帖子都会从“帖子”表中进行一次快速的选择，然后在统计数据中进行一次插入。 当您从忙碌的博客中进行选择时，对huuuge的post_stats表进行JOIN和COUNT()操作是不可想象的。 因此，您可以将一个具有计数的字段添加到名为“ hits”的“ posts”表中，这样简单的选择就足够了。 好的，但是您如何更新`hits`。 进行UPDATE posts SET hits = hits + 1不是一个好主意，因为您锁定表以进行更新，并且每次点击都会使其他访问者处于等待状态。

The solution would be to have a cron job that counts `post_stats` and updates the `hits` value in `posts`. And now you can do this within MySQL, no need to setup a Unix cronjob and to write a PHP script for this simple task. Let's see.

解决方案是拥有一个计入post_stats并更新post中的hits值的cron作业。 现在，您可以在MySQL中执行此操作，无需设置Unix cronjob并为该简单任务编写PHP脚本。 让我们来看看。

Connecting to our `blog` DB.

连接到我们的`blog`数据库。

C:\\Development\\Apache\\MySQL\\bin>mysql -u root
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.1.14-beta-community-nt MySQL Community Server (GPL)

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

mysql> \\u blog
Database changed

Hmm, what do we have here?

嗯，我们这里有什么？

mysql> SHOW TABLES;
+----------------+
| Tables_in_blog |
+----------------+
| post_stats     |
| posts          |
+----------------+
2 rows in set (0.00 sec)

Posts? What's in there?

帖子？ 里面有什么？

mysql> SELECT * FROM posts;
+----+---------+--------+------+
| id | title   | body   | hits |
+----+---------+--------+------+
|  1 | title 1 | body 1 |    0 |
|  2 | title 2 | body 2 |    0 |
|  3 | title 3 | body 3 |    0 |
|  4 | title 4 | body 4 |    0 |
|  5 | title 5 | body 5 |    0 |
|  6 | title 6 | body 6 |    0 |
+----+---------+--------+------+
6 rows in set (0.04 sec)

The field `hits` was just added and it has all zeros.

字段“ hits”刚刚添加，并且全为零。

What's in `post_stats`?

post_stats中有什么？

mysql> SHOW FIELDS FROM post_stats;
+---------+----------+------+-----+---------+-------+
| Field   | Type     | Null | Key | Default | Extra |
+---------+----------+------+-----+---------+-------+
| post_id | int(11)  | NO   | MUL |         |       |
| ts      | datetime | NO   | MUL |         |       |
+---------+----------+------+-----+---------+-------+
2 rows in set (0.00 sec)

Good, let's pretend we have visitors that cause INSERTs into the stats table.

好，让我们假装有导致INSERT进入stats表的访问者。

mysql> INSERT INTO post_stats VALUES
    ->  (1,NOW()), (1,NOW()), (1,NOW());
Query OK, 3 rows affected (0.03 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> INSERT INTO post_stats VALUES
    ->  (2,NOW()), (2,NOW()), (2,NOW())
    -> , (2,NOW()), (2,NOW()), (2,NOW());
Query OK, 6 rows affected (0.02 sec)
Records: 6  Duplicates: 0  Warnings: 0

mysql> INSERT INTO post_stats VALUES
    ->  (3,NOW()), (3,NOW());
Query OK, 2 rows affected (0.03 sec)
Records: 2  Duplicates: 0  Warnings: 0

How many hits per post?

每个帖子有多少点击？

mysql> SELECT post_id, COUNT(*) FROM post_stats
    -> GROUP BY post_id;
+---------+----------+
| post_id | COUNT(*) |
+---------+----------+
|       1 |        3 |
|       2 |        6 |
|       3 |        2 |
+---------+----------+
3 rows in set (0.01 sec)

Now let's write up an UPDATE that updates the hits in `posts`. Hold your breath, this is fancy stuff - a subquery. An UPDATE that uses an aggregate SELECT that COUNTs. This way we update all posts in one shot.

现在，让我们写一个UPDATE来更新post中的匹配。 屏住呼吸，这是花哨的东西-子查询。 使用COUNT个汇总SELECT的UPDATE。 这样一来，我们就可以更新所有帖子。

mysql> UPDATE LOW_PRIORITY posts
    -> SET hits = (
    ->  SELECT COUNT(*)
    ->  FROM post_stats
    ->  WHERE posts.id = post_stats.post_id
    -> );
Query OK, 6 rows affected (0.02 sec)
Rows matched: 6  Changed: 6  Warnings: 0

LOW_PRIORITY because this an unobtrusive statement that is as polite as it is powerful and waits for everybody to finish with their SELECTs.

LOW_PRIORITY，因为这是一条礼貌而又强大的语句，它等待每个人完成自己的SELECT语句。

Does it work?

它行得通吗？

mysql> SELECT * FROM posts;
+----+---------+--------+------+
| id | title   | body   | hits |
+----+---------+--------+------+
|  1 | title 1 | body 1 |    3 |
|  2 | title 2 | body 2 |    6 |
|  3 | title 3 | body 3 |    2 |
|  4 | title 4 | body 4 |    0 |
|  5 | title 5 | body 5 |    0 |
|  6 | title 6 | body 6 |    0 |
+----+---------+--------+------+
6 rows in set (0.00 sec)

Kool! Let's reset the hits.

酷！ 让我们重置点击。

mysql> UPDATE posts SET hits = 0;
Query OK, 6 rows affected (0.03 sec)
Rows matched: 6  Changed: 6  Warnings: 0

Now let's create the event already. It's scheduled to start right away, to run once an hour (is that close enough to real-time? No? We can alter it after that) and to DO that funky update query every time it fires.

现在让我们创建事件。 它计划立即开始，每小时运行一次(是否足够接近实时？否？我们可以在此之后对其进行更改)，并在每次触发时执行该时髦的更新查询。

mysql> CREATE EVENT stats_update
    ->     ON SCHEDULE EVERY 1 HOUR
    ->     STARTS CURRENT_TIMESTAMP
    ->     DO
    ->       UPDATE LOW_PRIORITY posts
    ->       SET hits = (
    ->         SELECT COUNT(*)
    ->         FROM post_stats
    ->         WHERE posts.id = post_stats.post_id
    ->       );
Query OK, 0 rows affected (0.00 sec)

Let's see what we did.

让我们看看我们做了什么。

mysql> SHOW EVENTS\\G;
*************************** 1. row *********
            Db: blog
          Name: stats_update
       Definer: root@localhost
          Type: RECURRING
    Execute at: NULL
Interval value: 1
Interval field: HOUR
        Starts: 2006-12-14 20:29:17
          Ends: NULL
        Status: ENABLED
1 row in set (0.00 sec)

Now let's ALTER the event a little, to ease the testing.

现在，让我们ALTER事件一点点，以减轻测试。

mysql> ALTER EVENT stats_update
    ->   ON SCHEDULE every 1 MINUTE;
Query OK, 0 rows affected (0.00 sec)

If event_scheduler is not already ON by configuration, let's turn it ON.

如果event_scheduler尚未通过配置打开，我们将其打开。

mysql> SET GLOBAL event_scheduler = "ON";
Query OK, 0 rows affected (0.00 sec)

Initial state:

初始状态：

mysql> SELECT * FROM posts;
+----+---------+--------+------+
| id | title   | body   | hits |
+----+---------+--------+------+
|  1 | title 1 | body 1 |    0 |
|  2 | title 2 | body 2 |    0 |
|  3 | title 3 | body 3 |    0 |
|  4 | title 4 | body 4 |    0 |
|  5 | title 5 | body 5 |    0 |
|  6 | title 6 | body 6 |    0 |
+----+---------+--------+------+
6 rows in set (0.00 sec)

After a minute:

一分钟后：

mysql> SELECT * FROM posts;
+----+---------+--------+------+
| id | title   | body   | hits |
+----+---------+--------+------+
|  1 | title 1 | body 1 |    3 |
|  2 | title 2 | body 2 |    6 |
|  3 | title 3 | body 3 |    2 |
|  4 | title 4 | body 4 |    0 |
|  5 | title 5 | body 5 |    0 |
|  6 | title 6 | body 6 |    0 |
+----+---------+--------+------+
6 rows in set (0.00 sec)

Let's insert some more stats data.

让我们再插入一些统计数据。

mysql> INSERT INTO post_stats VALUES
    ->  (4,NOW()), (4,NOW()), (4,NOW());
Query OK, 3 rows affected (0.05 sec)
Records: 3  Duplicates: 0  Warnings: 0

... and check after a while.

...，过一会儿再检查。

mysql> SELECT * FROM posts;
+----+---------+--------+------+
| id | title   | body   | hits |
+----+---------+--------+------+
|  1 | title 1 | body 1 |    3 |
|  2 | title 2 | body 2 |    6 |
|  3 | title 3 | body 3 |    2 |
|  4 | title 4 | body 4 |    3 |
|  5 | title 5 | body 5 |    0 |
|  6 | title 6 | body 6 |    0 |
+----+---------+--------+------+
6 rows in set (0.00 sec)

mysql> wicked! awesome!
    -> ;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that
corresponds to your MySQL server version for the right syntax to use near 'wicke
d! awesome!' at line 1
mysql> w00t! roxor
    -> ;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that
corresponds to your MySQL server version for the right syntax to use near 'w00t!
 roxor' at line 1
mysql>

In case you've missed the previous "I [heart] MySQL" posts:

如果您错过了以前的“ I [heart] MySQL”帖子：

• Events

  大事记

• Views

  观看次数

• Triggers

  扳机

Tell your friends about this post on Facebook and Twitter

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

翻译自: https://www.phpied.com/mysql-events-scenario-and-a-funky-subquery/