Deterministic vs Non-Deterministic Functions

A. Deterministic Function:

A deterministic function always returns the same result when given the same input, regardless of when or how many times it is called.

  • Does not rely on time-based or random functions like NOW(), RAND(), or UUID().
  • Common examples include mathematical operations (x * x, LENGTH('text')) and string manipulations.
  • Suitable for replication and binary logging because output is consistent.
  • Must be declared with DETERMINISTIC if used in stored functions.

B. Non-Deterministic Function:

A non-deterministic function may return different results even with the same input, depending on system or session conditions.

  • Uses functions like NOW(), CURDATE(), RAND(), UUID(), which change with time or context.
  • Cannot guarantee consistent output across multiple calls with the same input.
  • Declared using NOT DETERMINISTIC in MySQL.
  • May cause issues with replication or caching if misused in stored functions.

Note: You must declare either DETERMINISTIC or NOT DETERMINISTIC when creating a function.

PreviousNext