Crons, Timestamp or none?
Posted: Wed Jan 08, 2014 7:13 pm
At first, this is my first humble contribution, of an article i published some time ago on other place, and decided to place it here as it might be useful for someone around the community, so bare with me! 
As the title says, the use of crons is the most widely use because mainly the limitations of the offered DB solution by the hosting companies (MySQL).
What if you could create jobs from inside the DB itself to make the work that usually you would be doing by the use of crons or even timestamped events?
Well as of MySQL 5.1 you can do it. Jobs on MySQL are simply called events, and they work EXACTLY the same way as your crons.
I will ilustrate with a small example, but remember that only MySQL 5.1 or higher is capable to do this.
then...
Now let's enable the event (MySQL default value is off (0))
As you can see the semanthics is very similar to what you are already used to...
And 9 minutes later
So far so good, but you might be asking... what limitations am i going to find?
You will not be able to have $vars stucked there, as it only works with plain db calls.
I'll give a practical example on a query i used to have running on my own game that was using events (yes this is the names for mysql jobs).
I have redrawn the bank system to it's own table and have the event running once a week, and you might be asking how do i make calculations on interest right? i have an extra field that shows the users bank interest (my system allows about 7 different interest rates) so the only thing i need is to call the field to make interest calculations and the query would be set like this.
Hope this is usefull, and if you need any assistance, feel free to ask, even if you think "its a dumb question", as someone told me a few yeas ago "everyone is born naked"
And finally... the resource on MySQL website can be found here... http://dev.mysql.com/doc/refman/5.7/en/ ... event.html
As the title says, the use of crons is the most widely use because mainly the limitations of the offered DB solution by the hosting companies (MySQL).
What if you could create jobs from inside the DB itself to make the work that usually you would be doing by the use of crons or even timestamped events?
Well as of MySQL 5.1 you can do it. Jobs on MySQL are simply called events, and they work EXACTLY the same way as your crons.
I will ilustrate with a small example, but remember that only MySQL 5.1 or higher is capable to do this.
Code: Select all
CREATE TABLE IF NOT EXISTS `table` (
`tID` int(11) NOT NULL AUTO_INCREMENT,
`tDATETIME` datetime NOT NULL,
UNIQUE KEY `tID` (`tID`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;
Code: Select all
CREATE EVENT gettime
ON SCHEDULE EVERY 1 MINUTE DO
INSERT INTO test.table values ('',current_timestamp)
Code: Select all
set global event_scheduler = 1;
And 9 minutes later
Code: Select all
SELECT * FROM test.table
1 2014-01-08 18:12:17
2 2014-01-08 18:13:17
3 2014-01-08 18:14:17
4 2014-01-08 18:15:17
5 2014-01-08 18:16:17
6 2014-01-08 18:17:17
7 2014-01-08 18:18:17
8 2014-01-08 18:19:17
9 2014-01-08 18:20:17
You will not be able to have $vars stucked there, as it only works with plain db calls.
I'll give a practical example on a query i used to have running on my own game that was using events (yes this is the names for mysql jobs).
I have redrawn the bank system to it's own table and have the event running once a week, and you might be asking how do i make calculations on interest right? i have an extra field that shows the users bank interest (my system allows about 7 different interest rates) so the only thing i need is to call the field to make interest calculations and the query would be set like this.
Code: Select all
CREATE EVENT bank_interest
ON SCHEDULE EVERY 1 WEEK DO
UPDATE uBANK SET bMONEY = bMONEY + floor( bINTEREST * bMONEY / 100 ) WHERE bMONEY > 100
And finally... the resource on MySQL website can be found here... http://dev.mysql.com/doc/refman/5.7/en/ ... event.html