MySQL auto increment value set to zero

Question

Here is a table

CREATE TABLE `mytable` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `val` char(128) NOT NULL DEFAULT '',
  PRIMARY KEY (`id`),
  UNIQUE KEY (val)
) ENGINE=InnoDB DEFAULT CHARSET=ascii;

Any idea why is this happening, I expected it to set id to zero in the first query itself

MariaDB > insert into mytable set id=0, val="" on duplicate key update id=0, val=val;
Query OK, 1 row affected (0.01 sec)

MariaDB > select * from mytable;
+----+-------+
| id | val |
+----+-------+
|  1 |       |
+----+-------+
1 row in set (0.00 sec)

MariaDB > insert into mytable set id=0, val="" on duplicate key update id=0, val=val;
Query OK, 2 rows affected (0.01 sec)

MariaDB > select * from mytable;
+----+-------+
| id | val |
+----+-------+
|  0 |       |
+----+-------+
1 row in set (0.00 sec)

MariaDB > insert into mytable set id=0, val="" on duplicate key update id=0, val=val;
Query OK, 0 rows affected (0.01 sec)

Any explanation will be appreciated.

Update: I am aware of using AUTO_INCREMENT=0 but the real question here is that query explicitly set id=0, so why it is setting it as 1 in first query. It seems mysql ok to set it 0 in duplicate instance.

Thanks

Can you please try, truncating table which will set auto_increment to 0. and confirm it with http://stackoverflow.com/a/15821543/410439. Then execute the above step again. — Ravi Parekh, Apr 20 '17 at 11:43

score 4 · Answer 1 · answered Apr 20 '17 at 09:58

When inserting a new record, setting an AUTO_INCREMENT column to 0 means "generate a new value for this column" (ref). Values for AUTO_INCREMENT columns start from 1. Thus:

insert into mytable set id=0, val="" on duplicate key update id=0, val=val;

is equivalent to:

insert into mytable set id=1, val="";

The second insert you call would create a duplicate key (for the val field, not the id field). This causes the update statement to be run, thus updating id to zero. The "2 rows affected" message appears because the on duplicate key update statement returns 2 in case an existing row is updated (ref).

The third insert does nothing. Both keys are duplicate, but the existing row doesn't need to be updated because its values are already what you expect them to be. In this case the on duplicate key update statement returns "0 rows affected".

score 3 · Answer 2 · answered Apr 20 '17 at 09:42

3

By default, the starting value for AUTO_INCREMENT is 1, and it will increment by 1 for each new record. To let the AUTO_INCREMENT sequence start with another value, use the following SQL statement:

ALTER TABLE myTabel AUTO_INCREMENT=0;

Credits

answered Apr 20 '17 at 09:42

Abel Masila

736
2
7
25

I know that. However, the question is that the query explicitly set id=0, then why should it set 1. – mesibo Apr 20 '17 at 09:54
you should have said that in the original question before the edit – Abel Masila Apr 20 '17 at 10:13

score 1 · Answer 3 · answered Feb 07 '21 at 18:57

The answer about NO_AUTO_VALUE_ON_ZERO is correct, although a bit incomplete. There is an option to sql_mode to allow for an explicit value of zero to be entered in an autoincrement field. By default 0 is treated the same as null. If you add the NO_AUTO_VALUE_ON_ZERO option, you are allowed to specify a zero value in that field. I have this in my cnf file: sql_mode='NO_AUTO_VALUE_ON_ZERO,STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION'

score 0 · Answer 4 · edited Feb 04 '21 at 08:00

0

please check NO_AUTO_VALUE_ON_ZERO option.

By default, auto_increment column cannot be inserted zero value.

If you set NO_AUTO_VALUE_ON_ZERO on, you can force to input auto_increment column zero value.

edited Feb 04 '21 at 08:00

Martin Brisiak

3,872
12
37
51

answered Feb 04 '21 at 05:54

shystyle

1

MySQL auto increment value set to zero

4 Answers4