1, System encapsulation function

MySQL has many built-in functions, which can quickly solve some business requirements in development, including process control function, numerical function, string function, date time function, aggregation function, etc. The functions commonly used in these classifications are listed below.

1. Control flow function

• case...when

Judge the return value according to the value, and judge it by analogy with IF-ELSE in programming.

-- DEMO 01
SELECT CASE DATE_FORMAT(NOW(),'%Y-%m-%d') 
	WHEN '2019-12-29' THEN 'today' 
	WHEN '2019-12-28' THEN 'yesterday' 
	WHEN '2019-12-30' THEN 'tommor' 
	ELSE 'Unknow' END;
-- DEMO 02
SELECT (CASE WHEN 1>0 THEN 'true' ELSE 'false' END) AS result;

• if(expr1,expr2,expr3)

If expr1 is TRUE, the return value of IF() is expr2; otherwise, the return value is expr3.

SELECT IF(1>2,'1>2','1<2') AS result ; 
SELECT IF(1<2,'yes ','no') AS result ;
SELECT IF(STRCMP('test','test'),'no','yes');

• ifnull(expr1,expr2)

If the expression expr1 is not NULL, the return value is expr1; otherwise, the return value is expr2.

SELECT IFNULL(NULL,'cicada');
SELECT IFNULL(1/1,'no');

2. Common string functions

• CHAR_LENGTH()

The return value is the length of the string.

SELECT CHAR_LENGTH(' c i c ') ;-- Include spaces
SELECT LENGTH(' S q l ') ;

• CONCAT(str1...)

Concatenate string.

SELECT CONCAT('My', 'S', 'ql');
SELECT CONCAT('My', NULL, 'QL'); -- Contain Null Then return Null
SELECT CONCAT("%", "Java", "%"); -- mybatis Middle splicing fuzzy query

• ELT(N,str1,str2,...)

If N = 1, the return value is str1, if N = 2, the return value is str2, and so on, which can be used to convert the status of the returned page.

SELECT ELT(1,'Submission','In audit','Rule passing') ;
SELECT ELT(2,'Submission','In audit','Rule passing') ;

• FORMAT(X,D)

Format the number type.

SELECT FORMAT(3.1455,2); -- round to reserve two digits
SELECT TRUNCATE(3.1455,2); -- directly intercept two bits

• TRIM(str)

Empty the string space.

Select ltrim ('hel l o '); -- clear left
Select rtrim ('hel l o '); -- clear right
Select trim ('hel l o '); -- clear both sides
SELECT REPLACE('M y S Q L ',', '); -- replace all spaces

3. Numerical function

• FLOOR(X)

Returns the maximum integer value not greater than X.

SELECT FLOOR(1.23); -- 1
SELECT FLOOR(-1.23); -- -2

• MOD(N,M)

Module operation. Returns the remainder of N divided by M.

SELECT MOD(29,9); -- 2
SELECT 29 MOD 9; -- 2

• RAND() RAND(N)

Returns a random floating-point value in the range 0 to 1. If an integer parameter N is specified, it is used as a seed value to produce a repeating sequence.

SELECT RAND(); -- 0.923
SELECT RAND(20) = RAND(20) ; -- TRUE

4. Time date function

• ADDDATE(date,INTERVAL expr type)

To specify a date for operation of the specified type.

SELECT DATE_ADD('2019-12-29', INTERVAL 3 DAY); -- 2020-01-01

• CURDATE()

Returns the current date as a value in the format of 'YYYY-MM-DD' or YYYYMMDD, depending on whether the function is used in the context of a string or a number.

SELECT CURDATE(); -- '2019-12-29' Character string
SELECT CURDATE() + 0; -- 20180725 number

• DATE(expr)

Extract the date part of the date or time date expression expr.

SELECT DATE('2019-12-31 01:02:03'); -- '2019-12-31'
SELECT DATE('2019-12-31 01:02:03')+0; -- 20191231

• DATE_FORMAT(date,format)

Format the date value according to the format string.

