Usage of replace INTO statement in Mysql _mysql

When you insert data into a table, you often encounter situations like this:

1, first judge whether the data exists;
2, if it does not exist, then insert;
3, if present, then update.

You can write this in SQL Server:

Copy Code code as follows:

If not exists (select 1 from table where id = 1) inserts into table (ID, Update_time) VALUES (1, GETDATE ()) Else Update table Set update_time = GETDATE () Where id = 1

In MySQL can also be a select, to determine whether the existence of the update otherwise insert
But there are simpler ways to use the Replace into keyword in MySQL

Copy Code code as follows:

Replace into table (ID, Update_time) VALUES (1, now ());

Or

Copy Code code as follows:

Replace into table (ID, update_time) Select 1, now ();

The replace into is similar to the Insert feature, except that replace into first attempts to insert data into the table.

1. If you find that this row of data is already in the table (judged by a primary key or a unique index), the row data is deleted and the new data is inserted.
2, otherwise, insert new data directly.

Note that the table that inserts the data must have a primary key or a unique index! Otherwise, replace into inserts the data directly, which causes duplicate data to appear in the table.

There are three ways to type replace into MySQL:

Copy Code code 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. Where the "into" keyword can be omitted, but it is best to add "into", which means more intuitive.
In addition, MySQL automatically assigns default values to columns that are not given values.

Unfortunately, replace does not support some features of the update and cannot be used directly as update:

Common update notation: Update table set col=col+1 where id=1;
Using the replace into does not support such a notation: REPLACE into table set col=col+1,id=1;

1. First of all, determine whether the data exists; (no problem)
2, if not present, insert; (no problem)
3, if there is a field value on the original base plus or minus a certain number, such as adding an operation. (Not supported)