mysql更新或插入多个记录(如果表中尚不存在) [英] mysql update or insert multiple records if not already exists in a table
问题描述
在mysql数据库中有一个名为"inventory_item"的表. "id","product_id"和"quantity"是表的列. "id"是主键,当插入记录时会自动生成.
there is a table named "inventory_item" in a mysql database. "id", "product_id" and "quantity" are columns of the table. "id" is the primary key and auto generate when a record is inserted.
当用户提交要在表中插入多条记录的表单时,可以在foreach循环中收集product_ids的所有数据及其数量.
when the user submit the form which is to insert multiple records to the table all the data of product_ids and their quantities can be collected in foreach loop.
所以我需要同时插入多个记录,并且仅当表中已经存在"product_id"而不要作为新记录插入时才应该更新数量.
so i need to insert multiple records at the same time and should only update quantity if "product_id" is already exist in the table without inserting as a new record.
这是我的代码块.
foreach ($dataArray as $value) {
$pcid = $value["pcid"];
$quantity = $value["quantity"];
$sql = "
UPDATE table.inventory_item
SET quantity='$quantity'
WHERE product_category_id='$pcid'
IF ROW_COUNT()=0
INSERT INTO table.inventory_item
(product_category_id, quantity)
VALUES ('$pcid', '$quantity')
";
$result = $conn->query($sql);
var_dump($result);
}
推荐答案
插入.....在重复键上.... 和您的朋友.通过这种组合,您可以插入新记录或更新现有记录.为此,有必要在一个字段上定义一个唯一键,例如:示例中的 product_category_id
INSERT INTO ..... ON DUPLICATE KEY .... ist your friend. with this combination you can insert new records or update exiting records. for this it is necessary to have a unique key on a field that define your row like: product_category_id in your sample
*具有唯一键的表**
*a table with unique Key**
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;
样品
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>
这篇关于mysql更新或插入多个记录(如果表中尚不存在)的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!