Enhanced version of INSERT into and insert ignore into

Insert into is the most commonly used statement to insert a table, in general, we insert data into the tables to determine whether there is, if it does not exist, insert, and then update the changes. Give me a chestnut ~ ~ ~

If not exists (select 1 from t where id = 1) Then

INSERT into T (ID, Update_time) VALUES (1, GETDATE ())

Else

Update T Set update_time = GETDATE () Where id = 1

Replace into also inserts data into the table, which performs the logic of inserting the data first, determining whether there is any data based on the primary key or index, and then replacing the existing data. Replace finds duplicate deletions and inserts, and if there are multiple fields in the record, the newly inserted records will be empty if there are no assigned fields at the time of insertion. (Increase data volume, update duplicate data)

But. If the table is a self added field, replace into should be used with caution, why should be careful to use please copy link https://blog.xupeng.me/2013/10/11/mysql-replace-into-trap/

Replace into

Table 1

ID name PS

1 TB A

2 ZP b

Table2

ID (primary key) name

1 TB

2 CW

3 ZP

Replace into table1 select * Table2 execution result is

Table1

ID name PS

1 TB NULL

2 CW NULL

3 ZP NULL

Insert ignore into ignores existing data and inserts nonexistent data. Table 1 Inserts table 2, and if Table 1 's data already exists in table 2, skip and insert a number that does not exist. (Increase the amount of data, do not change the duplicate data)

Insert into

Table 1

ID Name

1 TB

2 ZP

Table2

ID (primary key) name

1 TB

2 CW

3 ZP

Insert Ignore into table1 select * from table2 execution result is

Table1

ID Name

1 TB

2 ZP

3 ZP