mysql操作随笔

/*创建mysql定时任务*/
要查看当前是否已开启事件调度器，可执行如下SQL：
SHOW VARIABLES LIKE 'event_scheduler';
或
SELECT @@event_scheduler;
或
SHOW PROCESSLIST;
若显示：
+-----------------+-------+
| Variable_name | Value |
+-----------------+-------+
| event_scheduler | OFF |
+-----------------+-------+
则可执行
SET GLOBAL event_scheduler = 1;
或
SET GLOBAL event_scheduler = ON;
来开启

CREATE EVENT e_hengtu_demand
ON SCHEDULE EVERY 2 SECOND STARTS NOW()
ON COMPLETION PRESERVE ENABLE
DO UPDATE hengtu_demand SET status=1 WHERE UNIX_TIMESTAMP(NOW())-UNIX_TIMESTAMP(releasetime)>=30 and ispush=0;

SELECT * FROM mysql.event;

DROP EVENT e_hengtu_demand;

CREATE EVENT e_test
ON SCHEDULE EVERY 2 SECOND STARTS NOW() /*2秒钟执行一次,从现在开始*/
ON COMPLETION PRESERVE ENABLE /*需要循环复用这个Event*/
DO call test1(); /*SQL语句 执行存储过程test1*/

/*创建mysql存储过程*/
CREATE PROCEDURE test1()
begin
if (select type from hengtu_service where sid=1)=1
then update hengtu_service set type=5 where sid=1;
end if;
end

call test1(); /*调用*/

drop procedure test1 /*删除*/

show create procedure test1 /*查看*/

SELECT
(
CASE (select type from hengtu_service where sid=1)
WHEN 2 THEN "update hengtu_service set type=5 where sid=1"
ELSE (SELECT "我就不执行呀")
END
) as result

/*创建mysql触发器*/
CREATE TRIGGER hengtu_demandpush_trigger
AFTER INSERT ON hengtu_demandpush
FOR EACH ROW
BEGIN
DECLARE didcount INT;
SELECT conut(did) INTO didcount FROM hengtu_demandpush WHERE did = new.did;
IF didcount<=1 THEN
UPDATE hengtu_demand SET ispush=1 WHERE did=new.did;
END IF;
END

/*删除触发器*/
DROP TRIGGER hengtu_demandpush_trigger;