mysql命令行创建函数

mysql命令行模式下创建函数

1、修改命令分隔符为 “$$”，防止执行中函数中的 “;” 符截断函数

delimiter $$

2、执行创建函数

CREATE DEFINER=`root`@`%` FUNCTION `huai_GETPARLST`(rootId VARCHAR(32)) RETURNS varchar(1000) CHARSET utf8
BEGIN
 DECLARE sTemp VARCHAR(1000);
 DECLARE sTempPar VARCHAR(1000);
  DECLARE namesss VARCHAR(1000); 
 DECLARE namek VARCHAR(1000); 
 SET sTemp = ''; 
 SET sTempPar = rootId; 
 SET namesss = '';
 SET namek = '';
 WHILE sTempPar is not null and sTempPar <> '' DO 
  IF sTemp != '' THEN
   SET sTemp = concat(sTempPar,',',sTemp);
   SELECT `name` INTO namek FROM flow_gathering_area where id = sTempPar;
   IF namek != '' THEN
   SET namesss = concat(namek,',',namesss);
   ELSE
   SET namesss = namesss;
   END IF;
  ELSE
   SET sTemp = sTempPar;
   SELECT `name` INTO namesss FROM flow_gathering_area where id = sTempPar;
  END IF;
  SELECT group_concat(parent_id) INTO sTempPar FROM flow_gathering_area where parent_id<>id and FIND_IN_SET(id,sTempPar)>0; 
 END WHILE; 
RETURN namesss; 
END$$

执行成功

3、恢复命令分隔符为 ";"

delimiter ;

中间遇到的问题

1、ERROR 1418 (HY000): This function has none of DETERMINISTIC, NO SQL, or READS SQL

意思就是mysql现在是不允许创建函数。此时如何解决这个问题呢？分二种情况。
①、单个数据库，数据库没有开启主从复制。

1、更改全局配置（重启服务失效）
      SET GLOBAL log_bin_trust_function_creators = 1;  
      有主从复制的时候 , 从机必须要设置  不然会导致主从同步失败
2、更改配置文件my.cnf  
      log-bin-trust-function-creators=1   重启服务生效

MySQL参数log_bin_trust_function_creators 简单介绍

当二进制日志启用后，这个变量就会启用。它控制是否可以信任存储函数创建者，
不会创建写入二进制日志引起不安全事件的存储函数。如果设置为0（默认值），
用户不得创建或修改存储函数，除非它们具有除CREATE ROUTINE或ALTER ROUTINE特权之外的SUPER权限。
 设置为0还强制使用DETERMINISTIC特性或READS SQL DATA或NO SQL特性声明函数的限制。 
 如果变量设置为1，MySQL不会对创建存储函数实施这些限制。 此变量也适用于触发器的创建。

查看mysql参数log_bin_trust_function_creators状态

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

OFF代表0，此时执行创建函数就会报此异常。

②、数据库开启主从复制（主从复制会开启二进制日志，此处也可以使用第一种方式解决）[推荐]。
明确指明函数的类型，如果我们开启了二进制日志， 那么我们就必须为我们的function指定一个参数。其中下面几种参数类型里面，只有 DETERMINISTIC, NO SQL 和 READS SQL DATA 被支持。这样一来相当于明确的告知MySQL服务器这个函数不会修改数据。

 1. DETERMINISTIC 确定的
 2. NO SQL 没有SQl语句，当然也不会修改数据
 3. READS SQL DATA 只是读取数据，当然也不会修改数据
 4. MODIFIES SQL DATA 要修改数据
 5. CONTAINS SQL 包含了SQL语句

修改完之后的函数

CREATE DEFINER=`root`@`%` FUNCTION `huai_GETPARLST`(rootId VARCHAR(32)) RETURNS varchar(1000) CHARSET utf8
READS SQL DATA 
BEGIN
 DECLARE sTemp VARCHAR(1000);
 DECLARE sTempPar VARCHAR(1000);
  DECLARE namesss VARCHAR(1000); 
 DECLARE namek VARCHAR(1000); 
 SET sTemp = ''; 
 SET sTempPar = rootId; 
 SET namesss = '';
 SET namek = '';
 WHILE sTempPar is not null and sTempPar <> '' DO 
  IF sTemp != '' THEN
   SET sTemp = concat(sTempPar,',',sTemp);
   SELECT `name` INTO namek FROM flow_gathering_area where id = sTempPar;
   IF namek != '' THEN
   SET namesss = concat(namek,',',namesss);
   ELSE
   SET namesss = namesss;
   END IF;
  ELSE
   SET sTemp = sTempPar;
   SELECT `name` INTO namesss FROM flow_gathering_area where id = sTempPar;
  END IF;
  SELECT group_concat(parent_id) INTO sTempPar FROM flow_gathering_area where parent_id<>id and FIND_IN_SET(id,sTempPar)>0; 
 END WHILE; 
RETURN namesss; 
END$$

那么为什么MySQL有这样的限制呢？ 因为我们的数据库是主从复制的，所以会开启二进制日志。二进制日志的一个重要功能是用于主从复制，而存储函数有可能导致主从的数据不一致。所以当开启二进制日志后，参数log_bin_trust_function_creators就会生效，限制存储函数的创建、修改、调用。
2、The user specified as a definer (‘root’@’%’) does not exist
权限问题，由于此函数不需要权限限制，都可以使用，所以去掉权限限制。
修改完之后的函数（去掉 DEFINER=root@% ）

CREATE FUNCTION `huai_GETPARLST`(rootId VARCHAR(32)) RETURNS varchar(1000) CHARSET utf8
READS SQL DATA
BEGIN
 DECLARE sTemp VARCHAR(1000);
 DECLARE sTempPar VARCHAR(1000);
  DECLARE namesss VARCHAR(1000); 
 DECLARE namek VARCHAR(1000); 
 SET sTemp = ''; 
 SET sTempPar = rootId; 
 SET namesss = '';
 SET namek = '';
 WHILE sTempPar is not null and sTempPar <> '' DO 
  IF sTemp != '' THEN
   SET sTemp = concat(sTempPar,',',sTemp);
   SELECT `name` INTO namek FROM flow_gathering_area where id = sTempPar;
   IF namek != '' THEN
   SET namesss = concat(namek,',',namesss);
   ELSE
   SET namesss = namesss;
   END IF;
  ELSE
   SET sTemp = sTempPar;
   SELECT `name` INTO namesss FROM flow_gathering_area where id = sTempPar;
  END IF;
  SELECT group_concat(parent_id) INTO sTempPar FROM flow_gathering_area where parent_id<>id and FIND_IN_SET(id,sTempPar)>0; 
 END WHILE; 
RETURN namesss; 
END$$

参考：https://www.cnblogs.com/kerrycode/p/7641835.html