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

This blog introduces the transaction function and trigger function of MySQL, as well as their usage.

One. MySQL transaction

A transaction is a set of atomic SQL queries, or a separate unit of work. All operations in the transaction either succeed or fail.

In MySQL, the transaction function can be used to selectively roll back and commit the data operations in the table, similar to the memory function in word (CTRL+Z undo if you do something wrong)

MySQL default principle for handling tasks: add, delete, and change operations will automatically save data to the database and table records

Life example: just like playing a game, when the player is playing the game, if the money is deducted, but the props are not available immediately, this will obviously greatly reduce the player's experience of the game and eventually lead to unhappiness on both sides. what happens in this situation is that the transaction of the data is not guaranteed. If this happens, the transaction function can solve this problem, that is, if the props do not arrive, the balance of the player's account will not be deducted.

There are two main ways to handle transactions in MySQL:

1. Use BEGIN, ROLLBACK, COMMIT to implement

Begin: start a transaction, then execute create, update, insert and other commands to operate on the data

Rollback: transaction rollback (equivalent to ctrl+z undo in word)

Commit: transaction confirmation (commit, equivalent to ctrl+s save in word)

2. Change the auto-commit mode of mysql through set

Note: MYSQL is automatically submitted by default, that is, if you submit a QUERY, it will be executed directly! We can pass.

Show variables\ G View MYSQL environment variables

Show variables like 'autocom%'; to view the status of the autocommit environment variable

Set autocommit=0 forbids autocommit (temporary setting), that is, the transaction feature is enabled automatically. You do not need to start a transaction with begin, but you must use commit to commit the operation or use rollback to undo the operation. Check the autocommit environment variable at this time. Value is OFF:

Set autocommit=1 enables autocommit (you must start a transaction with begin and end it with commit or rollback) to implement transaction processing. Check that the auto threshold value is status ON.

But note that when you use set autocommit=0, all your future SQL will be transacted until you end with commit confirmation or rollback, and when you finish the transaction, you also start a new transaction! Follow the first method to treat the current as a transaction only! Personal recommendation to use the first method!

Only data tables of type INNODB and BDB in MYSQL can support transactions! Other types are not supported!

Note: the transaction feature is only valid for insert, delete, and update operations of the table.

Example: enter the database, use the test database, create a testdb table, view the data in the table, then turn on the transaction function, insert 2 pieces of data, commit the transaction, and view the data in the table; then open a new transaction, insert a record, do a rollback operation (undo), and view the data in the table

Step 1: create a testdb table in the test database and view the data in the table

Step 2: turn on the transaction function, insert the data, and then commit

Step 3: open a new transaction, insert a record, roll back (undo), and view the data in the table

Two. Mysql trigger function

Trigger is a special stored procedure, its execution is not called by the program, nor started manually, but triggered by the event (event).

For example, when an operation event (insert,delete, update) is performed on table A, it is activated to execute. Triggers are often used to strengthen data integrity constraints and business rules.

Take the life example as an example: press the computer boot button at An and the computer will turn on.

Press the light switch at An and the light on the ceiling at B will come on.

Sign in at A, and the aisle door opens.

Create trigger (trigger) syntax:

CREATE TRIGGER trigger name BEFORE | AFTER trigger event

ON table name FOR EACH ROW

BEIGN

Trigger program body

END

Up to 64 characters, which is named in the same way as other objects in MySQL

{BEFORE | AFTER} trigger timing

{INSERT | UPDATE | DELETE} triggered event

ON identifies the name of the table on which the trigger is created, that is, on which table the trigger is created

Execution interval of FOR EACH ROW triggers: the FOR EACH ROW clause tells the trigger to perform an action every other row, rather than on the entire table

SQL statements to be triggered: available sequence, judgment, loop and other statements to achieve the logic functions needed by general programs

Example: create a stu student table and a stu_ total table to count the number of stu tables in the test database, then create a trigger in which the student table changes and the stu_total table changes, operate on the stu table, and then see what changes the stu_total will have due to the trigger.

Step 1: create these two tables and view the data in the table

Step 2: create trigger stu_insert_trigger and trigger stu_delete_trigger

Step 3: define back to the action Terminator (;) again and look at the trigger

Step 4: detect the trigger result

Step 5: delete the stu table to verify the experimental effect again.

As can be seen from the experimental results, when we add or delete data from the stu table, after defining the trigger, the stu_total table will change differently according to the different operations of the stu table!

Delete trigger: DROP TRIGGER trigger name

Note: the trigger failure bug of this example: if the initial statistics in the stu_total table are incorrect, the trigger defined above will cause the total value in the stu_total table to be asymmetric with the total number of records in the stu table, so there is a problem with this trigger. The correct solution is to use the count function to count the number of records in the stu table, not to add or subtract 1.

The correct optimized trigger:

# trigger stu_insert_trigger drop triggers stu_insert_trigger; delimiter $$create trigger stu_insert_trigger after insert on stu for each row BEGIN update stu_total set total= (select count (*) from stu); END$$ delimiter

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.