MySQL supports some procedural extensions to SQL. By using them, you can manage the control flow, create loops and use cursors. These features allow you to create stored programs, which may be of 3 kinds:
MySQL future versions will support stored program written in other languages, not only SQL. You will have the ability to manage new languages as PLUGINs. Also, the stored procedures will be compiled into C code, and thus they will be faster.
Triggers were added in MySQL 5.0.2. They work on persistent tables, but can't be associated with TEMPORARY tables.
To create a new trigger:
CREATE TRIGGER `delete_old` AFTER INSERT ON `articles` FOR EACH ROW BEGIN DELETE FROM `articles` ORDER BY `id` ASC LIMIT 1 END
This example trigger defines a stored program (which is the simple DELETE statement) called `delete_old`. It's automatically fired when a new record is INSERTed into `articles`. It's called after the INSERT, not before. If a single INSERT adds more than one row to the table, `delete_old` is called more than once. The idea is simple: when a new record is created, the oldest record is DELETEd.
A trigger may be executed BEFORE or AFTER a certain SQL statement. This is important because a trigger may execute one or more statements which activate other triggers; so, it may be important to decide their time order, to ensure the database's integrity.
The statement which fires the trigger must be a basic DML command:
A special case is INSERT ... ON DUPLICATE KEY UPDATE. If the INSERT is executed, both BEFORE INSERT and AFTER INSERT are executed. If the INSERT is not executed, and thus an UPDATE is executed instead, the order of events is the following: BEFORE INSERT, BEFORE UPDATE, AFTER UPDATE.
You can also specify the table's name by using the following syntax:
... ON `my_database`.`my_table` ...
Triggers' names must be unique in a database. Two tables located in the same database can't be associated to two different triggers with the same name.
Unlike other DBMSs and standard SQL, all triggers are fired FOR EACH ROW, and can't be executed for each statement.
A stored program must be specified between BEGIN and END reserved words. You can't use dynamic SQL here (the PREPARE statement); use can call a stored procedure, instead. If you execute only one statement, you can omit the BEGIN and END words.
You can access to the old value of a field (the value it has before the execution of the statement) and to the new value (the value it has after the execution of the statement. Example:
CREATE TRIGGER `use_values` AFTER INSERT ON `example_tab` FOR EACH ROW BEGIN UPDATE `changelog` SET `old_value`=OLD.`field1`, `new_value`=NEW.`field1` WHERE `backup_tab`.`id`=`example_tab`.`id` END
To DROP a trigger you can use the following syntax:
DROP TRIGGER `my_trigger`
DROP TRIGGER `my_database`.`my_trigger`
DROP TRIGGER IF EXISTS `my_trigger`
To alter an existing trigger, you must DROP and re-CREATE it.
This command returns the CREATE TRIGGER statement used to create the trigger and some information about the settings which may affect the statement.
SHOW CREATE TRIGGER delete_old;
This statement was added in MySQL 5.1.
If you want to have a list of all the triggers in the current database, you can type the following:
If you want to have a list of the triggers contained in another database, you can use:
SHOW TRIGGERS IN `my_db` SHOW TRIGGERS FROM `my_db` -- synonym
If you want to list the triggers whose name matches to a LIKE expression:
SHOW TRIGGERS FROM `my_db` LIKE 'my_%'
More complex filters:
SHOW TRIGGERS WHERE table='users'
You can't use LIKE and WHERE together.
The columns returned by this statement are:
The INFORMATION_SCHEMA virtual database has a `TRIGGERS` table. It has the following fields:
Events are also called Scheduled Events or Temporal Triggers. They are planned events which are executed at certain times, or at specified time intervals. They are similar to the UNIX crontab.
Once an Event is started, it must be completely executed. If it is re-activated before it ends its execution, a new instance of the same Event will be created. If this can happen, it may be a good idea to use LOCKs to assure data consistence.
The Event Scheduler is a thread which is permanently in execution. It starts the Events when they must be started. If you don't need Events, you can disable the Event Scheduler. You can do this starting MySQL with the following option:
Or you can add a line to the my.cnf configuration file:
If the Event Scheduler is not disabled, you will be able to turn it ON/OFF runtime. It is controlled by a global system variable:
SELECT event_scheduler -- values: ON / OFF / DISABLED SET GLOBAL event_scheduler = ON SET GLOBAL event_scheduler = OFF
If the Event Scheduler is ON, you can check its status with SHOW PROCESSLIST. It is shown like all other threads. Its `User` is 'event_scheduler'. When it is sleeping, the value for `State` is 'Waiting for next activation'.
You can use the SQL commands CREATE EVENT, ALTER EVENT and DROP EVENT.
The simplest case. We want a SQL command to be executed tomorrow:
CREATE EVENT `newevent` ON SCHEDULE AT CURRENT_TIMESTAMP + INTERVAL 1 DAY DO INSERT INTO `mydatabase`.`news` (`title`, `text`) VALUES ('Example!', 'This is not a reale news')
The event name must be specified after "EVENT".
If you want to create a task which will be executed only once at a certain time, you need the AT clause. If you don't want to specify an absolute time, but we want the task to be executed when a time interval is passed, "AT CURRENT_TIMESTAMP + INTERVAL ..." is a useful syntax.
If you want to create a recurring task (which will be executed at regular intervals) you need the EVERY clause:
CREATE EVENT `newevent2` ON SCHEDULE EVERY 2 DAY DO OPTIMIZE TABLE `mydatabase`.`news`
You can also specify a start time and/or an end time. The task will be executed at regular intervals from the start time until the end time:
CREATE EVENT `newevent2` ON SCHEDULE EVERY INTERVAL 1 DAY DO OPTIMIZE TABLE `mydatabase`.`news` STARTS CURRENT_TIMESTAMP + 1 MONTH ENDS CURRENT_TIMESTAMP + 3 MONTH
The allowed time units are:
YEAR, QUARTER, MONTH, WEEK, DAY, HOUR, MINUTE, SECOND, YEAR_MONTH, DAY_HOUR, DAY_MINUTE, DAY_SECOND, HOUR_MINUTE, HOUR_SECOND, MINUTE_SECOND
The DO clause specify which statement must be executed.
If a task is composed by more than 1 statement, the BEGIN ... END syntax must be used:
delimiter | CREATE EVENT `newevent` ON SCHEDULE EVERY 1 DAY DO BEGIN DELETE FROM `logs`.`user` WHERE `deletion_time` < CURRENT_TIMESTAMP - 1 YEAR; DELETE FROM `logs`.`messages` WHERE `deletion_time` < CURRENT_TIMESTAMP - 1 YEAR; UPDATE `logs`.`activity` SET `last_cleanup` = CURRENT_TIMESTAMP; END | delimiter ;
If an EVENT with the same name already exists you get an error from the server. To suppress the error, you can use the IF NOT EXISTS clause:
CREATE EVENT `newevent2` IF NOT EXISTS ON SCHEDULE EVERY 2 DAY DO OPTIMIZE TABLE `mydatabase`.`news`
After the EVENT is expired (when the timestamp specified in the AT clause or in the ENDS clause), MySQL drops the event by default, as it is no more useful. You may want to preserve it from deletion to ALTER it someday and activate it again, or just to have its code somewhere. You may do this with the ON COMPLETION clause:
CREATE EVENT `newevent2` ON SCHEDULE EVERY 2 DAY ON COMPLETION PRESERVE DO OPTIMIZE TABLE `mydatabase`.`news`
Or, you can explicitly tell MySQL to drop it, even if it's not necessary:
CREATE EVENT `newevent2` ON SCHEDULE EVERY 2 DAY ON COMPLETION NOT PRESERVE DO OPTIMIZE TABLE `mydatabase`.`news`
If you don't tell MySQL to preserve the EVENT after it's expired, but it is already expired immediatly after creation (which happens if you specify a past TIMESTAMP in the AT / ENDS clause), the server creates and drop it as you requested. However, in this case it will inform you returning a 1588 warning.
You can also specify if an EVENT must be enabled. This is done by specifying ENABLE, DISABLE or DISABLE ON SLAVES (used to execute the event on the master and not replacate it on the slaves). The EVENT is enabled by default.
CREATE EVENT `newevent2` ON SCHEDULE EVERY 2 DAY ON COMPLETION NOT PRESERVE DISABLE DO OPTIMIZE TABLE `mydatabase`.`news`
To modify this behaviour, you will use ALTER EVENT.
You can specify a comment for the EVENT. Comments have a 64 characters limit. The comment must be a literal, not an expression. Example:
CREATE EVENT `newevent2` ON SCHEDULE EVERY 2 DAY ON COMPLETION NOT PRESERVE DISABLE COMMENT 'let\'s optimize some tables!' DO OPTIMIZE TABLE `mydatabase`.`news`
You can also specify which user must be used to check privileges during the execution of the EVENT. By default, the CURRENT_USER is used. You can specify that explicitly:
CREATE DEFINER = CURRENT_USER EVENT `newevent2` ON SCHEDULE EVERY 2 DAY DO OPTIMIZE TABLE `mydatabase`.`news`
To specify a different user, you must have the SUPER privilege. In that case, you must specify both the username and the host:
CREATE DEFINER = 'allen@localhost' EVENT `newevent2` ON SCHEDULE EVERY 2 DAY DO OPTIMIZE TABLE `mydatabase`.`news`
The ALTER EVENT statement can be used to modify an existing EVENT.
CREATE EVENT `newevent2` ON SCHEDULE EVERY 2 DAY ON COMPLETION NOT PRESERVE RENAME TO `example_event` DISABLE COMMENT 'let\'s optimize some tables!' DO OPTIMIZE TABLE `mydatabase`.`news`
RENAME TO is used to rename the EVENT.
You only need to specify the clauses that you want to change:
CREATE EVENT `newevent2` ENABLE;
You need the EVENT privilege to drop an event. To drop an event you can type:
DROP EVENT `event_name`
If the EVENT does not exist, you get a 1517 error. To avoid this, you can use the IF EXISTS clause:
DROP EVENT IF EXISTS `event_name`
If the EVENT needs to be executed only once or just for a known time period, by default MySQL drops it automatically when it is expired (see the ON COMPLETE clause in CREATE EVENT).
This command returns the CREATE EVENT statement used to create the trigger and some information about the settings which may affect the statement.
SHOW CREATE EVENT newevent2;
The statement shows information about the EVENTs which are in the current database or in the specified database:
SHOW EVENTS SHOW EVENTS FROM `my_nice_db` SHOW EVENTS IN `my_nice_db` -- synonym SHOW EVENTS LIKE 'my_%' -- name starts with 'my_' SHOW EVENTS WHERE definer LIKE 'admin@%' -- filters on any field
The INFORMATION_SCHEMA virtual database has a `EVENTS` table. It's non-standard and has been added in 5.1. EVENTS has the following fields:
Stored Routines are modules written in SQL (with some procedural extensions) which may be called within another statement, using the CALL command.
Stored Routines are called FUNCTIONs if they return a result, or PROCEDUREs if they don't return anything. STORED PROCEDUREs must not be confused with the PROCEDUREs written in C or LUA which can be used in a SELECT statement; STORED FUNCTIONs must not be confused with UDF, even if they both are created with a CREATE FUNCTION statement.
CREATE DEFINER = `root`@`localhost` PROCEDURE `Module1` ( ) NOT DETERMINISTIC NO SQL SQL SECURITY DEFINER OPTIMIZE TABLE wiki1_page;
CALL `Module1` ;
DROP PROCEDURE `Module1` ;
DROP PROCEDURE `Module1` ; CREATE DEFINER = `root`@`localhost` PROCEDURE `Module1` ( ) NOT DETERMINISTIC NO SQL SQL SECURITY DEFINER BEGIN OPTIMIZE TABLE wiki1_page; OPTIMIZE TABLE wiki1_user; END
SHOW PROCEDURE STATUS;
SHOW CREATE PROCEDURE Module1;
The virtual database INFORMATION_SCHEMA has a table called `ROUTINES`, with the functions and procedures information.
This table contains all the stored functions values.
MySQL uses a character as delimiter - MySQL knows that where that character occurs a SQL statement ends and possibly another statement begins. That character is ';' by default. When you create a stored program which contains more than one statements, you enter only one statement: the CREATE command. However, it contains more then one statements in its body, separated with a ';'. In that case, you need to inform MySQL that ';' does not identify the end of the CREATE statement: you need another delimiter.
In the following example, '|' is used as a delimiter:
delimiter | CREATE EVENT myevent ON SCHEDULE EVERY 1 DAY DO BEGIN TRUNCATE `my_db`.`my_table`; TRUNCATE `my_db`.`another_table`; END delimiter ;
The keywords are:
IF, CASE, ITERATE, LEAVE LOOP, WHILE, REPEAT.
DELIMITER $$ CREATE PROCEDURE counter BEGIN DECLARE x INT; SET x = 1; WHILE x <= 5 DO SET x = x + 1; END WHILE; SELECT x; -- 6 END$$ DELIMITER ;
DELIMITER $$ CREATE PROCEDURE counter2 BEGIN DECLARE x INT; SET x = 1; boucle1: LOOP SET x = x + 1; IF x > 5 THEN LEAVE boucle1; END IF; END LOOP boucle1; SELECT x; -- 6 END$$ DELIMITER ;
DELIMITER $$ CREATE PROCEDURE counter3 BEGIN DECLARE x INT; SET x = 1; REPEAT SET x = x + 1; UNTIL x > 5 END REPEAT; SELECT x; -- 6 END$$ DELIMITER ;
The cursors allow to treat each row differently, but it considerably slows the queries.
DELIMITER $$ CREATE PROCEDURE cursor1 BEGIN DECLARE result varchar(100) DEFAULT ""; DECLARE c1 CURSOR FOR SELECT page_title FROM wiki1.wiki1_page WHERE page_namespace = 0; OPEN c1; FETCH c1 INTO result; CLOSE c1; SELECT result; END;$$ DELIMITER ;
They should be declared and open before the loop which should treat every records differently. To know the table end, we should create a handler after the cursor:
-- Concatenate all a table column values on a row DELIMITER $$ CREATE PROCEDURE cursor2 BEGIN DECLARE result varchar(100) DEFAULT ""; DECLARE total text DEFAULT ""; DECLARE done BOOLEAN DEFAULT 0; DECLARE c2 CURSOR FOR SELECT page_title FROM wiki1.wiki1_page WHERE page_namespace = 0; DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE; OPEN c2; REPEAT FETCH c2 INTO result; set total = concat(total, result); UNTIL done END REPEAT; CLOSE c2; SELECT total; END;$$ DELIMITER ;
A handler declaration permits to specify a treatment in case of error:
DECLARE CONTINUE HANDLER FOR SQLEXCEPTION
Moreover, the error type can be indicated:
DECLARE CONTINUE HANDLER FOR SQLSTATE [VALUE] sqlstate_value DECLARE CONTINUE HANDLER FOR SQLWARNING DECLARE CONTINUE HANDLER FOR NOT FOUND
Manage research, learning and skills at defaultLogic. Create an account using LinkedIn or facebook to manage and organize your Digital Marketing and Technology knowledge. defaultLogic works like a shopping cart for information -- helping you to save, discuss and share.Visit defaultLogic's partner sites below: