- MySQL Basics
- MySQL - Home
- MySQL - Introduction
- MySQL - Features
- MySQL - Versions
- MySQL - Variables
- MySQL - Installation
- MySQL - Administration
- MySQL - PHP Syntax
- MySQL - Node.js Syntax
- MySQL - Java Syntax
- MySQL - Python Syntax
- MySQL - Connection
- MySQL - Workbench
- MySQL Databases
- MySQL - Create Database
- MySQL - Drop Database
- MySQL - Select Database
- MySQL - Show Database
- MySQL - Copy Database
- MySQL - Database Export
- MySQL - Database Import
- MySQL - Database Info
- MySQL Users
- MySQL - Create Users
- MySQL - Drop Users
- MySQL - Show Users
- MySQL - Change Password
- MySQL - Grant Privileges
- MySQL - Show Privileges
- MySQL - Revoke Privileges
- MySQL - Lock User Account
- MySQL - Unlock User Account
- MySQL Tables
- MySQL - Create Tables
- MySQL - Show Tables
- MySQL - Alter Tables
- MySQL - Rename Tables
- MySQL - Clone Tables
- MySQL - Truncate Tables
- MySQL - Temporary Tables
- MySQL - Repair Tables
- MySQL - Describe Tables
- MySQL - Add/Delete Columns
- MySQL - Show Columns
- MySQL - Rename Columns
- MySQL - Table Locking
- MySQL - Drop Tables
- MySQL - Derived Tables
- MySQL Queries
- MySQL - Queries
- MySQL - Constraints
- MySQL - Insert Query
- MySQL - Select Query
- MySQL - Update Query
- MySQL - Delete Query
- MySQL - Replace Query
- MySQL - Insert Ignore
- MySQL - Insert on Duplicate Key Update
- MySQL - Insert Into Select
- MySQL Indexes
- MySQL - Indexes
- MySQL - Create Index
- MySQL - Drop Index
- MySQL - Show Indexes
- MySQL - Unique Index
- MySQL - Clustered Index
- MySQL - Non-Clustered Index
- MySQL Operators and Clauses
- MySQL - Where Clause
- MySQL - Limit Clause
- MySQL - Distinct Clause
- MySQL - Order By Clause
- MySQL - Group By Clause
- MySQL - Having Clause
- MySQL - AND Operator
- MySQL - OR Operator
- MySQL - Like Operator
- MySQL - IN Operator
- MySQL - ANY Operator
- MySQL - EXISTS Operator
- MySQL - NOT Operator
- MySQL - NOT EQUAL Operator
- MySQL - IS NULL Operator
- MySQL - IS NOT NULL Operator
- MySQL - Between Operator
- MySQL - UNION Operator
- MySQL - UNION vs UNION ALL
- MySQL - MINUS Operator
- MySQL - INTERSECT Operator
- MySQL - INTERVAL Operator
- MySQL Joins
- MySQL - Using Joins
- MySQL - Inner Join
- MySQL - Left Join
- MySQL - Right Join
- MySQL - Cross Join
- MySQL - Full Join
- MySQL - Self Join
- MySQL - Delete Join
- MySQL - Update Join
- MySQL - Union vs Join
- MySQL Keys
- MySQL - Unique Key
- MySQL - Primary Key
- MySQL - Foreign Key
- MySQL - Composite Key
- MySQL - Alternate Key
- MySQL Triggers
- MySQL - Triggers
- MySQL - Create Trigger
- MySQL - Show Trigger
- MySQL - Drop Trigger
- MySQL - Before Insert Trigger
- MySQL - After Insert Trigger
- MySQL - Before Update Trigger
- MySQL - After Update Trigger
- MySQL - Before Delete Trigger
- MySQL - After Delete Trigger
- MySQL Data Types
- MySQL - Data Types
- MySQL - VARCHAR
- MySQL - BOOLEAN
- MySQL - ENUM
- MySQL - DECIMAL
- MySQL - INT
- MySQL - FLOAT
- MySQL - BIT
- MySQL - TINYINT
- MySQL - BLOB
- MySQL - SET
- MySQL Regular Expressions
- MySQL - Regular Expressions
- MySQL - RLIKE Operator
- MySQL - NOT LIKE Operator
- MySQL - NOT REGEXP Operator
- MySQL - regexp_instr() Function
- MySQL - regexp_like() Function
- MySQL - regexp_replace() Function
- MySQL - regexp_substr() Function
- MySQL Fulltext Search
- MySQL - Fulltext Search
- MySQL - Natural Language Fulltext Search
- MySQL - Boolean Fulltext Search
- MySQL - Query Expansion Fulltext Search
- MySQL - ngram Fulltext Parser
- MySQL Functions & Operators
- MySQL - Date and Time Functions
- MySQL - Arithmetic Operators
- MySQL - Numeric Functions
- MySQL - String Functions
- MySQL - Aggregate Functions
- MySQL Misc Concepts
- MySQL - NULL Values
- MySQL - Transactions
- MySQL - Using Sequences
- MySQL - Handling Duplicates
- MySQL - SQL Injection
- MySQL - SubQuery
- MySQL - Comments
- MySQL - Check Constraints
- MySQL - Storage Engines
- MySQL - Export Table into CSV File
- MySQL - Import CSV File into Database
- MySQL - UUID
- MySQL - Common Table Expressions
- MySQL - On Delete Cascade
- MySQL - Upsert
- MySQL - Horizontal Partitioning
- MySQL - Vertical Partitioning
- MySQL - Cursor
- MySQL - Stored Functions
- MySQL - Signal
- MySQL - Resignal
- MySQL - Character Set
- MySQL - Collation
- MySQL - Wildcards
- MySQL - Alias
- MySQL - ROLLUP
- MySQL - Today Date
- MySQL - Literals
- MySQL - Stored Procedure
- MySQL - Explain
- MySQL - JSON
- MySQL - Standard Deviation
- MySQL - Find Duplicate Records
- MySQL - Delete Duplicate Records
- MySQL - Select Random Records
- MySQL - Show Processlist
- MySQL - Change Column Type
- MySQL - Reset Auto-Increment
- MySQL - Coalesce() Function
MySQL ALTER EVENT Statement
MySQL ALTER EVENT Statement
A MySQL Event is nothing but a task that execute at a particular schedule. An event can contain one or more MySQL statements these statements are stored in the databases and gets executed at the specified schedule.
The ALTER EVENT statement is used to change the characteristics of a MYSQL event.
Syntax
Following is the syntax of the MySQL ALTER EVENT statement −
ALTER EVENT old_event_name RENAME TO new_event_name
Where, old_event_name is the name of the event you need to alter, new_event_name is the new name to which you need to rename the event.
Example (Renaming an event)
In the following query, we are creating a table named Data −
CREATE TABLE Data (Name VARCHAR(255), age INT);
Now, we create an event which inserts records into the above created table −
CREATE EVENT sample_event ON SCHEDULE EVERY 1 MONTH DO TRUNCATE TABLE data;
After creating an event if you verify the list of events using the SHOW EVENTS statements you can observe the created event in the list −
SHOW EVENTS;
The above show statement produces the following output −
***************** 1. row ***************** Db: test Name: sample_event Definer: root@localhost Time zone: SYSTEM Type: RECURRING Execute at: NULL Interval value: 1 Interval field: MONTH Starts: 2023-12-04 14:08:03 Ends: NULL Status: ENABLED Originator: 1 character_set_client: cp850 collation_connection: cp850_general_ci Database Collation: utf8mb4_0900_ai_ci 1 row in set (0.01 sec)
Following query renames the above created event sample_event −
ALTER EVENT sample_event RENAME TO new_event_name;
Verification
If you get the list of events using the SHOW EVENTS Statement again you can observe the changed name −
SHOW EVENTS;
Following is the output of the above query −
***************** 1. row ***************** Db: test Name: new_event_name Definer: root@localhost Time zone: SYSTEM Type: RECURRING Execute at: NULL Interval value: 1 Interval field: MONTH Starts: 2023-12-04 14:08:03 Ends: NULL Status: ENABLED Originator: 1 character_set_client: cp850 collation_connection: cp850_general_ci Database Collation: utf8mb4_0900_ai_ci 1 row in set (0.00 sec)
Altering the schedule of an event
You can alter the schedule of an existing event by using the ON SCHEDULE clause of the ALTER statement.
Syntax
Following is the syntax of the MySQL ALTER EVENT statement to change the schedule of an existing event −
ALTER EVENT event_name ON SCHEDULE new_time_stamp
Where, event_name is the name of the event you need to alter, new_time_stamp is the new time stamp value.
Example
Assume we have created an event which inserts a record into a data table after 1 minute of the current time stamp as shown below −
CREATE EVENT example_event ON SCHEDULE AT CURRENT_TIMESTAMP + INTERVAL 1 Minute DO INSERT INTO new.Data VALUES('Rahman', 25);
Following query modifies the schedule of the above created event to every month −
ALTER EVENT example_event ON SCHEDULE EVERY 1 MONTH DO TRUNCATE TABLE data;
Altering the body of an event
You can alter the body of an existing event by using the DO clause of the ALTER statement.
Syntax
Following is the syntax of the MySQL ALTER EVENT statement −
ALTER EVENT event_name DO event_body
Where, event_name is the name of the event you need to alter, event_body is the new body of the event.
Example
Assume we have created an event that inserts 3 records into a data table as shown below −
CREATE EVENT test_event ON SCHEDULE AT CURRENT_TIMESTAMP + INTERVAL 1 Minute DO INSERT INTO Data VALUES('Rahman', 25), ('Ram', 35), ('Raj', 30);
If you verify the contents of the data table you can observe the inserted records −
select * from data;
Output
The above query produces the following output −
Name | age |
---|---|
Rahman | 25 |
Ram | 35 |
Raj | 30 |
Following query changes the body of the above event such that it truncates the contents of the data table −
ALTER EVENT test_event ON SCHEDULE AT CURRENT_TIMESTAMP + INTERVAL 1 Minute DO TRUNCATE TABLE data;
If you verify the contents of the data table (again) you can observe the empty table as shown below −
select * from data; Empty set (0.08 sec)
The COMMENT clause
You can add a comment while creating an event using the COMMENT clause as shown below −
Syntax
Following is the syntax to alter the comment if an existing event −
ALTER EVENT event_name COMMENT 'string';
Example
Assume we have created an event as shown below −
CREATE EVENT event_hourly ON SCHEDULE EVERY 1 MONTH COMMENT 'This truncates the data table each month' DO TRUNCATE TABLE data;
Following query alters the comment added above −
ALTER EVENT event_hourly COMMENT 'This is a new string';