插入......重复键......是你的朋友。通过这种组合,您可以插入新记录或更新现有记录。为此,必须在定义行的字段上有一个唯一的键,如下所示:产品类别 ID在你的样本中
*具有唯一键的表**
CREATE TABLE `table` (
`id` int(11) unsigned NOT NULL AUTO_INCREMENT,
`product_id` varchar(32) CHARACTER SET latin1 DEFAULT NULL,
`quantity` int(11) DEFAULT NULL,
PRIMARY KEY (`id`),
UNIQUE KEY `product_id` (`product_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
Sample
mysql> select * from `table`;
Empty set (0,00 sec)
mysql> INSERT into `table` (product_id,quantity) Values ('p1',9),('p2',13) ON DUPLICATE KEY UPDATE quantity=VALUES(quantity);
Query OK, 2 rows affected (0,01 sec)
Records: 2 Duplicates: 0 Warnings: 0
mysql> select * from `table`;
+----+------------+----------+
| id | product_id | quantity |
+----+------------+----------+
| 9 | p1 | 9 |
| 10 | p2 | 13 |
+----+------------+----------+
2 rows in set (0,00 sec)
mysql> INSERT into `table` (product_id,quantity) Values ('p3',9),('p2',15) ON DUPLICATE KEY UPDATE quantity=VALUES(quantity);
Query OK, 3 rows affected (0,00 sec)
Records: 2 Duplicates: 1 Warnings: 0
mysql> select * from `table`;
+----+------------+----------+
| id | product_id | quantity |
+----+------------+----------+
| 9 | p1 | 9 |
| 10 | p2 | 15 |
| 11 | p3 | 9 |
+----+------------+----------+
3 rows in set (0,00 sec)
mysql>