MySQL插入到不存在的地方 [英] MySQL INSERT INTO WHERE NOT EXIST

问题描述

如果product_id的ip_address不存在，我想插入到同一表中，但是最好不要创建两个单独的查询.我该如何实现?

I will like to insert into same table if the ip_address for the product_id wasn't exist but preferably not to create two separate query. How can I achieve it?

下面将是示例表:

id | product_id | ip_address |
------------------------------
a    11112        8.8.8.8
b    11234        9.9.9.9

我的代码是这样的，但是它不起作用.

and my code is something like this but it doesn't work.

INSERT INTO `'._DB_PREFIX_.'views` 
(SELECT '.$id_product.',"'.$ip_addr.'"
FROM `'._DB_PREFIX_.'views` 
WHERE NOT EXISTS (SELECT * FROM
`'._DB_PREFIX_.'views` v WHERE v.id_product ='.$id_product.' AND
t.ip_address ="'.$ip_addr.'"
))'

推荐答案

您可以使用INSERT IGNORE或REPLACE或INSERT ... ON DUPLICATE KEY UPDATE ...

每个要求您对product_id，ip_address都有唯一的约束

Each requires you to have a unique constraint on product_id, ip_address

插入信息

INSERT IGNORE INTO products VALUES (null, 111, '8.8.8.8')

如果条目已存在，将忽略插入.

will ignore the insert, if entry already exists.

替换

REPLACE INTO products VALUES (null, 111, '8.8.8.8')

将执行删除操作，然后执行新的插入操作.

will perform a delete plus a new insert.

插入...更新

INSERT INTO products VALUES (null, 111, '8.8.8.8')
ON DUPLICATE KEY UPDATE products SET last_modified = NOW()

将尝试插入，如果失败，则更新现有记录.

Will try to insert and if that fails update the existing record.

对于您来说，我认为INSERT IGNORE应该可以，但是如果您想更改其他值(如果记录已经存在)，则INSERT ... UPDATE ...应该可以正常工作.

In your case I think you should be fine with INSERT IGNORE, however If you want to change other values if the record already exists, INSERT ... UPDATE ... should work well.

除非您确实想要DELETE FROM table WHERE ...加INSERT INTO TABLE ...

更新

(在此示例中)这需要组合产品ip_address的唯一索引. 您可以使用

This requires (for this example) a unique index for the combination product, ip_address. You can achive this with

ALTER TABLE products
ADD UNIQUE INDEX `UIDX_PRODUCT__IP_ADRESS`(product, ipaddress);

这篇关于MySQL插入到不存在的地方的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！