今天遇到了不能创建mysql函数
今天用navicat 不能创建函数,查询了
MySQL函数不能创建,是未开启功能:
- mysql> show variables like '%func%';
- +---------------------------------+-------+
- | Variable_name | Value |
- +---------------------------------+-------+
- | log_bin_trust_function_creators | OFF |
- +---------------------------------+-------+
- 1 row in set (0.00 sec)
- mysql> set global log_bin_trust_function_creators=1;
- Query OK, 0 rows affected (0.00 sec)
- mysql> show variables like '%func%';
- +---------------------------------+-------+
- | Variable_name | Value |
- +---------------------------------+-------+
- | log_bin_trust_function_creators | ON |
- +---------------------------------+-------+
- 1 row in set (0.00 sec)mysql>
==================
这里写了2个函数,功能是去除用逗号分隔的字符串中重复的字符
CREATE DEFINER=`root`@`localhost` FUNCTION `str_str_unique`(str text,str1 text) RETURNS text CHARSET utf8
BEGIN
#str 要处理的字符 str1 分隔符
#功能:处理字符串中重复的字符
declare stri TEXT default '';
declare i int DEFAULT 1;
declare len int DEFAULT 0;
declare tmp TEXT default '' ;
declare tmp_str varchar(255) default '' ;
declare site varchar(255) DEFAULT null;
set tmp=JSON_OBJECT();
set len = str_str_count(str,str1);
if(len=0)then
return str;
end if ;
repeat
set tmp_str=SUBSTRING_INDEX(SUBSTRING_INDEX(str,str1,i),str1,-1);
set site = JSON_SEARCH(tmp,'one',tmp_str);
if( site is null and tmp_str is not null and tmp_str !='') then
i f(LENGTH(stri)>0)then
set stri = concat(stri,str1,tmp_str);
else
set stri = concat(stri,tmp_str);
end if;
set tmp = JSON_INSERT(tmp,concat('$.key',i),tmp_str);
end if;
set i = i + 1;
until i >len+1 end repeat;
return stri;
END
CREATE DEFINER=`root`@`%` FUNCTION `str_str_count`(str varchar(255),stri varchar(20) ) RETURNS int(12)
BEGIN
# 测量字符串中分隔符的个数
DECLARE total int(20);
SELECT LENGTH(str) - LENGTH(REPLACE(str, stri, '')) into total;
RETURN total;
END