SQL Reference - DETERMINISTIC

DETERMINISTIC

DETERMINISTIC is a keyword in SQL that is used with stored procedures to indicate that a function always returns the same result for the same input parameters. It is primarily used in replication scenarios to improve optimization and performance. When a function is not deterministic, the MySQL server will not cache the result.

Example

CREATE FUNCTION deterministic_example(input INT)
RETURNS INT
DETERMINISTIC
BEGIN
  RETURN input * 2;
END;

Output

Query OK, 0 rows affected (0.01 sec)

Explanation

In the provided example, a function named deterministic_example is created. It accepts an integer input, which it then multiplies by 2. The DETERMINISTIC statement means that MySQL can expect that for the same input, the function will always return the same output. The function is DETERMINISTIC since for any given input, the output (input * 2) will always be the same.

Example

CREATE FUNCTION get_sales_tax(total_sales NUMBER)
RETURN NUMBER DETERMINISTIC
IS
BEGIN
    RETURN total_sales * 0.09;
END;
/

Output

Function GET_SALES_TAX compiled

Explanation

In this example, we create a deterministic function called get_sales_tax that calculates and returns the sales tax. The DETERMINISTIC keyword signifies that the function will always return the same result given the same input.