mysql一些不常用的命令

mysql  event   (事件相关的命令) ：

SHOW VARIABLES LIKE 'event_scheduler';       //查看是否开启事件

 

SHOW EVENTS;          //查看所有的事件列表

 

SELECT * FROM information_schema.events WHERE event_name='del_chat_log';     //查看某个事件的具体信息 'event_name' 为事件名称

 

DROP EVENT del_chat_log;                                  //删除某个事件

 

DELIMITER //
CREATE EVENT `moliao`.`del_chat_log`

ON SCHEDULE EVERY 1 MINUTE           
    
    DO
    BEGIN
DELETE FROM `moliao`.`mo_chat_log_9` WHERE ADDTIME <=UNIX_TIMESTAMP(NOW())-86400;
DELETE FROM `moliao`.`mo_chat_log_8` WHERE ADDTIME <=UNIX_TIMESTAMP(NOW())-86400;
DELETE FROM `moliao`.`mo_chat_log_7` WHERE ADDTIME <=UNIX_TIMESTAMP(NOW())-86400;
DELETE FROM `moliao`.`mo_chat_log_6` WHERE ADDTIME <=UNIX_TIMESTAMP(NOW())-86400;
DELETE FROM `moliao`.`mo_chat_log_5` WHERE ADDTIME <=UNIX_TIMESTAMP(NOW())-86400;               
DELETE FROM `moliao`.`mo_chat_log_4` WHERE ADDTIME <=UNIX_TIMESTAMP(NOW())-86400;
DELETE FROM `moliao`.`mo_chat_log_3` WHERE ADDTIME <=UNIX_TIMESTAMP(NOW())-86400;                
DELETE FROM `moliao`.`mo_chat_log_2` WHERE ADDTIME <=UNIX_TIMESTAMP(NOW())-86400;
DELETE FROM `moliao`.`mo_chat_log_1` WHERE ADDTIME <=UNIX_TIMESTAMP(NOW())-86400;
DELETE FROM `moliao`.`mo_chat_log_0` WHERE ADDTIME <=UNIX_TIMESTAMP(NOW())-86400;
    END;
    //

SCHEDULE EVERY 1 MINUTE   // 表示一分钟执行一次

 BEGIN

//你需要执行的SQL语句

 END;

 

mysql 查询  (调试相关) ：

 

 

EXPLAIN SELECT * FROM moliao.`mo_chat_log_4` WHERE ADDTIME <=UNIX_TIMESTAMP(NOW())-86400;       //  EXPLAIN 用于分析查询语句的分析  

 

 

转载于:https://www.cnblogs.com/lixiaoquan/p/10185933.html