Mysql from entry to enchantment-8. Views, stored procedures, transaction processing

Mysql from entry to enchantment-8. Views, stored procedures, transaction processing

❤ Series content❤

Mysql from entry to enchantment-1. Data preparation (
starting article) Mysql from entry to enchantment-2. Manipulating databases and tables
Mysql from entry to enchantment-3. Query, sort, and WHERE filtering
Mysql from entry to enchantment- 4. Wildcards, calculated fields, and functions
Mysql from entry to enchantment-5. Aggregation, grouping, and sub-query
Mysql from entry to enchantment-6. Table join, combined query
Mysql from entry to enchantment-7. Insert, update and Delete
Mysql from entry to enchantment-8. Views, stored procedures, transaction processing
Mysql from entry to enchantment-9. Cursor, advanced SQL features (to be updated...)
Mysql from entry to enchantment-10. Shorthand for knowledge points (end) )(pending upgrade…)


Main content of this article

Why use views, view creation, modification, and deletion; why use stored procedures, the use of three parameters in stored procedures; how to perform transaction processing, and the types of transaction isolation levels and their respective defects. Not much to say, let's get to the point!



1. View

1.1 Why use views

The view is a virtual table . Unlike tables that contain data, views only contain queries that dynamically retrieve data when used.

The role of the view:

Reuse SQL statements.

Simplify complex SQL operations. After writing a query, you can easily reuse it without knowing the basic query details.

Use part of the table instead of the entire table.

Protect data. You can grant users access to specific parts of the table instead of access to the entire table.

Change the data format and presentation. The view can return data that is different from the representation and format of the underlying table.

After creating views, you can use them in basically the same way as tables. View may perform SELECToperations, sorting and filtering data, the view or views coupled to the other tables, and even add and update data.


1.2 Create a view

With the CREATE VIEWstatement to create a view. See the next section for detailed usage.


1.3 Simplify complex connections with views

One of the most common view applications is to hide complex SQL, which usually involves connections.

Now create named ProductCustomersviews, links three tables, have been ordered to return a list of all customers of any product.

mysql> CREATE VIEW ProductCustomers AS
    -> SELECT cust_name, cust_contact, prod_id
    -> FROM Customers, Orders, OrderItems
    -> WHERE Customers.cust_id = Orders.cust_id
    ->  AND OrderItems.order_num = Orders.order_num;

With this view, inquiry ordered products RGAN01the customer.

mysql> SELECT cust_name, cust_contact
    -> FROM ProductCustomers
    -> WHERE prod_id = 'RGAN01';
+---------------+--------------------+
| cust_name     | cust_contact       |
+---------------+--------------------+
| Fun4All       | Denise L. Stephens |
| The Toy Store | Kim Howard         |
+---------------+--------------------+

As can be seen, greatly simplified view of the complex SQLused statement.


1.4 Filter data with views

You can define CustomerEMailListviews, filters, no customers e-mail addresses.

mysql> CREATE VIEW CustomerEMailList AS
    -> SELECT cust_id, cust_name, cust_email
    -> FROM Customers
    -> WHERE cust_email IS NOT NULL;

Now, you can use the view like any other table CustomerEMailList.

mysql> SELECT *
    -> FROM CustomerEMailList;
+------------+--------------+-----------------------+
| cust_id    | cust_name    | cust_email            |
+------------+--------------+-----------------------+
| 1000000001 | Village Toys | [email protected] |
| 1000000003 | Fun4All      | [email protected]    |
| 1000000004 | Fun4All      | [email protected] |
+------------+--------------+-----------------------+


1.5 Modify the view

Use the ALTER VIEWstatement to modify the view, the view is modified to ensure that there is.

Now the same customer does not filter e-mail address, but only returns cust_idthe column.

mysql> ALTER VIEW CustomerEMailList AS
    -> SELECT cust_id
    -> FROM Customers
    -> WHERE cust_email IS NOT NULL;


1.6 Delete view

Use the DROP VIEWstatement to delete the view, when you delete a view to ensure that there is.

DROP VIEW CustomerEMailList;


1.7 Performance issues

Because the view does not contain data, every time you use the view, you must process all the retrievals that are required when the query is executed. If you use multiple joins and filters to create complex views or nested views, performance may drop drastically. Therefore, you should test before deploying an application that uses a large number of views.



2. Stored procedure

2.1 Why use stored procedures

