当前位置：网站首页>How to test insert and update statements before execution

How to test insert and update statements before execution

2022-07-21 23:48:00 【Navicat China】

In some cases , Run carefully drafted in the production environment UPDATE Statements can relieve the crisis . At other times , A clumsy UPDATE It may cause more harm than the original problem . Just like you can always execute data manipulation language on the development or test database （Data Manipulation Language,DML） sentence , But due to the difference of data , This method can only judge the impact of statements on production data at most .

that , Running INSERT、UPDATE or DELETE The statement before , Which options can accurately predict the impact of its results on production data ？ It depends at least in part on the database vendor and product . There are also some solutions that are widely supported . We will look at these two options in this article .

Syntax check

The process of testing statements can be divided into two stages . The first is to verify whether the statement is syntactically valid （ That is, it can execute ）. The next step is to determine whether it can produce the results you want .

One way to verify the syntax is to send it to the database （DB） Ask about the inquiry plan . This can tell you two things ：

Check whether there is syntax error ; If so , The query plan command itself will fail .
How the database plans to execute queries , For example, what index will be used .

In most relational databases , The query plan command is “explain” or “describe”, As shown below ：

explain update ...;

stay Navicat Database management and development tools , Click the button to run EXPLAIN command . If the statement fails , You will receive an error message similar to the following ：

otherwise , The query plan will be displayed in tabular form ：

Statement test

You can analyze a statement to see if it is syntactically valid , But that doesn't mean it will produce the right result . To see what the query actually does , You have several options .

Turn off auto submit

Most relational databases provide a way to disable automatic submission （autocommit） Pattern approach , So you have to send COMMIT Statement to store changes to disk , Or send out ROLLBACK To ignore changes .

stay MySQL in , The command to disable auto submit mode is ：

SET autocommit=0

Or

SET autocommit = OFF

stay SQL Server in , The order is ：

SET IMPLICIT_TRANSACTIONS OFF

After turning off auto submit , You can now run statements in transactions to try ：

-- 1. start a new transaction
START TRANSACTION;

-- 2. insert a new order for customer 145
INSERT INTO orders(orderNumber,
                   orderDate,
                   requiredDate,
                   shippedDate,
                   status,
                   customerNumber)
VALUES(@orderNumber,
       '2005-05-31',
       '2005-06-10',
       '2005-06-11',
       'In Process',
        145);
        
-- 3. then, after evaluating the results,
--    rollback the changes
ROLLBACK;

This will keep the database in exactly the same state as before running the statement .

Convert the statement to SELECT

test DML A low-tech approach to statements is to convert them into SELECT. As long as you don't expect them to retrieve the entire database , Use them as SELECT Running is a good way to accurately see which records will be affected . All you need to do is use SELECT Replace action words ：

INSERT INTO orders...

BECOMES

SELECT * FROM ORDERS...

summary

There is nothing like executing in a production environment DML The sentence is even more terrible . thankfully , There are some ways to minimize the risk , So you don't have to pray for good luck . If you want to try Navicat 16, You can here download 14 Day trial .