Home Database Mysql Tutorial MySQL定时器开启、调用实现代码_MySQL

MySQL定时器开启、调用实现代码_MySQL

Jun 01, 2016 pm 01:23 PM
timer

bitsCN.com 创建测试表

CREATE TABLE t
(
v VARCHAR(100) NOT NULL
) ENGINE INNODB DEFAULT CHARSET=utf8;
创建定时器 调用的存储过程
DELIMITER $$
DROP PROCEDURE IF EXISTS e_test $$
CREATE PROCEDURE e_test()
BEGIN
INSERT INTO t VALUES('1');
END $$
DELIMITER ;
要使定时起作用 MySQL的常量GLOBAL event_scheduler必须为on或者是1
-- 查看是否开启定时器
SHOW VARIABLES LIKE '%sche%';
-- 开启定时器 0:off 1:on
SET GLOBAL event_scheduler = 1;
-- 创建事件
--每隔一秒自动调用e_test()存储过程
CREATE EVENT IF NOT EXISTS event_test
ON SCHEDULE EVERY 1 SECOND
ON COMPLETION PRESERVE
DO CALL e_test();
-- 开启事件
ALTER EVENT event_test ON
COMPLETION PRESERVE ENABLE;
-- 关闭事件
ALTER EVENT event_test ON
COMPLETION PRESERVE DISABLE;
从现在开始每隔九天定时执行
CREATE EVENT EVENT1
ON SCHEDULE EVERY 9 DAY STARTS NOW()
ON COMPLETION PRESERVE ENABLE
DO
BEGIN
CALL TOTAL();
END
每个月的一号凌晨1 点执行
CREATE EVENT EVENT2
ON SCHEDULE EVERY 1 MONTH STARTS DATE_ADD(DATE_ADD(DATE_SUB(CURDATE(),INTERVAL DAY(CURDATE())-1 DAY), INTERVAL 1 MONTH),INTERVAL 1 HOUR)
ON COMPLETION PRESERVE ENABLE
DO
BEGIN
CALL STAT();
END
每个季度一号的凌晨2点执行
CREATE EVENT TOTAL_SEASON_EVENT
ON SCHEDULE EVERY 1 QUARTER STARTS DATE_ADD(DATE_ADD(DATE( CONCAT(YEAR(CURDATE()),'-',ELT(QUARTER(CURDATE()),1,4,7,10),'-',1)),INTERVAL 1 QUARTER),INTERVAL 2 HOUR)
ON COMPLETION PRESERVE ENABLE
DO
BEGIN
CALL SEASON_STAT();
END
每年1月1号凌晨四点执行
CREATE EVENT TOTAL_YEAR_EVENT
ON SCHEDULE EVERY 1 YEAR STARTS DATE_ADD(DATE(CONCAT(YEAR(CURDATE()) + 1,'-',1,'-',1)),INTERVAL 4 HOUR)
ON COMPLETION PRESERVE ENABLE
DO
BEGIN
CALL YEAR_STAT();
END

MySQL的event在进行按月,季,年进行自动调用存储过程时,为了测试可以把系统改为年的最后一天,如2010-12-31 23:59:55;bitsCN.com

Statement of this Website
The content of this article is voluntarily contributed by netizens, and the copyright belongs to the original author. This site does not assume corresponding legal responsibility. If you find any content suspected of plagiarism or infringement, please contact admin@php.cn

Hot AI Tools

Undresser.AI Undress

Undresser.AI Undress

AI-powered app for creating realistic nude photos

AI Clothes Remover

AI Clothes Remover

Online AI tool for removing clothes from photos.

Undress AI Tool

Undress AI Tool

Undress images for free

Clothoff.io

Clothoff.io

AI clothes remover

AI Hentai Generator

AI Hentai Generator

Generate AI Hentai for free.

Hot Article

R.E.P.O. Energy Crystals Explained and What They Do (Yellow Crystal)
2 weeks ago By 尊渡假赌尊渡假赌尊渡假赌
Repo: How To Revive Teammates
1 months ago By 尊渡假赌尊渡假赌尊渡假赌
Hello Kitty Island Adventure: How To Get Giant Seeds
4 weeks ago By 尊渡假赌尊渡假赌尊渡假赌

Hot Tools

Notepad++7.3.1

Notepad++7.3.1

Easy-to-use and free code editor

SublimeText3 Chinese version

SublimeText3 Chinese version

Chinese version, very easy to use

Zend Studio 13.0.1

Zend Studio 13.0.1

