SQL如何计算数据库中同一行的出现数量

Question

我的数据在mySQL表中：

Cart_id    Product_id    Quantity
 1           1             1

我可以以某种方式做到这一点：当我添加datas的下一个行时： cart_id 1和product_id 1和数量1，新行将不会插入，但是第一行将更新为2的数量？

但是，当我插入数据：cart_id 1和product_id 2时，将插入新行。

Answer 1

使用这样的查询：

INSERT INTO myTable (Cart_id,Product_id,Quantity) VALUES( 1,1,1)
ON DUPLICATE KEY UPDATE Quantity=Quantity+VALUES(Quantity);

在这里示例：

创建一个表：

CREATE TABLE `myTable` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `Cart_id` int(11) DEFAULT NULL,
  `Product_id` int(11) DEFAULT NULL,
  `Quantity` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

添加唯一索引：

ALTER TABLE `myTable` ADD UNIQUE INDEX (`Cart_id`, `Product_id`);

您还可以在创建创建索引时指导创建索引表

插入行样本：

MariaDB [bernd]> INSERT INTO myTable (Cart_id,Product_id,Quantity) VALUES( 1,1,1)
    -> ON DUPLICATE KEY UPDATE Quantity=Quantity+VALUES(Quantity);
Query OK, 1 row affected (0.003 sec)

MariaDB [bernd]> SELECT * from myTable;
+----+---------+------------+----------+
| id | Cart_id | Product_id | Quantity |
+----+---------+------------+----------+
|  1 |       1 |          1 |        1 |
+----+---------+------------+----------+
1 row in set (0.000 sec)

MariaDB [bernd]> 
MariaDB [bernd]> INSERT INTO myTable (Cart_id,Product_id,Quantity) VALUES( 1,1,1)
    -> ON DUPLICATE KEY UPDATE Quantity=Quantity+VALUES(Quantity);
Query OK, 2 rows affected (0.001 sec)

MariaDB [bernd]> SELECT * from myTable;
+----+---------+------------+----------+
| id | Cart_id | Product_id | Quantity |
+----+---------+------------+----------+
|  1 |       1 |          1 |        2 |
+----+---------+------------+----------+
1 row in set (0.001 sec)

MariaDB [bernd]> INSERT INTO myTable (Cart_id,Product_id,Quantity) VALUES( 1,1,1)
    -> ON DUPLICATE KEY UPDATE Quantity=Quantity+VALUES(Quantity);;
Query OK, 2 rows affected (0.001 sec)


MariaDB [bernd]> SELECT * from myTable;
+----+---------+------------+----------+
| id | Cart_id | Product_id | Quantity |
+----+---------+------------+----------+
|  1 |       1 |          1 |        3 |
+----+---------+------------+----------+
1 row in set (0.000 sec)

MariaDB [bernd]> INSERT INTO myTable (Cart_id,Product_id,Quantity) VALUES( 1,1,5)
    -> ON DUPLICATE KEY UPDATE Quantity=Quantity+VALUES(Quantity);
Query OK, 2 rows affected (0.002 sec)

MariaDB [bernd]> SELECT * from myTable;
+----+---------+------------+----------+
| id | Cart_id | Product_id | Quantity |
+----+---------+------------+----------+
|  1 |       1 |          1 |        8 |
+----+---------+------------+----------+
1 row in set (0.000 sec)

MariaDB [bernd]>