MYSQL update syntax with no repeated Inserts

MYSQL no repeated insert data UPDATE syntax & SQL statement: if the primary KEY is repeated during insert, the Statement ON DUPLICATE KEY UPDATE is specified, in this case, the unique or primary index will not insert content that is duplicated with the database record, but will also update the old records in the database. For example, if field a is declared as a unique index and contains only records with a value of 1, the following two statements will achieve the same effect: www.2cto.com I. insert into table (a, B, c) VALUES (1, 2, 3) on duplicate key update c = c + 1; 2. UPDATE table SET c = c + 1 WHERE a = 1; the affected rows are rows a = 1. When inserting, the value of c is added to 1. If field B is also unique, the insert statement will be the same as the following statement: UPDATE table SET c = c + 1 WHERE a = 1 OR B = 2 LIMIT 1; if a = 1 OR B = 2 matches more than one row, the first row is updated. Generally, if a table has multiple unique indexes, you should avoid using the on duplicate key clause.
You can use the VALUES (field name) function in insert update statement INSERT... UPDATE to associate a row of records. That is to say, VALUES (field name) can be used in the UPDATE statement to UPDATE the value of a field without duplicate keys. This function is particularly useful in multiline inserts. However, the VALUES () function is only valid in the INSERT... UPDATE statement. Otherwise, NULL is returned. Example: insert into table (a, B, c) VALUES (1, 2, 3), (4, 5, 6) ON DUPLICATE KEY UPDATE c = VALUES () + VALUES (B); this statement has the same effect as the following two statements: www.2cto.com insert into table (a, B, c) VALUES (1, 2, 3) on duplicate key update c = 3; insert into table (a, B, c) VALUES (4, 5, 6) ON DUPLICATE KEY UPDATE c = 9; if the table contains an auto increment field AUTO_INCREMENT, and INSERT... insert a row in UPDATE, function LAST_INSERT_ID () The value of AUTO_INCREMENT is returned. If this statement updates a row, LAST_INSERT_ID () is meaningless. However, you can use LAST_INSERT_ID (expr) to make it meaningful. If the id field is auto-incrementing, LAST_INSERT_ID () can be meaningful to the update statement as follows: insert into table (a, B, c) VALUES (1, 2, 3) ON DUPLICATE KEY UPDATE id = LAST_INSERT_ID (id), c = 3; if you use the on duplicate key update statement, the DELAYED execution option DELAYED will be ignored. Original English address: http://dev.mysql.com/doc/refman/5.0/en/insert-on-duplicate.html Other Voices: Insert into table_name (field_name1, field_name2 ,...) Values (value1, value2 ,...); Insert into values (value1, value2 ,...); // This form can only be used when the Value Order matches the field order (you can call the describe command to determine the field order ). The value that contains the reference flag must be enclosed by a backslash (/). Note: The into keyword is optional.
You can also insert multiple values at the same time and separate them with commas (,). For example: insert table_name values (value1, value2 ,...), (Value3, value4 ,...); You can also choose not to use insert... The value format is similar to the update statement. It uses the set clause to set values for each column. Insert into table_name set field_name1 = value1, field_name2 = value2 ,...; Use the default value Create table table_name (field_name1 type default def_value not null ,...); Insert table_name values (default ,...); // The default keyword is only supported in version 4.0.3 and later.
The AUTOINCREMENT Field automatically generates the next sequence number. This field must be set as the primary KEY. The UNIQUE field uses the IGNORE keyword or the on duplicate key update clause to skip the INSERT operation, interrupt the operation, or UPDATE the old record as the new value. Www.2cto.com insert ignore into TABLE_NAME (UNIQUE_FIELD ,...) VALUES (REPEAT ,...); If the record is repeated, no error is reported or the record is updated, and the data in the database remains unchanged. On duplicate key update differs from REPLACE: the former only updates the named field to a new value, the latter deletes the old record, and then completely replaces it with the new value.
Eg: insert into Menu value (null, 'mysql', 'www .mysql.com ') on duplicate key update label = 'Ms SQL', url = 'www .microsoft.com '; in this case, if MySQL finds that the table already contains records with the same unique KEY, the old record is automatically updated to the new value specified in the on duplicate key update clause. When many insert statements need to be executed sequentially, the IGNORE keyword makes the operation very convenient. This ensures that no matter whether an INSERT contains a duplicate value, MySQL will skip the operation without giving up all the operations.