0

i want to remove Duplicate IP Addresses from the List which is Stored in MySql Database, suppose a User clicks on Site 2 times, then my database will store his IP Address 2 times, but i want it to store only 1 and delete other 2, 3, or 4 Automatically

here's the table for Clicks

CREATE TABLE `clicks` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `offer_id` int(11) NOT NULL,
  `refer_uri` text NOT NULL,
  `user_id` varchar(10) NOT NULL,
  `c` text NOT NULL,
  `ip` varchar(20) NOT NULL,
  `device` text NOT NULL,
  `country` varchar(50) DEFAULT NULL,
  `city` varchar(100) NOT NULL,
  `onfor` text NOT NULL,
  `time` datetime NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=47717 DEFAULT CHARSET=latin1;
/*!40101 SET character_set_client = @saved_cs_client */;
Raymond Nijland
  • 11,488
  • 2
  • 22
  • 34
AnythingRolePro
  • 1
  • 5
    You could add unique constraint to the IP field to prevent such an occurrence. – PsychoMantis Feb 04 '19 at 00:14
  • 2
    i advice you to provide example data and expected results without it's impossible to help. i advice you to read this [Why should I provide an MCVE for what seems to me to be a very simple SQL query?](https://meta.stackoverflow.com/questions/333952/why-should-i-provide-an-mcve-for-what-seems-to-me-to-be-a-very-simple-sql-query) on how to provide those. – Raymond Nijland Feb 04 '19 at 00:14
  • Note 20 chars is too big for IPv4 and won't fit IPv6. [varbinary(16)](https://stackoverflow.com/questions/2542011/most-efficient-way-to-store-ip-address-in-mysql) maybe. – danblack Feb 04 '19 at 01:04

1 Answers1

0

Add a unique index:

CREATE UNIQUE INDEX ip ON clicks ( ip )

When inserting catch and ignore the duplicate key error.

danblack
  • 12,130
  • 2
  • 22
  • 41
  • Not sure if you should be suggesting to use `INSERT IGNORE`..You are aware that `INSERT IGNORE` will "waste" auto_increments id's for the InnoDB table engine right ? see [demo](https://www.db-fiddle.com/f/nXzPgbfy8JEFfSYrzC4iTD/1) – Raymond Nijland Feb 04 '19 at 00:42
  • 1
    Didn't really consider it. Its not unsigned so its wasting space anyway. So I guess the alternate is to cookie the user and check this? Other suggestions? – danblack Feb 04 '19 at 01:05
  • 1
    "Its not unsigned" Yea true unsigned has a much larger range.. "So I guess the alternate is to cookie the user and check this? Other suggestions? " Use a normal insert from the application without `IGORE` and let the application "nicely" handle the error... MIne last comment was more meant because it's a "click" table the ~4 million can be achieved pretty "quickly" in thoery on (very) high traffic websites/applications and the application can stop working when the key has given his last value.. – Raymond Nijland Feb 04 '19 at 14:34