Mysql replaceinto usage

When inserting data into a table, you often encounter this situation: 1. First, determine whether the data exists; 2. If the data does not exist, insert the data; 3. If the data exists, update the data. In SQL Server, you can perform the following operations: ifnotexists (select1fromtwhereid1) insertintot (id, update_time) values (1, getdate (

When inserting data into a table, you often encounter this situation: 1. First, determine whether the data exists; 2. If the data does not exist, insert the data; 3. If the data exists, update the data. In SQL Server, if not exists (select 1 from t where id = 1) insert into t (id, update_time) values (1, getdate (

When inserting data into a table, you often encounter this situation: 1. First, determine whether the data exists; 2. If the data does not exist, insert the data; 3. If the data exists, update the data.

In SQL Server, you can perform the following operations:

   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

replace into t(id, update_time) values(1, now());

Or

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

ReplaceSimilar to the insert function, the difference is:ReplaceFirst, try to insert data to the table. 1. If the table already has this row of data (determined based on the primary key or unique index), delete this 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,ReplaceData is inserted directly, which leads to duplicate data in the table.

MySQL replace into has three forms:

1. replace into tbl_name (col_name,...) values (...) or replace into tbl_name values (...);

2. replace into tbl_name(col_name, ...) select ...3. 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.