advantage:

  • By encapsulating "a certain process" in an easy-to-use unit, complex operations can be simplified
  • Since it is not required to repeatedly establish a series of processing steps, the consistency of the data is ensured.
  • Simplify the management of changes. If the table name, column name, or business logic (or other content) changes, then only the code of the stored procedure needs to be changed. People who use it don't even need to be aware of these changes.
  • Because stored procedures are usually stored in compiled form, the DBMS requires less work to process commands, which improves performance.

Simply put, there are three main benefits of using stored procedures: simplicity, security, and high performance.


2.2 Create a stored procedure

Let's look at a simple example of a stored procedure that counts customers with email addresses.

mysql> DELIMITER $$
mysql> CREATE PROCEDURE MailingListCount()
    -> BEGIN
    ->     SELECT COUNT(*) AS cust_count
    ->     FROM Customers
    ->     WHERE cust_email IS NOT NULL;
    -> END$$

When definition process, using the DELIMITER $$command sentence end symbol semicolon from ;temporary to $$that used in the body during a semicolon is directly transmitted to the server and will not be Mysqlexplained.

Use CALL 存储过程名(参数)to call a stored procedure.

mysql> DELIMITER ;
mysql> CALL MailingListCount();
+------------+
| cust_count |
+------------+
|          3 |
+------------+


2.3 Stored procedure body

Body of the procedure contains the statement must be executed when the procedure calls, such as: dml、ddlstatements, if-then-elseand while-dostatements, declare a variable of declarestatements and so on.

Process body format: to beginstart to endend (nestable)

BEGIN
  BEGIN
    BEGIN
      statements; 
    END
  END
END

Note : Each of the nested block and each statement must end with a semicolon, the representation of the body end begin-endblocks (also called compound statement compound statement), then no semicolon.


2.4 Stored procedure parameters

A stored procedure can have 0 or more parameters for the definition of the stored procedure.

3 types of parameters:

  • IN (Input parameter): indicates that the caller passes in a value to the process (the incoming value can be a literal or a variable)
  • OUT (Output parameter): indicates that the process sends out a value to the caller (multiple values ​​can be returned) (the outgoing value can only be a variable)
  • INOUT (Input and output parameters): It not only means that the caller passes in a value to the process, but also means that the process passes a value to the caller (the value can only be a variable)


2.4.1 IN input parameters

mysql> DELIMITER $$
mysql> CREATE PROCEDURE in_test(IN p_in INT)
    -> BEGIN
    ->     SELECT p_in;    -- 第一次查询
    ->     SET p_in = 2;   -- 修改p_in的值
    ->     SELECT p_in;    -- 第二次查询
    -> END$$
    
mysql> DELIMITER ;

mysql> SET @p_in = 1;

mysql> CALL in_test(@p_in);
+------+
| p_in |
+------+
|    1 |
+------+

+------+
| p_in |
+------+
|    2 |
+------+

mysql> SELECT @p_in;
+-------+
| @p_in |
+-------+
|     1 |
+-------+

Although p_inmodified during storage, but does not affect @p_inthe value, because in_testonly accepts input parameters, output parameters is not, therefore it corresponds to the change in function, but not to the output value @p_in.


2.4.2 OUT input parameters

mysql> DELIMITER $$
mysql> CREATE PROCEDURE out_test(OUT p_out INT)
    -> BEGIN
    ->     SELECT p_out;    -- 第一次查询
    ->     SET p_out = 2;   -- 修改p_out的值
    ->     SELECT p_out;    -- 第二次查询
    -> END$$

mysql> DELIMITER ;

mysql> SET @p_out = 1;

mysql> CALL out_test(@p_out);
+-------+
| p_out |
+-------+
|  NULL |
+-------+

+-------+
| p_out |
+-------+
|     2 |
+-------+

mysql> SELECT @p_out;
+--------+
| @p_out |
+--------+
|      2 |
+--------+

The first result returned NULLis because OUTthe output parameters to the caller, does not receive input parameters, so the first time the query p_outhas not been assigned, it is NULL. The last @p_outvalue of the variable becomes 2 because calling a out_teststored procedure output parameter, change the p_outvalue of the variable.


2.4.3 INOUT input and output parameters

mysql> DELIMITER $$
mysql> CREATE PROCEDURE inout_test(INOUT p_inout INT)
    -> BEGIN
    ->     SELECT p_inout;    -- 第一次查询
    ->     SET p_inout = 2;   -- 修改p_inout的值
    ->     SELECT p_inout;    -- 第一次查询
    -> END$$
    
