Usage of MySQL replace

In SQL Server, you can perform the following operations:

Copy codeThe Code is as follows:
If not exists (select 1 from t where id = 1)
Insert into t (id, update_time) values (1, getdate ())
Else
Update t set update_time = getdate () where id = 1

So how does MySQL implement this logic? Don't worry! MySQL has a simpler method: replace

Copy codeThe Code is as follows:
Replace into t (id, update_time) values (1, now ());
Or
Replace into t (id, update_time) select 1, now ();

Replace into is similar to insert. The difference is that replace into first tries to insert data into the table. if this row of data already exists in the table (determined based on the primary key or unique index), delete the row of data and insert new data. 2. Otherwise, insert new data directly. Note that the table to which data is inserted must have a primary key or a unique index! Otherwise, replace into inserts data directly, which leads to duplicate data in the table.

MySQL replace into has three forms:

Copy codeThe Code is as follows:
Replace into tbl_name (col_name,...) values (...)
Replace into tbl_name (col_name,...) select...
Replace into tbl_name set col_name = value ,...

The first two forms use more. The "into" keyword can be omitted, but it is better to add "into" to make it more intuitive. In addition, for columns without values, MySQL automatically assigns default values to these columns.