MySQL Bugs: #35285: Allow negative auto_increment values to be set

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

Description:
It doesn't seem to be possible to set a negative auto_increment value.

How to repeat:
alter table t auto_increment = -1

Many thanks for writing a feature request.

Please, can you explain which sense should a negative auto_increment should have?

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.

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

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 :)

Forgot to mention that this decrement, would be usefull for inserting on event_submission table.

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;