Stored Functions — MySQL

MySQL Stored Functions

A stored function is similar to a stored procedure but is designed to compute and return a single value. You call a stored function directly inside SQL expressions — in SELECT, WHERE, ORDER BY, and SET clauses — just like MySQL's built-in functions such as UPPER(), DATE_FORMAT(), or ROUND().

Function vs Procedure — Key Differences

Feature	Function	Procedure
Returns	Exactly one value via RETURN	Zero or more result sets via OUT params or SELECT
Called with	Used inside SQL expressions	Called with CALL statement
Use in SELECT	Yes — SELECT my_func(col)	No
Transactions	Cannot COMMIT or ROLLBACK (by default)	Can manage transactions
Side effects	Generally pure / read-only	Can INSERT, UPDATE, DELETE

CREATE FUNCTION Syntax

SQL

DELIMITER //

CREATE FUNCTION function_name(param1 TYPE, param2 TYPE)
RETURNS return_type
[DETERMINISTIC | NOT DETERMINISTIC]
[CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA]
BEGIN
  -- function body
  RETURN some_value;
END //

DELIMITER ;

Characteristics Clauses

MySQL requires at least one of these characteristic declarations when binary logging is enabled:

Clause	Meaning
DETERMINISTIC	Same inputs always produce the same output — safe for query caching and replication
NOT DETERMINISTIC	Output may vary for same inputs (e.g. uses NOW() or RAND())
CONTAINS SQL	Function body contains SQL but does not read or write data
NO SQL	Function body contains no SQL statements at all
READS SQL DATA	Function reads data with SELECT but does not modify data
MODIFIES SQL DATA	Function contains INSERT, UPDATE, or DELETE

Note

If you get the error "This function has none of DETERMINISTIC, NO SQL, or READS SQL DATA", add the appropriate clause. For pure calculations, use DETERMINISTIC. For functions that SELECT data, use READS SQL DATA.

Simple Calculation Function

SQL

DELIMITER //

CREATE FUNCTION calculate_tax(
  p_amount   DECIMAL(10,2),
  p_tax_rate DECIMAL(5,4)
)
RETURNS DECIMAL(10,2)
DETERMINISTIC
NO SQL
BEGIN
  RETURN ROUND(p_amount * p_tax_rate, 2);
END //

DELIMITER ;

-- Use directly in SQL
SELECT
  product_name,
  price,
  calculate_tax(price, 0.0875) AS tax,
  price + calculate_tax(price, 0.0875) AS total_with_tax
FROM products
WHERE category = 'electronics';

String Manipulation Function

SQL

DELIMITER //

-- Format a full name consistently
CREATE FUNCTION format_full_name(
  p_first  VARCHAR(100),
  p_middle VARCHAR(100),
  p_last   VARCHAR(100)
)
RETURNS VARCHAR(305)
DETERMINISTIC
NO SQL
BEGIN
  DECLARE v_name VARCHAR(305);

  IF p_middle IS NULL OR p_middle = '' THEN
    SET v_name = CONCAT(p_first, ' ', p_last);
  ELSE
    SET v_name = CONCAT(p_first, ' ', p_middle, ' ', p_last);
  END IF;

  -- Title-case: first letter upper, rest lower
  RETURN CONCAT(
    UPPER(LEFT(v_name, 1)),
    LOWER(SUBSTRING(v_name, 2))
  );
END //

-- Slug generator for URLs
CREATE FUNCTION to_slug(p_input VARCHAR(255))
RETURNS VARCHAR(255)
DETERMINISTIC
NO SQL
BEGIN
  DECLARE v_slug VARCHAR(255);
  SET v_slug = LOWER(TRIM(p_input));
  SET v_slug = REPLACE(v_slug, ' ', '-');
  SET v_slug = REPLACE(v_slug, '_', '-');
  -- Remove characters that are not alphanumeric or hyphens
  -- (simplified — full implementation uses REGEXP_REPLACE in MySQL 8)
  SET v_slug = REGEXP_REPLACE(v_slug, '[^a-z0-9-]', '');
  SET v_slug = REGEXP_REPLACE(v_slug, '-+', '-');
  RETURN v_slug;
END //

DELIMITER ;

SELECT format_full_name('john', NULL, 'doe');   -- 'John doe'
SELECT to_slug('Hello World! This is a Test');   -- 'hello-world-this-is-a-test'

Function that Reads SQL Data

SQL

DELIMITER //

CREATE FUNCTION get_customer_tier(p_customer_id INT)
RETURNS VARCHAR(20)
READS SQL DATA
DETERMINISTIC
BEGIN
  DECLARE v_total DECIMAL(10,2);
  DECLARE v_tier  VARCHAR(20);

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

  IF    v_total = 0      THEN SET v_tier = 'New';
  ELSEIF v_total < 500   THEN SET v_tier = 'Bronze';
  ELSEIF v_total < 2000  THEN SET v_tier = 'Silver';
  ELSEIF v_total < 10000 THEN SET v_tier = 'Gold';
  ELSE                        SET v_tier = 'Platinum';
  END IF;

  RETURN v_tier;
END //

DELIMITER ;

-- Use in queries seamlessly
SELECT
  c.customer_id,
  c.first_name,
  c.last_name,
  get_customer_tier(c.customer_id) AS tier
FROM customers c
ORDER BY tier;

-- Use in WHERE
SELECT * FROM customers
WHERE get_customer_tier(customer_id) = 'Platinum';

Tip

