MySQL (8), mysql

MySQL (8), mysql
1 transaction

• Requirement: there is A bank account table where user A transfers money to user B. Account A decreases first and Account B increases, but A loses power after operation.
• Solution: A reduces the money, but does not modify the data table immediately. B modifies the data table after receiving the money.

 

• Transaction: A series of continuous operations to take place.
• Transaction Security: a mechanism to ensure that continuous operations meet (Implementation) at the same time. The significance of transaction security is to ensure the completion of data operations.

 

• Sample SQL script

-- CREATE an account table create table my_account (id int primary key auto_increment, number CHAR (16) not null comment 'account', name varchar (20) not null comment 'name ', money DECIMAL (0.0) DEFAULT 0000000000000001 COMMENT 'account balance '); -- INSERT data INTO my_account VALUES (null, '123', 'zhang san', 1000 ); insert into my_account VALUES (null, '123456', 'lily', 0000000000000002 );

-- Zhangsan transfer UPDATE my_account SET money = money-1000 where name = 'zhangsan ';

 

• • Once Michael transferred the money, he withdrew from the system. The terrible thing is that Mr. Li did not receive the money, so Michael lost the 1000 yuan, which is terrible. If all the banks in the world did this, it is estimated that the Bank has already closed down. What to do is to perform transaction operations.

 

1.1 transaction operations

• There are two types of transaction operations: automatic transaction (default) and manual transaction.

 

• Manual transaction: Operation Process
  • ① Start transaction: tells the system that all the following operations (writes) should not be directly written to the database, and are first stored in the transaction log.

• • ② Perform transaction operations: a series of operations (Li Si lent money to Zhang San ).
    • Li Si account reduced

-- Transaction operation: 1 Li Si account reduced by 1000 UPDATE my_account SET money = money-1000 where name = 'Li si ';

SELECT * FROM my_account;

When we start a client, we will find the following conditions.

 

• • • Increase in Michael Jacob's account

-- Transaction operation: Two three accounts are added: 1000 UPDATE my_account SET money = money + 1000 where name = 'three accounts'; SELECT * FROM my_account;

If we enable another client, the following will happen:

• • Close the transaction: You can selectively Save the operation results in the transaction log file to the data table (synchronize) or directly clear the transaction log (all the original operations are cleared ).
    • Commit transaction: Synchronize the data table (Operation successful)

-- COMMIT transaction COMMIT;

When the transaction is committed, we will find that the data on both sides of the client is the same.

• • • Rollback: directly clears the log table (operation failed)

1.2 transaction principles

• Transaction Operation Principle: after the transaction is started, all operations will be temporarily saved to the transaction log. The transaction log will be synchronized to the database table only after the commit command is obtained, transaction logs (rollback, power-off, and disconnection) will be cleared in any other situation ).

 

 

1.3 rollback point

• Rollback point: after a successful operation is completed, subsequent operations may succeed or fail. However, no matter whether the operation succeeds or fails, the previous operation has been successful; you can set a point in the current successful position for subsequent failed operations to return to this position, instead of returning all operations. This point is the rollback point.

 

• Set the rollback point: the name of the savepoint rollback point.
• Return to the rollback point: the name of the rollback to rollback point.

-- Start transaction; -- TRANSACTION processing 1: Zhang San's salary UPDATE my_account SET money = money + 10000 where name = 'zhang san'; -- SET the rollback point SAVEPOINT sp1; -- bank tax deduction UPDATE my_account SET money = money-10000*0.5 where name = 'Li si'; -- rollback to rollback point rollback to sp1; -- continue tax deduction for Michael Jacob UPDATE my_account SET money = money-10000*0.5 where name = 'Michael Jacob '; -- transaction COMMIT;

 

1.4 Automatic transactions

• In MySQL: automatic transaction processing is performed by default, and user operations are synchronized to the data table immediately.

 

• Automatic transaction: The system uses the variable control of autocommit.

SHOW VARIABLES LIKE 'autocommit';

 

• Disable Automatic transactions: set autocommit = off;

SET autocommit = off;

• Enable Automatic transaction again: set autocommit = on;

 

1.5 specific ACID of a transaction)

• Transactions have four features: ACID
  • ① A: atomic, atomicity. the whole transaction is an operation, which is inseparable, either all succeeded or all failed.
  • ② C: consistency, consistency, before and after transaction operations, the data in the transaction table has not changed.
  • ③ I: isolation, isolation, and transaction operations are mutually isolated and unaffected.
  • ④ D: durability, persistence. Once data is submitted, the table cannot be changed permanently.

 

• Lock Mechanism: innodb uses row locks by default. However, if indexes are not used during transaction operations, the system automatically retrieves data from the entire table and upgrades it to table locks.
  • Row lock: only the current row is locked and cannot be operated by other users.
  • Table lock: the entire table is locked and cannot be operated by other users.

 

2 variables

• There are two types of variables: System variables and custom variables.

 

2.1 System Variables

