插入和更新重复行的语句 [英] Insert and update statement for duplicate rows

问题描述

我有一个表postviews，其中包含post_id和ipaddress列。每当打开帖子时，我都会在帖子页面上使用INSERT语句，该帖子的ID和查看器的IP地址将被发送到数据库并保存在各自的列中。

  $ stmt  = $ db-> prepare（  INSERT INTO postviews（post_id，IPaddress）VALUES（：post_id，：IPaddress））; 

现在每当一个用户再次打开相同的帖子时，会插入新的行，并且具有我想要阻止的相同的帖子ID和IP地址。所以现在我已经为这个表添加了第三列计数器，所以现在每当一个用户一次又一次地打开相同的帖子而不是插入具有相同的帖子ID和IP地址的新行时，我宁愿增加我的计数器列。我不知道该怎么做。这样的事情会起作用：

  $ stmt  = $ db-> prepare（  INSERT INTO postviews（post_id，IPaddress）VALUES（：post_id，：IPaddress）
 ON DUPLICATE KEY UPDATE counter = counter + 1 ）; 

我想阻止只有当两列（帖子ID和IP地址）相同时才创建新行，但如果其中任何一行与前一行不同，则应创建一个新行，因此此处无法使用唯一的复合键。

解决方案

stmt =

db-> prepare（ INSERT INTO postviews（post_id ，IPaddress）VALUES（：post_id，：IPaddress））;

现在每当一个用户再次打开同一个帖子时再插入新行，其中包含我想要阻止的相同的帖子ID和IP地址。所以现在我已经为这个表添加了第三列计数器，所以现在每当一个用户一次又一次地打开相同的帖子而不是插入具有相同的帖子ID和IP地址的新行时，我宁愿增加我的计数器列。我不知道该怎么做。这样的事情会起作用：

stmt =

I have a table postviews which has columns post_id and ipaddress . I use a INSERT statement for this table on posts page whenever a post is opened, id of that post and IP address of viewer are sent to database and saved in their respective columns.

$stmt =$db->prepare("INSERT INTO postviews (post_id,IPaddress) VALUES (:post_id,:IPaddress)");

Now whenever one user opens same post again and again new rows are inserted with same post id and IP address which I want to prevent. So now I have added a third column counter to this table, so now whenever one user opens same post again and again instead of inserting new rows with same post id and IP address I would rather like to increase my counter column. I am not sure how to do it. Will something like this work:

$stmt =$db->prepare("INSERT INTO postviews (post_id,IPaddress) VALUES (:post_id,:IPaddress)
ON DUPLICATE KEY UPDATE counter = counter + 1");

I want to prevent creating new row only when both columns (post id and IP address) are same , but a new row should be created if any one of them is different than previous row, so unique composite key couldn't be used here.

解决方案

stmt =

db->prepare("INSERT INTO postviews (post_id,IPaddress) VALUES (:post_id,:IPaddress)");

Now whenever one user opens same post again and again new rows are inserted with same post id and IP address which I want to prevent. So now I have added a third column counter to this table, so now whenever one user opens same post again and again instead of inserting new rows with same post id and IP address I would rather like to increase my counter column. I am not sure how to do it. Will something like this work:

stmt =

这篇关于插入和更新重复行的语句的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！