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

Source code of this article: GitHub click here | | GitEE click here |

1. Stored procedure 1. Introduction to concept

A stored program is a combined SQL statement stored in the server, which is compiled, created and saved in the database, and the user can call and execute it through the name of the stored procedure. The core idea of stored procedure is the encapsulation and reusability of database SQL language. The use of stored procedures can reduce the business complexity of the application system, but it will increase the load of the database server system, so comprehensive business considerations are needed when using it.

2. Basic syntax format CREATE PROCEDURE sp_name ([proc_parameter [,...]]) [characteristic...] Routine_body case 1: calculate consumption discount-create stored procedure DROP PROCEDURE IF EXISTS p01_discount; CREATE PROCEDURE p01_discount (IN consume NUMERIC (5Magne2), OUT payfee NUMERIC (5Magne2) BEGIN-determine charging method IF (consume > 100.00 AND consume300.00) THEN SET payfee=consume*0.6; ELSE SET payfee=consume; END IF; SELECT payfee AS result;END -- call the stored procedure CALL p01_discount (100.0); case 2: While..Do writes data

Provide a data sheet

CREATE TABLE `t03Proced` (`id`int (11) NOT NULL AUTO_INCREMENT COMMENT 'primary key ID', `temp_ name` varchar (20) DEFAULT NULL COMMENT' name', PRIMARY KEY (`id`) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT=' trigger to write data'

Storage program

Determine the number of pieces of data written to the t03_proced table based on the parameters passed in.

DROP PROCEDURE IF EXISTS p02_batch_add; CREATE PROCEDURE p02_batch_add (IN count INT (11)) BEGIN DECLARE temp int default 0; WHILE temp < count DO INSERT INTO t03_proced (temp_name) VALUES ('pro_name'); SET temp = temp+1; END WHILE;END;-- Test: write 10 pieces of data call p02_batch_add (10); 3. Precautions business scenario

Stored procedures are not widely used in actual development, and complex business scenarios are usually developed at the application level, which can be better managed, maintained and optimized.

Execution speed

If, in the simple scenario of single table data writing, client writes based on application programs or database connections will be much slower than stored procedures, and stored procedures have to a large extent no network communication overhead, parsing overhead, optimizer overhead, etc.

II. MySQL View 1. Basic concepts

The view itself is a virtual table that does not hold any data. When using SQL statements to access the view, the data obtained is generated by MySQL from other tables, and the view and the table are in the same namespace. View query data is relatively safe, it can hide some data and structures, and only let users see the data within the authority, so that complex queries are easy to understand and use.

2. View usage

Now based on the basic usage of the user and order management demo view.

Basic table structure CREATE TABLE v01_user (id INT (11) NOT NULL AUTO_INCREMENT COMMENT 'primary key ID', user_name VARCHAR (20) DEFAULT NULL COMMENT' user name', phone VARCHAR (20) DEFAULT NULL COMMENT 'mobile number', pass_word VARCHAR (64) DEFAULT NULL COMMENT 'password', card_id VARCHAR (18) DEFAULT NULL COMMENT'ID card ID', pay_card VARCHAR (25) DEFAULT NULL COMMENT 'card number' PRIMARY KEY (id)) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT 'user Table' CREATE TABLE v02_order (id INT (11) NOT NULL AUTO_INCREMENT COMMENT 'primary key ID', user_id INT (11) NOT NULL COMMENT' user ID', order_no VARCHAR (32) DEFAULT NULL COMMENT 'order number', good_name VARCHAR (60) DEFAULT NULL COMMENT 'commodity name', good_id INT (11) DEFAULT NULL COMMENT 'commodity ID', num INT (11) DEFAULT NULL COMMENT' purchase quantity' Total_price DECIMAL (10jue 2) DEFAULT NULL COMMENT 'Total Price', PRIMARY KEY (id)) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT 'order Table' Basic syntax CREATE OR REPLACE VIEW view_name AS select_statement

Note: tables and views share the same namespace in the database, so the database cannot contain tables and views with the same name.

User order view CREATE OR REPLACE VIEW user_order_view AS SELECT t1.iddiary t1.userroomnamediary t2.orderorders nomemorialt2.goodstationid, t2.goodstationnamememothet2.numrecothet2.totalaccounpricefrom v01_user t1LEFT JOIN v02_order T2 ON t2.user_id = t1.id; call the view

This is basically the same as MySQL's table query, and you can use a variety of query conditions.

SELECT * FROM user_order_view WHERE user_name='Cicada'; View SHOW CREATE VIEW user_order_view; modify view ALTER VIEW view_name AS select_statement; delete view DROP VIEW [IF EXISTS] view_name; 3. View update

If the specified conditions permit, you can update the related tables involved in the view by updating, deleting, or even writing data on the view.

UPDATE user_order_view SET user_name='smile' WHERE id='1'

Here, the v01 _ user table data is updated by performing an update operation on the view. If the view is defined using special operations such as aggregate functions, grouping, and so on, it cannot be updated. MySQL does not support creating triggers on views.

4. View implementation of temporary table algorithm

The server will save the data of the view query SQL in the temporary table, and the structure of the temporary table is consistent with the view field structure, which is the most taboo operation in SQL query optimization. If the amount of data is slightly large, it will seriously affect the performance. If the view cannot have an one-to-one mapping with the original table, a temporary table will be generated, which shows that the view is not very simple, or even very complex.

Merging algorithm

The server executes the query based on the table used in the view, and finally merges the query structure and returns it to the client.

Distinguishing method

Execute the following query statement to analyze the executed performance parameters.

EXPLAIN SELECT * FROM user_order_view

Observe the select_type field in the query results, and if it is DERIVED, the temporary table is used. Here SQL performs parsing after the syntax optimization section will be explained in more detail.

5. Notice the performance problems.

MySQL does not support creating indexes in views, which may cause many query performance problems when using views, so it is recommended to carefully examine and test them from multiple angles when using them.

Special usage

For view-based queries, you can modify part of the table structure so that the query of the view is not affected as long as the fields are not used in the view.

Source code address GitHub address https://github.com/cicadasmile/mysql-data-baseGitEE address https://gitee.com/cicadasmile/mysql-data-base

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.