This article uses examples to illustrate the principles and usage of MySQL custom functions. Share with you for your reference, the details are as follows:

In this article:

• What is a function
• Function creation
• Function call
• Function View
• Function modification
• Deleting a function

Release date: 2018-04-18

What is a function:

• A function stores a series of SQL statements, and calling a function executes these statements at once. So functions can reduce statement duplication. [But please note that the function focuses on the return value, not the execution process, so some statements cannot be executed. So the function is not just a collection of SQL statements. 】
• MySQL functions have their own custom functions (defined functions). For more information, please refer to my other blog post: Common MySQL functions
• Here we mainly introduce how to customize functions.

Replenish:

• The difference between a function and a stored procedure: A function can only return a value and is not allowed to return a result set. Functions emphasize return values, so functions are not allowed to return multiple values, even in query statements.

  -- Unacceptable code: Not allowed to return a result set from a function
  create function myf()returns int 
  begin
  select * from student;
  return 100;
  end;

Function creation:

• grammar:

  create function function name([parameter list]) returns data type begin
   sql statement;
   return value;
  end;

  • The format of the parameter list is: variable name data type
• Example:

  -- The simplest function with only one SQL statement: create function myselect2() returns int return 666;
  select myselect2(); -- call function --
  create function myselect3() returns int
  begin 
    declare c int;
    select id from class where cname="python" into c;
    return c;
  end;
  select myselect3();
  -- Function with parameter passing create function myselect5(name varchar(15)) returns int
  begin 
    declare c int;
    select id from class where cname=name into c;
    return c;
  end;
  select myselect5("python");

Replenish:

• There can also be some special options, which are written after return and before begin, such as:
  • comment: a description of the function
  • There are some other options such as sql security. If you are interested, you can search on Baidu. I won’t explain it here, I’ll just mention this knowledge point.

Function call:

• You can call it directly using the function name (). [ Although it is said, it returns a result. If select is not used in sql, no result can be displayed (so a simple call will report an error).]
• If you want to pass in parameters, you can use the function name (parameters)
• Calling method [The functions called below are all created above. 】：

  -- Call select myselect3() without parameters;
  -- Call select myselect5("python");
  select * from class where id=myselect5("python");

Function view:

• View the function creation statement: show create function function name;
• View all functions: show function status [like 'pattern'];

Function modification:

• The function can only modify some options such as comment, but cannot modify the internal SQL statement and parameter list.
• alter function function name option;

Deletion of functions:

• drop function function name;

Readers who are interested in more MySQL-related content can check out the following topics: "Summary of MySQL Common Functions", "Summary of MySQL Log Operation Skills", "Summary of MySQL Transaction Operation Skills", "Summary of MySQL Stored Procedure Skills" and "Summary of MySQL Database Lock-Related Skills".

I hope this article will be helpful to everyone's MySQL database design.