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

Recently, we have to carry out a regular migration of the data in the database, in order to prevent the data transfer confusion caused by the error report of the sql statement in the execution process, so we need to control the transaction of our script.

First, let's build a tran_test table.

CREATE TABLE tran_test (F1 VARCHAR (10) NOT NULL, f2 INT (1) DEFAULT NULL, PRIMARY KEY (F1)) ENGINE=INNODB CHARSET=utf8

I want to insert two pieces of data into tran_test, but to prevent errors in the insert, I want to keep the insert statement within one transaction.

At this time, if you check some people's articles, many times you will be given such an answer.

START TRANSACTION; INSERT INTO tran_test VALUES ('Achillery 1); INSERT INTO tran_test VALUES (' Blossom 2); ROLLBACK

Or

START TRANSACTION; INSERT INTO tran_test VALUES ('Achillery 1); INSERT INTO tran_test VALUES (' Blossom 2); COMMIT

It looks like a simple sql statement, and these two sentences can indeed be committed or rolled back.

But can this really achieve our goal? The answer is no.

For example, the first paragraph is to ROLLBACK all your sql statements in the transaction, no matter whether they are right or wrong. This absolute rollback makes your sql meaningless.

So if we want to really control the transaction, my idea is to detect anomalies on the sql to be executed. If there is no exception in sql, COMMIT, and if an exception is caught, then ROLLBACK.

At this point, we need to build a stored procedure to catch exceptions. COMMIT,sql if the execution is successful, ROLLBACK will be performed when the execution fails.

Two ways of thinking can achieve the effect I want.

The first is to catch the sql we are going to execute. We define a variable, t_error, and when the exception is caught, let t_error=1. Then judge the condition of t_error, if t_error=1, carry on ROLLBACK, otherwise carry on COMMIT.

DROP PROCEDURE IF EXISTS tactile; DELIMITER / / CREATE PROCEDURE t_test () BEGIN DECLARE t_error INTEGER; DECLARE CONTINUE HANDLER FOR SQLEXCEPTION SET t_error = 1; START TRANSACTION; INSERT INTO tran_test VALUES ('Aphroma1); INSERT INTO tran_test VALUES (' Blossary 2); IF t_error = 1 THEN ROLLBACK; ELSE COMMIT; END IF; END// CALL t_test ()

The other is the simplification of the first, that is, ROLLBACK directly if the exception is caught, and COMMIT directly if the exception is not caught.

DROP PROCEDURE IF EXISTS tasking test; DELIMITER / / CREATE PROCEDURE t_test () BEGIN DECLARE EXIT HANDLER FOR SQLEXCEPTION ROLLBACK; START TRANSACTION; INSERT INTO tran_test VALUES ('Achievement page1); INSERT INTO tran_test VALUES (' Blossary page2); COMMIT; END// CALL t_test ()

In this way, the two insert statements are really controlled within a transaction.

The above examples can be tested in this time, if you have other additions and questions, you can contact the editor directly, thank you for your support.

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.