In the article, we learn how to create the Deterministic function in MySQL. Providing examples of how to perform global settings and call the function.
MySQL allows us to create stored functions and call them. It contains a collection of SQL commands/expressions in the form of routines that are stored in a database. We can call a stored function by specifying its name and parameter list wherever an expression of the appropriate data type may be used.
MySQL Deterministic Functions
A stored function specified with DETERMINISTIC will return the same value for the same input parameters every time we call it. But when we do not specify it, the function will be considered as NON-DETERMINISTIC by default, where it can return different results for the same input parameters.
The function can be used explicitly by providing global settings in MySQL server, which is set as NOT DETERMINISTIC by default. The syntax to set it on is as follows:
SET GLOBAL log_bin_trust_function_creators = 1;
Syntax to create a stored function specified with DETERMINISTIC
See the following syntax and description for creating a deterministic function in MySQL:
CREATE FUNCTION [FunctionName] ( [ParameterName] [DataType] ) RETURNS [DataType] [DETERMINISTIC]
- CREATE FUNCTION statement is a header to create functions.
- [FunctionName] contains the name of the function to be created.
- [ParameterName] contains the name of parameters as the input value to the function according to the given data type.
- RETURNS [DataType] is to determine the data type that will be returned by the function.
- [DETERMINISTIC] is to determine which users can use/execute this function.
Examples for the Deterministic function
Here is a table named student that contains columns such as ID and name of the students. The table is created in the school database. See the following syntax to create them and fill the table with some sample records:
CREATE DATABASE school; USE school; CREATE TABLE student (ID INT, student_name VARCHAR(25)); INSERT INTO student VALUES (1,'Zein'), (2, 'Xavier');
We can see the data in the student table using the command “SELECT * FROM student;” and the results are as follows:
Table 1. An example of the student table
Example #1 Create a DETERMINISTIC function with a parameter input
We create a stored function specified with DETERMINISTIC named getStudentName with the student_id as the input parameter. The function contains the SELECT statement to get a student’s name by an ID. The syntax to create the function is as follows:
DELIMITER ## CREATE FUNCTION getStudentName (student_ID INT) RETURNS VARCHAR(25) DETERMINISTIC BEGIN DECLARE nm VARCHAR(25); SELECT student_name INTO nm FROM student WHERE ID = student_ID; RETURN nm; END##
The DELIMITER is used to tell MySQL the end of the statement. By default, we use “;“, but we can also use something else like “##” to end the statement (see in the example above ). The getStudentName function is specified with DETERMINISTIC because it returns the same student name when we call it with the same student_ID value.
Example #2 Call the getStudentName function
By the example functions above that have been created, afterward, we can call the functions using the SELECT statement. Suppose we want to get the name of the students in the student table by using the getStudentName function with an ID of 2.
See the following syntax to call the function.
SELECT getStudentName(2) AS "Name"##
The result will appear:
Table 2. Execution of the getStudentName function
Instant livechat to an Expert!
Most of the time, the problem you will need to solve will be more complex than a simple method. If you want to save hours of research and frustration, try our live SQLExpert service!
Our SQLQuerychat Experts are available 24/7 to answer any questions you may have. We guarantee a connection within 30 seconds and a customized solution within 20 minutes.