怎样在 Mysql 8.0 中使用计划事件

介绍

MySQL 调度事件是调度程序线程在特定时间执行的基于时间的 SQL 语句,通常是重复的。 这些任务就像 Linux 中的 cron 作业。

在实际场景中,您将使用 MySQL 调度程序来自动化业务逻辑。 例如,在设计银行应用程序时,您可以使用调度程序将每日储蓄利息发布到数据库表中,或者安排一项作业以向贷款违约者收取展期费用。

本指南解释了如何使用 MySQL 计划事件创建示例数据库和实现按分钟计费的应用程序。

先决条件

要完成本教程,请确保您拥有:

一个 Ubuntu 20.04 服务器。 一个非 root 用户 sudo 特权。 一个 MySQL 服务器。

1. 创建示例数据库

以 root 身份登录 MySQL。

$ sudo mysql - u root -p

MySQL 使用线程在后台运行预定事件。 此行为由名为的全局变量控制 event_scheduler

要检查该功能是否已启用,请运行以下命令。

mysql> SELECT @@event_scheduler;

特点是 ON 要么 OFF.

+-------------------+
| @@event_scheduler |
+-------------------+
| ON                |
+-------------------+
1 row in set (0.00 sec)

如果功能被打开 OFF,通过执行以下命令启用它。

mysql> SET GLOBAL event_scheduler = 1;

调度器运行后,创建一个 sample_db 数据库。

mysql> CREATE DATABASE sample_db;

选择你的新 sample_db 数据库。

mysql> USE sample_db;

定义一个 products 桌子。

此表包含有关您假设的公司提供的不同计划的信息。 每个计划都有一个独特的 product_id 和一个可区分的 product_name 寻找存储在一个特定价格 billing_per_minute 柱子。 使用 DECIMAL 此列的数据类型以利用其精度,因为您正在处理需要精度的非常低的浮点值。

创建 products 表通过运行以下命令。

mysql> CREATE TABLE products (
         product_id BIGINT NOT NULL AUTO_INCREMENT PRIMARY KEY,
         product_name VARCHAR(50),
         billing_per_minute  DECIMAL(17, 6)
       ) ENGINE = InnoDB;

将一些项目插入 products 桌子。

出于演示目的,输入两个产品。 本指南假设您提供教程即服务,并且您按分钟向客户收费。 客户可以订阅您的 PYTHON TUTORIAL$0.003472 每分钟($5 每天)或 C# TUTORIAL 那是为了 $0.006944 每分钟($10 每天)。

mysql> INSERT INTO products (product_name, billing_per_minute) VALUES('PYTHON TUTORIAL', '0.003472');
       INSERT INTO products (product_name, billing_per_minute) VALUES('C# TUTORIAL', '0.006944');

创建一个 customers 桌子。

此表包含有关您的客户的信息。 每个客户都有一个唯一的主键,由 customer_id 柱子。 此外,使用 first_namelast_name 领域。

mysql> CREATE TABLE customers (
         customer_id BIGINT NOT NULL AUTO_INCREMENT PRIMARY KEY,
         first_name VARCHAR(50),
         last_name  VARCHAR(50)
       ) ENGINE = InnoDB;

将一些客户端添加到 customers 桌子。

mysql> INSERT INTO customers (first_name, last_name) VALUES('JOHN', 'DOE');
       INSERT INTO customers (first_name, last_name) VALUES('SMITH', 'JANE');
       INSERT INTO customers (first_name, last_name) VALUES('MARY', 'MIKE');

定义一个 subscriptions 桌子。

这张表提供了一个多对多的关系 customersproducts 表,因为一个客户可以订阅许多不同的产品,而一个产品可以有多个订阅者。

您将使用 subscription_id 在这种情况下,这是主键。 接下来,您将插入 customer_id's 和订阅的 product_id's 取决于每个客户订阅的内容。 最后,由于客户可能随时取消订阅,请添加 is_active 您可以随时使用标志激活或停用的字段 YN.

mysql> CREATE TABLE subscriptions (
         subscription_id BIGINT NOT NULL AUTO_INCREMENT PRIMARY KEY,
         customer_id BIGINT NOT NULL,
         product_id BIGINT NOT NULL,
         is_active CHAR(1)
       ) ENGINE = InnoDB;

输入一些数据到 subscriptions 桌子。

mysql> INSERT INTO subscriptions (customer_id, product_id, is_active) VALUES(1, 1, 'Y');
       INSERT INTO subscriptions (customer_id, product_id, is_active) VALUES(2, 2, 'Y');
       INSERT INTO subscriptions (customer_id, product_id, is_active) VALUES(3, 1, 'Y');

通过执行下面的 SQL 语句来确认客户的订阅,该语句连接三个表以检索他们订阅的客户名称和关联的产品名称。

