Shulou(Shulou.com)05/31 Report--

It is believed that many inexperienced people are at a loss about how to achieve no data insertion and data update in MySQL. Therefore, this paper summarizes the causes and solutions of the problem. Through this article, I hope you can solve this problem.

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 last_name='binghe' and first_name='binghe' data already exists in the table, it will not be inserted, and if not, a new piece of data will be inserted.

The above is a usage, you can also use INSERT. To implement the SELECT statement, there are no examples here.

Analyze the title

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

Here, there are two ways to achieve this. One method is to combine INSERT statements with ON DUPLICATE KEY UPDATE statements, and the other is to implement it through REPLACE statements.

Implementation of INSERT statement and ON DUPLICATE KEY UPDATE statement

If ON DUPLICATE KEY UPDATE is specified and inserting rows results in duplicate values in an UNIQUE index or PRIMARY KEY, UPDATE is executed. For example, if column an is defined as UNIQUE and contains a value of 1, the following two statements have the same effect:

INSERT INTO table (a dint bpene c) VALUES (1m m 2pm 3) ON DUPLICATE KEY UPDATE c=c+1

UPDATE table SET c=c+1 WHERE axi1

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 to form an atomic operation. This eliminates the need to add complex operations such as 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 in which the index is located cannot allow null values, otherwise REPLACE will be exactly the same as INSERT. After executing REPLACE, the system returns the number of rows affected. If 1 is returned, there is no duplicate record in the table. If 2 is returned, there is a duplicate record. The system automatically calls DELETE to delete the record, and then the record is inserted with INSERT.

The syntax is very similar to INSERT, for example, the following REPLACE statement inserts or updates a record.

REPLACE INTO users (id,name,age) VALUES (1, 'binghe', 18); after reading the above, have you mastered how to achieve no data insertion and data update in MySQL? If you want to learn more skills or want to know more about it, you are welcome to follow the industry information channel, thank you for reading!

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.