Several ways for mysql to ignore primary key conflicts and avoid repeated inserts _ MySQL

Mysql ignores primary key conflicts and prevents repeated insertion of bitsCN.com

Several ways for mysql to ignore primary key conflicts and avoid repeated inserts

Solution 1: Use the ignore keyword

Solution 2: Use replace

Solution 3: ON DUPLICATE KEY UPDATE

Solution 1: Use the ignore keyword

If you use the primary key primary or the unique index unique to distinguish the uniqueness of a record, you can use the following to avoid repeated insertion of records:

insert ignore into table_name(email,phone,user_id) values('test9@163.com','99999','9999')

In this way, when there is a repeat

The record is ignored. after execution, the number 0 is returned. Another application is to copy the table to avoid repeated records:

insert ignore into table(name)  select  name from table2

Solution 2: Use replace

The syntax format of replace is:

1. replace into table_name(col_name, ...) values(...) 2. replace into table_name(col_name, ...) select ... 3. replace into table_name set col_name=value, ...

Algorithm description:

The REPLACE operation is similar to the INSERT operation. However, if the old record has the same value as the new record, the old record is deleted before the new record is inserted, that is:

1. try to insert a new row into the table

2. when insertion fails due to a duplicate keyword error for the primary key or unique keyword:

Delete conflicting rows with duplicate keyword values from the table

Try to insert a new row into the table again

The criteria for determining the values of the old record and the new record are as follows: The table has a primary key or UNIQUE index. otherwise, using a REPLACE statement is meaningless.

. This statement is the same as INSERT, because no index is used to determine whether other rows have been copied in the new row.

Return value:

The REPLACE statement returns a number to indicate the number of affected rows. This is the sum of the number of deleted and inserted rows.

The number of affected rows can be easily determined whether REPLACE only adds one row, or whether REPLACE also replaces other rows: check whether this number is 1 (added) or

Larger (replace ).

Example:

Eg: (The phone field is a unique index)

replace  into table_name(email,phone,user_id) values('test569','99999','123')

In addition, in SQL Server, you can perform the following operations:

if not exists (select phone from t where phone= '1')             insert into t(phone, update_time) values('1', getdate())  else           update t set update_time = getdate() where phone= '1'

Solution 3: ON DUPLICATE KEY UPDATE

For example‍You can also add the on duplicate key update method to insert.

If you specify on duplicate key update and insert a row

Or duplicate values in the primary key,

The old row is updated. For example, if Column a is defined as UNIQUE and contains a value of 1, the following two statements have

Same effect:

mysql>INSERT INTO table (a,b,c) VALUES (1,2,3)             ->ON DUPLICATE KEY UPDATE c=c+1; mysql>UPDATE table SET c=c+1 WHERE a=1;

If a row is inserted as a new record, the value of the affected row is 1. if the original record is updated, the value of the affected row is 2.

Note: If Column B is also a unique column, INSERT is equivalent to this UPDATE statement:

mysql> UPDATE table SET c=c+1 WHERE a=1 OR b=2 LIMIT 1;

If a = 1 OR B = 2 matches multiple rows, only one row is updated. Generally, you should avoid using the on duplicate key clause for tables with multiple unique keywords.

You can use the VALUES (col_name) function in the UPDATE clause to reference the column VALUES from the INSERT section of the INSERT... UPDATE statement.

In other words, if no duplicate keyword conflict occurs, the VALUES (col_name) in the UPDATE clause can reference the inserted

The value of col_name. This function is particularly applicable to multiline inserts. The VALUES () function is only valid in the INSERT... UPDATE statement.

Returns NULL.

mysql> INSERT INTO table (a,b,c) VALUES (1,2,3),(4,5,6)            -> ON DUPLICATE KEY UPDATE c=VALUES(a)+VALUES(b);

This statement serves the same purpose as the following two statements:

mysql> INSERT INTO table (a,b,c) VALUES (1,2,3)       -> ON DUPLICATE KEY UPDATE c=3;mysql> INSERT INTO table (a,b,c) VALUES (4,5,6)            -> ON DUPLICATE KEY UPDATE c=9;

When you use on duplicate key update, the DELAYED option is ignored.

Example: This example is used in actual projects: import the data of a table to another table, and the data duplication must be considered (as follows ).

Unique index: email

INSERT INTO table_name1(title,first_name,last_name,email,phone,user_id,role_id,status,campaign_id)SELECT '','','',table_name2.email,table_name2.phone,NULL,NULL,'pending',29 FROM table_name2  WHERE table_name2.status = 1 ON DUPLICATE KEY UPDATE table_name1.status = 'pending'

The key points of the statement are highlighted ~

Add another example:

insert into class select * from class1ON DUPLICATE KEY UPDATE class.course = class1.course

Other key: DELAYED is used as a quick insert, and is not very concerned with the invalidation and improves the insert performance.

IGNORE only follows that the record corresponding to the primary key does not exist. If no record exists, the record is added. If yes, the record is ignored.

Note: In MYSQL, the UNIQUE index will invalidate the null field, that is, (create a UNIQUE index on field ):

                    insert into test(a) values(null)                    insert into test(a) values(null)

Yes, it can be inserted repeatedly (The same is true for the Union of unique indexes ).

BitsCN.com