MySQL storage functions

MySQL storage function (custom function), a function is generally used to calculate and return a value, and you can write a calculation or function that you often need to use as a function.

1. Create a storage function

In MySQL, the CREATE FUNCTION keyword is used to create a stored function, and its basic form is as follows:

CREATE FUNCTION func_name ([param_name type[,...]])RETURNS type[characteristic ...] BEGIN	routine_bodyEND;

Parameter Description:

(1) func_name: the name of the storage function.

(2) param_name type: optional, specify the parameters of the storage function. The type parameter is used to specify the parameter type of the stored function, which can be all supported types in the MySQL database.

(3) RETURNS type: Specify the type of return value.

(4) characteristic: optional, specify the characteristics of the storage function.

(5) routine_body: SQL code content.

2. Call the stored function

In MySQL, the use of stored functions is basically the same as that of MySQL internal functions. User-defined storage functions have the same nature as MySQL internal functions. The difference is that the storage function is user-defined. The internal functions are provided by MySQL. Its grammatical structure is as follows:

SELECT func_name([parameter[,…]]);

Example: Create a storage function to obtain the user name function based on the user number.

(1) Create tb_user (user information table) first, and add data.

-- 创建用户信息表CREATE TABLE IF NOT EXISTS tb_user(	id INT AUTO_INCREMENT PRIMARY KEY COMMENT '用户编号',	name VARCHAR(50) NOT NULL COMMENT '用户姓名') COMMENT = '用户信息表'; -- 添加数据INSERT INTO tb_user(name) VALUES('jenrey`s csdn');INSERT INTO tb_user(name) VALUES('jenrey');INSERT INTO tb_user(name) VALUES('zhangsan');INSERT INTO tb_user(name) VALUES('lisi');INSERT INTO tb_user(name) VALUES('wangwu');INSERT INTO tb_user(name) VALUES('maliu');INSERT INTO tb_user(name) VALUES('');

Query data results:

(2) Create a storage function

-- 创建存储函数DROP FUNCTION IF EXISTS func_user;CREATE FUNCTION func_user(in_id INT)RETURNS VARCHAR(50)BEGIN	DECLARE out_name VARCHAR(50); 	SELECT name INTO out_name FROM tb_user	WHERE id = in_id; 	RETURN out_name;END;

(3) Call storage function

-- 调用存储函数SELECT func_user(1);SELECT func_user(2);SELECT func_user(3);SELECT func_user(4);SELECT func_user(5);SELECT func_user(6);SELECT func_user(7);

Results of the:

3. Modify the storage function

In MySQL, the storage function is modified through the ALTER FUNCTION statement. The syntax format is as follows:

ALTER FUNCTION func_name [characteristic ...]characteristic:    COMMENT 'string'  | LANGUAGE SQL  | { CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA }  | SQL SECURITY { DEFINER | INVOKER }

The above grammatical structure is officially given by MySQL. The modified content can contain SQL statements or not. It can be SQL for reading data or SQL for modifying data and permissions. In addition, when modifying the function, you need to pay attention that you cannot use this statement to modify the parameters of the function and the function body. If you want to change these, you need to delete the function and create it again.

4. Delete the storage function

MySQL uses the DROP FUNCTION statement to delete stored functions.

Example: Delete a stored function.


Other supplements:

If you are prompted with the following error when creating a stored function:

[Err] 1418-This function has none of DETERMINISTIC, NO SQL, or READS SQL DATA in its declaration and binary logging is
enabled (you *might* want to use the less safe log_bin_trust_function_creators variable)

This is when we enable bin-log, we must specify whether our function is
2 NO SQL has no SQl statement, of course it will not modify the data
3 READS SQL DATA just reads the data, of course it will not modify Data
4 MODIFIES SQL DATA To modify data
5 CONTAINS SQL contains SQL statements

Among them, in function, only DETERMINISTIC, NO SQL and READS SQL DATA are supported. If we enable bin-log, we must specify a parameter for our function.


There are also two solutions. The first is to declare one of DETERMINISTIC or NO SQL and READS SQL DATA when creating a subprogram (stored procedure, function, trigger), for example: CREATE DEFINER = CURRENT_USER PROCEDURE `NewProc`( ) DETERMINISTIC BEGIN #Routine body goes here... END;;

The second is to trust the creator of the subroutine. It is forbidden to create or modify the subroutine for the requirements of the SUPER authority. Set the log_bin_trust_routine_creators global system variable to 1.

There are three setting methods:

(1) Execute SET GLOBAL log_bin_trust_function_creators = 1 on the client.

(2) When MySQL starts, add --log-bin-trust-function-creators to select the talents, and set the parameter to 1.

(3) Add to the [mysqld] section in the MySQL configuration file my.ini or my.cnf

vi /etc/my.cnf

log-bin-trust-function-creators=1 .

Then execute the command to view the binlog parameters: show global variables like "%log_bin%"

For the above configuration explanation, please refer to:

Introduction to MySQL parameter log_bin_trust_function_creators