1

I have a query which runs on each page load on my website. In a simple form, it checks if the users IP is in the database and if it isn't it will add it, and do nothing if it is. To save myself having to run two queries to accomplish this (one to check and one to insert) I'm using this SQL:

INSERT IGNORE INTO `limits`
SET `ip` = :ip, `limit` = :limit

This is meant to do nothing if :ip already exists and create the record if it doesn't. It works fine, however I am noticing that my primary key for the table is incrementing on every page load.

For example, the primary key field is called id and it's int(11) AUTO_INCREMENT. The first record in the database obviously had an id value of 1. After refreshing the page 10 times, it now has a value of 11, even though my IP hasn't changed and the query doesn't insert a new record.

Is there any way of making the query not update the id field on every execution?

James Dawson
  • 5,309
  • 20
  • 72
  • 126
  • What's a typical `INSERT` look like? Are you sure `:ip` isn't `NULL` by accident? – tadman Jan 31 '13 at 22:08
  • 1
    I would note that you should probably not be _depending_ on incremental ids in your PK field. It is intended to be _unique_ but not necessarily _continuously incremental_. It happens that _incremental_ implies _unique_. As soon as one gets deleted, for example, they are no longer continuously incrementing. – Michael Berkowski Jan 31 '13 at 22:09
  • The title of the question is misleading. The primary key is not updated. The next auto-increment value is. – ypercubeᵀᴹ Jan 31 '13 at 22:12
  • @MichaelBerkowski: +1 and for 9 other reasons ... – ypercubeᵀᴹ Jan 31 '13 at 22:12
  • `:ip` isn't null and everything does work as it should, I just thought it was strange behaviour and was wondering if there's an easy fix. I can certainly live with this behaviour but it's not *ideal*. – James Dawson Jan 31 '13 at 22:27
  • Naturally this SQL inserts a records each time you execute it. You'll need to make the ip field UNIQUE in order to prevent this behavior. – georgepsarakis Feb 01 '13 at 14:21

0 Answers0