MySQL/Stored Programs

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:

Triggers - programs which are triggered before / after a certain event involves a table (DELETE, INSERT, UPDATE);
Events - programs which are executed regularly after some time intervals;
Stored Procedures - programs which can be called via the CALL SQL command.

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

Managing Triggers

Triggers were added in MySQL 5.0.2. They work on persistent tables, but can't be associated with TEMPORARY tables.

CREATE TRIGGER

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:

INSERT, which includes LOAD DATA and REPLACE
DELETE, which includes REPLACE, but not TRUNCATE
UPDATE

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

DROP TRIGGER

To DROP a trigger you can use the following syntax:

 DROP TRIGGER `my_trigger`

Or:

 DROP TRIGGER `my_database`.`my_trigger`

Or:

 DROP TRIGGER IF EXISTS `my_trigger`

To alter an existing trigger, you must DROP and re-CREATE it.

Metadata

SHOW CREATE TRIGGER

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;

Trigger - Trigger name
sql_mode - The value of SQL_MODE at the time of the execution of the statement
SQL Original Statement
character_set_client
collation_connection
Database Collation

This statement was added in MySQL 5.1.

SHOW TRIGGERS

If you want to have a list of all the triggers in the current database, you can type the following:

 SHOW TRIGGERS

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:

Trigger - Trigger's name
Event - The SQL command that fires the trigger
Table - The table that is associated to the trigger
Statement - The statement that is executed by the trigger
Timing - BEFORE or AFTER
Created - It's always NULL
sql_mode - The SQL_MODE which was set when the trigger was created
Definer - The user who created the trigger
character_set_client - The value of the `character_set_client` variable when the trigger was created
collation_connection - The value of the `collation_connection` variable when the trigger was created
Database Collation - The COLLATION used by the database (and the trigger)

INFORMATION_SCHEMA.TRIGGERS

The INFORMATION_SCHEMA virtual database has a `TRIGGERS` table. It has the following fields:

TRIGGER_CATALOG - What catalog contains the trigger (not implemented yet)
TRIGGER_SCHEMA - What SCHEMA (DATABASE) contains the trigger
TRIGGER_NAME - Trigger's name
EVENT_MANIPULATION - INSERT / UPDATE /DELETE
EVENT_OBJECT_CATALOG - Not implemented yet
EVENT_OBJECT_SCHEMA - SCHEMA containing the table associated to the trigger
EVENT_OBJECT_NAME - Name of the table associated to the trigger
ACTION_ORDER - Not implemented yet
ACTION_CONDITION - Not implemented yet
ACTION_STATEMENT - Statement(s) to be executed when trigger activates
ACTION_ORIENTATION - Not implemented yet
ACTION_TIMING - BEFORE / AFTER
ACTION_REFERENCE_OLD_TABLE - Not implemented
ACTION_REFERENCE_NEW_TABLE - Not implemented
ACTION_REFERENCE_OLD_ROW - Not implemented
ACTION_REFERENCE_NEW_ROW - Not implemented
CREATED - Creation time (not implemented yet)
SQL_MODE - SQL_MODE valid for this trigger's execution
DEFINER - User who created the trigger, in the form 'user@host'
CHARACTER_SET_CLIENT - The value of the `character_set_client` variable when the trigger was created
COLLATION_CONNECTION - The value of the `collation_connection` variable when the trigger was created
DATABASE_COLLATION - The COLLATION used by the database (and the trigger)

Events

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:

mysqld --event-scheduler=DISABLED

Or you can add a line to the my.cnf configuration file:

event_scheduler=DISABLED

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'.

Managing Events

You can use the SQL commands CREATE EVENT, ALTER EVENT and DROP EVENT.

