Geek Answers Handbook

How to prevent MySQL from automatically increasing the primary key when using ON DUPLICATE KEY UPDATE when the duplicate is another 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 purpose of this query is to insert a new provider name into the table, and if the supplier name already exists, the number of columns should be increased by 1. This works, however, the primary key of the current column will also be automatically -incremented. How can I prevent MySQL from automatically increasing the primary key in these cases? Is there a way to do this with a single request?

Thank.

+5
source share
2 answers

, . MySQL ?

UPDATE, :

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

ON DUPLICATE KEY UPDATE, :

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

, vendor_id ...

, , . , ON DUPLICATE UPDATE, , (, INSERT).

+1

, . - .

, .

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

More articles:


All Articles