What are the creation and execution methods of custom functions in Mysql

Creation and execution of Mysql custom function

Assume that the students table contains two fields, id and name, and create a function. The function of the function is based on ID search name

1. Create a table and insert data

create table students(id int,name varchar(100));
insert into students(id,name) values(1,'annie'),(2,'bell'),(3,'danny');

2. Create a function

DELIMITER //
create function find_student(id int) returns varchar(100)
READS SQL DATA
begin
    declare sname varchar(100) default '';
    select students.name into sname from students where students.id=id;
    return sname;
end //
DELIMITER ;

Things to note:

1) Use DELIMITER//to modify the delimiter

Mysql's default statement end symbol is a semicolon. When mysql encounters a semicolon, it automatically executes the current statement. Because the function definition contains multiple sql statements, use DELIMITER // to set the delimiter to // first, and then change the delimiter back to a semicolon after the function creation statement is completed.

2) READS SQL DATA

I didn’t write this sentence before, but mysql reported an error when creating it, prompting Error Code: 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)

I checked online, which means that the mysql function is not declared Type:

mysql turns on bin-log, we must specify what type our function is:

• 1 DETERMINISTIC Uncertain

• 2 NO SQL There is no SQl statement, and of course the data will not be modified

• 3 READS SQL DATA Just read the data, of course it will not modify the data

• 4 MODIFIES SQL DATA To modify the data

• 5 CONTAINS SQL contains SQL statements

• So I Added READS SQL DATA

3) Use local variables

Variable definition: I use declare sname varchar(100) default ‘ ’;The local variable sname is defined,

Variable usage:

You can use select students.name into sname from students where students.id=id; to assign a value to the variable

You can also directly use the set statement to assign values, such as set sname=‘test’

3. Execute function: select function name (parameter value);

select find_student(3);

Mysql Custom function creation failure problem

Case

Currently in the project, an error occurs when executing the function that creates mysql.

Mysql creation function error message is as follows:

Caused by: java.sql.SQLException: 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)
at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:965)
at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3976)
at com.mysql.jdbc.MysqlIO. checkErrorPacket(MysqlIO.java:3912)
at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:871)
at com.mysql.jdbc.MysqlIO.readAllResults(MysqlIO.java:2373)
at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2739)
at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2482)
at com.mysql.jdbc.ConnectionImpl. execSQL(ConnectionImpl.java:2440)
at com.mysql.jdbc.StatementImpl.executeInternal(StatementImpl.java:845)
at com.mysql.jdbc.StatementImpl.execute(StatementImpl.java:745)
... 35 more

This is because a security parameter is not turned on. The default value of log_bin_trust_function_creators is 0, which does not allow function synchronization. If this parameter is turned on, the creation will be successful.

Check whether it is enabled:

show variables like '%func%';
+---------------------------------+-------+ 
| Variable_name     | Value | 
+---------------------------------+-------+ 
| log_bin_trust_function_creators | ON | 
+---------------------------------+-------+ 
1 row in set (0.00 sec)

If on, it means it is enabled.

set global log_bin_trust_function_creators = 1;

can be set through this command, but it will become invalid after MySQL is restarted.

All is finally done by modifying the configuration file of the MySQL database

Configure log_bin_trust_function_creators=1 in the [mysqld] configuration file /etc/my.cnf

Restart MySQL after modification.

The above is the detailed content of What are the creation and execution methods of custom functions in Mysql. For more information, please follow other related articles on the PHP Chinese website!