CODEWITHSUNDEEP
mysqlauto-increment
kiks73
kiks73

Reputation: 3778

MySQL AUTO DECREMENT instead of AUTO INCREMENT, is it possible?

I would like to have negative auto generated keys on a table.

Instead of Keys: 1,2,3,..,X I would like Keys: -1,-2,-3,...,-X

I need this because I have to create a mirror table to use in UNION with the original and 'understand' which records come from the original table and which ones come from the mirror table.

Do you think this is a good idea? Is there a simple way to get negative auto decremented key? Or do I have to use triggers?

Upvotes: 3

Views: 3671

Answers (1)

Bill Karwin
Bill Karwin

Reputation: 562881

Don't do this.

https://dev.mysql.com/doc/refman/5.7/en/innodb-auto-increment-handling.html says:

  • Assigning a negative value to the AUTO_INCREMENT column

    In all lock modes (0, 1, and 2), the behavior of the auto-increment mechanism is not defined if you assign a negative value to the AUTO_INCREMENT column.

In other words, down that path lies chaos.

MySQL supports an option auto_increment_increment with which you can define how much to increment by each time you increment, but the value must be greater than 0.

mysql> set session auto_increment_increment = -1;
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> show warnings;
+---------+------+----------------------------------------------------------+
| Level   | Code | Message                                                  |
+---------+------+----------------------------------------------------------+
| Warning | 1292 | Truncated incorrect auto_increment_increment value: '-1' |
+---------+------+----------------------------------------------------------+

mysql> select @@auto_increment_increment;
+----------------------------+
| @@auto_increment_increment |
+----------------------------+
|                          1 |
+----------------------------+

You explained why you want to do this:

I need this because I have to create a mirror table to use in UNION with the original and 'understand' which records come from the original table and which ones come from the mirror table.

Use another column to indicate which table the rows came from:

SELECT 'original' AS WhichTable, column1, column2, column3 
FROM MyOriginalTable
UNION ALL
SELECT 'mirror', column1, column2, column3 
FROM MyMirrorTable;

Upvotes: 4

Related Questions