Stored Functions and UDFs

Stored Functions vs Procedures link

Stored functions return a single scalar value and can be used in SQL expressions. Stored procedures perform actions and are invoked with CALL. Functions must be deterministic when declared as such and cannot modify data (no INSERT, UPDATE, DELETE inside a function body in strict mode).

  DELIMITER //
CREATE FUNCTION calculate_discount(
    p_amount DECIMAL(10,2),
    p_tier VARCHAR(20)
) RETURNS DECIMAL(10,2)
DETERMINISTIC
READS SQL DATA
BEGIN
    DECLARE discount DECIMAL(10,2) DEFAULT 0;
    IF p_tier = 'gold' THEN
        SET discount = p_amount * 0.15;
    ELSEIF p_tier = 'silver' THEN
        SET discount = p_amount * 0.10;
    ELSE
        SET discount = p_amount * 0.05;
    END IF;
    RETURN discount;
END //
DELIMITER ;

SELECT id, total, calculate_discount(total, tier) AS discount
FROM orders WHERE status = 'pending';
  

Function Characteristics link

MySQL tracks function metadata that affects replication and indexing:

  -- Non-deterministic function (uses current time)
DELIMITER //
CREATE FUNCTION is_business_hours() RETURNS TINYINT
NOT DETERMINISTIC
NO SQL
BEGIN
    DECLARE hour_val INT;
    SET hour_val = HOUR(CONVERT_TZ(NOW(), 'UTC', 'America/New_York'));
    RETURN IF(hour_val BETWEEN 9 AND 17, 1, 0);
END //
DELIMITER ;
  

Input Validation and Error Handling link

Always validate inputs at the function boundary to prevent silent bad data propagation.

  DELIMITER //
CREATE FUNCTION safe_divide(a DECIMAL(18,6), b DECIMAL(18,6))
RETURNS DECIMAL(18,6)
DETERMINISTIC
NO SQL
BEGIN
    IF b = 0 THEN
        SIGNAL SQLSTATE '45000'
            SET MESSAGE_TEXT = 'Division by zero in safe_divide';
    END IF;
    RETURN a / b;
END //
DELIMITER ;
  

Use SIGNAL for explicit errors instead of returning NULL when the caller cannot distinguish “no result” from “error.”

Table-Valued Logic with Functions link

Functions cannot return result sets, but they can encapsulate lookups used in WHERE, SELECT, and ORDER BY:

  DELIMITER //
CREATE FUNCTION customer_lifetime_value(p_user_id INT)
RETURNS DECIMAL(12,2)
READS SQL DATA
BEGIN
    DECLARE ltv DECIMAL(12,2);
    SELECT COALESCE(SUM(total), 0) INTO ltv
    FROM orders
    WHERE user_id = p_user_id AND status = 'completed';
    RETURN ltv;
END //
DELIMITER ;

SELECT u.name, customer_lifetime_value(u.id) AS ltv
FROM users u
WHERE customer_lifetime_value(u.id) > 1000;
  

Performance warning: calling a function per row in a large scan prevents index use on the outer query. For heavy analytics, prefer a JOIN or subquery.

User-Defined Functions (UDFs) link

UDFs are compiled shared libraries (.so on Linux) loaded into the server. They extend MySQL beyond SQL:

  CREATE FUNCTION metaphon RETURNS STRING SONAME 'udf_example.so';
SELECT metaphon('Smith');
  

UDFs require C/C++ development, strict memory management, and version compatibility testing. Most teams prefer stored functions or application-layer logic unless performance demands native code.

Deployment and Versioning link

Track function definitions in migration scripts:

  DROP FUNCTION IF EXISTS calculate_discount;
-- Recreate with updated logic
DELIMITER //
CREATE FUNCTION calculate_discount(...) ...
  

Use SHOW CREATE FUNCTION calculate_discount\G to export definitions. Store in Git alongside application migrations.

Binary Logging and Replication link

Non-deterministic functions in replicated environments can cause replica drift if statement-based replication is used. With binlog_format = ROW (recommended), function results are logged as row changes, avoiding most issues.

  [mysqld]
binlog_format = ROW
log_bin_trust_function_creators = 1
  

Set log_bin_trust_function_creators = 1 only when you trust function authors — it allows non-superusers to create functions that write to binary logs.

Best Practices link

1. Keep functions small and pure — complex business logic belongs in application code for testability
2. Mark DETERMINISTIC only when mathematically true
3. Avoid functions in WHERE clauses on large tables — precompute or JOIN instead
4. Document expected input ranges and return semantics
5. Test with EXPLAIN to verify the outer query still uses indexes

Common Mistakes link

Troubleshooting link

  -- Check if function exists
SHOW FUNCTION STATUS WHERE Db = 'myapp';

-- Permission errors
GRANT CREATE ROUTINE ON myapp.* TO 'dev_user'@'%';

-- Function creation blocked by binary logging
SHOW VARIABLES LIKE 'log_bin_trust_function_creators';
  

Error: “This function has none of DETERMINISTIC, NO SQL, or READS SQL DATA” — add the appropriate characteristic clause to the CREATE FUNCTION statement.

Production Scenario: Pricing Engine link

An e-commerce platform centralizes tiered pricing in a function used by reporting and batch jobs:

  SELECT
    o.id,
    o.total,
    calculate_discount(o.total, u.tier) AS discount,
    o.total - calculate_discount(o.total, u.tier) AS net_total
FROM orders o
JOIN users u ON o.user_id = u.id
WHERE o.created_at >= CURDATE() - INTERVAL 7 DAY;
  

For the OLTP checkout path, the same logic runs in application code (cached config). The function serves BI dashboards and ad-hoc analyst queries without duplicating SQL in multiple reports.

Performance Tips link

• Cache expensive function results in application memory when inputs repeat
• Use generated columns instead of functions when the computation is row-local and indexable:

  ALTER TABLE orders
ADD COLUMN net_total DECIMAL(10,2)
    AS (total - calculate_discount(total, 'standard')) STORED,
ADD INDEX idx_net_total (net_total);
  

Stored functions are powerful for encapsulating reusable SQL logic — use them where clarity wins, and avoid them where they become hidden performance traps.