需求：
假如有一張書目表，字段為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)
大功告成，相當之好用！
