How to use 2 auto increment columns in MySQL phpmyadmin

Question

Is it possible to use 2 auto increment values ?

• One starting from 0
• Another starting from 4000400

Please help me

Answer 1

No, you can't. Not out of the box. Possible workarounds:

1. Triggers (an AFTER INSERT trigger).

   Disadvantages:

   • Plain horror. Comes with all the other disadvantages of triggers, like maintenance and debugging nightmares.

   Advantages:

   • You can have FOREIGN KEY constraints that reference this column.
   • You can update the first and the second id columns individually.

2. Views. If you only a value that is always +4000400 of the first auto incremented value, you can use a view, so basically not store this value at all, just calculate it when you need it:

       CREATE VIEW 
           tablex_with_2nd_AI AS
       SELECT 
           tablex_id,
           colA,                                 -- other columns
           --                                    -- you need
           tablex_ix + 4000400  AS second_id
       FROM 
           tablex ;
   

   Disadvantages:

   • You can't have a FOREIGN KEY constraint that references this (virtual) column.

   Advantages:

   • Saves some space. Slightly faster inserts.

3. Don't do that, don't have a second auto incremented column at all. Do you really need a second auto incremented value? What for? A description of the actual problem you are trying to solve would help others help you better. I think you have only told here how you tried to solve a problem and not what the actual problem is.

Answer 2

I have good news and bad news

GOOD NEWS

You can multiple auto_increment values

BAD NEWS

Two things:

1. It is only available for the MyISAM Storage Engine
2. Only one numeric auto_increment value is allowed. Each auto_increment must be have associated column to define uniqueness from other auto_increment values within the same table.

I have discussed this before

• Jun 10, 2012 : MySQL get next unique value without auto increment
• Apr 21, 2012 : How can you have two auto-incremental columns in one table?

Here is a sample table

USE test
DROP TABLE IF EXISTS stores;
CREATE TABLE stores
(
    store_type int not null,
    id int not null auto_increment,
    store_name varchar(128) not null,
    PRIMARY KEY (store_type,id)
) ENGINE=MyISAM;

Here is sample data

INSERT INTO stores (store_type,store_name) VALUES
(1,'Red Lobster'),(1,'Olive Garden'),
(2,'ShopRite'),(2,'PathMark'),(2,'Wegman''s'),
(3,'McDonald''s'),(3,'Wendy''s'),(3,'Burger King'),
(1,'Ruby Tuesdays'),(1,'TGI Fridays'),
(4,'BJs'),(4,'Costco'),(1,'Bennigan''s');

Let's load it

mysql> USE test
Database changed
mysql> DROP TABLE IF EXISTS stores;
Query OK, 0 rows affected (0.00 sec)

mysql> CREATE TABLE stores
    -> (
    ->     store_type int not null,
    ->     id int not null auto_increment,
    ->     store_name varchar(128) not null,
    ->     PRIMARY KEY (store_type,id)
    -> ) ENGINE=MyISAM;
Query OK, 0 rows affected (0.08 sec)

mysql> INSERT INTO stores (store_type,store_name) VALUES
    -> (1,'Red Lobster'),(1,'Olive Garden'),
    -> (2,'ShopRite'),(2,'PathMark'),(2,'Wegman''s'),
    -> (3,'McDonald''s'),(3,'Wendy''s'),(3,'Burger King'),
    -> (1,'Ruby Tuesdays'),(1,'TGI Fridays'),
    -> (4,'BJs'),(4,'Costco'),(1,'Bennigan''s');
Query OK, 13 rows affected (0.00 sec)
Records: 13  Duplicates: 0  Warnings: 0

mysql>

Now, look at the data:

mysql> SELECT * FROM stores;
+------------+----+---------------+
| store_type | id | store_name    |
+------------+----+---------------+
|          1 |  1 | Red Lobster   |
|          1 |  2 | Olive Garden  |
|          2 |  1 | ShopRite      |
|          2 |  2 | PathMark      |
|          2 |  3 | Wegman's      |
|          3 |  1 | McDonald's    |
|          3 |  2 | Wendy's       |
|          3 |  3 | Burger King   |
|          1 |  3 | Ruby Tuesdays |
|          1 |  4 | TGI Fridays   |
|          4 |  1 | BJs           |
|          4 |  2 | Costco        |
|          1 |  5 | Bennigan's    |
+------------+----+---------------+
13 rows in set (0.00 sec)

mysql>

Now, look at the data ordered by the PRIMARY KEY columns

