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;
完整的案例
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
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