Powerful PHP integrated development environment

Dreamweaver CS6

Dreamweaver CS6

Visual web development tools

SublimeText3 Mac version

SublimeText3 Mac version

God-level code editing software (SublimeText3)

How to set a timer on your iPhone camera How to set a timer on your iPhone camera Apr 14, 2023 am 10:43 AM

How long can you set a timer on your iPhone camera? When you access the timer options in the iPhone's camera app, you'll be given the option to choose between two modes: 3 seconds (3s) and 10 seconds (10s). The first option lets you take a quick selfie from the front or rear camera while you're holding your iPhone. The second option is useful in scenes where you can mount your iPhone on a tripod from a distance to click group photos or selfies. How to Set a Timer on an iPhone Camera While setting a timer on an iPhone camera is a fairly simple process, exactly how to do it varies depending on the iPhone model you're using.

How to implement the timer function in the Workerman document How to implement the timer function in the Workerman document Nov 08, 2023 pm 05:06 PM

How to implement the timer function in the Workerman document Workerman is a powerful PHP asynchronous network communication framework that provides a wealth of functions, including the timer function. Use timers to execute code within specified time intervals, which is very suitable for application scenarios such as scheduled tasks and polling. Next, I will introduce in detail how to implement the timer function in Workerman and provide specific code examples. Step 1: Install Workerman First, we need to install Worker

How to set a timer for daily scheduled task execution in Java? How to set a timer for daily scheduled task execution in Java? Dec 27, 2023 am 11:10 AM

Java timer: How to set a scheduled execution task every day? In daily Java development, we often encounter the need to perform a certain task regularly every day. For example, perform a data backup task at 1 a.m. every day, or send a daily email at 8 p.m., etc. So in Java, we can use timers to achieve such a function. Java provides a variety of timer implementation methods. This article will introduce two methods based on Timer and ScheduledExecutorService.

What is java timer expression What is java timer expression Dec 27, 2023 pm 05:06 PM

The timer expression is used to define the execution plan of the task. The timer expression is based on the model of "execute a task after a given time interval". The expression usually consists of two parts: an initial delay and a time interval.

How does a timer work? How does a timer work? Aug 16, 2023 pm 02:18 PM

The working principle of timers can be divided into two types: hardware timers and software timers. The working principle of the hardware timer is that the clock signal source provides a stable clock signal as the reference of the timer. The counter starts counting from a preset value and is incremented every time the clock signal arrives. When the counter reaches the preset value, the timer will trigger an interrupt signal to notify the interrupt controller to process the corresponding interrupt service routine. In the interrupt service routine, some predetermined operations can be performed. The working principle of the software timer is implemented through library functions or system calls provided by the programming language or system, etc.

Master the time.NewTimer function in Go language documentation to implement a single timer Master the time.NewTimer function in Go language documentation to implement a single timer Nov 03, 2023 pm 02:19 PM

Master the time.NewTimer function in the Go language documentation to implement a one-shot timer, and attach specific code examples. Time is the benchmark of our lives, and timers are one of the most commonly used tools in programming. In the Go language, we can use the time package to handle time-related operations, and the NewTimer function can be used to create a one-shot timer. This article will introduce how to use the NewTimer function to implement a simple one-shot timer, and attach specific code examples. In Go language, tim

How to set up scheduled monthly tasks in Java? How to set up scheduled monthly tasks in Java? Jan 11, 2024 pm 04:50 PM

Java timer: How to set a monthly scheduled execution task? Introduction: In development, we often encounter scenarios that require monthly execution of tasks, such as monthly update of statistical data, regular sending of reports, etc. Java provides a variety of timer implementation methods. This article will introduce how to use Java timers to implement monthly scheduled execution tasks and provide specific code examples. 1. Use the Timer class to implement monthly scheduled tasks. The Timer class is the most basic timer class provided by Java, through which simple scheduled tasks can be implemented.

Phalcon middleware: Add the function of scheduled tasks and timers to applications Phalcon middleware: Add the function of scheduled tasks and timers to applications Jul 30, 2023 pm 06:08 PM

Phalcon middleware: the ability to add scheduled tasks and timers to applications Introduction: When developing web applications, we often encounter the need to perform certain tasks regularly or perform a certain function within a specific time interval. As a high-performance PHP framework, Phalcon provides a flexible way to implement these functions, which is to add scheduled tasks and timers through middleware. 1. Introduction to Phalcon middleware Phalcon middleware is a tool that can be used to process HTTP requests.

See all articles