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?
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;