Several things about MySQL insert (delayed,ignore,on DUPLICATE KEY UPDATE) _mysql

Insert Syntax
INSERT [Low_priority | Delayed | High_priority] [IGNORE]
[Into] tbl_name [(Col_name,...)]
VALUES ({expr | DEFAULT},...), (...),...
[on DUPLICATE KEY UPDATE col_name=expr, ...]
Or:
INSERT [Low_priority | Delayed | High_priority] [IGNORE]
[Into] Tbl_name
SET col_name={expr | DEFAULT}, ...
[on DUPLICATE KEY UPDATE col_name=expr, ...]
Or:
INSERT [Low_priority | High_priority] [IGNORE]
[Into] tbl_name [(Col_name,...)]
SELECT ...
[on DUPLICATE KEY UPDATE col_name=expr, ...]
first, the use of delayed
Using deferred insert operations
The delayed modifier applies to the INSERT and replace statements. When the delayed insert operation arrives,
The server puts the data rows into a queue and immediately returns a status message to the client so that the customer
The end can continue to operate before the data table is actually inserted into the record. If the reader from this data
Table, the data in the queue is persisted until no reader is present. Then the server
Begins inserting data rows in a deferred data row (Delayed-row) queue. At the same time as the insert operation, the server
Also check for new read requests to arrive and wait. If so, the delay data row queue is suspended,
Allows the reader to continue the operation. When there is no reader, the server starts inserting the deferred data rows again.
This process continues until the queue is empty.
A few things to note:
· Insert delayed should be used only for INSERT statements that specify a list of values. Server ignored for insert delayed ... The delayed of the SELECT statement.
· Server ignored for insert delayed ... On the delayed of the DUPLICATE UPDATE statement.
· Because the statement returns immediately before the row is inserted, you cannot use last_insert_id () to get the auto_increment value. The auto_increment value may be generated by the statement.
· For a SELECT statement, the delayed row is not visible until the rows are actually inserted.
· Delayed is ignored in the slave replication server because delayed does not produce data that is not the same as the primary server on the secondary server.
Note that the rows currently in the queue are only in storage until they are inserted into the table. This means that if you forcibly abort the mysqld (for example, using kill-9)
Or if mysqld stops unexpectedly, all rows that are not written to the disk will be lost.
second, the use of ignore
Ignore is the extension of MySQL relative to standard SQL. If you have duplicate keywords in the new table,
Or, when a warning occurs after the strict mode is started, use ignore to control the operation of ALTER TABLE.
If ignore is not specified, the copy operation is discarded and the previous step is returned when a duplicate keyword error occurs.
If ignore is specified, only the first row is used for rows with duplicate keywords, and other conflicting rows are deleted.
Also, correct the error values so that they are as close to the correct values as possible.
Insert ignore into TB (...) value (...)
This does not need to verify the existence of, is ignored, no add
use of on DUPLICATE KEY update
If you specify on DUPLICATE key update and the insert row causes duplicate values to occur in a unique index or primary key, the old row update is executed. For example, if column A is defined as unique and contains a value of 1, the following two statements have the 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 the row is inserted as a new record, the value of the affected row is 1, and if the existing 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 the a=1 OR b=2 matches more than one row, only one row is updated. In general, you should try to avoid using the on DUPLICATE key clause on tables with multiple unique keywords.

You can use the values (col_name) function in the UPDATE clause from the INSERT ... The insert portion of the UPDATE statement references the column value. In other words, if no duplicate keyword conflict occurs, values (col_name) in the update clause can reference the value of the inserted col_name. This function is especially useful for multiple rows of inserts. The VALUES () function is only in the insert ... is meaningful in the UPDATE statement and returns null at other times.

Example:

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 acts the same 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.


Summary: Delayed as a quick insert, not very concerned about the failure, improve the insertion performance.
Ignore only focus on primary key corresponding record is not present, added without, and ignored.
On DUPLICATE Key UPDATE when adding, focus on non primary key columns, pay attention to the difference with ignore. The specified column is updated and none is added.