• MySQL Event Scheduler

    If you are running a large public web applications like shopping and emails portals, you have to handle lots of unwanted data rows for example spam emails and unused shopping cart data. Sure it will create problem in database overload. So that I want to explain a simple tip called how to use MySQL event scheduler for deleting unwanted data rows from database.

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    15
    16
    17
    18
    19
    20
    21
    22
    23
    24
    25
    26
    27
    28
    29
    
    Steps:
    1. Create a table for example cart in mysql database.
    Sample database cart table contains four columns cart_id, user_id, product_id and created_at. 
     
    CREATE TABLE cart
    (
    cart_id INT  AUTO_INCREMENT,
    user_id INT,
    product_id INT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY (cart_id)
    ) ;
     
    2. Let’s enable MySQL Event Scheduler.
     
    Start MySQL event scheduler by executing following query in PhpMyAdmin or MySQL command prompt.
     
    SET GLOBAL event_scheduler = ON;
    Or
    SET GLOBAL event_scheduler = 1;
     
    3. Create an Event
     
    Here the following event will run every day and clear/delete 10 days old data from cart table based on time stamp
     
    CREATE EVENT newEvent
    ON SCHEDULE EVERY 1 DAY
    DO
    DELETE FROM cart WHERE created_at
    Tags:

Comments on this post

Leave a Reply

  • Security Code :


    + 8 = fourteen