What is a stored procedure?

The MySQL5.0 version starts with supported stored procedures. Stored Procedure is a database object that stores complex programs in a database for external programs to call. A stored procedure is a set of SQL statements designed to perform a specific function. It is created by compilation and stored in a database. The user can invoke the execution of the stored procedure by specifying its name and giving parameters (if necessary). Stored procedure is very simple in idea, which is code encapsulation and reuse at database SQL language level.Copy the code

2. Advantages and disadvantages of stored procedures

2.1 the advantages

  1. Stored procedures can encapsulate and hide complex business logic.
  2. Stored procedures can send back values and can accept parameters.
  3. The stored procedure cannot be used
  4. A stored procedure cannot be run using the SELECT specification because it is a subroutine, unlike lookup tables, data tables, or function definitions.
  5. Stored procedures can be used to validate data, enforce business logic, and so on.

2.2 disadvantages

  1. Stored procedures are often customized on a particular database because of the different programming languages supported. When switching to another vendor’s data system, the existing stored procedures need to be rewritten.
  2. Performance tuning writing of stored procedures is limited to a variety of database systems.

What are the features of stored procedures

  1. There are input and output parameters, variables can be declared, if/else, case,while and other control statements, by writing stored procedures, you can achieve complex logic functions.
  2. Common features of functions: modularization, encapsulation, code reuse.
  3. Fast, only the first execution needs to go through the compilation and optimization steps, subsequent calls can be directly executed, skip the above steps.

Four, actual combat operation

Generating tens of millions of data volumes

First we create 2 tables, one table is to store real data, the other table to create a memory table for temporary storage of data. We can be very efficient with stored procedures.

4.1 Creating memory tables and common tables

CREATE TABLE 'user' (' id' int(11) NOT NULL AUTO_INCREMENT primary key comment '主 库 iD ', ` user_id ` varchar (36), NOT NULL comment 'user id' ` user_name ` varchar (30) NOT NULL comment 'user name', 'phone' varchar(20) NOT NULL comment 'varchar ',' lan_id 'int(9) NOT NULL comment' varchar ', 'lan_id' int(9) NOT NULL comment 'varchar ', 'region_ID' int(9) NOT NULL comment 'region_ID ',' create_time 'datetime NOT NULL comment' create_time ', KEY `idx_user_id` (`user_id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; CREATE TABLE user_memory (' id' int(11) NOT NULL PRIMARY KEY AUTO_INCREMENT comment '主键 iD ', ` user_id ` varchar (36), NOT NULL comment 'user id' ` user_name ` varchar (30) NOT NULL comment 'user name', 'phone' varchar(20) NOT NULL comment 'varchar ',' lan_id 'int(9) NOT NULL comment' varchar ', 'lan_id' int(9) NOT NULL comment 'varchar ', 'region_ID' int(9) NOT NULL comment 'region_ID ',' create_time 'datetime NOT NULL comment' create_time ', KEY `idx_user_id` (`user_id`) ) ENGINE=MEMORY DEFAULT CHARSET=utf8mb4;Copy the code

4.2 Creating a Stored Procedure

4.2.1 Creating and generating N random number functions

DELIMITER $$CREATE FUNCTION randNumber(N int) RETURNS VARCHAR(255) BEGIN # Define a default value 0-9 declare CHARs_str varchar(20) default '0123456789'; Return_declare return_str vARCHar (255) DEFAULT "; DECLARE I INT DEFAULT 0; DECLARE I INT DEFAULT 0; SET return_str = concat(return_str, substring(chars_str, FLOOR(1 + RAND() * 10), 1)); SET I = I + 1; END WHILE; RETURN return_str; END $$DELIMITER;Copy the code

4.2.2 Generating mobile Phone Numbers

DELIMITER $$CREATE FUNCTION genePhone() RETURNS VARCHar (20)BEGIN define a variable to save the DECLARE head char(3); DECLARE phone vARCHar (20); DECLARE BODys VARCHar (100) default "130 131 132 133 134 135 136 138 138 139 186 187 189 151 157"; # DECLARE starts int; SET starts = 1 + floor(rand() * 15) * 4; SET head = trim(substring(bodys, starts, 3)); SET phone = trim(concat(head, randNumber(8))); # data RETURN phone; END $$DELIMITER ;Copy the code

4.2.3 Generating random Strings

# to CREATE random string and random FUNCTION of time DELIMITER $$the CREATE FUNCTION randString INT (N) RETURNS a varchar (255) CHARSET utf8mb4DETERMINISTICBEGIN DECLARE CHARs_str vARCHAR (100) DEFAULT DECLARE CHARs_str vARCHar (100) DEFAULT DECLARE chars_str vARCHar (100) DEFAULT 'abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789! @ # $% ^ & * () _ +} {" :? <,. / >? <? '; DECLARE return_str vARCHar (255) DEFAULT "; DECLARE return_str vARCHar (255) DEFAULT "; DECLARE I INT DEFAULT 0; DECLARE I INT DEFAULT 0; SET return_str = concat(return_str, substring(chars_str, FLOOR(1 + RAND() * 79), 1)); SET i = i + 1; END WHILE; RETURN return_str; END$$ DELIMITER;Copy the code

4.2.4 Inserting N Entries into a MEMORY table

DELIMITER $$CREATE PROCEDURE 'add_user_memory' (IN N int) BEGIN DECLARE I int DEFAULT 1; WHILE (i <= n) DO INSERT INTO user_memory (user_id, user_name, phone, lan_id,region_id, create_time) VALUES (uuid(), randString(20), genePhone(), FLOOR(RAND() * 1000), FLOOR(RAND() * 100), NOW()); SET i = i + 1; END WHILE; END $$ DELIMITER ; Insert data from the memory table into the ordinary table. DELIMITER $$CREATE PROCEDURE 'add_user_MEMORY_to_outside' (IN n int, IN count int) BEGIN DECLARE i INT DEFAULT 1; WHILE (i <= n) DO CALL add_user_memory(count); INSERT INTO user SELECT * FROM test_user_memory; delete from ser_memory; SET i = i + 1; END WHILE; END $$ DELIMITER ;Copy the code

4.3 Inserting Data for testing

CALL add_user_memory(10000); CALL add_user_memory(10000); INSERT INTO user SELECT * FROM user_memory; Delete from user_memory; Select count(1) from user#10000 using add_user_memory_to_outside 00 add_user_memory_to_outside (1000100) # 10010000Copy the code

Summary: This blog explains what a stored procedure is and what it can do. If MySQL stores a lot of data, it is very slow to query based on conditions. In tens of millions of data volume we query a piece of query data will need more than 3 seconds of time, often in the enterprise to query a data need several seconds of time is certainly not good