Beyond Simple SQL: MySQL Stored Procedures, Functions, and Triggers Optimization

1. Stored Procedure Writing and Debugging

A stored procedure is a pre‑compiled collection of SQL statements stored in the database and invoked as needed. Its advantages include reduced network traffic, code reuse, enhanced security, and higher execution efficiency.

Reduce network traffic

Improve code reuse

Enhance security

Increase execution efficiency

1.2 Basic Syntax

-- Create stored procedure
DELIMITER $$
CREATE PROCEDURE sp_get_user_by_id(
    IN user_id INT,               -- input parameter
    OUT user_name VARCHAR(50),    -- output parameter
    INOUT count INT               -- input‑output parameter
)
BEGIN
    -- Declare variables
    DECLARE temp_count INT DEFAULT 0;
    DECLARE exit_handler BOOLEAN DEFAULT FALSE;
    
    -- Exception handling
    DECLARE CONTINUE HANDLER FOR SQLEXCEPTION
    BEGIN
        SET exit_handler = TRUE;
        ROLLBACK;
    END;
    
    -- Start transaction
    START TRANSACTION;
    
    -- Business logic
    SELECT COUNT(*) INTO temp_count FROM users WHERE id = user_id;
    
    IF temp_count > 0 THEN
        SELECT name INTO user_name FROM users WHERE id = user_id;
        SET count = count + temp_count;
    ELSE
        SET user_name = 'User not found';
    END IF;
    
    -- Conditional logic
    CASE
        WHEN temp_count = 1 THEN SET @message = 'Found 1 user';
        WHEN temp_count > 1 THEN SET @message = 'Found multiple users';
        ELSE SET @message = 'No user found';
    END CASE;
    
    -- Loop example
    WHILE temp_count > 0 DO
        SET temp_count = temp_count - 1;
        -- Loop body
    END WHILE;
    
    -- Cursor example (row‑by‑row processing)
    DECLARE done INT DEFAULT FALSE;
    DECLARE cur_id INT;
    DECLARE cur_name VARCHAR(50);
    DECLARE user_cursor CURSOR FOR SELECT id, name FROM users WHERE status = 1;
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;
    
    OPEN user_cursor;
    read_loop: LOOP
        FETCH user_cursor INTO cur_id, cur_name;
        IF done THEN LEAVE read_loop; END IF;
        -- Process each row
    END LOOP;
    CLOSE user_cursor;
    
    -- Commit transaction if no error
    IF NOT exit_handler THEN COMMIT; END IF;
END$$
DELIMITER ;

1.3 Calling a Stored Procedure

SET @my_count = 0;
CALL sp_get_user_by_id(1, @user_name, @my_count);
SELECT @user_name, @my_count;

-- List all stored procedures
SHOW PROCEDURE STATUS;

-- Show definition of a procedure
SHOW CREATE PROCEDURE sp_get_user_by_id;

1.4 Debugging Techniques

Method 1: Use SELECT to output debug information

CREATE PROCEDURE sp_debug_demo()
BEGIN
    DECLARE debug_msg VARCHAR(100);
    SET debug_msg = 'Start execution';
    SELECT debug_msg AS 'Debug Info';
    -- Business logic ...
    SET debug_msg = 'Execution completed';
    SELECT debug_msg AS 'Debug Info';
END;

Method 2: Create a debug log table

-- Create debug log table
CREATE TABLE debug_log (
    id INT AUTO_INCREMENT PRIMARY KEY,
    proc_name VARCHAR(50),
    log_time DATETIME DEFAULT CURRENT_TIMESTAMP,
    message TEXT
);

-- Insert log entries inside a procedure
CREATE PROCEDURE sp_with_logging()
BEGIN
    INSERT INTO debug_log (proc_name, message) VALUES ('sp_with_logging', 'Start execution');
    -- Business logic ...
    INSERT INTO debug_log (proc_name, message) VALUES ('sp_with_logging', 'Execution completed');
END;

2. Custom Function Creation

2.1 Difference Between Functions and Stored Procedures

2.2 Creating Custom Functions

DELIMITER $$
-- Calculate user age
CREATE FUNCTION fn_get_user_age(birth_date DATE)
RETURNS INT DETERMINISTIC READS SQL DATA
BEGIN
    DECLARE age INT;
    SET age = YEAR(CURDATE()) - YEAR(birth_date);
    -- Adjust if birthday hasn't occurred yet
    IF MONTH(CURDATE()) < MONTH(birth_date) OR
       (MONTH(CURDATE()) = MONTH(birth_date) AND DAY(CURDATE()) < DAY(birth_date)) THEN
        SET age = age - 1;
    END IF;
    RETURN age;
END$$

