iwantcoding.com
🔥 Daily 👥 Rooms 🏆 Top Log in Sign up

Functions

By · Reviewed & last updated · Part of MySQL Tutorial

Stored functions in MySQL behave like SQL-level helpers: they take inputs, return a single value, and can be used inside SELECT, WHERE, and ORDER BY. They share the engine with stored procedures but cannot affect transaction state directly. Use them for tidy reuse of formatting, calculations, and constant tables.

Create, call, and inline stored functions

EXAMPLE
-- 1) A simple deterministic function
DELIMITER //
CREATE FUNCTION cents_to_dollars(cents BIGINT)
RETURNS DECIMAL(10,2)
DETERMINISTIC
SQL SECURITY INVOKER
BEGIN
  RETURN cents / 100.0;
END//
DELIMITER ;

SELECT id, total_cents, cents_to_dollars(total_cents) AS total_aud
FROM orders LIMIT 10;

-- 2) Function that reads from another table (data-modifying = NO; reads = YES)
DELIMITER //
CREATE FUNCTION customer_lifetime_value(p_customer_id BIGINT)
RETURNS DECIMAL(12,2)
READS SQL DATA
BEGIN
  DECLARE total DECIMAL(12,2);
  SELECT COALESCE(SUM(total_cents) / 100.0, 0) INTO total
  FROM orders
  WHERE customer_id = p_customer_id AND status IN ('paid', 'shipped');
  RETURN total;
END//
DELIMITER ;

-- Use it like any expression
SELECT
  c.id, c.name,
  customer_lifetime_value(c.id) AS ltv
FROM customers c
ORDER BY ltv DESC
LIMIT 20;

-- 3) Use in a WHERE clause — careful with indexes! The planner cannot
--    use an index on orders here because the predicate is on a function output.
SELECT id, name
FROM customers
WHERE customer_lifetime_value(id) > 500;

-- 4) Avoid the index-killing pattern above by storing/maintaining
--    the value (trigger or materialised view) and querying it directly.

-- 5) Inspect and drop
SHOW CREATE FUNCTION cents_to_dollars;
DROP FUNCTION IF EXISTS cents_to_dollars;

-- 6) Permissions: GRANT EXECUTE so an app role can call without SELECT-ing the underlying tables
CREATE USER 'reporting'@'%' IDENTIFIED BY 'strong-secret';
GRANT EXECUTE ON FUNCTION shop.customer_lifetime_value TO 'reporting'@'%';
GRANT EXECUTE ON FUNCTION shop.cents_to_dollars        TO 'reporting'@'%';
REVOKE SELECT ON shop.* FROM 'reporting'@'%';

Why it matters

Stored functions placed in WHERE or ORDER BY hide row-by-row work from the query plan. If you find yourself calling one against millions of rows, materialise the result with a generated column or a precomputed table — the function call cost compounds invisibly until a planner change makes it slow on a Friday.

Tip: Tweak the snippet with Try it Yourself », then sit the quiz at the bottom of the page.

Example

Example
DELIMITER //
CREATE FUNCTION add_int(a INT, b INT)
    RETURNS INT DETERMINISTIC
BEGIN
    RETURN a + b;
END//
DELIMITER ;
Try it Yourself »

Discussion

Loading…