MySQL INSERT ... On DUPLICATE KEY UPDATE statement

Online about Insert ... On DUPLICATE KEY Update most articles are the same article, first of all the purpose of this syntax is to resolve the repetition, when there is a record in the database, the execution of this statement will update it, and the absence of this record, it will be inserted.

Equivalent to the first to determine whether a record exists, there is an update, otherwise insert. Its syntax is:

INSERT INTO tablename(field1,field2, field3, ...) VALUES(value1, value2, value3, ...) ON DUPLICATE KEY UPDATE field1=value1,field2=value2, field3=value3, ...;

TableName is the name of the table, field1,field2,field3 and so on are the field names, VALUE1,VALUE2,VALUE3, etc. are the field values.

For example:

INSERT INTO t_stock_chg(f_market, f_stockID, f_name) VALUES(‘SH‘, ‘600000‘, ‘白云机场‘) ON DUPLICATE KEY UPDATE f_market=‘SH‘, f_name=‘浦发银行‘;

If the record does not exist, the record is inserted and the f_market is changed to Sh,f_name to Pune bank. Now there is a question: what is the standard for this statement to determine if the record exists? Because the same value can appear in more than one record at a time, you must have a field that is unique and cannot be duplicated.

The rule is this: if you insert a record that causes a unique index or a primary key (primary key) to repeat, then the record is assumed to be present, then the UPDATE statement is executed instead of the INSERT statement, and conversely, the INSERT statement is executed instead of the UPDATE statement. So on DUPLICATE KEY update is not able to write where conditions, such as the following syntax is wrong:

INSERT INTO t_stock_chg(f_market, f_stockID, f_name) VALUES(‘SH‘, ‘600000‘, ‘白云机场‘) ON DUPLICATE KEY UPDATE f_market=‘SH‘, f_name=‘浦发银行‘ WHERE f_stockID=‘600000‘;

Because a unique index or primary key guarantees uniqueness, the where sub-condition is not required. So the above insert into T_STOCK_CHG (F_market, F_stockid, F_name) VALUES (' SH ', ' 600000 ', ' Baiyun Airport ') on DUPLICATE KEY UPDATE f_market= ' SH ', f_name= ' Pune Bank '; F_stockid is the only primary key.

It is particularly important to note that 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, and if the updated data is identical to the existing data, the number of rows affected is 0, which means that it will not be updated. This means that even if you have a timestamp that automatically records the last update time, the timestamp will not change. For example:

2. CREATE TABLE ' T_stock_chg ' (
4. ' F_market ' varchar (+) not NULL COMMENT ' Market ',
6. ' F_stockid ' varchar (+) not NULL DEFAULT ' COMMENT ' stock code ',
8. ' F_updatetime ' timestamp not NULL DEFAULT current_timestamp on UPDATE Current_ TIMESTAMP COMMENT ' Insert timestamp ',
10. ' F_name ' varchar (+) DEFAULT NULL COMMENT ' stock name ',
12. PRIMARY KEY (' f_market ',' F_stockid ')
14. ) Engine=myisam DEFAULT charset=utf8

The field f_updatetime here is updated automatically each time the data is updated, but if there is a piece of data in the record and then it is updated, and the updated data is identical to the original data, the field is not updated and remains the last time. At this point insert ... On DUPLICATE KEY update affects the number of rows is 0.

MySQL INSERT ... On DUPLICATE KEY UPDATE statement