MySQL - “如果不存在则插入"询问? [英] MySQL - "insert if not exists" query?

问题描述

我正在尝试编写一个 MySQL 查询，如果用户在过去 24 小时内没有访问过该帖子，它将更新博客帖子查看计数器.我正在尝试写这样的东西(但这不起作用):

I'm trying to write a MySQL query which will update a blog post view counter if the user has not visited the post in the last 24 hours. I'm trying to write something like this (but this does not work):

  IF EXISTS (
     SELECT 1
     FROM `posts-views`
     WHERE
        `ip` = '".$_SERVER['REMOTE_ADDR']."'
        AND
        `time` > ".($time-60*60*24)."
        AND
        `post` = $id
  ) THEN
  NULL

  ELSE

  INSERT INTO `posts-views`
  (`post`, `time`, `ip`, `user`)
  VALUES
  ($id, $time, '".$_SERVER['REMOTE_ADDR']."', $me)

修复查询的正确方法是什么?

What's the correct way to fix the query?

推荐答案

据我所知，在这种情况下您不能使用 INSERT IGNORE，但应该使用以下内容:

From what I see you cannot use INSERT IGNORE in that case, but something like following should do the job :

  INSERT INTO `posts-views`
  (`post`, `time`, `ip`, `user`)
  SELECT $id, $time, '".$_SERVER['REMOTE_ADDR']."', $me 
  FROM dual 
  WHERE NOT EXISTS (
     SELECT 1
     FROM `posts-views`
     WHERE
    `ip` = '".$_SERVER['REMOTE_ADDR']."'
      AND
    `time` > ".$time-60*60*24."
    AND
    `post` = $id
  )

我完全省略了转义变量，这绝对应该在实际代码中完成.

I completely omit escaping variables which definitely should be done in real code.

UPDATED - 添加了 from dual 以避免语法错误

UPDATED - added from dual to avoid syntax error

这篇关于MySQL - “如果不存在则插入"询问?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！