0

I have one of this situations :

CREATE TABLE table (
    `col1` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
    `col2` VARCHAR(512) NOT NULL COLLATE 'utf8mb4_general_ci',
    PRIMARY KEY (`col1`) USING BTREE,
    UNIQUE INDEX `col2` (`col2`) USING BTREE,
[...]

and then I have such a statement :

INSERT INTO table (col2, col3) VALUES ( val2, val3) ON DUPLICATE KEY UPDATE (col2=val2, col3=val3)

if there is a duplicate key on col2 UPDATE will auto_increment col1

Question is there a way to make a table auto increment only in INSERT ?

4
  • no it doesn't increment neither mysql dbfiddle.uk/ZNwTt1YN nor MAriadb dbfiddle.uk/rErFnmaP
    – nbk
    Mar 17 at 16:31
  • @nbk You're missing another insert before the final select. This will get 4 for the auto_incremented PK column, just as the OP described. dbfiddle.uk/WUiefpXe
    – dbdemon
    Mar 17 at 17:19
  • You insert the row where only the values for col2 and col3 are set. So the only duplicate violation is possible, and this is a violation by the unique index col2. So the assignment UPDATE col2=val2 in ODKU clause makes no sense obviously. if there is a duplicate key on col2 UPDATE will auto_increment col1 This is absolutely impossible. Maybe you tell about autoincrement value gaps?
    – Akina
    Mar 17 at 17:44
  • @Akina yes you are right.
    – Max Muster
    Mar 26 at 22:22

2 Answers 2

2

In the default InnoDB lock mode, the answer is no. Auto-inc values are generated quickly, before duplicate key checks are done. Once the table's auto-inc lock is released, there is no "undo" and the values generated will not be reused.

However, you can use a different lock mode, that was the way InnoDB worked years ago. The table's auto-inc lock is held until the end of the INSERT statement, including any duplicate key checks and subsequent updates. If the new auto-inc values were not used, then they can be preserved for some other session to use.

Demo:

I changed the lock mode in /etc/my.cnf and restarted my MySQL Server process. This option is a global, read-only option, and can only be changed in the options file.

[mysqld]
...
innodb_autoinc_lock_mode=0

Then I tested:

mysql> select @@innodb_autoinc_lock_mode;
+----------------------------+
| @@innodb_autoinc_lock_mode |
+----------------------------+
|                          0 |
+----------------------------+

mysql> show create table mytable\G
*************************** 1. row ***************************
       Table: mytable
Create Table: CREATE TABLE `mytable` (
  `col1` int unsigned NOT NULL AUTO_INCREMENT,
  `col2` varchar(512) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL,
  `col3` int DEFAULT NULL,
  PRIMARY KEY (`col1`) USING BTREE,
  UNIQUE KEY `col2` (`col2`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
1 row in set (0.00 sec)

mysql> insert into mytable set col2='col2', col3=5 
  on duplicate key update col2=values(col2), col3=values(col3);
Query OK, 2 rows affected, 2 warnings (0.01 sec)

mysql> show create table mytable\G
*************************** 1. row ***************************
       Table: mytable
Create Table: CREATE TABLE `mytable` (
  `col1` int unsigned NOT NULL AUTO_INCREMENT,
  `col2` varchar(512) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL,
  `col3` int DEFAULT NULL,
  PRIMARY KEY (`col1`) USING BTREE,
  UNIQUE KEY `col2` (`col2`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
1 row in set (0.00 sec)

Notice the table's AUTO_INCREMENT did not advance, as it would if the innodb_autoinc_lock_mode were the default value of 2.

In general, the default lock mode is preferred, because it means the table lock is held more briefly, and this has been important when multiple sessions are inserting to the same table. It allows for greater throughput in parallel.

Read more about the InnoDB lock modes here: https://dev.mysql.com/doc/refman/8.0/en/innodb-auto-increment-handling.html

1
  • Thank you, innodb_autoinc_lock_mode solves my problem.
    – Max Muster
    Mar 26 at 22:22
2

You execute INSERT INTO .. ON DUPLICATE KEY UPDATE.

Firstly INSERT INTO is executed. Your values part does not contain the value for the primary key column which is defined as autoincremented one. So new autoincremented value is generated for this newly inserted row.

If the values provided in the query does not cause duplicate violation then the row with newly generated autoincrement value will be inserted.

If the values provided in the query causes duplicate violation then the UPDATE action specified in ON DUPLICATE KEY UPDATE part of the query will be executed.

But in both cases new autoincremented value is already generated. So in both cases the next query will generate the next autoincremented value. You cannot "not generate if ON DUPLICATE KEY UPDATE was fired".

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.