• System variables: variables defined by the system. Most of the time, you do not need to use system variables. System variables are used to control the performance of servers, such as autocommit.

 

• View System Variables

-- View All system variables show variables;

• View the specific system variable value: select @ system variable name;

select @@version,@@autocommit;

 

• Modify system variables
  • You can modify system variables at the session level or global level.
    • Session level: temporary modification. The current client connection is valid.

Set variable = value;

• • • Global level: one change takes effect permanently (multiple clients take effect)

Set global variable name = value;

 

2.2 custom Variables

• Define variables:
  • To distinguish between system variables and custom variables, add @

Set @ variable = value;

• • View custom Variables

Slect @ variable;

 

• In MySQL, "=" is treated as a comparison symbol by default (in many places). To distinguish between comparison and value assignment, MySQL redefined a new value assignment Symbol: =. Of course, this symbol is generally used in SQL programming.

 

• MySQL allows you to obtain data from a data table and assign values to variables in two ways.
  • ① Assign values while viewing the results

Select @ variable name: = field name from data source; -- assign the value from the field to the variable name

• • ② There is only a value assignment and no result. Strict requirements: up to one data record can be obtained, and MySQL does not support arrays.

Select field name from data source [where condition] into Variable list

SELECT NAME FROM my_account WHERE id = 1 INTO @name;SELECT @name;

 

• All custom variables are session-level: the current client is valid for the current connection.

3 trigger

• Trigger: trigger, which binds a piece of code to a table in advance. When some content in the table changes (add, delete, or modify), the system will automatically trigger the code and execute it.

 

• Attacker: event type, trigger time, and trigger object.
  • Event Type: add, delete, and modify.
  • Trigger time: before and after, before, and after.
  • Trigger object: each record In the table, for rows.

 

• A table can have only one trigger of the trigger time. A table can have up to six triggers.

 

3.1 create a trigger

 

• Trigger Syntax:

-- Temporarily modify the statement Terminator DELIMITER custom symbol: in the subsequent Code, only the custom symbol is met to end the create trigger name TRIGGER time event type ON table name for each rowbegin -- meaning that the Left braces start -- the content of the TRIGGER is: each line of content must be used; END -- END of right braces -- custom symbol of statement Terminator -- modify adjacent water to DELIMITER;

 

• Sample script:

Create table goods (id int primary key AUTO_INCREMENT, name varchar (20) not null, price DECIMAL (10, 2) DEFAULT 1, inv int comment 'inventory count '); insert into goods VALUES (NULL, 'iphone6', 2680,100); insert into goods VALUES (NULL, 'iphone6s', 2880,100); create table 'order' (id int primary key AUTO_INCREMENT, g_id int not null comment 'item id', g_number int comment 'item qty ');

 

 

• Create a trigger

-- Trigger: generate an order, delete one item inventory -- temporarily modify the statement Terminator DELIMITER/create trigger after_order after insert on 'order' for each rowbegin update goods SET inv = inv-1 WHERE id = 2; END -- END trigger/

-- Modify the temporary statement Terminator DELIMITER;

 

3.2 view trigger

• View All triggers or fuzzy match

show triggers like 'pattern';

 

 

• View trigger creation statements

 

show create trigger after_order;

 

• All triggers are stored in the information_schema.triggers table.

 

3.3 Use a trigger

• Trigger: you do not need to call the trigger manually. It is triggered automatically when a certain situation occurs.

 

• Insert order

SELECT * FROM goods;

INSERT INTO `order` VALUES (NULL,1,2);

SELECT * FROM goods;

• • ① The attacker does work: after the order is generated, the number of items in the corresponding item table is reduced. ② The current product is reduced, not the product generated in the order; it is a fixed product (the trigger is not suitable ).

 

3.4 modify and delete a trigger

• The trigger cannot be modified. You can only delete the trigger and then add a new trigger.

Drop trigger name;

 

3.5 trigger record

• Trigger record: no matter whether the trigger is triggered or not, as long as an operation is ready to be executed, the system will save the current status of the operation record and the new status after the execution, respectively, used by the trigger. The current state of the operation is saved to old, and the possible state after the operation is saved to new.

• Old indicates the old record, and new indicates the new record.
  • There is no new When deleting, but there is no old when inserting.

 

• Both old and new represent the record itself: in addition to data, any record also has a field name.
  • Usage: old. Field/new. Field.

DELIMITER $$CREATE      TRIGGER `after_order` AFTER INSERT ON `order`     FOR EACH ROW BEGIN    UPDATE goods SET inv = inv -new.g_number WHERE id = new.g_id;END;$$DELIMITER ;

SELECT * FROM goods;

INSERT INTO `order` VALUES (NULL,1,2);

• If the trigger has only one SQL command to execute, You can omit begin and end.

 

• Trigger: it can coordinate the data processing sequence and relationship in the table. However, from the java perspective, the trigger increases the difficulty of database maintenance, so the trigger is rarely used.