Shulou(Shulou.com)05/31 Report--

MYSQL no repeated insertion of data update syntax example analysis, many novices are not very clear about this, in order to help you solve this problem, the following editor will explain in detail for you, people with this need can come to learn, I hope you can gain something.

If you specify the ON DUPLICATE KEY UPDATE command statement, then under the action of the unique index or the primary index, the content that duplicates the record will not be inserted, but the old record in the database will be updated at the same time. For example, if field an is declared as a unique index and contains only records with a value of 1, the following two statements have the same effect:

Www.2cto.com

INSERT INTO table (a, b, c) VALUES (1, 2, 3)

ON DUPLICATE KEY UPDATE c = c + 1

UPDATE table SET c = c + 1 WHERE a = 1

It is the row of axi1 that is affected, and the value of c is increased by 1 when inserted.

If field b is also unique, the insert statement will have the same effect as the following statement:

UPDATE table SET c=c+1 WHERE axi1 OR baked 2 LIMIT 1

If there is a match of more than one row, only the first row will be updated. In general, you should avoid using the ON DUPLICATE KEY clause if there is more than one unique index in the table.

You can insert the update statement INSERT. The VALUES (field name) function is used in UPDATE to associate a row of records. That is, VALUES (field name) can be used in UPDATE statements to update the value of a field without duplicate keys. This function is especially useful in multi-line inserts. But the function VALUES () can only be used in INSERT... It only makes sense in the UPDATE statement, otherwise NULL will be returned. For example:

INSERT INTO table (a, b, c) VALUES (1,2,3), (4,5,6)

ON DUPLICATE KEY UPDATE c = VALUES (a) + VALUES (b)

This statement has the same effect as the following two:

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 automatically incrementing field, AUTO_INCREMENT, and use INSERT. UPDATE inserts a row and the function LAST_INSERT_ID () returns the value of AUTO_INCREMENT. If this statement updates a row, LAST_INSERT_ID () is meaningless. However, you can make it meaningful by using LAST_INSERT_ID (expr), and if the id field is automatically incremented, the ways to make LAST_INSERT_ID () meaningful for update statements are 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 deferred execution option DELAYED will be ignored.

Insert record

Insert into table_name (field_name1, field_name2, …) Values (value1, value2, …)

Insert into values (value1, value2, …) ; / / this form can only be applied if the order of the values matches the order of the fields (the order of the fields can be determined by calling the describe command).

Values containing reference flags need to be preceded by a backslash / / escape character in quotation marks

Note: the into keyword is optional

You can also insert multiple values at the same time, separated by commas

Eg:insert table_name values (value1, value2, …) , (value3, value4,...)

You don't have to use insert. Values format, while using a similar update statement, which uses the set clause to set values for each column separately.

Insert into table_name set field_name1 = value1, field_name2 = value2, …

Use 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

Use the AUTOINCREMENT field

The next sequence number is automatically generated, and the field must be set as the primary key

Use the UNIQUE field

Use the IGNORE keyword or ON DUPLICATE KEY UPDATE clause to skip the INSERT, interrupt the operation, or update the old record to a new value. Www.2cto.com

INSERT IGNORE INTO TABLE_NAME (UNIQUE_FIELD, …) VALUES (REPEAT, …)

If the record is duplicated, no error is reported, the record is not updated, and the data in the database remains unchanged.

The difference between ON DUPLICATE KEY UPDATE and REPLACE: the former only updates the named field to the new value, while the latter deletes the old record and 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 a record with the same unique key, it automatically updates the old record as the new value specified in the ON DUPLICATE KEY UPDATE clause.

The IGNORE keyword makes the operation convenient when there are many insert statements that need to be executed sequentially. This ensures that regardless of whether an INSERT contains a duplicate value, the MySQL will skip without giving up all operations.

Is it helpful for you to read the above content? If you want to know more about the relevant knowledge or read more related articles, please follow the industry information channel, thank you for your support.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.