A simple question first

When officially answering this interview question, let's first look at a simple question: how to insert data into MySQL, ignore it if it exists, and insert it if it doesn't exist? In fact, this simple topic has the same place as the title of the title: when there is no data to be inserted in MySQL, the data to be inserted is inserted into MySQL. The difference is: the title in the title is to perform an update operation when there is data to be inserted, while the simple title is to ignore and do nothing when there is data to be inserted.

Let's answer this simple question first. In fact, in the interview process, we need to speculate on the psychology of the interviewer. Obviously, here, the interviewer wants to ask how to achieve it through SQL statements, and such questions often have a precondition: that is, the data table must be There is a unique key, that is, a unique index. If you answer that you wrote a piece of Java code or C language code to implement, then you are basically passed. No need to say, because you answered in a different direction than the interview expected!

For this simple topic, we can use the insert ignore statement. The syntax format is as follows.

insert ignore into table(col1,col2) values ('value1','value2');

For example, we execute the following SQL statement to insert data into MySQL.

insert ignore into user_info (last_name,first_name) values ('binghe','binghe');

In this way, if data with last_name='binghe' and first_name='binghe' already exists in the table, it will not be inserted, and if not, a new data will be inserted.

The above is a usage, and it can also be implemented with INSERT .... SELECT statement, so I won't give an example here.

Analyze headlines

Next, let's look at the title in the title, insert data into MySQL, update if it exists, and insert if it does not exist. In essence, the data table still needs to have a unique key, that is, a unique index. Often in the interview, the interviewer will acquiesce to the existence of these preconditions.

Here, there are two ways to achieve this effect. One way is to combine the INSERT statement with the ON DUPLICATE KEY UPDATE statement, and the other way is to implement the REPLACE statement.

INSERT statement and ON DUPLICATE KEY UPDATE statement implementation

UPDATE is performed if ON DUPLICATE KEY UPDATE is specified and inserting a row would result in a duplicate value in a UNIQUE index or PRIMARY KEY. For example, if column a is defined as UNIQUE and contains the value 1, the following two statements have the same effect:

INSERT INTO table (a,b,c) VALUES (1,2,3)  ON DUPLICATE KEY UPDATE c=c+1; 
UPDATE table SET c=c+1 WHERE a=1; 

If the row is inserted as a new record, the value of the affected row is 1; if the original record is updated, the value of the affected row is 2.

REPLACE statement implementation

The biggest advantage of using REPLACE is that you can combine DELETE and INSERT into one atomic operation. This eliminates the need to consider complex operations such as adding transactions when using DELETE and INSERT at the same time. When using REPLACE, there must be a unique index in the table, and the field where the index is located cannot allow null values, otherwise REPLACE is exactly the same as INSERT. After executing REPLACE, the system returns the number of rows affected. If it returns 1, it means that there are no duplicate records in the table. If it returns 2, it means that there is a duplicate record. The system automatically calls DELETE to delete this record, and then Then record with INSERT to insert this record.

The syntax is very similar to INSERT, such as the following REPLACE statement to insert or update a record.

REPLACE INTO users (id,name,age) VALUES(1, 'binghe', 18);