To prevent a semicolon-generated output, define a delimiter, which is modeled after the official MySQL example: Using two dollar sign $$ as the segmentation symbol, this code is to create a custom MySQL function prototype, can be modified on this basis, so that, Creating a function does not result in many errors.
Set global log_bin_trust_function_creators = 1; --Open Bin_log copy function creation
DROP FUNCTION IF EXISTS Hello; --Delete the existing
DELIMITER $$--Define delimiter, must have, can not $$
CREATE FUNCTION Hello (s varchar (30))--For multiple arguments, the type of the split parameter must be the type of the MySQL column
RETURNS VARCHAR (255)--Specifies the return value type, if you are unsure of the length of the returned text, you can use the text
BEGIN
DECLARE str varchar (255) Default ' Hello '; --Define a variable, you can specify the default value
SET str = concat (str,s); --Set the value of the variable to change the edge
RETURN str; --Return value
End $$--notice that this end has the split symbol you defined earlier
DELIMITER $$--well, here we are.
/**
* Open Database Custom Function function
*/
Set global Log_bin_trust_function_creators=1;
/*
* Calculate POI Point Distance
*
*/
DELIMITER $$
CREATE definer = current_user FUNCTION ' getdistance ' (' lon1 ' float, ' lat1 ' float, ' lon2 ' float, ' lat2 ' float)
RETURNS Double
Begin
declare d double;
declare radius int;
Set radius = 6378140; #假设地球为正球形 with a diameter of 6378140 m
Set D = (2*atan2 (SQRT (SIN (LAT1-LAT2) *pi ()/180/2)
*sin ((LAT1-LAT2) *pi ()/180/2) +
COS (Lat2*pi ()/180) *cos (LAT1*PI ()/180)
*sin ((Lon1-lon2) *pi ()/180/2)
*sin ((Lon1-lon2) *pi ()/180/2)),
SQRT (1-sin (LAT1-LAT2) *pi ()/180/2)
*sin ((LAT1-LAT2) *pi ()/180/2)
+cos (Lat2*pi ()/180) *cos (LAT1*PI ()/180)
*sin ((Lon1-lon2) *pi ()/180/2)
*sin ((Lon1-lon2) *pi ()/180/2))) *radius;
return D;
END $$
DELIMITER $$;
MySQL Create user Custom function