mysql> SELECT
       customers.customer_id,
       customers.first_name,
       customers.last_name,
       products.product_name,
       products.billing_per_minute
       FROM subscriptions
       LEFT JOIN customers
       ON subscriptions.customer_id = customers.customer_id
       LEFT JOIN products
       ON subscriptions.product_id = products.product_id
       ORDER BY customers.customer_id;

正如您可以从下面的输出中确认的那样, JOHN DOEMARY MIKE 已订阅 PYTHON TUTORIAL 尽管 SMITH JANE 选择了 C# tutorial. 在所有情况下,您都在使用 MySQL 的强大功能 JOIN 语句来检索每分钟每个订阅的成本。

+-------------+------------+-----------+-----------------+--------------------+
| customer_id | first_name | last_name | product_name    | billing_per_minute |
+-------------+------------+-----------+-----------------+--------------------+
|           1 | JOHN       | DOE       | PYTHON TUTORIAL |           0.003472 |
|           2 | SMITH      | JANE      | C# TUTORIAL     |           0.006944 |
|           3 | MARY       | MIKE      | PYTHON TUTORIAL |           0.003472 |
+-------------+------------+-----------+-----------------+--------------------+
3 rows in set (0.00 sec)

定义一个 billings 桌子。

您将使用 MySQL 计划事件的强大功能来记录每个客户端的计费分钟数,具体取决于他们在此表中的订阅。

mysql> CREATE TABLE billings (
         billing_id BIGINT NOT NULL AUTO_INCREMENT PRIMARY KEY,
         subscription_id BIGINT NOT NULL,
         time  DATETIME,
         amount DECIMAL(17, 6)
       ) ENGINE = InnoDB;

您的数据库和所有必需的表现已就绪。 接下来,您将安排一个事件以在每分钟后执行您的计费逻辑。

2. 了解 MySQL 事件语法

MySQL 带有用于创建计划事件的优雅语法,如下所示。

mysql> CREATE EVENT IF NOT EXISTS SAMPLE_EVENT_NAME
       ON SCHEDULE
           SAMPLE_SCHEDULE
       DO
           SAMPLE_EVENT_BODY
       ;

在为您的计费应用程序创建计划事件之前,请熟悉如下所述的语法。

样本事件名称:. 这是您的事件的名称,它在您的数据库中必须是唯一的。 例如, billing_event. 请参阅下面的示例。

CREATE EVENT IF NOT EXISTS billing_event
...

SAMPLE_SCHEDULE:. 这是事件触发的确切时间。 使用 AT 排队的关键字 一度 事件,如下图。

ON SCHEDULE
    AT SAMPLE_TIME_STAMP + INTERVAL SAMPLE_INTERVAL

例如,要根据 MySQL 服务器的时区在当前时间戳立即执行事件,请使用以下语法。

...
ON SCHEDULE
    AT CURRENT_TIMESTAMP
...

创建一个 再次发生的 本指南的每分钟计费软件中的事件,请使用 EVERY 关键词。

...
ON SCHEDULE
    EVERY SAMPLE_INTERVAL
...

例如,要在每 1 分钟后运行一个事件,请使用以下语法。

...
ON SCHEDULE
    EVERY 1 MINUTE
...

此外,您可以指定重复发生的事件的时间 开始停止 通过使用 STARTSENDS 关键字如下图。

...
STARTS
    SAMPLE_TIME_STAMP + INTERVAL SAMPLE_INTERVAL
ENDS
    SAMPLE_TIME_STAMP + INTERVAL SAMPLE_INTERVAL
...

例如,要在当前时间后一小时开始重复发生的事件并在一年后结束,请使用以下语法。

...
STARTS
    CURRENT_TIMESTAMP + INTERVAL 1 HOUR
ENDS
    CURRENT_TIMESTAMP + INTERVAL 1 YEAR
...

以下是可用于为重复事件计时的所有时间间隔的列表。

YEAR | QUARTER | MONTH | DAY | HOUR | MINUTE |WEEK | SECOND | YEAR_MONTH | DAY_HOUR | DAY_MINUTE | DAY_SECOND | HOUR_MINUTE | HOUR_SECOND | MINUTE_SECOND

一些间隔结合了两个计时周期。 例如, MINUTE_SECOND 定义分钟和秒。 在这种情况下,“10 分 4 秒”间隔可以表示如下。

+ INTERVAL '10:4' MINUTE_SECOND

既然您已经熟悉了在 MySQL 中创建计划事件的语法,那么在下一步中为您的计费软件创建一个事件。

3. 安排计费事件

在本演示中,您将创建一个计费软件计划事件,将每分钟订阅活动记录到 billings 数据库表。

创建 billing_event 通过运行下面的命令。

mysql> CREATE EVENT IF NOT EXISTS billing_event
       ON SCHEDULE
           EVERY 1 MINUTE
       DO
           INSERT INTO billings(
             subscription_id,
             time,
             amount
           )
           SELECT
             subscriptions.subscription_id,
             NOW(),
             products.billing_per_minute
          FROM subscriptions
          LEFT JOIN products
          ON subscriptions.product_id = products.product_id
          WHERE subscriptions.is_active="Y";