Functions that call SELECT (READS SQL DATA) are convenient but can be slow when called per-row on large result sets — MySQL executes a subquery for every row. Consider a JOIN or subquery instead for large tables.

Date/Time Helper Function

SQL

DELIMITER //

-- Returns the number of business days between two dates (excludes weekends)
CREATE FUNCTION business_days_between(
  p_start DATE,
  p_end   DATE
)
RETURNS INT
DETERMINISTIC
NO SQL
BEGIN
  DECLARE v_days        INT DEFAULT 0;
  DECLARE v_current     DATE;
  DECLARE v_weekday     INT;

  SET v_current = p_start;

  WHILE v_current <= p_end DO
    -- DAYOFWEEK: 1=Sunday, 7=Saturday
    SET v_weekday = DAYOFWEEK(v_current);
    IF v_weekday NOT IN (1, 7) THEN
      SET v_days = v_days + 1;
    END IF;
    SET v_current = DATE_ADD(v_current, INTERVAL 1 DAY);
  END WHILE;

  RETURN v_days;
END //

DELIMITER ;

SELECT business_days_between('2024-01-01', '2024-01-31') AS biz_days;

-- Use in a query
SELECT
  order_id,
  order_date,
  shipped_date,
  business_days_between(order_date, shipped_date) AS fulfillment_days
FROM orders
WHERE shipped_date IS NOT NULL;

Recursive Functions

MySQL supports recursive stored functions (calling itself), but you must set max_sp_recursion_depth since the default is 0:

SQL

-- Enable recursion (session-level)
SET max_sp_recursion_depth = 20;

DELIMITER //

-- Recursive factorial function
CREATE FUNCTION factorial(n INT)
RETURNS BIGINT
DETERMINISTIC
NO SQL
BEGIN
  IF n <= 1 THEN
    RETURN 1;
  ELSE
    RETURN n * factorial(n - 1);
  END IF;
END //

DELIMITER ;

SELECT factorial(10);  -- 3628800

Warning

Recursive functions in MySQL are rarely the right tool for relational data — use recursive CTEs (WITH RECURSIVE) for hierarchical data instead. They are more readable and do not require changing server variables.

Viewing and Managing Functions

SQL

-- List all user-defined functions
SHOW FUNCTION STATUS WHERE Db = DATABASE()G

-- Show the function source code
SHOW CREATE FUNCTION calculate_taxG

-- Query information_schema
SELECT ROUTINE_NAME, DATA_TYPE AS return_type, IS_DETERMINISTIC, CREATED
FROM information_schema.ROUTINES
WHERE ROUTINE_SCHEMA = DATABASE()
  AND ROUTINE_TYPE   = 'FUNCTION';

-- Drop a function
DROP FUNCTION IF EXISTS calculate_tax;

Built-in vs User-Defined Functions

MySQL ships with hundreds of built-in functions. Use them first — they are optimized in C and cannot be outperformed by SQL-level user functions:

Category	Built-in Examples
String	CONCAT, SUBSTRING, REPLACE, REGEXP_REPLACE, TRIM, UPPER, LOWER, LENGTH
Numeric	ROUND, CEIL, FLOOR, ABS, MOD, POW, SQRT, RAND
Date/Time	NOW, DATE_FORMAT, DATEDIFF, DATE_ADD, STR_TO_DATE, UNIX_TIMESTAMP
Aggregate	COUNT, SUM, AVG, MIN, MAX, GROUP_CONCAT
Control	IF, IFNULL, NULLIF, COALESCE, CASE
JSON	JSON_EXTRACT, JSON_OBJECT, JSON_ARRAYAGG, JSON_VALUE

Write a user-defined function only when no built-in covers your need.

Performance Considerations

A function called in the WHERE clause executes once per candidate row — avoid on large tables without index support
DETERMINISTIC functions are safe for query caching and statement-based replication
NOT DETERMINISTIC functions using NOW() or RAND() are excluded from query cache
Functions that use SELECT (READS SQL DATA) create a correlated subquery per row — benchmark carefully
For complex multi-row aggregations, a view or derived table is usually faster than a per-row function

Practical Example: Price Formatter

SQL

DELIMITER //

CREATE FUNCTION format_currency(
  p_amount       DECIMAL(15,2),
  p_currency     CHAR(3)
)
RETURNS VARCHAR(30)
DETERMINISTIC
NO SQL
BEGIN
  DECLARE v_symbol VARCHAR(5);

  CASE p_currency
    WHEN 'USD' THEN SET v_symbol = '$';
    WHEN 'EUR' THEN SET v_symbol = 'EUR ';
    WHEN 'GBP' THEN SET v_symbol = 'GBP ';
    ELSE             SET v_symbol = CONCAT(p_currency, ' ');
  END CASE;

  RETURN CONCAT(v_symbol, FORMAT(p_amount, 2));
END //

DELIMITER ;

SELECT
  product_name,
  format_currency(price, 'USD') AS display_price
FROM products
ORDER BY price DESC
LIMIT 10;

Best Practices

Always declare the correct characteristics (DETERMINISTIC / READS SQL DATA) — wrong declarations cause replication issues
Keep functions pure when possible — no side effects, no writes — so they are safe to use anywhere in a query
Prefix parameters with p_ to avoid ambiguity with column names in SQL statements inside the function
Unit-test functions with SELECT my_func(input) before embedding them in critical queries
Store function definitions in version-controlled SQL migration files alongside schema changes
Avoid calling user-defined functions inside indexes (MySQL does not support functional indexes with UDFs)