Mysql:on duplicate key update with replace into

When inserting data into a table, it is often necessary to: a. First to determine whether the data exists in the library; b. Insert if not present; C. Presence Update

One, replace into

Premise: The database must have a primary key or a unique index, or replace into will directly insert new data, resulting in a data table with duplicate data

Try inserting data at execution time:

A. When the data table is present (judging by a primary key or a unique index), the data is first deleted and then the new data is inserted.

B. If the data does not exist in the table, insert the data directly

Replace into is an enhanced version of INSERT INTO, with syntax similar to insert Iton

Replace into table_name (columns) values (VALUES1,VALUES2);

Replace into table_name (columns) Select columns from table_name2

Test data (This table establishes a composite unique index User_add):

CREATE TABLE ' relace_on ' (

　　　　　　' id ' int (one) unsigned not NULL auto_increment,
' user_id ' int (one) unsigned not NULL,
' Interal ' tinyint (3) unsigned not NULL,
' Add_time ' date not NULL,
PRIMARY KEY (' id '),
　　　　　　UNIQUE KEY ' user_add ' (' user_id ', ' add_time ') USING BTREE
) Engine=innodb auto_increment=1 DEFAULT charset=latin1;

Insert test data:　　　　

INSERT into relace_on (user_id, interal, Add_time)
VALUES
(1, 20, ' 2016-05-06 '),
(2,20, ' 2016-05-06 '),
(3,20, ' 2016-05-06 '),
(1, 20, ' 2016-05-07 '),
(2,20, ' 2016-05-07 '),
(3,20, ' 2016-05-07 ')

Database data now:

　　　　

Next execute the REPLACE into statement (present): Replace into relace_on (user_id, interal, add_time) values(1, 40, ' 2016-05-06 '), (2,60 , ' 2016-05-06 '), (3,80, ' 2016-05-06 ')

At this point, the SQL execution succeeds, the number of rows affected is 6 rows (delete three, insert three)

In contrast, you'll find that user_id's account was originally data-only in 2016-05-06, and the ID was (all-in-one); now that the replace into is executed, the ID becomes (7,8,9), And the value of the Interal field is the value of the execution statement, at which point the replace into statement finds that the user in the data table who user_id to (three-in-one) has a record in 2016-05-06, based on the unique index of User_add in the datasheet. At this point, the original three data deleted, re-inserted three, so the ID from the three-way to 7,8,9, and the value of interal changed

Next execute the REPLACE into statement (not present): Replace into relace_on (user_id, interal, Add_time) VALUES (4,40, ' 2016-05-06 '), (5,60, ' 2016-05-06 '), (6,80, ' 2016-05-06 ')

At this point, the SQL execution succeeds, the number of rows affected is 3 rows (insert three)

　　In contrast, you will find that the original data has not changed, just added three data, also 2016-05-06 this day, but user_id is (4,5,6) according to user_add this composite unique index, these three data does not exist in the data table, so directly insert can

　　　　

Second, on duplicate key update

It can also be used to update the data, a bit similar to replace into, but on duplicate key update is the data table inside the data is updated, does not exist then inserted, and replace into is the existence of the Delete, then insert, Does not exist then insert

Still use the existing data above to test:

Add a field first to update multiple fields with the following: ALTER TABLE ' relace_on ' Add COLUMN ' copy_interal ' tinyint (3) UNSIGNED not NULL after ' interal ';

Grammar:

　　　Update a single field: INSERT INTO table_name (columns) VALUES (VALUES1,VALUES2) on duplicate key update column=values (column) or column= Value (1, ' ZGW ')

Update multiple fields: INSERT INTO table_name (columns) VALUES (VALUES1,VALUES2) on duplicate key update column1=values (column1), Column2 =values (Column2)

Execute a statement (present): INSERT into relace_on (user_id, interal,copy_interal, Add_time) VALUES (6,100,200, ' 2016-05-06 ') on duplicate KEY update interal=values (interal), Copy_interal=values (copy_interal)

　　

, user_id=6,add_time= ' 2016-05-06 ' This data exists, update the values of the interal and copy_interal two fields (Interal the original 80,copy_interal new field defaults to 0)

Execute a statement again (not present): INSERT into relace_on (user_id, interal,copy_interal, Add_time) VALUES (7,100,200, ' 2016-05-06 ') on Duplicate KEY update interal=values (interal), Copy_interal=values (copy_interal)

　　

Mysql:on duplicate key update with replace into