-- Calculate discounted price
CREATE FUNCTION fn_get_discounted_price(
    original_price DECIMAL(10,2),
    discount_rate DECIMAL(5,2)
)
RETURNS DECIMAL(10,2) DETERMINISTIC
BEGIN
    DECLARE discounted_price DECIMAL(10,2);
    -- Parameter validation
    IF original_price <= 0 THEN
        SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Price must be greater than 0';
    END IF;
    IF discount_rate < 0 OR discount_rate > 1 THEN
        SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Discount rate must be between 0 and 1';
    END IF;
    SET discounted_price = original_price * (1 - discount_rate);
    RETURN ROUND(discounted_price, 2);
END$$
DELIMITER ;

2.3 Using Custom Functions

-- Use in SELECT
SELECT name, birth_date, fn_get_user_age(birth_date) AS age FROM users;

-- Use in WHERE clause
SELECT * FROM orders WHERE fn_get_discounted_price(price, 0.1) > 100;

-- Use in UPDATE statement
UPDATE products SET sale_price = fn_get_discounted_price(price, 0.2) WHERE category_id = 1;

3. Trigger Application Scenarios and Pitfalls

3.1 Trigger Basics

-- Create trigger syntax
CREATE TRIGGER trigger_name
{ BEFORE | AFTER } { INSERT | UPDATE | DELETE }
ON table_name
FOR EACH ROW
BEGIN
    -- Trigger logic
END;

3.2 Real‑World Use Cases

Scenario 1: Audit Log

-- Create audit table
CREATE TABLE user_audit (
    id INT AUTO_INCREMENT PRIMARY KEY,
    user_id INT,
    action VARCHAR(10),
    old_data JSON,
    new_data JSON,
    changed_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    changed_by VARCHAR(50)
);

-- Trigger for updates on users table
DELIMITER $$
CREATE TRIGGER trg_users_audit_update
AFTER UPDATE ON users
FOR EACH ROW
BEGIN
    INSERT INTO user_audit (user_id, action, old_data, new_data, changed_by)
    VALUES (
        NEW.id,
        'UPDATE',
        JSON_OBJECT('name', OLD.name, 'email', OLD.email, 'status', OLD.status),
        JSON_OBJECT('name', NEW.name, 'email', NEW.email, 'status', NEW.status),
        CURRENT_USER()
    );
END$$
DELIMITER ;

Scenario 2: Data Integrity Maintenance

-- Maintain total order amount
DELIMITER $$
CREATE TRIGGER trg_order_items_update
AFTER INSERT ON order_items
FOR EACH ROW
BEGIN
    DECLARE total_amount DECIMAL(10,2);
    SELECT SUM(price * quantity) INTO total_amount FROM order_items WHERE order_id = NEW.order_id;
    UPDATE orders SET total_amount = total_amount WHERE id = NEW.order_id;
END$$
DELIMITER ;

Scenario 3: Derived Column Calculation

-- Auto‑calculate employee full name
DELIMITER $$
CREATE TRIGGER trg_employees_name
BEFORE INSERT ON employees
FOR EACH ROW
BEGIN
    SET NEW.full_name = CONCAT(NEW.first_name, ' ', NEW.last_name);
    SET NEW.created_at = NOW();
END$$

CREATE TRIGGER trg_employees_name_update
BEFORE UPDATE ON employees
FOR EACH ROW
BEGIN
    SET NEW.full_name = CONCAT(NEW.first_name, ' ', NEW.last_name);
    SET NEW.updated_at = NOW();
END$$
DELIMITER ;

4. Best‑Practice Summary

4.1 Stored Procedure Best Practices

Naming convention : use a uniform prefix (sp_, usp_)

Parameter validation : always validate input parameters

Error handling : implement robust exception handling

Transaction management : control transaction boundaries wisely

Documentation : add detailed usage comments

4.2 Function Best Practices

Keep pure : avoid side effects

Performance considerations : avoid complex queries inside functions

Determinism : be careful with the DETERMINISTIC attribute

Input validation : prevent illegal inputs

4.3 Trigger Best Practices

Keep simple : trigger logic should be as simple as possible

Avoid recursion : watch out for mutual trigger calls

Performance monitoring : regularly check trigger impact

Have alternatives : consider whether the same goal can be achieved without a trigger

4.4 Debugging and Maintenance

-- Debug tool stored procedure
CREATE PROCEDURE sp_debug_trigger_info()
BEGIN
    -- List all triggers
    SELECT trigger_name, event_object_table, action_timing, event_manipulation, action_statement
    FROM information_schema.triggers
    WHERE trigger_schema = DATABASE();
    
    -- List stored procedures
    SHOW PROCEDURE STATUS WHERE Db = DATABASE();
    
    -- List functions
    SHOW FUNCTION STATUS WHERE Db = DATABASE();
END;

To master MySQL stored procedures, functions, and triggers, follow these steps: understand basic syntax, practice each example in a test environment, analyze real business scenarios to design appropriate objects, monitor performance and optimize regularly, and maintain thorough documentation and version control.