• notice
  • Congratulations on the launch of the Sought Tech site

Detailed explanation of the use of ReplaceInto statement in mysql

When inserting data into the table, we often encounter this situation:

1, first determine whether the data exists;
2.If it does not exist, insert it;
3.If it exists, update it.
 
It can be written like this in SQL Server:

The code is as follows:

if not exists (select 1 from table where id=1) insert into table(id, update_time) values(1, getdate()) else update table set update_time=getdate() where id=1

In MySQL, you can also select first to determine whether it exists, then update if it exists, otherwise insert
But there is an easier way in MySQL, using the replace into keyword
The code is as follows:
replace into table(id, update_time) values(1, now());

Or
The code is as follows:

replace into table(id, update_time) select 1, now();

The replace into function is similar to the insert function, but the difference is: replace into first tries to insert data into the table.

1.If it is found that there is already this row of data in the table (judging by the primary key or unique index), delete this row of data first, and then insert new data.
2.Otherwise, insert the new data directly.

It should be noted that the table into which data is inserted must have a primary key or a unique index! Otherwise, replace into will directly insert the data, which will cause duplicate data in the table.
 
There are three ways to write replace into in MySQL:

The code is as follows:

1.replace into table(col,...) values(...)
2.replace into table(col,...) select...
3.replace into table set col=value,...

The first two forms are used more.The "into" keyword can be omitted, but it is better to add "into" to make the meaning more intuitive.
In addition, for those columns that are not given values, MySQL will automatically assign default values ​​to these columns.
 
It is a pity that replace does not support certain features of update, so it cannot be used directly as update:

Common update writing: update table set col=col+1 where id=1;
Using replace into does not support such writing: replace into table set col=col+1,id=1;

1.First determine whether the data exists; (no problem)
2.If it does not exist, insert it; (no problem)
3.If it exists, add or subtract a certain number from the original value of a certain field, such as adding one.(Not supported)

Tags

Technical otaku

Sought technology together

Related Topic

1 Comments

author

lipitor 20mg brand & lt;a href="https://lipiws.top/"& gt;atorvastatin 80mg over the counter& lt;/a& gt; lipitor 20mg us

Uwxkuh

2024-03-07

Leave a Reply

+