Dear Readers, this blog will be talking about how to create even in MySQL and some advanced MySQL commands. To create event in MySQL, we have to follow a Pattern which is written below..
Make data easy with Helical Insight.
Helical Insight is the world’s best open source business intelligence tool.
1. Declare Delimiter
2. Define Name of the event
3. Define when to schedule
4. Start with “DO”
5. Then “Begin”
6. Define Business logic
(Like variable declaration, job which you want to schedule through your Event, any condition…So mainly it is the body of your Event)
7. Then Declare end of your event like “END <delimiter>”
8. Change Delimiter to normal Delimiter.
Example:- Here I tried to call a stored procedure in this event which is scheduled after every 4-Hour, and passed the parameter for that stored procedure by taking two date parameter, and also with some additional parameters, I tried to use loop and if-else condition also in this event… (Tested and executed Event)
delimiter $$
CREATE EVENT ue_schedule_test
ON SCHEDULE
EVERY 4 HOUR
DO
BEGIN
DECLARE to_temp TEXT(25);
DECLARE from_temp TEXT(25);
DECLARE pv_temp TEXT(20);
DECLARE done INT DEFAULT FALSE;
DECLARE curs1 CURSOR FOR SELECT name FROM data ;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;
OPEN curs1;
read_loop: LOOP
FETCH curs1 INTO pv_temp;
SELECT DATE_FORMAT(DATE_ADD(convert_tz(CURDATE(),’SYSTEM’,’+00:00′),INTERVAL -4 HOUR),’%Y-%m-%d %H:%i:%S’) INTO from_temp;
SELECT DATE_FORMAT(DATE_ADD(convert_tz(CURDATE(),’SYSTEM’,’+00:00′),INTERVAL 4 HOUR),’%Y-%m-%d %H:%i:%S’) INTO to_temp;
DELETE FROM availibility WHERE Date=from_temp;
CALL usp_availability_test(from_temp,to_temp,pv_temp,’Total’,’+00:00′,’custom’);
IF done THEN
LEAVE read_loop;
END IF;
END LOOP;
CLOSE curs1;
END $$
delimiter ;
Some Other Important My-Sql Commands
1. To clear console — \! clear
2. To delete procedure — drop prcedure
3. Show all stored procedure — show procedure status
4. To get 2nd highest salary —SELECT DISTINCT(Salary) FROM employee ORDER BY Salary DESC LIMIT 1,1
5. To get 3rd highest salary —SELECT DISTINCT(Salary) FROM employee ORDER BY Salary DESC LIMIT 2,1
Make data easy with Helical Insight.
Helical Insight is the world’s best open source business intelligence tool.
6. To convert string into datetime type —SELECT STR_TO_DATE(yourdatefield, ‘%m/%d/%Y’) FROM
7. To check event scheduler is ON/OFF — select @@event_scheduler
8. To start event-scheduler — set GLOBAL event_scheduler=ON
9. To delete duplicate records from table
— delete from table1 USING table1, table1 as vtable
WHERE table1.ID<vtable.ID AND table1.field_name=vtable.field_name;
So, These are the some Advanced My-SQL features , which may help you…
Have a Good Day………!!!
PUSHPRAJ KUMAR
Helical IT Solutions
Best Open Source Business Intelligence Software Helical Insight is Here
