Shulou(Shulou.com)06/01 Report--

Let's talk about MySQL custom functions. The secret of the text is that it is close to the topic. So, no gossip, let's go straight to the following, I believe you will benefit from reading this article on MySQL custom functions.

1. Introduction to custom functions

Custom functions: user-defined functions (user-defined function,UDF) are a way to extend MySQL in the same way as built-in functions

Two necessary conditions for a custom function: (1) parameter (2) return value

Custom function:

Create a custom function

CREATE FUNCTION function_nameRETURNS {STRING | INTEGER | REAL | DECIMAL} routine_body

About the function body:

1. The function body can be made up of legal SQL statements

two。 The function body can be a simple SELECT or INSERT statement

3. If the function body is a compound structure, use the begin.. end statement.

4. Composite structures can contain declarations, loops, and control structures.

two。 Create a custom function without arguments

Create date and time in minutes and seconds format of year, month, day and point

Mysql > CREATE FUNCTION F1 () RETURNS VARCHAR (30)-> RETURN DATE_FORMAT (NOW (),'% Y% m / d% H point:% I:% s seconds'); Query OK, 0 rows affected (0.00 sec) mysql > SELECT F1 () +-- + | F1 () | +-- + | 08:00, November 28th, 2016: 34: 55: 55 | +- -- +

3. Create a custom function with parameters

Create a function that can calculate the average of two numbers

Mysql > CREATE FUNCTION f2 (num1 SMALLINT UNSIGNED,num2 SMALLINT UNSIGNED)-> RETURNS FLOAT (10Lab 2) UNSIGNED-> RETURN (num1+num2) / 2persQuery OK, 0 rows affected (0.00 sec) mysql > SELECT f2 (10Lab 16); +-+ | f2 (10jue 16) | +-+ | 13.00 | +-+

4. Create a custom function with the body of the structural function

You can modify the default Terminator in MySQL through the DELIMITER Delimiter

Delete function:

DROP FUNCTION [IF EXISTS] function_name

If the custom function exists in accordance with the structure, multiple statements, the function body should be included in the BEGIN...END, and the default Terminator should be changed through DELIMITER; modify it to other symbols, such as / / $$, so that the function will not be interrupted by the; sign at the end of the statement.

Mysql > DELIMITER / / mysql > CREATE FUNCTION adduser (username VARCHAR (20))-> RETURNS INT UNSIGNED-> BEGIN-> INSERT test (username) VALUES (username);-> RETURN LAST_INSERT_ID ();-> END-> / / mysql > DELIMITER; mysql > SELECT adduser ('Tom') +-+ | adduser ('Tom') | +-+ | 3 | +-+ mysql > SELECT adduser (' Rool') +-+ | adduser ('Rool') | +-+ | 4 | +-+ mysql > SELECT * FROM user +-+ | id | username | +-+-+ | 1 | Drive | | 2 | Cve | | 3 | Tom | | 4 | Rool | +-+

For the above MySQL custom function related content, is there anything you do not understand? Or if you want to know more about it, you can continue to follow our industry information section.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.