mysql> SELECT * FROM stores ORDER BY store_type,id;
+------------+----+---------------+
| store_type | id | store_name    |
+------------+----+---------------+
|          1 |  1 | Red Lobster   |
|          1 |  2 | Olive Garden  |
|          1 |  3 | Ruby Tuesdays |
|          1 |  4 | TGI Fridays   |
|          1 |  5 | Bennigan's    |
|          2 |  1 | ShopRite      |
|          2 |  2 | PathMark      |
|          2 |  3 | Wegman's      |
|          3 |  1 | McDonald's    |
|          3 |  2 | Wendy's       |
|          3 |  3 | Burger King   |
|          4 |  1 | BJs           |
|          4 |  2 | Costco        |
+------------+----+---------------+
13 rows in set (0.00 sec)

mysql>

Give it a Try !!!

UPDATE 2013-02-26 12:00 EST

Let's augment the SuperMarkets (store_type 2) by add 4000400 to the id values

mysql> UPDATE stores SET id = id + 4000400 WHERE store_type = 2;
Query OK, 3 rows affected (0.02 sec)
Rows matched: 3  Changed: 3  Warnings: 0

mysql> SELECT * FROM stores ORDER BY store_type,id;
+------------+---------+---------------+
| store_type | id      | store_name    |
+------------+---------+---------------+
|          1 |       1 | Red Lobster   |
|          1 |       2 | Olive Garden  |
|          1 |       3 | Ruby Tuesdays |
|          1 |       4 | TGI Fridays   |
|          1 |       5 | Bennigan's    |
|          2 | 4000401 | ShopRite      |
|          2 | 4000402 | PathMark      |
|          2 | 4000403 | Wegman's      |
|          3 |       1 | McDonald's    |
|          3 |       2 | Wendy's       |
|          3 |       3 | Burger King   |
|          4 |       1 | BJs           |
|          4 |       2 | Costco        |
+------------+---------+---------------+
13 rows in set (0.00 sec)

mysql>

Let's add A & P as another SuperMarket

mysql> INSERT INTO stores (store_type,store_name) VALUES (2,'A & P');
Query OK, 1 row affected (0.00 sec)

mysql> SELECT * FROM stores ORDER BY store_type,id;
+------------+---------+---------------+
| store_type | id      | store_name    |
+------------+---------+---------------+
|          1 |       1 | Red Lobster   |
|          1 |       2 | Olive Garden  |
|          1 |       3 | Ruby Tuesdays |
|          1 |       4 | TGI Fridays   |
|          1 |       5 | Bennigan's    |
|          2 | 4000401 | ShopRite      |
|          2 | 4000402 | PathMark      |
|          2 | 4000403 | Wegman's      |
|          2 | 4000404 | A & P         |
|          3 |       1 | McDonald's    |
|          3 |       2 | Wendy's       |
|          3 |       3 | Burger King   |
|          4 |       1 | BJs           |
|          4 |       2 | Costco        |
+------------+---------+---------------+
14 rows in set (0.00 sec)

mysql>

Looks like it can work for you !!!

Answer 3

Have a look at the triggers tab - you should be able to make a trigger to update a 2nd auto increment column based on an insert.

    CREATE TABLE IF NOT EXISTS test2 (
    id          int(11) NOT NULL AUTO_INCREMENT,
    another_id      int(11) ,
    blah        varchar(250),
    PRIMARY KEY (`id`)  
)   ENGINE=MyISAM  DEFAULT CHARSET=latin1 ; 

The trigger should increment the column 'another_id' (which you set to 40004000) when a row is inserted into the table

Answer 4

I just ran into this problem and would like to contribute to the answers. I used Ypercube's answer to solve it, but in a slightly different way. First though,

Why do you need the second auto increment column besides ID?

There are some objects, the quantity of which can be pretty sensitive corporate info. For example, I may not want user_id 37 to suggest that I only have 37 users on my website. Number of claims, number of purchases, number of anything that is good or bad that you don't want users, competitors, or regulators to know.

Yet it is useful, at least in my case, to assign a unique publicly available number to these objects so that I can manage them easier with user input. For example, this question on SE has a number 35449 and I can get it by typing this number into browser: https://dba.stackexchange.com/questions/35449

And finally I don't want to create any complex overhead trying to obscure id's. Auto increment of a number greater than ID will do.

So that's the problem. Ypercube's answer has a pretty cool solution, just add a constant to the id. Only I did it in PHP in my case with an extra query and put it in a separate function so I can easily keep track of it and it doesn't get tightly coupled with the rest of SQL. It is slower but easier to manage as for me.

Note, if trying to lessen appeared quantity of something negative, like complaint_id or refund_id, don't subtract as you may go into negative, just add a huge random number, in billions+ with no consecutive zeroes (e.g. id + 159035466234) that will make it clear that it has nothing to do with quantity of those objects.