mysql函数操作-增加自定义函数

#首先执行看mysql是否开启函数
SHOW VARIABLES LIKE '%func%';
------------------------------------------------------------------------------

#如果没有开启（OFF）则设置开启
SET GLOBAL log_bin_trust_function_creators=1;
------------------------------------------------------------------------------

#下面是一个测试函数
DELIMITER $$
CREATE FUNCTION test_func(param1 VARCHAR(5),parmam2 VARCHAR(5),param3 VARCHAR(10))
RETURNS TINYINT
BEGIN
   RETURN 1;
END
------------------------------------------------------------------------------
#测试
select test_func('aaa','bbb','ccc');
------------------------------------------------------------------------------
#删除函数
 drop function test_func ;

------------------------------------------------------------------------------
#显示函数
show function status
------------------------------------------------------------------------------
#显示函数源码
show create function first_func;

 

完整的案例

DELIMITER $$
CREATE FUNCTION test(x1 FLOAT,y1 FLOAT,x2 FLOAT,y2 FLOAT)
RETURNS TINYINT
BEGIN
    IF x1 = x2 AND y1 = y2 THEN
        RETURN 0;
    ELSE
        SET @PI = PI();
        SET @a=(@x1-@x2)*@p/180;
        SET @b=@p/2-@y1*@p/180;
        SET @c=@p/2-@y2*@p/180;
        SET @d=COS(@b)*COS(@c)+COS(@a)*SIN(@b)*SIN(@c);
        SET @r = ROUND(ACOS(@d)*1000*6371.004,0);
        RETURN @r;
    END IF;
    
END