MySQL automatically updates when records are inserted when the record does not exist

The important thing is the above mentioned:

INSERT ... SELECT

INSERT ... On DUPLICATE KEY UPDATE

INSERT ... On DUPLICATE REPLACE

For example, to insert a piece of data into a table, if the table does not have that data inserted, if the data already exists is not inserted.

First, when you create a table, you set the field that does not need to be duplicate to unique, and then use the Insert Ignore statement when inserting

MySQL implementation (Duplicate key) if it does not exist, insert, existing is updated:

INSERT into Ipstats VALUES (' 192.168.0.1′, 1) on DUPLICATE KEY UPDATE clicks=clicks+1;

Example

code is as follows

copy code

mysql> insert INTO ' 200702 ' (' Domain ', ' 2nd_domain ', ' TLD ', ' query_ns1 ', ' que Ry_ns2 ', ' report_date ') VALUES (' dnspod.com ', ' dnspod ', ' com ', 1000, Watts, ' 2007-02-04 ') on DUPLICATE KEY UPDATE ' Query_ns 1 ' = ' query_ns1 ' + 1000, ' query_ns2 ' = ' query_ns2 ' + 2000; Query OK, 2 rows affected (0.01 sec) Example 2 Ysql> insert INTO ' 200702 ' (' Domain ', ' 2nd_domain ', ' TLD ', ' query_ns1 ', ' query_ns2 ', ' report_date ') VALUES (' Dnspod . com ', ' dnspod ', ' com ', 1000, Watts, ' 2007-02-04 ') on DUPLICATE KEY UPDATE ' query_ns1 ' = ' query_ns1 ' + 1000, ' query_ns2 ' = ' Query_ns2 ' + 2000; Query OK, 1 row Affected (0.00 sec)

In addition to using insert ... On DUPLICATE KEY Update We can also use (insert if not exists)

Example one: inserting more than one record

Suppose you have a clients table with a primary key of client_id, you can use the following statement:

The code is as follows	Copy Code
INSERT into clients (client_id, Client_name, Client_type) SELECT supplier_id, Supplier_name, Supplier_type From suppliers WHERE NOT EXISTS (SELECT * from clients where clients.client_id = suppliers.supplier_id);

Example two: inserting a single record

The code is as follows	Copy Code
INSERT into clients (client_id, Client_name, Client_type) SELECT 10345, ' IBM ', ' advertising ' From dual WHERE NOT EXISTS (SELECT * from clients where clients.client_id = 10345);

Using dual as a table name allows you to follow directly to the value of the field you want to insert after the SELECT statement, even if the values do not yet exist in the current table.

Test performance Discovery If the same amount of data uses insert ... On DUPLICATE KEY update is much better than not exists, since the former is a MySQL-brought statement that handles duplicate data.