每周或每月的MYSQL查询自动化(MYSQL Query Automation on Weekly or Monthly Basis)

我在数据库中有两个表。 Table1从某个Utility程序获取数据。我希望每周或每月通过avg值更新我的table2，无需任何用户干预。任何有想法如何做的人请以示例指导我。谢谢！！

I have two table in database. Table1 gets data from some Utility program. I want to update my table2 by avg value on weekly or monthly basis without any user intervention. Anyone having idea how to do that please guide me with example. Thank You!!

最满意答案

我希望每周或每月通过avg值更新我的table2，无需任何用户干预。

您可以自动使用MySQL event scheduler 。

您可以为每个事件时间定义多个调度程序，即一个用于每周更新，另一个用于每月更新

示例：

delimiter // drop event if exists monthly_event_scheduler_on_table2; create event if not exists monthly_event_scheduler_on_table2 on schedule every 1 month -- you can change the following statement -- to start it within next few minutes or so ... starts timestamp( current_date + interval 1 month, '00:00:00' ) -- starts now(); comment 'monthly update event scheduling on table2' do -- better practice is writing a procedure for the update process. call db_name.procedure_name_that_updates_the_table2_records(); ; // delimiter ;

您可以定义在计划的时间戳上每周运行的类似事件。

确保您没有定义此类事件。如果存在，则必须删除它并使用新定义重新创建。

请参阅 ： MySQL：CREATE EVENT语法

事件调度程序的默认状态为DISABLED。您需要通过以下任何语句启用它。

SET GLOBAL event_scheduler = ON; SET @@global.event_scheduler = ON; SET GLOBAL event_scheduler = 1; SET @@global.event_scheduler = 1;

当事件调度程序为ON时，事件调度程序线程在SHOW PROCESSLIST的输出中列为守护进程，其状态如下所示：

mysql> SHOW PROCESSLIST\G *************************** 2. row *************************** Id: 2 User: event_scheduler Host: localhost db: NULL Command: Daemon Time: 3 State: Waiting for next activation Info: NULL

将事件调度程序设置为ON后，您会看到它正常工作。

请参阅 ： MySQL事件调度程序配置

I want to update my table2 by avg value on weekly or monthly basis without any user intervention.

You can automate using MySQL event scheduler.

You can define multiple schedulers for each of the event time, i.e. one for weekly updates and the other for monthly updates

Example:

delimiter // drop event if exists monthly_event_scheduler_on_table2; create event if not exists monthly_event_scheduler_on_table2 on schedule every 1 month -- you can change the following statement -- to start it within next few minutes or so ... starts timestamp( current_date + interval 1 month, '00:00:00' ) -- starts now(); comment 'monthly update event scheduling on table2' do -- better practice is writing a procedure for the update process. call db_name.procedure_name_that_updates_the_table2_records(); ; // delimiter ;

You can define a similar event that runs on every week on a scheduled timestamp.

Make sure that you do not have such event already defined. If exists, you have to drop it and create afresh with new definition.

Refer to: MySQL: CREATE EVENT Syntax

Default state of Event Scheduler is DISABLED. You require to enable it by any of the following statements.

SET GLOBAL event_scheduler = ON; SET @@global.event_scheduler = ON; SET GLOBAL event_scheduler = 1; SET @@global.event_scheduler = 1;

When the Event Scheduler is ON, the event scheduler thread is listed in the output of SHOW PROCESSLIST as a daemon process, and its state is represented as shown here:

mysql> SHOW PROCESSLIST\G *************************** 2. row *************************** Id: 2 User: event_scheduler Host: localhost db: NULL Command: Daemon Time: 3 State: Waiting for next activation Info: NULL

Once the Event Scheduler is set ON, you would see it working.

Refer to : MySQL Event Scheduler Configuration

更多推荐