How to Use DETERMINISTIC in MySQL Functions

In MySQL, use the DETERMINISTIC keyword when creating a stored function to tell the server that the function always returns the same result for the same input values. This helps MySQL optimize queries and cache results safely. Add DETERMINISTIC after the RETURNS clause and before the function body.

Syntax

The DETERMINISTIC keyword is used in the CREATE FUNCTION statement to declare that the function always returns the same result for the same input parameters.

Syntax parts:

• CREATE FUNCTION function_name(parameters): Defines the function name and inputs.
• RETURNS data_type: Specifies the return type.
• DETERMINISTIC: Declares the function's output is predictable.
• BEGIN ... END: Contains the function logic.

sql

CREATE FUNCTION function_name(parameter_list)
RETURNS data_type
DETERMINISTIC
BEGIN
  -- function logic
END

Example

This example creates a deterministic function that calculates the square of a number. Since the output depends only on the input, it is safe to mark it as DETERMINISTIC.

sql

DELIMITER $$
CREATE FUNCTION square_num(n INT)
RETURNS INT
DETERMINISTIC
BEGIN
  RETURN n * n;
END$$
DELIMITER ;

-- Using the function
SELECT square_num(5) AS result;

Output

result
25

Common Pitfalls

Common mistakes when using DETERMINISTIC include:

• Marking a function as DETERMINISTIC when it uses non-deterministic features like NOW(), RAND(), or reads from tables that change.
• Not specifying DETERMINISTIC when the function is actually deterministic, which can limit optimization.
• Confusing DETERMINISTIC with NO SQL or READS SQL DATA which describe side effects, not output predictability.

Wrong example:

sql

DELIMITER $$
CREATE FUNCTION random_plus_one()
RETURNS INT
DETERMINISTIC
BEGIN
  RETURN FLOOR(RAND() * 100) + 1;
END$$
DELIMITER ;

-- This is incorrect because RAND() is non-deterministic.

Quick Reference

Keyword	Meaning
DETERMINISTIC	Function always returns the same result for the same inputs.
NOT DETERMINISTIC	Function may return different results for the same inputs.
NO SQL	Function does not contain SQL statements.
READS SQL DATA	Function reads data but does not modify it.
MODIFIES SQL DATA	Function modifies data.

Key Takeaways

Use DETERMINISTIC to declare functions that always return the same output for the same inputs.

Avoid marking functions with random or time-based operations as DETERMINISTIC.

DETERMINISTIC helps MySQL optimize and cache function results safely.

Specify DETERMINISTIC right after RETURNS in the function definition.

Understand the difference between DETERMINISTIC and SQL data access characteristics.