MySQL Procedures, Triggers, and Events Cheatsheet

DELIMITER $$
CREATE PROCEDURE get_user_by_email(IN p_email VARCHAR(255))
BEGIN
  SELECT id, email, full_name
  FROM users
  WHERE email = p_email;
END $$
DELIMITER ;

# Define routines containing semicolons.

CALL get_user_by_email('ava@example.com');

# Execute a procedure with input arguments.

DELIMITER $$
CREATE FUNCTION full_label(p_first VARCHAR(100), p_last VARCHAR(100))
RETURNS VARCHAR(255)
DETERMINISTIC
BEGIN
  RETURN CONCAT(p_first, ' ', p_last);
END $$
DELIMITER ;

# Return a computed value from SQL code.

SHOW PROCEDURE STATUS WHERE Db = 'app_db';

# Display procedures visible in a schema.

SHOW CREATE PROCEDURE get_user_by_email\G

# Display the CREATE PROCEDURE statement for an existing routine.

DELIMITER $$
CREATE TRIGGER trg_users_before_insert
BEFORE INSERT ON users
FOR EACH ROW
BEGIN
  SET NEW.email = LOWER(NEW.email);
END $$
DELIMITER ;

# Normalize row data before insert.

SHOW TRIGGERS;

# Display triggers defined in the current schema.

CREATE EVENT ev_purge_expired_sessions
ON SCHEDULE EVERY 1 DAY
DO
  DELETE FROM sessions WHERE expires_at < NOW();

# Run a periodic cleanup task inside MySQL.

SHOW EVENTS FROM app_db;

# Inspect scheduled events.

DROP TRIGGER IF EXISTS trg_users_before_insert;

# Remove a trigger definition.