在上面的代码中,您已经使用关键字指示 MySQL 每 1 分钟运行一次事件 ON SCHEDULE EVERY 1 MINUTE. 接下来,您将使用 MySQL INSERT INTO SELECT 命令复制 subscription_id's 和相关的 billing_per_minute 金额来自 subscriptionsproducts 表,分别。 你也在使用 NOW() 函数来记录每个计费记录的当前时间戳。 语句`WHERE subscriptions.is_active=”Y” 确保您只对活动订阅进行计费。

通过执行以下命令确保您的事件已在数据库中成功创建。

mysql> SHOW EVENTS FROM sample_db;

您的 billing_event 应列在列表中,如下所示。

+-----------+---------------+----------------+-----------+-----------+------------+----------------+----------------+---------------------+------+---------+------------+----------------------+----------------------+--------------------+
| Db        | Name          | Definer        | Time zone | Type      | Execute at | Interval value | Interval field | Starts              | Ends | Status  | Originator | character_set_client | collation_connection | Database Collation |
+-----------+---------------+----------------+-----------+-----------+------------+----------------+----------------+---------------------+------+---------+------------+----------------------+----------------------+--------------------+
| sample_db | billing_event | [email protected] | SYSTEM    | RECURRING | NULL       | 1              | MINUTE         | 2021-07-08 12:41:01 | NULL | ENABLED |          1 | cp850                | cp850_general_ci     | utf8mb4_0900_ai_ci |
+-----------+---------------+----------------+-----------+-----------+------------+----------------+----------------+---------------------+------+---------+------------+----------------------+----------------------+--------------------+
1 row in set (0.00 sec)

您的活动现在安排在每分钟后运行。 在下一步中,您将确认逻辑是否按预期工作。

4. 确认计费逻辑

等待几分钟让调度程序插入几条记录到 billings 表,然后运行以下 SELECT 表的声明。

mysql> SELECT
       billing_id,
       subscription_id
       time,
       amount
       FROM billings;

随着时间的推移,billings 表中的列表如下增长。

+------------+-----------------+---------------------+----------+
| billing_id | subscription_id | time                | amount   |
+------------+-----------------+---------------------+----------+
|          1 |               1 | 2021-07-08 12:41:01 | 0.003472 |
|          2 |               3 | 2021-07-08 12:41:01 | 0.003472 |
|          3 |               2 | 2021-07-08 12:41:01 | 0.006944 |
|          4 |               1 | 2021-07-08 12:42:01 | 0.003472 |
|          5 |               3 | 2021-07-08 12:42:01 | 0.003472 |
|          6 |               2 | 2021-07-08 12:42:01 | 0.006944 |
|          7 |               1 | 2021-07-08 12:43:01 | 0.003472 |
|          8 |               3 | 2021-07-08 12:43:01 | 0.003472 |
|          9 |               2 | 2021-07-08 12:43:01 | 0.006944 |
...
+------------+-----------------+---------------------+----------+
n rows in set (0.00 sec)

这确认 MySQL 事件调度程序按预期工作。 通过执行以下命令检索每个客户订阅的总计费金额。

mysql> SELECT
       customers.customer_id,
       CONCAT(customers.first_name, ' ', customers.last_name) as customer_full_name,
       sum(amount) as total_due
       FROM billings
       LEFT JOIN subscriptions
       ON billings.subscription_id = subscriptions.subscription_id
       LEFT JOIN customers
       ON customers.customer_id = subscriptions.customer_id
       GROUP BY billings.subscription_id;

您现在应该会看到如下所示的详细报告。 您的数字应根据您预定的活动运行的时间而有所不同。

+-------------+--------------------+-----------+
| customer_id | customer_full_name | total_due |
+-------------+--------------------+-----------+
|           1 | JOHN DOE           |  0.010416 |
|           2 | SMITH JANE         |  0.020832 |
|           3 | MARY MIKE          |  0.010416 |
+-------------+--------------------+-----------+
3 rows in set (0.00 sec)

接下来,您将学习如何删除已安排的事件。

5. 掉落事件

您可以通过执行以下命令在 MySQL 中删除计划事件。 代替 SAMPLE_EVENT_NAME 与您的活动名称。

mysql> DROP EVENT SAMPLE_EVENT_NAME;

例如,要删除事件 billing_event,运行下面的命令。

mysql> DROP EVENT billing_event;

删除事件后,它将立即停止执行。

结论

在本指南中,您已经设置了一个示例数据库并学习了在 MySQL 中创建和调度事件的基本语法。 您还使用假设的计费应用程序对预定作业的工作流程进行了试验。 您可以使用本指南中的知识来创建任何需要重复作业来处理某些业务逻辑的数据库应用程序。

注:本教程在Vultr VPS上测试通过,如需部署请前往Vultr.com