Ryan Castle Ryan Castle - 7 months ago 9
SQL Question

SQL Query - INSERT but only if record doesn't already exist?

So, I am writing an SQL Query, as you do, and I have stumbled across an issue. What I am wanting to do is fire a query at the database every time a unique user visits a certain page. The unique-ness is done through session_id(), as that is generally quite reliable.

What I want is an SQL query which inserts a row of data into two columns, but ONLY if those two columns don't already have the same value.

if user

x
visits the page named
page1
, then in the database, it will log as shown below:

+------------+---------+
| page_name | user_id |
+------------+---------+
| page1 | x |
+------------+---------+


How would I go about writing a query which checks the table to see if the data being inserted will be a duplicate?

My current query is as follows (it's just a standard insert query):

INSERT INTO `page_views` (`page_name`, `user_id`) VALUES ('.$pageName.', \''.session_id().'\')

Answer

Use INSERT IGNORE rather than INSERT. If a record doesn't duplicate an existing record, MySQL inserts it as usual. If the record is a duplicate, the IGNORE keyword tells MySQL to discard it silently without generating an error.

Following example does not error out and same time it will not insert duplicate records.

The syntax is simple - just add "IGNORE" after "INSERT" like so:

INSERT IGNORE INTO mytable
    (pk, f1, f2)
VALUES
    ('abc', 1, 2);

Inserting multiple records When inserting mutiple records at once, any that cannot be inserting will not be, but any that can will be:

INSERT IGNORE INTO mytable
    (pK, f1, f2)
VALUES
    ('abc', 1, 2),
    ('def', 3, 4),
    ('ghi', 5, 6);

In the second example above, if records already existed for 'abc' and 'def' then those two records would not be inserted, but the record for 'ghi' would be.