Onduplicatekeyupdate for MYSQL

Requirement: if there is a bibliography table with the field id, name, count (inventory), a new record will be inserted. If the database does not have this record, the inventory will be updated if it already exists. Solution: mysql onduplicatekeyupdate syntax. The following describes the solution. Create a test database mysqlCREATEDATABASE 'tes

Requirement: if there is a bibliography table with the field id, name, count (inventory), a new record will be inserted. If the database does not have this record, the inventory will be updated if it already exists. Solution: mysql's on duplicate key update syntax. The following describes the solution. CREATE a test DATABASE mysql create database 'tes

Requirements:

Assume that there is a bibliography table with the fields id, name, count (inventory)

A new record is to be inserted. If the database does not have this record

If it already exists, the inventory is updated.

Solution: mysql's on duplicate key update syntax.

The following describes the solution.

Create Test Database

Mysql> create database 'test' default character set utf8 COLLATE utf8_general_ci;

Query OK, 1 row affected (0.14 sec)

Mysql> use test;
Database changed

Create test data table

Mysql> create table if not exists 'books '(
-> 'Id' int (10) unsigned not null auto_increment,
-> 'Name' varchar (50) not null,
-> 'Count' smallint (5) unsigned not null default '0 ',
-> Primary key ('id '),
-> Unique key ('name ')
->) ENGINE = innodb default charset = utf8;
Query OK, 0 rows affected (0.79 sec)

View index information

Mysql> show index from books;
+ ------- + ------------ + ---------- + Primary + --------- + primary + ---------- | Table | Non_unique | Key_name | primary | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment |
+ ------- + ------------ + ---------- + -------------- + ------------- + ----------- + ------------- + ---------- + -------- + ------------ + ----------- +
| Books | 0 | PRIMARY| 1 | id | | 0 | NULL | NULL | | BTREE | |
| Books | 0 | name | 1 | name | | 0 | NULL | NULL | | BTREE | |
+ ------- + ------------ + ---------- + -------------- + ------------- + ----------- + ------------- + ---------- 2 rows in set (0.00 sec)

Insert the first test data

Mysql> insert into 'books 'VALUES ('', 'windows', 5 );
Query OK, 1 row affected, 1 warning (0.04 sec)

View data

Mysql> select * from books;
+ ---- + --------- + ------- +
| Id | name | Count |
+ ---- + --------- + ------- +
|1 | windows | 5 |
+ ---- + --------- + ------- +
1 row in set (0.00 sec)

Insert windows again

Mysql> insert into 'books 'VALUES ('', 'windows', 1)
-> On duplicate key update 'Count' = 'Count' + VALUES ('Count ');
Query OK, 2 rows affected, 1 warning (0.12 sec)

View data again

Mysql> select * from books;
+ ---- + --------- + ------- +
| Id | name | Count |
+ ---- + --------- + ------- +
|1 | windows | 6 |
+ ---- + --------- + ------- +
1 row in set (0.00 sec)