MYSQL 之 on duplicate key update

来源：互联网发布：古天乐捐学校知乎编辑：程序博客网时间：2024/06/05 18:21

需求：

假如有一张书目表，字段为id，name，count（库存）

现要插入一条新纪录，如果数据库没有这条纪录，插入

若已经存在，则更新库存。

解决方法：mysql 的 on duplicate key update 语法。

下面给出解决过程。

创建测试数据库

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

创建测试数据表

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)

查看索引信息

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

插入第一条测试数据

mysql> INSERT INTO `books` VALUES ('','windows',5);
Query OK, 1 row affected, 1 warning (0.04 sec)

查看数据

mysql> select * from books;
+----+---------+-------+
| id | name | count |
+----+---------+-------+
| 1 | windows | 5 |
+----+---------+-------+
1 row in set (0.00 sec)

再次插入windows这本书

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)

再来查看数据

mysql> select * from books;
+----+---------+-------+
| id | name | count |
+----+---------+-------+
| 1 | windows | 6 |
+----+---------+-------+
1 row in set (0.00 sec)

0 0