MySQL:如果此IP没有任何记录，则插入 [英] MySQL: Insert if this ip dont have any records

问题描述

我使用:

INSERT INTO `rating` (`name`, `user`, `rating`, `section`, `ip`)
VALUES ('$name', '{$_SESSION['user']}', '$rate', '$section',
        '{$_SERVER['REMOTE_ADDR']}');";

我想在IF语句中添加一个if条件.

I would like to add an if condition in the IF statement so that.

IF SELECT ip from rating 
where ip={$_SERVER['REMOTE_ADDR']} AND section=$section AND name=$name 
then update ELSE INSERT new row

是可行的还是我最好在php中编写代码? 非常感谢你

is it doable or I better code it in php ? thank you very much

P.s:我知道如何使用php进行操作，我想通过MySQL进行学习.

P.s: I know how to do it with php, I want to learn it with MySQL.

我还要求所有3个名称，部分，ip不仅要与ip匹配，而且要匹配

推荐答案

以扩展Eric的出色答案.

To expand on Eric's excellent answer.

要在ip，name和section列中添加唯一约束，请在数据库上运行以下代码

To add a unique constraint on each of the columns ip, name, section run the following code on the database

ALTER TABLE `test`.`rating` 
  ADD UNIQUE INDEX `name`(`name`),
  ADD UNIQUE INDEX `section`(`section`),
  ADD UNIQUE INDEX `ip`(`ip`);

要对ip + name + section列的组合运行唯一约束，请执行以下操作:

To run a unique constraint on the combination of columns ip+name+section do:

ALTER TABLE `test`.`rating` 
  ADD UNIQUE INDEX `name_section_ip`(`name`, `section`, `ip`);

最后一件事可能就是您想要的.

The last thing is probably what you want.

最后一件事
我不确定100％，但是查询中{$_SERVER['REMOTE_ADDR']}的这种用法看起来不太安全，可以使用SQL注入.
考虑将其更改为:

One last thing
I'm not 100% sure, but this usage of {$_SERVER['REMOTE_ADDR']} in the query does not look SQL-injection safe.
Consider changing it into:

$remote_adr = mysql_real_escape_string($_SERVER['REMOTE_ADDR']);
$session = mysql_real_escape_string($_SESSION['user']);
$query = "INSERT INTO `rating` (`name`, `user`, `rating`, `section`, `ip`)
  VALUES ('$name', '$session', '$rate', '$section','$remote_adr')";

最后加上一个;"在mysql_query()这样的状态下，mysql_query("select * from x;");不起作用mysql_query()只会执行一次查询，并且会拒绝您的;.

Finally putting a ";" in a mysql_query() like so mysql_query("select * from x;"); does not work mysql_query() will only ever execute one query and will reject your ;.

这篇关于MySQL:如果此IP没有任何记录，则插入的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！