Replace into 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:

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 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.

Replace into Syntax:

REPLACE [LOW_PRIORITY | DELAYED] [INTO] tbl_name [(col_name,...)] VALUES ({expr | DEFAULT },...), (...),... or REPLACE [LOW_PRIORITY | DELAYED] [INTO] tbl_name SET col_name = {expr | DEFAULT },... or REPLACE [LOW_PRIORITY | DELAYED] [INTO] tbl_name [(col_name,...)] SELECT...

 

Attached the usage of a replace into shown in UCHome:

$_POST['config']['ipaccess'] = trim($_POST['config']['ipaccess']);$_POST['config']['ipbanned'] = trim($_POST['config']['ipbanned']);foreach ($_POST['config'] as $var => $value) {  $value = trim($value);  if(!isset($_SCONFIG[$var]) || $_SCONFIG[$var] != $value) {    $setarr[] = "('$var', '$value')";  }}if($setarr) {  $_SGLOBAL['db']->query("REPLACE INTO ".tname('config')." (var, datavalue) VALUES ".implode(',', $setarr));}//REPLACE INTO uchome_config (var, datavalue) VALUES ('ipaccess', '127.0.0.1'),('ipbanned', '10.3.1')