MySQL Stored Functions: CREATE FUNCTION, DETERMINISTIC

A stored function takes parameters and returns one value. Unlike a procedure, you use it inside other statements, like a built-in function.

query.sql

SQL

DELIMITER //

CREATE FUNCTION price_with_tax(price DECIMAL(10,2), rate_percent DECIMAL(5,2))
RETURNS DECIMAL(10,2)
DETERMINISTIC
NO SQL
BEGIN
    RETURN ROUND(price * (1 + rate_percent / 100), 2);
END//

CREATE FUNCTION initials(full_name VARCHAR(100))
RETURNS VARCHAR(10)
DETERMINISTIC
NO SQL
BEGIN
    RETURN CONCAT(UPPER(LEFT(full_name, 1)), UPPER(LEFT(SUBSTRING_INDEX(full_name, ' ', -1), 1)));
END//

DELIMITER ;

SELECT price_with_tax(80, 18) AS price_incl_tax, initials('Maya Shah') AS initials;

Output

OUTPUT

+----------------+----------+
| price_incl_tax | initials |
+----------------+----------+
|          94.40 | MS       |
+----------------+----------+
1 row in set

Use it in a table query:

query.sql

SQL

CREATE TABLE products (id INT AUTO_INCREMENT PRIMARY KEY, name VARCHAR(30), price DECIMAL(8,2));
INSERT INTO products (name, price) VALUES ('Notebook', 80), ('Backpack', 999), ('Pen', 15.5);

SELECT name, price, price_with_tax(price, 18) AS with_gst FROM products ORDER BY id;

Output

OUTPUT

+----------+--------+----------+
| name     | price  | with_gst |
+----------+--------+----------+
| Notebook |  80.00 |    94.40 |
| Backpack | 999.00 |  1178.82 |
| Pen      |  15.50 |    18.29 |
+----------+--------+----------+
3 rows in set

Characteristics

Characteristic	Meaning
`DETERMINISTIC`	The same input always gives the same output (no `NOW()`, `RAND()`, table reads). Required to use the function in some places (generated columns, indexes) and helps replication
`NOT DETERMINISTIC`	The default
`NO SQL`	The body has no SQL statements
`READS SQL DATA`	It reads tables but does not change them
`MODIFIES SQL DATA`	It changes data
`CONTAINS SQL`	It has SQL that neither reads nor writes (default)

Be honest when you declare them: MySQL does not verify DETERMINISTIC, but a wrong claim can corrupt replication or give wrong results.

A function that reads a table

query.sql

SQL

CREATE TABLE tax_rates (category VARCHAR(20) PRIMARY KEY, rate_percent DECIMAL(5,2) NOT NULL);
INSERT INTO tax_rates VALUES ('books', 5), ('electronics', 18), ('food', 0);

DELIMITER //

CREATE FUNCTION tax_rate_for(cat VARCHAR(20))
RETURNS DECIMAL(5,2)
READS SQL DATA
BEGIN
    DECLARE r DECIMAL(5,2);
    SELECT rate_percent INTO r FROM tax_rates WHERE category = cat;
    RETURN COALESCE(r, 18.00);
END//

DELIMITER ;

SELECT tax_rate_for('books') AS books, tax_rate_for('food') AS food, tax_rate_for('toys') AS unknown_default;

Output

OUTPUT

+-------+------+-----------------+
| books | food | unknown_default |
+-------+------+-----------------+
|  5.00 | 0.00 |           18.00 |
+-------+------+-----------------+
1 row in set

Functions vs procedures

	Function	Procedure
Called with	Inside any SQL expression	`CALL`
Returns	One value	Nothing, or `OUT` parameters, or result sets
Can run `SELECT` that returns a result set	No	Yes
Can change data	Yes, with restrictions	Yes
Can use transactions (`COMMIT`, `ROLLBACK`)	No	Yes
Typical use	Calculations, formatting, lookups	Multi-step tasks

The performance trap

A stored function in a WHERE clause or select list runs once per row. A function that queries a table makes that one query per row, which is the N+1 problem inside the database (The N+1 Problem):

query.sql

SQL

SELECT COUNT(*) AS rows_over_100 FROM products WHERE price_with_tax(price, 18) > 100;

Output

OUTPUT

+---------------+
| rows_over_100 |
+---------------+
|             1 |
+---------------+
1 row in set

Also, a function in WHERE cannot use an index on the column it wraps. A join with a rates table is usually better than a function that looks the rate up for each row.

Errors: the binary log setting

If the server has binary logging on (replication, point-in-time recovery), MySQL requires a function to be declared DETERMINISTIC, NO SQL or READS SQL DATA, unless the creating user has the SET_USER_ID privilege, or the server variable log_bin_trust_function_creators is ON. Without it, you get error 1418 (“This function has none of DETERMINISTIC, NO SQL, or READS SQL DATA in its declaration and binary logging is enabled”). Declare the characteristic that is true for your function.

When to use stored functions

A small, pure calculation that must be identical in many queries and many applications (a tax formula, a code normaliser).
Generated columns and functional indexes (the function must be deterministic, and not read tables).

When in doubt, put the calculation in the application, or use a built-in function. Stored functions are harder to test and deploy, and hide cost.

Dropping and changing

query.sql

SQL

1
2

DROP FUNCTION IF EXISTS initials;
SELECT routine_name, routine_type, is_deterministic FROM information_schema.routines WHERE routine_schema = 'learn' AND routine_type = 'FUNCTION' ORDER BY routine_name;

Output

OUTPUT

+----------------+--------------+------------------+
| ROUTINE_NAME   | ROUTINE_TYPE | IS_DETERMINISTIC |
+----------------+--------------+------------------+
| price_with_tax | FUNCTION     | YES              |
| tax_rate_for   | FUNCTION     | NO               |
+----------------+--------------+------------------+
2 rows in set

lightbulb

Prefer built-ins first

MySQL has hundreds of built-in functions. Check MySQL Functions before writing your own.