Some operations of mysql insert of DELAYED IGNORE ON DUPLICATE KEY UPDATE

  • 2020-05-09 19:28:50
  • OfStack

INSERT grammar
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, ... ]
1. Use of DELAYED
Use a deferred insert operation
The DELAYED regulator applies to the INSERT and REPLACE statements. When the DELAYED insert arrives,
The server puts the data row into a queue and immediately returns a status message to the client, so that the client
The end can then proceed before the data table is actually inserted into the record. If the reader from the data
When data is read from a table, the data in the queue is held until there are no readers. Next server
Start inserting the data row in the delayed data row (delayed-row) queue. At the same time as the insert operation, the server
Also check if any new read requests are arriving and waiting. If so, the delayed row queue is suspended,
Allows the reader to continue. When there is no reader, the server again begins to insert delayed rows of data.
This process continues until the queue is empty.
A few points to note:
· INSERT DELAYED should only be used for INSERT statements specifying the list of values. Server ignored for INSERT DELAYED... DELAYED for the SELECT statement.
· server ignored for INSERT DELAYED... DELAYED for ON 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 a statement.
· for SELECT statements, DELAYED rows are not visible until they are actually inserted.
· DELAYED is ignored in slave replication servers because DELAYED does not generate data in a slave that is different from that of the primary server.
Note that currently the rows in the queue are only kept in storage until they are inserted into the table. This means that if you forcibly abort mysqld (for example, using kill-9)
Or if the mysqld stops unexpectedly, all rows that were not written to disk will be lost.
2. Use of IGNORE
IGNORE is an extension of MySQL relative to the standard SQL. If there are duplicate keywords in the new table,
Or when a warning appears after STRICT mode is started, IGNORE is used to control the operation of ALTER TABLE.
If IGNORE is not specified, when a repeat keyword error occurs, the copy operation is aborted and the previous step is returned.
If IGNORE is specified, only line 1 will be used for lines with duplicate keywords, and other conflicting lines will be deleted.
Also, fix the error value to get it as close to the correct value as possible.
insert ignore into tb(...) value(...)
So you don't have to check if it exists, you ignore it, you don't add it
3. Use of ON DUPLICATE KEY UPDATE
If you specify ON DUPLICATE KEY UPDATE, and inserting a row results in a duplicate value in an UNIQUE index or PRIMARY KEY, then the old line UPDATE is executed. For example, if the column a is defined as UNIQUE and contains the value 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; If the original record is updated, the value of the affected row is 2.

Note: if column b is also the only 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 row directions, only 1 row is updated. In general, you should try to avoid using the ON DUPLICATE KEY clause for tables with multiple 1-only keywords.

You can use the VALUES(col_name) function in the UPDATE clause from INSERT... The INSERT part of the UPDATE statement references the column values. In other words, VALUES(col_name) in the UPDATE clause can reference the inserted value of col_name if there is no duplicate keyword conflict. This function is especially useful for multi-row inserts. VALUES() function only in INSERT... UPDATE has meaning in the statement, and other times it returns NULL.

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: as a quick insert, DELAYED is not very concerned about failure and improves insert performance.
ignore only CARES if the record corresponding to the primary key does not exist, if no, it is added, and if any, it is ignored.
ON DUPLICATE KEY UPDATE operates at add time, focusing on non-primary key columns and noting the difference from ignore. Updates the specified column if there is, adds if there is none.

Related articles: