MySQL function syntax

delimiter

stay mysql The role of command line operations ： Specify a symbol as a SQL The end of the statement , The default is semicolon . One SQL At the end of the statement, it will execute , and procedure And function Semicolon in , So define procedure And function Before , You need to modify this symbol first . Such as ：delimiter //

Variable

A variable called ‘@xxx’ form , No declaration required , Can be used directly , The default value is NULL（ but procedure Or local variable names in functions without @, And need to declare , Format ：declare Variable name datatype. but procedure Or functions and other structures can also be used directly ’@xxx’ Variable of form , And can be accessed externally ）

SET Instructions

Variable assignment

Delete function

drop function if exists fn_name;

Specify the type of function

by function Specify a parameter . DETERMINISTIC, NO SQL and READS SQL DATA It is equivalent to a clear notice MySQL The server function does not modify the data

1 DETERMINISTIC affirmatory

2 NO SQL No, SQl sentence , Of course, it doesn't change the data

3 READS SQL DATA Just read the data , Of course, it doesn't change the data

4 MODIFIES SQL DATA To modify data

5 CONTAINS SQL Contains SQL sentence

Example

delimiter $$
CREATE FUNCTION `fn_cutAppName`(appName varchar(4000)) RETURNS varchar(4000) CHARSET utf8mb4
    DETERMINISTIC
begin
    DECLARE ret varchar(4000);
    case when (appName like '% passport No %' or appName like '% ID number %') then
        set @leftindex= LOCATE('（',appName);
		if (@leftindex =0) then set @leftindex = LOCATE('(',appName); end if;
		if (@leftindex =0) then set @leftindex = LOCATE(' passport No ',appName); end if;
		if (@leftindex =0) then set @leftindex = LOCATE(' ID number ',appName); end if;
        case when (@leftindex >0) then set ret = trim(left(appName,(@leftindex-1)));
        else set ret = appName ;
        end case;
	else 
		set ret = appName;
    end case;
    return (select ret);
end
end $$

Call function

select fn_cutAppName(' Unlimited liability company ');

Reference resources ：
https://blog.csdn.net/yuxin6866/article/details/52722913

https://blog.csdn.net/qq_19865749/article/details/68962005