How do I prevent MySQL from auto-incrementing the Primary Key while using ON DUPLICATE KEY UPDATE when the duplicate is a different unique column?

Consider the following table:

+-------------+------------------+------+-----+---------+----------------+
| Field       | Type             | Null | Key | Default | Extra          |
+-------------+------------------+------+-----+---------+----------------+
| vendor_id   | int(10) unsigned | NO   | PRI | NULL    | auto_increment |
| vendor_name | varchar(100)     | NO   | UNI | NULL    |                |
| count       | int(10) unsigned | NO   |     | 1       |                |
+-------------+------------------+------+-----+---------+----------------+

I have the following MySQL query:

INSERT INTO `table` 
   (`vendor_name`) 
VALUES 
   ('foobar') ON DUPLICATE KEY UPDATE `count` = `count` + 1

The intent of this query is to insert a new vendor name to the table and in case the vendor name already exists, the column count should be incremented by 1.开发者_Python百科 This works however the primary key of the current column will also be auto-incremented. How can I prevent MySQL from auto-incrementing the primary key in these cases? Is there a way to do this with one query?

Thank you.

This works however the primary key of the current column will also be auto-incremented. How can I prevent MySQL from auto-incrementing the primary key in these cases?

By using an UPDATE statement when the value already exists:

IF EXISTS(SELECT NULL
            FROM TABLE
           WHERE vendor_name = $vendor_name) THEN

    UPDATE TABLE
       SET count = count + 1
     WHERE vendor_name = $vendor_name

ELSE

    INSERT INTO TABLE
       (vendor_name)
    VALUES
       ($vendor_name

END IF

I tried the alternative to ON DUPLICATE KEY UPDATE, REPLACE INTO:

REPLACE INTO vendors SET vendor_name = 'foobar', COUNT = COUNT + 1

It updates the count, and the vendor_id so it's worse...

The database & data doesn't care if the numbers aren't sequential, only that the values are unique. If you can live with that, I'd use the ON DUPLICATE UPDATE syntax though I admit the behaviour is weird (understandable considering using an INSERT statement).

I think this might do it. But it's very much against the principles of Daoism - you're really going against the grain.

There is probably a better solution.

INSERT INTO `table` 
   (`vendor_name`) 
VALUES 
   ('foobar') ON DUPLICATE KEY UPDATE `count` = `count` + 1, `vendor_id`=`vendor_id`-1