SELECT DATE_FORMAT(NOW(), '%Y-%m-%d'); -- 2019-12-29
SELECT DATE_FORMAT(NOW(), '%Y year%m month%d day'); -- 2019 December 29, 2006

5. Aggregate function

AVG([distinct] expr) average
Count ({* | [distinct]} expr) count the number of rows
Maximum ([distinct] expr)
MIN([distinct] expr) for minimum
SUM([distinct] expr)

2, Custom function

1. Concept introduction

The function stores a series of sql statements, which are executed once when the function is called. So functions can reduce statement repetition. Function focuses on return value, while trigger focuses on execution process, so some statements cannot be executed. So the function is not just a collection of sql statements.

2. How to use

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

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

• No reference case

CREATE FUNCTION mysum1 () RETURNS INT RETURN (2+3)*2;
SELECT mysum1 () ;

• Parametric function

Table structure

CREATE TABLE t01_user (
	id int(11) NOT NULL AUTO_INCREMENT PRIMARY KEY  COMMENT 'Primary key ID',
  user_name varchar(20) DEFAULT NULL COMMENT 'User name'
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT 'User table';

Function usage

create function get_name(p_id INT) returns VARCHAR(20)
begin 
    declare userName varchar(20);
    select user_name from t01_user where id=p_id into userName;
    return userName;
end;

SELECT get_name(1) ;

3. Function view

show create function get_name ;

4. Delete function

drop function get_name ;

5. Function considerations

Functions can only be called in the server environment after being compiled in advance, so MySQL Cluster environment needs to be compiled synchronously; MySQL is a multi-threaded environment, so it is necessary to ensure that functions are also thread safe.

3, Trigger

1. Trigger introduction

Trigger is a special stored procedure. The difference is that the stored procedure needs to be called with CALL, while trigger does not need to use CALL. It does not need to be started manually, as long as a predefined event occurs, it will be automatically triggered and called by MYSQL.

2. Create trigger

Trigger syntax

CREATE TRIGGER trigger_name trigger_time trigger_event 
ON tbl_name FOR EACH ROW trigger_stmt

• trigger_name: trigger name;
• Trigger "time: the time when the action is triggered;
• Trigger? Event: the statement type of the activation trigger;
• tbl_name: the indication of trigger function, non temporary table;
• Trigger? Stmt: the statement that triggers the execution of the program;

Table data synchronization

When data is written to the user table T01 user, a copy of backup data is written to the T02 back table at the same time.

-- User backup table
CREATE TABLE t02_back (
	id int(11) NOT NULL PRIMARY KEY COMMENT 'Primary key ID',
  user_name varchar(20) DEFAULT NULL COMMENT 'User name'
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT 'User backup';

-- Trigger program
DROP TRIGGER IF EXISTS user_back_trigger ;
CREATE TRIGGER user_back_trigger AFTER INSERT ON t01_user FOR EACH ROW
BEGIN
	INSERT INTO t02_back (id,user_name)
VALUES (new.id,new.user_name);
END ;

-- Test case
INSERT INTO t01_user (user_name) VALUES ('smile'),('mysql') ;
SELECT * FROM t02_back ;

3. View triggers

View trigger refers to the definition, status and syntax information of the existing trigger in the database. You can view trigger information in the TRIGGERS table.

SELECT * FROM `information_schema`.`TRIGGERS` 
WHERE `TRIGGER_NAME`='user_back_trigger';

4. Delete trigger

The DROP TRIGGER statement can delete the trigger defined in MYSQL and the basic syntax of the trigger.

DROP TRIGGER [schema_name.]trigger_name

5. Trigger considerations

• Trigger event

For the same table, only one trigger can be created for the same event. For example, if AFTER INSERT trigger is created twice for table T01 [user], an error will be reported.

• Execution efficiency

Trigger can reduce the communication times and business logic between application and database, but if the data set is very large, the efficiency will be reduced.

• Transaction problem

Whether the execution statements of trigger and original table are in the same transaction depends on whether the published storage engine supports transactions.