• Home
  • MAD
  • Concept Series
    • Software Design
    • Software Arch
    • GIT & Github
    • System Design
    • Cloud
    • Database Integration
    • Push Notification
    • API Integration
    • Cocoa PODS
  • DSA
  • Interview
  • Tips&Tricks
  • YT
  • Home
  • MAD
  • Concept Series
    • Software Design
    • Software Arch
    • GIT & Github
    • System Design
    • Cloud
    • Database Integration
    • Push Notification
    • API Integration
    • Cocoa PODS
  • DSA
  • Interview
  • Tips&Tricks
  • YT
  • #News
  • #APPS
  • #Events
    • #WWDC
    • #I/O
    • #Ignite
  • #Let’s Talk

MyCodeTips mycodetips-newlogocopy1

  • Home
  • MAD
  • Concept Series
    • Software Design
    • Software Arch
    • GIT & Github
    • System Design
    • Cloud
    • Database Integration
    • Push Notification
    • API Integration
    • Cocoa PODS
  • DSA
  • Interview
  • Tips&Tricks
  • YT
Database, Programming, Tips&Tricks

Creating Simple Event with MySQL

Creating a First Event

Creating an Event is somewhat like creating stored procedure or user defined function in MySQL. You will have DELIMITER, BEGIN, DO and END keywords.

As mentioned earlier we can define the execution time for the event while creating it. So now let’s create a basic event which executes after 5 minutes of creating event. Have a look at the below block of queries for the same.

DELIMITER $$

CREATE EVENT first_event

ON SCHEDULE AT CURRENT_TIMESTAMP + INTERVAL 5 MINUTE

DO

BEGIN

UPDATE table_name SET field_name = field_name + 1;

END;

$$;

Now once you have created the event you must make sure that event is created or not. So to make sure you will have to execute a single query to get all events which your MySQL server has.

SHOW EVENTS;

You can also write multiple SQL queries between BEGIN and END block of event. And yes you will have to separate multiple queries separated with ‘;’

So this is the basic step to create a simple event in MySQL. This is the basic and one-time event, which means this will be scheduled only once and after the execution is completed this event will be deleted from the server.

As I have already mentioned that this event will get removed once it is executed unless you have mentioned anything on ON COMPLETION block. If you write ON COMPLETION PRESERVE then event will not get deleted after execution is completed. Let’s create one event which will not get deleted after completion.

DELIMITER $$

CREATE EVENT first_event

ON SCHEDULE AT CURRENT_TIMESTAMP + INTERVAL 5 MINUTE

ON COMPLETION PRESERVE

DO

BEGIN

UPDATE table_name SET field_name = field_name + 1;

END;

$$;
Edit the Existing Events

You can edit events normally with the ALTER EVENT clause. Have a look at the below query block which shows the editing of the existing event in MySQL.

DELIMITER $$

ALTER EVENT first_event

ON SCHEDULE AT CURRENT_TIMESTAMP + INTERVAL 2 MINUTE

ON COMPLETION PRESERVE

DO

BEGIN

UPDATE table_name SET field_name = field_name + 1;

END;

$$;
After executing above query your event will be set to execute after 2 minute from current time.

Rename Events in MySQL

Renaming event is very easy here. Have a look at below query and I think you will not require any explanation,

ALTER EVENT first_event

RENAME TO first_event_edited;
Delete Events in MySQL

Deleting events are simple as renaming the event.

DROP EVENT first_event;

 

 

 

Liked it? Take a second to support Ranjan on Patreon!
become a patron button
  • Click to share on Reddit (Opens in new window)
  • Click to share on Facebook (Opens in new window)
  • Click to share on Twitter (Opens in new window)
  • Click to share on LinkedIn (Opens in new window)
  • Click to share on Tumblr (Opens in new window)
  • More
  • Click to share on Pocket (Opens in new window)
  • Click to share on Pinterest (Opens in new window)
Written by Ranjan - 1312 Views
Tags | DATABASE, mySql
AUTHOR
Ranjan

Namaste, My name is Ranjan, I am a graduate from NIT Rourkela. This website is basically about of what i learnt from my years of experience as a software engineer on software development specifically on mobile application development, design patterns/architectures, its changing scenarios, security, troubleshooting, tools, tips&tricks and many more.

You Might Also Like

1386376017 database

Tips for Database Normalization

December 6, 2013
mycodetips-newlogo2

What is IndexedDB in HTML5 ?

October 8, 2013
1386376017 database

Tips for SQL Query Optimization

December 5, 2013
Next Post
Previous Post

Support us

mycodetips
mycodetips

Follow us @ LinkedIn 2850+

Subscribe for updates

Join 8,213 other subscribers

Latest Posts

  • YT-Featured-solidprinciples
    SOLID Principles of Software Design
  • IOS 16 Features
    Latest features in IOS 16
  • r-language
    How can R language be used for data analysis?
  • wordpress-coding-blog
    Guide To WordPress Coding Standards
  • YT-Featured-Algorithm
    What is Algorithm?
  • Frameworks of IOS
    Frameworks of IOS – Part ( I )
  • NSFileManager or NSPathUtilities
    NSFileManager or NSPathUtilities in Objective-C
  • Passing data between view controllers in Objective-C
    Passing data between view controllers in Objective-C
  • structures-classes-enum
    Structures and Classes in swift !
  • control-system-swift
    Control Flow in Swift
whiteboard

Whiteboard(PRO)

whiteboard

Whiteboard(lite)

alphabets

Kids Alphabet

techlynk

Techlynk

techbyte

Do2Day

techbyte

Techbyte

  • #about
  • #myapps
  • #contact
  • #privacy
  • #Advertise
  • #myQuestions

Android Android Studio API APP Programming Apps blogging CSS DATABASE dsa Features HTML HTML5 installation Interview Questions IOS iPhone javascript Mac objective-c OS Programming quicktips SDK SEO SQL swift Tips & Tricks Tools UI Web Wordpress Xcode

  • SOLID Principles of Software Design
  • Latest features in IOS 16
  • How can R language be used for data analysis?
  • Guide To WordPress Coding Standards
  • What is Algorithm?

©mycodetips.com