CREATE 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`

ALTER EVENT

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;

DROP EVENT

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).

Metadata

SHOW 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.

Syntax:

 SHOW CREATE EVENT newevent2;

Event - Event name.
sql_mode - SQL mode which was in effect when the CREATE EVENT statement was executed.
time_zone - Time zone that was used when the statement was executed.
Create Event - Statement used to create the event.
character_set_client
collation_connection
Database Collation

SHOW EVENTS

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

Db Database name.
Name Event name.
Definer User which created the EVENT and the host he used, in the form user@host.
Time zone Timezone in use for the EVENT. If it never changed, it should be 'SYSTEM', which means: server's timezone.
Type 'ONE TIME' for EVENTs which are executed only once, 'RECURRING' for EVENTs which are executed regularly.
Executed At The TIMESTAMP of the moment the EVENT will be executed. NULL for recursive EVENTs.
Interval Value Number of intervals between EVENT's executions. See next field. NULL for EVENTs which are executed only once.
Interval Field Interval type to wait between EVENTs executions. For example, if `Interval Field` is 'SECOND' and `Interval Value` is 30, the EVENT will be executed every 30 seconds. NULL for EVENTs which are executed only once.
Starts First execution DATETIME for recurring EVENTs. NULL for events which are executed only once.
Ends Last execution DATETIME for recurring EVENTs. NULL for events which are executed only once.
Status ENABLED, DISABLED, or SLAVESIDE_DISABLED. For ENABLED and DISABLED, see above. SLAVESIDE_DISABLED was added in 5.1 and means that the EVENT is enabled on the master but disabled on the slaves.
Originator Id of the server where the EVENT was created. If it has been created on the current server this value is 0. Added in 5.1.
character_set_client
collation_connection
Database Collation

INFORMATION_SCHEMA.EVENTS

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:

EVENT_CATALOG Always NULL (CATALOGs are not implemented in MySQL).
EVENT_SCHEMA Database name.
EVENT_NAME Event name.
DEFINER User which created the EVENT and the host he used, in the form user@host.
TIME_ZONE Timezone in use for the EVENT. If it never changed, it should be 'SYSTEM', which means: server's timezone.
EVENT_BODY Language used to write the routine that will be executed.
EVENT_DEFINITION Routine that will be executed.
EVENT_TYPE 'ONE TIME' for EVENTs which are executed only once, 'RECURRING' for EVENTs which are executed regularly.
EXECUTE_AT The TIMESTAMP of the moment the EVENT will be executed. NULL for recursive EVENTs.
INTERVAL_VALUE Number of intervals between EVENT's executions. See next field. NULL for EVENTs which are executed only once.
INTERVAL_FIELD Interval type to wait between EVENTs executions. For example, if `Interval Field` is 'SECOND' and `Interval Value` is 30, the EVENT will be executed every 30 seconds. NULL for EVENTs which are executed only once.
SQL_MODE SQL mode which was in effect when the EVENT has been created.
STARTS First execution DATETIME for recurring EVENTs. NULL for events which are executed only once.
ENDS Last execution DATETIME for recurring EVENTs. NULL for events which are executed only once.
STATUS ENABLED, DISABLED, or SLAVESIDE_DISABLED. For ENABLED and DISABLED, see above. SLAVESIDE_DISABLED was added in 5.1 and means that the EVENT is enabled on the master but disabled on the slaves.
ON_COMPLETION 'NOT PRESERVE' (the EVENT will be deleted) or 'PRESERVE' (the EVENT won't be deleted'.
CREATED Creation DATETIME.
LAST_ALTERED Last edit's DATETIME. If the EVENT has never been altered, `LAST_ALTERED` has the same value as `CREATED`.
LAST_EXECUTED Last execution TIMESTAMP. If the EVENT has never been executed yet, this value is NULL.
EVENT_COMMENT Comment associated to the EVENT. Is there is no comment, this value is an empty string.
ORIGINATOR Id of the server where the EVENT was created. If it has been created on the current server this value is 0. Added in 5.1.
character_set_client
collation_connection
Database Collation

Stored Routines

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.

Advantages of Stored Routines

They reduce network traffic: they may contain many statements, but only one statement need to be sent to invoke them.
Ability to keep the logic within the database.
Reusable modules which can be called from external programs, no matter in what language they are written.
You can modify the Stored Routines without changing your programs.
The user which invokes a Stored Routine doesn't need to have access to the tables which it reads / writes.
Calling Stored Routines are faster than executing single statements.

Managing Stored Routines

CREATE PROCEDURE

 CREATE DEFINER = `root`@`localhost` PROCEDURE `Module1` ( ) NOT DETERMINISTIC NO SQL SQL SECURITY DEFINER OPTIMIZE TABLE wiki1_page;

CALL

 CALL `Module1` ();

DROP PROCEDURE

 DROP PROCEDURE `Module1` ;

Modification

 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

Metadata

SHOW FUNCTION / PROCEDURE STATUS

 SHOW PROCEDURE STATUS;

SHOW CREATE FUNCTION / PROCEDURE

 SHOW CREATE PROCEDURE Module1;

INFORMATION_SCHEMA.ROUTINES

The virtual database INFORMATION_SCHEMA has a table called `ROUTINES`, with the functions and procedures information.

INFORMATION_SCHEMA.PARAMETERS

This table contains all the stored functions values.

Procedural extensions to standard SQL

Delimiter

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 ;

Flow control

The keywords are: IF, CASE, ITERATE, LEAVE LOOP, WHILE, REPEAT^[1].

Loops

WHILE

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 ;

LOOP

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 ;

REPEAT

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 ;

Cursors

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 ;

Error handling

A handler declaration permits to specify a treatment in case of error^[2]:

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

References

[1] ttp://dev.mysql.com/doc/refman/5.0/en/flow-control-statements.html

[2] ttp://dev.mysql.com/doc/refman/5.7/en/declare-handler.html

[1]

[2]