MySQL database replace into usage (enhanced version of insert)

Error Code: 1062. Duplicate entry 'xxxxx' for key 'primary' (PRIMARY key conflict)

In this way, we have to check whether the primary key exists. If it exists, it is updated. If it does not exist, it is inserted. Or write an SQL flow control statement (if... else ...)
 

Mysql provides the same replace into and insert into functions. The difference is:

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. Otherwise, insert new data directly.

Note: to judge whether duplicate data exists based on the primary key or unique index, the operated table must have a primary key or unique index. Otherwise, replace into inserts data directly.
 

Mysql replace into can be used in three ways. The simplest method is to replace the "insert" keyword with "replace.

Insert into t (...,...) values (...,...)

Change

Replace into t (...,...) values (...,...)

When inserting data into a table, you may encounter the following situations:

1. First, determine whether the data exists;

2. If it does not exist, insert it;

3. If yes, it is updated.

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 ();

Replace into is similar to insert. The difference is that replace into first tries to insert data into the table:

1. If this row of data is found 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: 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:

1. replace into tbl_name (col_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.