How to create a function in Mysql

How to create a function in Mysql

DELIMITER $$
DROP FUNCTION IF EXISTS genPerson$$
CREATE FUNCTION genPerson(name varchar(20)) RETURNS varchar(50)
BEGIN
  DECLARE str VARCHAR(50) DEFAULT '';
  SET @tableName=name;
  SET str=CONCAT('create table ', @tableName,'(id int, name varchar(20));');
  return str;
END $$
DELIMITER ;

As far as the sql in the above picture is concerned

(1) DELIMITER $ $ defines the terminator. MySQL's default terminator is a semicolon, but a semicolon may be used in the function body. To avoid conflicts, additional terminators need to be defined.

(2) DROP FUNCTION IF EXISTS genPerson$$ If the function genPerson already exists, delete it.

(3) CREATE FUNCTION creates the function genPerson. The parameter of the function is name and the return value is varchar(50).

(4) The function body is placed between BEGIN and END.

(5) DECLARE declares variables. The str type is varchar(50), and the default value is empty.

(6) CONCAT connects multiple strings.

(7) RETURN returns the concatenated string str.

It can be seen from the execution results

Mysql cannot create function error code 1418

In When creating a function in mysql, the error code

is reported: 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 means that the create function function is not turned on.

Solution

1. Check whether the create function function is turned on

show variables like '%func%';
-------------------------------------------
+---------------------------------+-------+
| Variable_name                   | Value |
+---------------------------------+-------+
| log_bin_trust_function_creators | OFF   |
+---------------------------------+-------

2. Turn it on

SET GLOBAL log_bin_trust_function_creators = 1;

3.Check again

show variables like '%func%';
-------------------------------------------
+---------------------------------+-------+
| Variable_name                   | Value |
+---------------------------------+-------+
| log_bin_trust_function_creators | ON   |
+---------------------------------+-------

The above is the detailed content of How to create a function in Mysql. For more information, please follow other related articles on the PHP Chinese website!