Insert a Row Only if a Row does not Exist

问题:

I am building a hit counter. I have an article directory and tracking unique visitors. When a visitor comes i insert the article id and their IP address in the database. First I check to see if the ip exists for the article id, if the ip does not exist I make the insert. This is two queries -- is there a way to make this one query

Also, I am not using stored procedures I am using regular inline sql

回答1:

Here are some options:

 INSERT IGNORE INTO `yourTable`
  SET `yourField` = 'yourValue',
  `yourOtherField` = 'yourOtherValue';

from MySQL reference manual: "If you use the IGNORE keyword, errors that occur while executing the INSERT statement are treated as warnings instead. For example, without IGNORE, a row that duplicates an existing UNIQUE index or PRIMARY KEY value in the table causes a duplicate-key error and the statement is aborted.".) If the record doesn't yet exist, it will be created.

Another option would be:

INSERT INTO yourTable (yourfield,yourOtherField) VALUES ('yourValue','yourOtherValue')
ON DUPLICATE KEY UPDATE yourField = yourField;

Doesn't throw error or warning.

回答2:

Yes, you create a UNIQUE constraint on the columns article_id and ip_address. When you attempt to INSERT a duplicate the INSERT will be refused with an error. Just answered the same question here for SQLite.

回答3:

IF NOT EXISTS (SELECT * FROM MyTable where IPAddress...)
   INSERT...

回答4:

Not with SQL Server. With T-SQL you have to check for the existence of a row, then use either INSERT or UPDATE as appropriate.

Another option is to try UPDATE first, and then examine the row count to see if there was a record updated. If not, then INSERT. Given a 50/50 chance of a row being there, you have executed a single query 50% of the time.

MySQL has a extension called REPLACE that has the capability that you seek.

回答5:

The only way I can think of is execute dynamic SQL using the SqlCommand object.

IF EXISTS(SELECT 1 FROM IPTable where IpAddr=<ipaddr>)
--Insert Statement

回答6:

I agree with Larry about using uniqueness, but I would implement it like this:

• IP_ADDRESS, pk
• ARTICLE_ID, pk, fk

This ensures that a record is unique hit. Attempts to insert duplicates would get an error from the database.

回答7:

I would really use procedures! :)

But either way, this will probably work:

Create a UNIQUE index for both the IP and article ID columns, the insert query will fail if they already exist, so technically it'll work! (tested on mysql)

回答8:

try this (it's a real kludge, but it should work...):

Insert TableName ([column list])
Select Distinct @PK, @valueA, @ValueB, etc. -- list all values to be inserted
From TableName
Where Not Exists 
    (Select * From TableName
     Where PK == @PK)