Bug #35285 Allow negative auto_increment values to be set
Submitted: 14 Mar 2008 9:23 Modified: 13 May 2010 16:03
Reporter: Mark Email Updates:
Status: Open Impact on me:
None 
Category:MySQL Server: InnoDB storage engine Severity:S4 (Feature request)
Version:5.0.27 OS:Linux
Assigned to: CPU Architecture:Any
Tags: auto_increment, innodb, negative

[14 Mar 2008 9:23] Mark
Description:
It doesn't seem to be possible to set a negative auto_increment value.

How to repeat:
alter table t auto_increment = -1
[19 Mar 2008 16:53] Susanne Ebrecht
Many thanks for writing a feature request.

Please, can you explain which sense should a negative auto_increment should have?
[19 Mar 2008 17:38] Mark
Susan,

I had a situation where I wanted to explicitly set positive primary key
values for some records, but wanted a separate series of records
in that table which had automatically-set unique keys.

So I thought I'd set the auto_increment_increment for the table to -1,
giving auto-ids of -1, -2, -3...

However I found it wasn't possible to set a negative and table-dependent auto_increment_increment value, so I decided to instead set the initial
auto_increment value to a large negative number, and have this series
increase to zero from there.  However I received an error whenever I
tried to set any auto_increment value that was negative.
[8 Apr 2009 21:14] Shimon Pozin
While I would consider it a bug, too, there is a workaround for this problem.
You can do it in two steps:
1. Regular insert, e.g.
insert into t values (...);
2. Update with set, e.g.
update t set id = -id where id = last_insert_id();

Of course it would be nice if we just had negative auto-increment instead :(

Shimon
[10 Feb 2011 1:20] David Carlos
It would be usefull in some scenarios. This is just my example.
Suppose you have a table which could reference two distinct things.
For example positive values are user_data_submissions, and negative values are event_submissions.

In this scenario it could be useful to have auto increment of -1, to ease insert of rows.

Anyway, I don't think it would hurt anyone if implemented :)
[10 Feb 2011 1:31] David Carlos
Forgot to mention that this decrement, would be usefull for inserting on event_submission table.
[1 Mar 2017 16:50] billy noah
I'm accomplishing this for multiple rows with an INSERT / SELECT statement as follows:

For normal insert:

INSERT INTO people (id, firstname, lastname)
SELECT
	NULL,
	firstname,
	lastname,
FROM normal_visitors;

For auto-decrement insert:

INSERT INTO people (id, firstname, lastname)
SELECT
	LEAST((SELECT MIN(id) FROM people,0) - (@rownum := @rownum + 1),
	firstname,
	lastname,
FROM weird_visitors,
(SELECT @rownum:=0) rownum;