mysql> DELIMITER ;

mysql> SET @p_inout = 1;

mysql> CALL inout_test(@p_inout);
+---------+
| p_inout |
+---------+
|       1 |
+---------+

+---------+
| p_inout |
+---------+
|       2 |
+---------+

mysql> SELECT @p_inout;
+----------+
| @p_inout |
+----------+
|        2 |
+----------+

Call the inout_teststored procedure, both received input parameters, and output parameters, @p_inoutthe values are changed.


2.5 Delete the stored procedure

Use DROP PROCEDUREto delete a stored procedure.

mysql> DROP PROCEDURE in_test;


3. Management transaction processing

3.1 Transaction processing

Transaction processing ( transaction processing)是一种机制, 用来管理必须成批执行的SQL` operation, to ensure that the database does not contain incomplete operation results. Using transaction processing, you can ensure that a set of operations will not stop halfway, they are either completely executed or not executed at all (unless explicitly indicated). If no error occurs , The entire set of statements is submitted (written to) the database table; if an error occurs, it is rolled back (undo), and the database is restored to a known and safe state to maintain the integrity of the database.

Transaction processing terms:

  • Transaction (Transaction) : refers to a set of SQLstatements;
  • Rollback (ROLLBACK) : refers to revoke the designation SQLstatement process;
  • Submit (the commit) : refers to the unsaved SQLstatement results into the database table;
  • Savepoint : Refers to the temporary placeholder set in the transaction process, and can be issued a rollback (different from rollback of the entire transaction process).

Transactions used to manage INSERT, UPDATEand DELETEstatements. Can not rollback SELECTstatement (rollback SELECTstatement is not necessary), can not be rolled back CREATEor DROPoperation. These statements can be used in transaction processing, but these operations are not undone during rollback.

Generally speaking, transactions must meet 4 conditions ( ACID): Atomicity ( Atomicity , or indivisibility), consistency ( Consistency ), isolation ( Isolation , also known as independence), durability ( Durability ).

  • Atomicity:transaction All operations in a transaction ( ) are either completed or not completed at all, and will not end in a certain link in the middle. If an error occurs during the execution of the transaction, it will be rolled back ( Rollback) to the state before the transaction started, as if the transaction had never been executed.
  • Consistency: Before the start of the transaction and after the end of the transaction, the integrity of the database has not been destroyed. This means that the written data must fully comply with all the preset rules, which includes the accuracy and continuity of the data, and the subsequent database can spontaneously complete the scheduled work.
  • Isolation: The ability of the database to allow multiple concurrent transactions to read, write and modify its data at the same time. Isolation can prevent data inconsistencies caused by cross execution when multiple transactions are executed concurrently. Transaction isolation is divided into different levels, including read uncommitted ( Read uncommitted), read commit ( read committed), repeatable read ( repeatable read) and serialization ( Serializable).
  • Persistence: After the transaction is completed, the modification of the data is permanent, and it will not be lost even if the system fails.
Under the default settings of the MySQL command line, transactions are automatically committed, that is, the COMMIT operation will be executed immediately after the SQL statement is executed. Therefore, to explicitly open a transaction, you must use the command BEGIN or START TRANSACTION, or execute the command SET AUTOCOMMIT=0, which is used to prohibit the automatic submission of the current session.


3.2 Control transaction processing

  • BEGIN / START TRANSACTION : Explicitly start a transaction;
  • COMMIT / COMMIT WORK : Commit the transaction to make all modifications to the database permanent;
  • ROLLBACK / ROLLBACK WORK: Rollback will end the user's transaction and cancel all uncommitted changes in progress ;
  • SAVEPOINT: SAVEPOINTAllow to create a save point in a transaction, there can be multiple in a transaction SAVEPOINT;
  • RELEASE SAVEPOINT : Delete the savepoint of a transaction. When there is no specified savepoint, an exception will be thrown when the statement is executed;
  • ROLLBACK TO : Roll back the transaction to the marked point;
  • SET TRANSACTION: Used to set the isolation level of the transaction. InnoDBThe storage engine provides transaction isolation levels READ UNCOMMITTED(read uncommitted), READ COMMITTED(read submitted, commonly used in projects), REPEATABLE READ(repeatable read, Mysqldefault isolation level) and SERIALIZABLE(serialization).

Simple example:

mysql> use test;

mysql> CREATE TABLE transaction_test(id int(5)) ENGINE = INNODB;  # 创建数据表
 
mysql> SELECT * FROM transaction_test;
Empty set (0.01 sec)

mysql> BEGIN;  # 开始事务
 
mysql> INSERT INTO runoob_transaction_test VALUE(1);
 
mysql> INSERT INTO runoob_transaction_test VALUE(2);
 
mysql> COMMIT; # 提交事务
 
mysql>  SELECT * FROM transaction_test;
+------+
| id   |
+------+
| 1    |
| 2    |
+------+



mysql> BEGIN;    # 开始事务

mysql> INSERT INTO transaction_test VALUES(3);

mysql> SAVEPOINT first_insert;    # 声明一个保存点

mysql> INSERT INTO transaction_test VALUES(4);

mysql> SAVEPOINT second_insert;    # 声明一个保存点

mysql> INSERT INTO transaction_test VALUES(5);

mysql> ROLLBACK TO second_insert;    # 回滚到 second_insert 保存点

mysql> SELECT * FROM transaction_test;    # 因为回滚所以数据没有插入
+------+
| id   |
+------+
|    1 |
|    2 |
|    3 |
|    4 |
+------+

mysql> ROLLBACK TO first_insert;

mysql> SELECT * FROM transaction_test;
+------+
| id   |
+------+
|    1 |
|    2 |
|    3 |
+------+

mysql> COMMIT;    # 执行 COMMIT 或 ROLLBACK 后保存点自动释放


3.3 Transaction isolation level

Let's take a look at how to view the transaction isolation level.

# 查看默认事务隔离级别(session)
select @@transaction_isolation;
# 查看当前会话的隔离级别
select @@session.transaction_isolation;
# 查看全局的事务隔离级别
select @@global.transaction_isolation;

In Mysqlisolation level transaction under four, from low to high was Read uncommitted, , Read committed(theRepeatable read default), Serializablethese four levels of the three levels can be solved one by one after dirty reads , non-repeatable read , phantom read problems.


3.3.1 Dirty read

For two transactions T1 and T2, after T1 reads a field that has been updated by T2 but has not yet been committed, if T2 rolls back at this time, the content read by T1 is temporary and invalid.

Example:

Open two Mysql clients and perform the following operations respectively to query the isolation level of the current session (default REPEATABLE READ). Modify the current session isolation level to ( READ UNCOMMITTED). The global transaction isolation level is still REPEATABLE READ.

mysql> SELECT @@session.transaction_isolation;
+-------------------------+
| @@transaction_isolation |
+-------------------------+
| REPEATABLE-READ         |
+-------------------------+

mysql> SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;    # 修改会话隔离级别

mysql> SELECT @@session.transaction_isolation;    # 当前会话隔离级别已修改
+---------------------------------+
| @@session.transaction_isolation |
+---------------------------------+
| READ-UNCOMMITTED                |
+---------------------------------+

mysql> SELECT @@global.transaction_isolation;    # 全局事务隔离级别未修改
+--------------------------------+
| @@global.transaction_isolation |
+--------------------------------+
| REPEATABLE-READ                |
+--------------------------------+

After that, the black box is used for update, and the white box is used for query.

Insert picture description here

Due to the rollback of ④ in the black frame, the data read by ③ in the client with a white background is temporary and invalid. That is dirty read.


3.3.2 Non-repeatable read

For two transactions T1 and T2, T1 reads a field, and then T2 updates the field and submits it. When T1 reads it again, inconsistent results occur.

Insert picture description here

Due to the update operation of the black frame, the results of the two reads in the white frame are inconsistent.


3.3.3 Phantom reading

For the two transactions T1 and T2, T1 reads data from the table, and then T2 performs an INSERT operation and submits it. When T1 reads again, the results are inconsistent.

Insert picture description here

Due to the insertion of the black frame, the results of the two reads in the white frame are inconsistent.


The possibility of several phenomena in each isolation level :

Isolation levelDirty ReadNonRepeatable ReadPhantom Read
Read uncommittedmaymaymay
Read committedimpossiblemaymay
Repeatable readimpossibleimpossiblemay
Serializable (Serializable)impossibleimpossibleimpossible

Reference link:
Create MySQL stored procedure and call
MySQL transaction (dirty read, non-repeatable read, phantom read)

That's all there is to this article, if it feels good. ❤ Click a like before leaving! ! ! ❤Follow

Insert picture description here


-up will continue to share the "Mysql From Getting Started to Enchanting" series of articles, if you are interested, you can click to pay attention to not get lost~.