MySQL updates do not exist insert

Check the program and find that MySQL has this function!

Write a program today and discover new things ........................, Pretty good ^ _ ^, skipping a lot of effort, more than 1 GB of logs every day !!

MySQL versions later than MySQL 4.1 Support insert... The on duplicate key update syntax allows you to execute three SQL statements (select, insert, update) and reduce them to one statement.

Insert... on duplicate key update: If the inserted record causes a primary key conflict or violates the unique constraint, update the old record. Otherwise, the new record is inserted.

For example, the ipstats table structure is as follows:

CREATE TABLE ipstats (ip VARCHAR(15) NOT NULL UNIQUE,clicks SMALLINT(5) UNSIGNED NOT NULL DEFAULT '0');

Originally, three SQL statements were required:

IF (SELECT * FROM ipstats WHERE ip='192.168.0.1') {    UPDATE ipstats SET clicks=clicks+1 WHERE ip='192.168.0.1';} else {    INSERT INTO ipstats (ip, clicks) VALUES ('192.168.0.1', 1);}

Now, you only need to run the following SQL statement:

INSERT INTO ipstats VALUES('192.168.0.1', 1) ON DUPLICATE KEY UPDATE clicks=clicks+1;

Note: To use this statement, the table must have a unique index or primary key.

Let's look at another example:
Mysql> DESC test;
+ ------- + ------------- + ------ + ----- + --------- + ------- +
| FIELD | type | null | key | default | extra |
+ ------- + ------------- + ------ + ----- + --------- + ------- +
| Uid | int (11) | no | pri |
| Uname | varchar (20) | Yes | null |
+ ------- + ------------- + ------ + ----- + --------- + ------- +
2 rows in SET (0.00 Sec)

Mysql> select * from test;
+ ----- + -------- +
| Uid | uname |
+ ----- + -------- +
| 1 | uname1 |
| 2 | uname2 |
| 3 | Me |
+ ----- + -------- +
3 rows in SET (0.00 Sec)

Mysql> insert into test values (3, 'insertname ')
-> On duplicate key update uname = 'updatename ';
Query OK, 2 rows affected (0.03 Sec)

Mysql> select * from test;
+ ----- + ------------ +
| Uid | uname |
+ ----- + ------------ +
| 1 | uname1 |
| 2 | uname2 |
| 3 | updatename |
+ ----- + ------------ +
3 rows in SET (0.00 Sec)

Mysql> Create index I _test_uname on test (uname );
Query OK, 3 rows affected (0.20 Sec)
Records: 3 duplicates: 0 Warnings: 0

Mysql> insert into test values (1, 'uname2 ')
-> On duplicate key update uname = 'update2records ';
Query OK, 2 rows affected (0.00 Sec)

Mysql> select * from test;
+ ----- + ---------------- +
| Uid | uname |
+ ----- + ---------------- +
| 2 | uname2 |
| 1 | update2records |
| 3 | updatename |
+ ----- + ---------------- +
3 rows in SET (0.00 Sec)

The insert operation will conflict with the two records, which are caused by the primary key and unique index. However, only one of them is updated. This statement is not recommended when multiple unique indexes (or keys and unique indexes) exist.

Create Table XX (SAD, xasd, Asda, primary key (A, X, A); you can use it. Note that there must be primary keys and unique indexes ^_^.