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

In this issue, the editor will bring you about how to avoid the application of Duplicate key in data table insertion. The article is rich in content and analyzes and describes for you from a professional point of view. I hope you can get something after reading this article.

Insert data into a data table to prevent duplicate data insertion. Generally speaking, most DBA practices are

The unique index, the primary key, shuts out the duplicate data through the unique constraint or check of the database in the process of inserting duplicate data.

In many cases, this is not appropriate, because the programmer you meet may not be able to deal with the subsequent processing of data refusing to insert, which is an embarrassing problem. How to get him to solve this problem with a very simple SQL statement requires DBA to do something.

In general, it is used in SQL SERVER (see the following sentence). By judging during the insertion process, it is determined that the data from tbl_B inserted into tbl_A should not be duplicated with tbl_A, that is, there should be a filter of opportunity identification key before insertion.

INSERT tbl_A (col, col2) SELECT col, col2 FROM tbl_B WHERE NOT EXISTS (SELECT col FROM tbl_A A2 WHERE A2.col = tbl_B.col)

In this way, it looks like LOW. In fact, it is generally efficient. So the method recommended by Microsoft is as follows

Merge function, which I have worked for more than ten years, is relatively rare, because in most scenarios, the operation of CRUD can already cover most of the functions of database operations, and most of the functions of calculation and judgment are done in the application layer. Through program practice, the database is more and more used like a container, as long as the database is well MVCC. The matter of ISOLATE is OK, so the function of MERGE is rarely referenced to the use of the database.

And when to use the MERGE function, a recent project modification, encountered, in the original data insertion, the use of cursors, such a result can be imagined, must be bad, the database to use cursors is the next choice, if a programmer uses cursors, unless the amount of data is very small, and the logic is very complex, and must be done with database PROCEDURE Otherwise, the cursor should be kicked out of the statement layer of the database.

In the modified stored procedure, there are no cursors, which is a gratifying thing, but bad things happen again. In the logic of the program, it is necessary to judge whether the inserted data already exists in the database. If so, do not insert, otherwise insert.

Of course, there are many ways to solve this problem, and there are many restrictions on each method.

1 unique index, joint unique index (rejected, the stored procedure provided by the consultant will not use this method to deal with those interrupts and errors, using this method or the program will report an error, the goal has not been achieved) PASS

2 insert into. Select. Where not exist (select.... (let's not talk about this, there is already such a sentence on it.

3 the focus of this time, the merge into statement, we also use the above statement to rewrite into merge into to achieve. INSERT tbl_A (col, col2) SELECT col, col2 FROM tbl_B WHERE NOT EXISTS (SELECT col FROM tbl_A A2 WHERE A2.col = tbl_B.col)

After rewriting

Merge into tab1 as tab1

Using (select id,size_2 from tab2) as tab2 on tab1.id_1 = tab2.id

WHEN NOT MATCHED THEN

Insert (size_1) values (size_2)

Result: when there is no error, the record of the coincidence of the two tables is removed, and the different results are inserted.

Problem solving

MYSQL

In MYSQL, there are more ways to deal with such things than SQL SERVER, there are two main ways

1 REPLACE INTO

2 DUPLICATE KEY UPDATE

The above two methods, in this case, the use of DUPLICATE KEY UPDATE is more appropriate, specific Replace into

There is no introduction here, and the two differences are also obvious. One matches DELETE, in INSERT, and the other matches UPDATE.

This is the obvious difference between the two ways.

Here are also two similar SQL SERVER tables of MYSQL

Or do you want to insert tab2 data that is different from tab1 into tab1?

Insert into tab1 (id,name) select id,name_2 from tab2 on duplicate key update tab1.name= tab2.name_2

The above statement can do this by judging the duplicate data based on the primary key or unique index and updating it tightly, otherwise it will insert data that does not exist in tab1 that does not exist in tab2.

Compared with SQL SERVER, MySQL is obviously much more convenient in this work.

-

ORACLE handles such data in a similar way to SQL SERVER.

Merge into tab1 using tab2 on (tab1.id=tab2.id) when not matched then insert (id,name) values (tab2.id,tab2.name_2)

_ _ _

Brief description of Postgresql: Postgresql is indeed the dark horse of the database world, whether it is MYSQL's

Both duplicate key update and MERGE INTO syntax supported by ORACLE SQL SERVER are supported in the database (version 11)

-- Summary:

Compared with MYSQL, SQL SERVER and ORACLE are more troublesome in dealing with duplicate values. Although SQL SERVER and ORACLE are similar in the number of ways to deal with duplicate values, there are differences. PostgreSQL is indeed the latecomer, and the three database support methods are supported in the latest version of the database.

1 ORACLE wins, the statement under MATCH can still add where conditions, so the operation will be more flexible, SQL SERVER can not

2 if SQL SERVER wins, SQL SERVER can delete the data not operated by the target table in judgment, but ORACLE cannot

3 MYSQL is the easiest and fastest way to remove duplicate records in use, but the function is simple. If you want to carry out the complex functions of ORACLE or SQL SERVER, there is no ready-made statement to complete.

4 PostgreSQL, win, three database support methods are supported, disadvantages, need to update the 11 version of PostgreSQL.

The above is the editor for you to share how to avoid the application of Duplicate key in data table insertion, if you happen to have similar doubts, you might as well refer to the above analysis to understand. If you want to know more about it, you are welcome to follow the industry information channel.

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.