Stored Functions in MySQL

What Is a MySQL Stored Function?

A stored function is a named stored routine that accepts input values and returns one value. It is invoked as part of an SQL expression:

SELECT service_fee(1000.00);
SELECT order_id, service_fee(total_amount)
FROM orders_func_lab;

A function must declare a return type with RETURNS, and its body must return a value using RETURN. Function parameters do not use IN, OUT or INOUT mode keywords; they are input parameters.

Verified Orders Lab

DROP TABLE IF EXISTS orders_func_lab;
CREATE TABLE orders_func_lab (
  order_id INT PRIMARY KEY,
  customer_id INT NOT NULL,
  status VARCHAR(20) NOT NULL,
  order_date DATE NOT NULL,
  total_amount DECIMAL(10,2) NOT NULL
) ENGINE = InnoDB;

INSERT INTO orders_func_lab VALUES
(1,101,'PAID','2026-08-01',1200.00),
(2,101,'PENDING','2026-08-05',500.00),
(3,102,'PAID','2026-08-03',750.00),
(4,103,'CANCELLED','2026-08-04',300.00),
(5,101,'PAID','2026-08-10',1500.00),
(6,102,'PENDING','2026-08-11',900.00),
(7,104,'PAID','2026-08-12',2200.00),
(8,101,'PAID','2026-08-14',650.00);

The first function is pure arithmetic and easy to verify. A later function reads this table to demonstrate different characteristics and performance consequences.

Create, RETURN and Use a Scalar Function

DELIMITER //

CREATE FUNCTION service_fee(
  p_amount DECIMAL(10,2)
)
RETURNS DECIMAL(10,2)
DETERMINISTIC
NO SQL
BEGIN
  IF p_amount IS NULL OR p_amount < 0 THEN
    RETURN NULL;
  ELSEIF p_amount >= 1000 THEN
    RETURN ROUND(p_amount * 0.02, 2);
  ELSE
    RETURN ROUND(p_amount * 0.03, 2);
  END IF;
END //

DELIMITER ;

SELECT service_fee(1500.00) AS fee;

SELECT order_id, total_amount,
       service_fee(total_amount) AS fee
FROM orders_func_lab
WHERE status = 'PAID'
ORDER BY order_id;

DETERMINISTIC NO SQL is truthful here: the calculation uses only its input and no SQL data access. The function handles invalid or NULL amounts deliberately.

Declare Routine Characteristics Truthfully

These are declarations, not automatic enforcement of truth. MySQL does not inspect the entire routine to prove that a declared deterministic function is genuinely deterministic.

-- Nondeterministic examples:
RETURN UUID();
RETURN RAND();
RETURN NOW();

Do not label these deterministic. Also be careful with table-reading functions: even when the input is unchanged, underlying table contents can change.

A Stored Function That Reads Table Data

DELIMITER //

CREATE FUNCTION customer_paid_total(
  p_customer_id INT
)
RETURNS DECIMAL(12,2)
NOT DETERMINISTIC
READS SQL DATA
SQL SECURITY INVOKER
BEGIN
  DECLARE v_total DECIMAL(12,2);

  SELECT COALESCE(SUM(total_amount), 0)
  INTO v_total
  FROM orders_func_lab
  WHERE customer_id = p_customer_id
    AND status = 'PAID';

  RETURN v_total;
END //

DELIMITER ;

SELECT customer_paid_total(101) AS paid_total;

The function is marked NOT DETERMINISTIC because table data may change. INVOKER means the caller needs the underlying access required by the routine. With DEFINER, underlying checks use the definer account.

Stored Function vs Procedure vs Built-in Expression

-- Procedure
CALL customer_paid_summary(101, @count, @total);

-- Function
SELECT customer_paid_total(101);

-- Set-based alternative for all customers
SELECT customer_id, SUM(total_amount) AS paid_total
FROM orders_func_lab
WHERE status = 'PAID'
GROUP BY customer_id;

A function is syntactically convenient inside SELECT, but convenience can hide repeated work. Measure the expanded workload.

Privileges, Security Context and Binary Logging

• CREATE ROUTINE is normally required to create stored functions.
• EXECUTE is required to invoke them, subject to grants and configuration.
• SQL SECURITY DEFINER is default; INVOKER uses caller privileges.
• Use a durable least-privileged definer and validate function inputs.
• With binary logging enabled, function creation can require explicit safety characteristics and additional privileges or trusted-creator policy.

MySQL may reject a function that declares none of DETERMINISTIC, NO SQL or READS SQL DATA while binary logging protections apply. The correct fix is not to add a false keyword. Declare actual behavior and follow the server's replication/security policy.

Inspect, Test, Optimize and Drop

SHOW CREATE FUNCTION service_fee;
SHOW FUNCTION STATUS WHERE Db = DATABASE();

SELECT ROUTINE_NAME, IS_DETERMINISTIC,
       SQL_DATA_ACCESS, SECURITY_TYPE, DATA_TYPE
FROM information_schema.ROUTINES
WHERE ROUTINE_SCHEMA = DATABASE()
  AND ROUTINE_TYPE = 'FUNCTION';

DROP FUNCTION IF EXISTS customer_paid_total;

1. Test normal, boundary, NULL, negative and maximum values.
2. Verify the returned type, scale, character set and collation.
3. Declare determinism/data access honestly.
4. Prefer NO SQL functions for simple calculations.
5. Index predicates used inside table-reading functions.
6. Avoid per-row table-reading function calls over large result sets.
7. Review DEFINER/INVOKER and EXECUTE privileges.
8. Test replication and restore behavior where binary logging is used.
9. Keep DROP/CREATE definitions in source-controlled migrations.
10. Measure queries that invoke the function; stored code is not free.

Compare with stored procedures, review query optimization and continue to MySQL triggers.

Official References

• MySQL 8.4: CREATE PROCEDURE and FUNCTION
• MySQL 8.4: Stored Program Binary Logging
• MySQL 8.4: Stored Program Restrictions

Return syntax, characteristics, security, invocation and binary-log requirements were checked against the official MySQL 8.4 manual.